SQL Sub-Queries- Tutorial using Oracle
Create an employee table with the following records
SQL> select * From employee;
Output
ID NAME DESIGNATION SALARY DEP_NO
---------- -------------------- -------------------- ---------- ----------
111 rahul Associate 10000 421
112 ram Associate 40000 421
113 tom Assistant 5000 422
114 jeff Assistant 2000 422
115 mohan professor 50000 422
116 venkat professor 70000 422
6 rows selected.
Create a Department Table with the following records
SQL> select * from department;
Output
DNO DNAME
---------- ----------
421 research
422 sbst
Create a project table with the following records
SQL> select * from project;
Output
PID DEPNO
---------- ----------
1 421
2 422
Get the details of employees who get a salary greater than the salary of tom
SQL> select id,name,salary from employee where salary > (select salary from employee where name='tom');
Output
ID NAME SALARY
---------- -------------------- ----------
111 rahul 10000
112 ram 40000
115 mohan 50000
116 venkat 70000
Find all the employees who are getting the minimum salary from the employee table
SQL> select id,name,salary from employee where salary = (select min(salary) from employee);
Output
ID NAME SALARY
---------- -------------------- ----------
114 jeff 2000
select id name and salary for employees who are working for a department that is not present in
the department table
SQL> select id,name,salary from employee where dep_no not in (select dno from department);
Output
ID NAME SALARY
---------- -------------------- ----------
118 chris 85000
117 mathew 90000
select id name and salary for employees who are working for a department that is present in
the department table
SQL> select id,name,salary from employee where dep_no in (select dno from department);
Output
ID NAME SALARY
---------- -------------------- ----------
111 rahul 10000
112 ram 40000
113 tom 5000
114 jeff 2000
115 mohan 50000
116 venkat 70000
6 rows selected.
Display employees who work for a department that has a project
SQL> select id,name,dep_no from employee where dep_no in (Select dno from department where dno in (select depno from project));
Output
ID NAME DEP_NO
---------- -------------------- ----------
111 rahul 421
112 ram 421
113 tom 422
114 jeff 422
115 mohan 422
116 venkat 422
6 rows selected.
Fetch all employees whose salary is greater than all employees of department 421
SQL> select id,name,salary from employee where salary > all (select salary from employee where dep_no=421);
Output
ID NAME SALARY
---------- -------------------- ----------
115 mohan 50000
116 venkat 70000
118 chris 85000
117 mathew 90000
Fetch all employees whose salary is greater than any employees of department 421
SQL> select id,name,salary from employee where salary > any (select salary from employee where dep_no=421);
Output
ID NAME SALARY
---------- -------------------- ----------
117 mathew 90000
118 chris 85000
116 venkat 70000
115 mohan 50000
112 ram 40000
Display the employee id and name of the employee who is getting the highest salary in the
department by name research
SQL> select id,name from employee where salary = (select max(salary) from employee,department where dep_no = dno and dname='research' group by dname);
Output
ID NAME
---------- --------------------
112 ram