NESTED QUERIES AND SUB QUERIES Ex:2 [DBMS Lab]

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; 

    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-98
            8500          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-05
            5600          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-03
            210000          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