Oracle Practice Sheet 1
Oracle sheet 1
Overview
- Â
Practice Sheet 1
Create the table by using the above data and Solve the following Queries.
To show information about the teachers of the history department.
To list the names of teachers earning a salary between 20000 and 30000.
To count the number of male teachers.
Display gender wise total number of teachers.
To list the name and age of teachers of female teachers in descending order of date of join.
Increase the salary by 10% for Maths departments.
To delete the record of teacher Neeraj.
1. How to create table :-
Solution :-Â
create table teacher
(
tid varchar2(100) primary key,
Name varchar2(100),
Age number,
Dept varchar2(100),
DOJ date,
Sal number,
gender varchar(10)
);
TRY YOURSELF
2. DESCRIBE THE TABLE.Â
Solution :-Â
DESC teacher;Â
TRY YOURSELF
3. How to insert data into table Teacher.
Solution :-Â
insert into teacher values(‘T118′,’Navin’,40,’Computer’,’10-jan-2010′,12000,’M’);
insert into teacher values(‘T107′,’Chetna’,37,’History’,’24-Mar-2008′,20000,’F’);
insert into teacher values(‘T105′,’Sandeep’,46,’Maths’,’12-DEC-2006′,30000,’M’);
insert into teacher values(‘T110′,’Sangeeta’,35,’History’,’01-JUL-2010′,25000,’F’);
insert into teacher values(‘T101′,’Rudransh’,42,’Maths’,’05-SEP-2004′,40000,’M’);
insert into teacher values(‘T121′,’Neeraj’,38,’Physics’,’01-APR-2011′,28000,’M’);
TRY YOURSELF
4. To show information about the teachers of the history department.
Solution :-Â
select * from teacher where dept=’History’;
TRY YOURSELF
5. To list the names of teachers earning a salary between 20000 and 30000.
Solution :-Â
select name from teacher where sal between 20000 and 30000;
TRY YOURSELF
6. To count the number of male teachers.
Solution :-Â
select count(gender) from teacher where gender=’M’;
TRY YOURSELF
7. Display gender wise total number of teachers.
Solution :-Â
select gender , count(gender) from teacher group by gender;
TRY YOURSELF
8. To list the name and age of teachers of female teachers in descending order of date of join.
Solution :-Â
select name,age from teacher where gender =’F’ order by doj desc;Â
TRY YOURSELF
9. Increase the salary by 10% for Maths departments.
Solution :-Â
update teacher set sal = sal + (10*sal)/100 where dept=’Maths’;
TRY YOURSELF
10. To delete the record of teacher Neeraj.
Solution :-Â
delete from teacher where Name=’Neeraj’;
