NESTED QUERIES AND SUB QUERIES
EX.NO:2
SQL>
create table employee(empno number(10)primary key,empname
varchar(20),designation varchar(20),manager number(4),joindate date,salary number(7),deptno
number(2),age number(2));
Table created.
SQL>
create table department(deptno number(3)primary key,deptname varchar(20),location
varchar(20));
Table created.
SQL>
alter table employee add constraint dpfk1 foreign key(deptno)references
department(deptno);
Table altered.
SQL>
alter table employee add constraint dpfk1 foreign key(deptno)references
department(deptno);
Table altered.
SQL>
insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 1
Enter value for deptname: Managing
Enter value for location: Mumbai
old 1: insert into department
values(&deptno,'&deptname','&location')
new 1: insert into department
values(1,'Managing','Mumbai')
1 row created.
SQL>
insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 2
Enter value for deptname: Invigilence
Enter value for location: Delhi
old 1: insert into department
values(&deptno,'&deptname','&location')
new 1: insert into department
values(2,'Invigilence','Delhi')
1 row created.
SQL>
insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 3
Enter value for deptname: Purchase
Enter value for location: Chennai
old 1: insert into department
values(&deptno,'&deptname','&location')
new 1: insert into department
values(3,'Purchase','Chennai')
1 row created.
SQL>
insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 4
Enter value for deptname: Distribution
Enter value for location: Madurai
old 1: insert into department
values(&deptno,'&deptname','&location')
new 1: insert into department
values(4,'Distribution','Madurai')
1 row created.
SQL>
insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 5
Enter value for deptname: Stack Incharge
Enter value for location: Kolkatta
old 1: insert into department
values(&deptno,'&deptname','&location')
new 1: insert into department
values(5,'Stack Incharge','Kolkatta')
1 row created.
SQL>
insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 6
Enter value for deptname: Supervision
Enter value for location: Bangalore
old 1: insert into department
values(&deptno,'&deptname','&location')
new 1: insert into department
values(6,'Supervision','Bangalore')
1 row created.
SQL>
insert into department values(&deptno,'&deptname','&location');
Enter value for deptno: 7
Enter value for deptname: Production
Enter value for location: Jharkand
old 1: insert into department
values(&deptno,'&deptname','&location')
new 1: insert into department
values(7,'Production','Jharkand')
1 row created.
SQL>
insert into employee values(&empno,'&empname','&designation',&managerid,
'&joindate',&salary,&deptno,&age);
Enter value for empno: 101
Enter value for empname: Kumar
Enter value for designation: Manager
Enter value for managerid: 0
Enter value for joindate: 21-FEB-1998
Enter value for salary: 8500
Enter value for deptno: 1
Enter value for age: 36
old 1: insert into employee
values(&empno,'&empname','&designation',&managerid,'&joindate',&salary
new 1: insert into employee
values(101,'Kumar','Manager',0,'21-FEB-1998',8500,1,36)
1 row created.
SQL>
insert into employee values(&empno,'&empname','&designation',&managerid,
'&joindate', &salary,&deptno,&age);
Enter value for empno: 7902
Enter value for empname: Santhosh
Enter value for designation: Manager
Enter value for managerid: 0
Enter value for joindate: 09-APR-2003
Enter value for salary: 180000
Enter value for deptno: 1
Enter value for age: 27
old 1: insert into employee
values(&empno,'&empname','&designation',&managerid,'&joindate',&salary
new 1: insert into employee
values(7902,'Santhosh','Manager',0,'09-APR-2003',180000,1,27)
1 row created.
SQL>
insert into employee values(&empno,'&empname','&designation',&managerid,
'&joindate', &salary,&deptno,&age);
Enter value for empno: 7789
Enter value for empname: Ganesh
Enter value for designation: Assistant
Enter value for managerid: 7902
Enter value for joindate: 09-MAY-2003
Enter value for salary: 18000
Enter value for deptno: 1
Enter value for age: 24
old 1: insert into employee
values(&empno,'&empname','&designation',&managerid,'&joindate',&salary
new 1: insert into employee values(7789,'Ganesh','Assistant',7902,'09-MAY-2003',18000,1,24)
1 row created.
SQL>
insert into employee values(&empno,'&empname','&designation',&managerid,
'&joindate', &salary,&deptno,&age);
Enter value for empno: 4526
Enter value for empname: Varun
Enter value for designation: Clerk
Enter value for managerid: 101
Enter value for joindate: 10-SEP-2005
Enter value for salary: 5600
Enter value for deptno: 5
Enter value for age: 38
old 1: insert into employee
values(&empno,'&empname','&designation',&managerid,'&joindate',&salary
new 1: insert into employee
values(4526,'Varun','Clerk',101,'10-SEP-2005',5600,5,38)
1 row created.
SQL>
insert into employee values(&empno,'&empname','&designation',&managerid,
'&joindate', &salary,&deptno,&age);
Enter value for empno: 5268
Enter value for empname: Vikram
Enter value for designation: Dealer
Enter value for managerid: 101
Enter value for joindate: 11-OCT-2007
Enter value for salary: 6200
Enter value for deptno: 4
Enter value for age: 42
old 1: insert into employee
values(&empno,'&empname','&designation',&managerid,'&joindate',&salary
new 1: insert into employee
values(5268,'Vikram','Dealer',101,'11-OCT-2007',6200,4,42)
1 row created.
SQL>
insert into employee values(&empno,'&empname','&designation',&managerid,
'&joindate', &salary,&deptno,&age);
Enter value for empno: 7566
Enter value for empname: Manoj
Enter value for designation: Manager
Enter value for managerid: 0
Enter value for joindate: 25-NOV-2006
Enter value for salary: 156000
Enter value for deptno: 1
Enter value for age: 29
old 1: insert into employee
values(&empno,'&empname','&designation',&managerid,'&joindate',&salary
new 1: insert into employee
values(7566,'Manoj','Manager',0,'25-NOV-2006',156000,1,29)
1 row created.
SQL> insert into
employee values(&empno,'&empname','&designation',&managerid, '&joindate',
&salary,&deptno,&age);
Enter value for empno: 8521
Enter value for empname: Tharun
Enter value for designation: Invigilator
Enter value for managerid: 7566
Enter value for joindate: 18-FEB-2003
Enter value for salary: 210000
Enter value for deptno: 2
Enter value for age: 48
old 1: insert into employee
values(&empno,'&empname','&designation',&managerid,'&joindate',&salary
new 1: insert into employee
values(8521,'Tharun','Invigilator',7566,'18-FEB-2003',210000,2,48)
1 row created.
SQL> select * from department;
SQL> select * from department;
DEPTNO
DEPTNAME
LOCATION
---------- --------------------
--------------------
1 Managing
Mumbai
2 Invigilence Delhi
3
Purchase
Chennai
4 Distribution Madurai
5 Stack Incharge Kolkatta
6 Supervision Bangalore
7 Production
Jharkand
7 rows selected.
SQL>
select * from employee;
EMPNO
EMPNAME
DESIGNATION
MANAGERID JOINDATE
----------
-------------------- -------------------- ---------- ---------
SALARY
DEPTNO AGE
----------
---------- ----------
101
Kumar
Manager
0 21-FEB-988500 1 36
7902 Santhosh Manager 0 09-APR-03
180000 1 27
7789 Ganesh Assistant 7902 09-MAY-03
18000 1 24
EMPNO EMPNAME DESIGNATION MANAGERID JOINDATE
----------
-------------------- -------------------- ---------- ---------
SALARY
DEPTNO AGE
----------
---------- ----------
4526
Varun
Clerk
101 10-SEP-055600 5 38
5268 Vikram Dealer 101 11-OCT-07
6200 4 42
7566 Manoj Manager 0 25-NOV-06
156000 1 29
EMPNO EMPNAME DESIGNATION MANAGERID JOINDATE
----------
-------------------- -------------------- ---------- ---------
SALARY
DEPTNO AGE
----------
---------- ----------
8521
Tharun
Invigilator
7566 18-FEB-03210000 2 48
7 rows selected.
SQL>
select empname,salary from employee where salary between 1500 and 3500;
no rows selected
SQL>
select empname,managerid from employee where managerid
in(7902,7566,7789);
EMPNAME
MANAGERID
--------------------
----------
Ganesh
7902
Tharun
7566
SQL>
select empname from employee where empname LIKE 'J%' or empname LIKE
'T%';
EMPNAME
--------------------
Tharun
SQL>
select distinct designation from employee;
DESIGNATION
--------------------
Assistant
Clerk
Dealer
Invigilator
Manager
SQL>
select empname,salary,(salary+0.15*salary) from employee;
EMPNAME
SALARY (SALARY+0.15*SALARY)
-------------------- ----------
--------------------
Kumar
8500
9775
Santhosh
180000
207000
Ganesh
18000
20700
Varun
5600
6440
Vikram
6200
7130
Manoj
156000
179400
Tharun
210000
241500
7 rows selected.
SQL>
select count(distinct designation) from employee;
COUNT(DISTINCTDESIGNATION)
--------------------------
5
SQL>
select max(salary)-min(salary) from employee;
MAX(SALARY)-MIN(SALARY)
-----------------------
204400
SQL>
select empname,salary from employee where designation LIKE 'M%' and
salary>(select min(salary)from employee);
EMPNAME
SALARY
--------------------
----------
Kumar
8500
Santhosh
180000
Manoj
156000
SQL>
select empname,salary from employee where salary in(select min(salary)from
employee group by managerid);
EMPNAME
SALARY
--------------------
----------
Varun
5600
Kumar
8500
Ganesh
18000
Tharun
210000
SQL>
select deptname,count(empname)from employee,department where
employee.deptno=department.deptno group by deptname;
DEPTNAME
COUNT(EMPNAME)
--------------------
--------------
Distribution
1
Invigilence
1
Managing
4
Stack
Incharge
1
SQL>
select distinct deptno,empname from employee where designation='Manager' and
extract(year from joindate)>2000;
DEPTNO EMPNAME
----------
--------------------
1 Manoj
1 Santhosh
No comments:
Post a Comment