Sub Queries

-------------sub queries------------------sql> select max(salary) from employees; max(salary) ----------24000 sql> select * from emp where salary=(select max(salary) from employees); select * from emp where salary=(select max(salary) from employees) * error at line 1: ora-00942: table or view does not exist sql> ed wrote file afiedt.buf 1* select * from employees where salary=(select max(salary) from employees) sql> / employee_id first_name last_name ----------- -------------------- ------------------------email phone_number hire_date job_id ------------------------- -------------------- --------- ---------- ---------commission_pct manager_id department_id -------------- ---------- ------------100 steven king sking 515.123.4567 17-jun-87 ad_pres 90

salary

24000

sql> select a.first_name,b.department_name from employees a,departments b where a.salary=(select max (salary) from employees); first_name department_name -------------------- -----------------------------steven administration steven marketing steven purchasing steven human resources steven shipping steven it steven public relations steven sales steven executive steven finance steven accounting first_name department_name -------------------- ------------------------------

steven steven steven steven steven steven steven steven steven steven steven

treasury corporate tax control and credit shareholder services benefits manufacturing construction contracting operations it support noc

first_name department_name -------------------- -----------------------------steven it helpdesk steven government sales steven retail sales steven recruiting steven payroll 27 rows selected. sql> ed wrote file afiedt.buf 1* select a.first_name,b.department_name from employees a,departments b where a.salary=(select max sql> / first_name department_name -------------------- -----------------------------steven executive sql> sql> ed wrote file afiedt.buf 1 select a.first_name,b.department_name from employees a,departments b--------wat tables 2 conn------------2 where a.salary=(select max(salary) from employees)---------------wat condition-----------------3* and a.department_id=b.department_id-----------this s the link--------sql> / first_name department_name -------------------- -----------------------------steven executive

------------------any,all operators--------------------------------

sql> select * from emp where sal in (select sal from emp where deptno=10); empno ename job mgr hiredate sal comm deptno ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7934 miller clerk 7782 23-jan-82 1300 10 7782 clark manager 7839 09-jun-81 2450 10 7839 king president 17-nov-81 5000 10 sql> select * from emp where sal=1300 or sal=5000; empno ename job mgr hiredate sal comm deptno ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7839 king president 17-nov-81 5000 10 7934 miller clerk 7782 23-jan-82 1300 10 sql> select * from emp where sal >any (select sal from emp where deptno=10); empno ename job mgr hiredate sal comm deptno ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7839 king president 17-nov-81 5000 10 7698 blake manager 7839 01-may-81 2850 30 7782 clark manager 7839 09-jun-81 2450 10 7566 jones manager 7839 02-apr-81 2975 20 7499 allen salesman 7698 20-feb-81 1600 300 30 7844 turner salesman 7698 08-sep-81 1500 0 30 7902 ford analyst 7566 03-dec-81 3000 20 7788 scott analyst 7566 09-dec-82 3000 20 8 rows selected. sql> select * from emp where sal >all (select sal from emp where deptno=10); no rows selected sql> select * from emp where sal =any (select sal from emp where deptno=10); empno ename job mgr hiredate sal comm deptno ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7934 miller clerk 7782 23-jan-82 1300 10 7782 clark manager 7839 09-jun-81 2450 10 7839 king president 17-nov-81 5000 10 sql> select * from emp where sal =all (select sal from emp where deptno=10);\ 2 sql> select * from emp where sal =all (select sal from emp where deptno=10) 2 sql> / no rows selected

1.------------------->any=>min------------------------greater the min values of deptno=10---------sql> select * from emp where sal >any(select sal from emp where deptno=10);

empno ename job mgr hiredate sal comm deptno ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7839 king president 17-nov-81 5000 10 7698 blake manager 7839 01-may-81 2850 30 7782 clark manager 7839 09-jun-81 2450 10 7566 jones manager 7839 02-apr-81 2975 20 7499 allen salesman 7698 20-feb-81 1600 300 30 7844 turner salesman 7698 08-sep-81 1500 0 30 7902 ford analyst 7566 03-dec-81 3000 20 7788 scott analyst 7566 09-dec-82 3000 20 8 rows selected.

2.----------------->all=>max----------------------greater than max value of deptno=10-----------sql> select * from emp where sal >all(select sal from emp where deptno=10); no rows selected

3.----------------- select * from emp where sal
4.--------------- select * from emp where sal
comm

deptno 1400

7900 james 7521 ward 30 7369 smith 7876 adams

clerk salesman clerk clerk

7698 03-dec-81 7698 22-feb-81

950 1250

7902 17-dec-80 7788 12-jan-83

800 1100

30 500 20 20

5.---------------------=any = in------------------------shows the values of deptno=10----------------sql rel="nofollow"> select * from emp where sal in(select sal from emp where deptno=10); empno ename job mgr hiredate sal comm deptno ---------- ---------- --------- ---------- --------- ---------- ---------- ---------7934 miller clerk 7782 23-jan-82 1300 10 7782 clark manager 7839 09-jun-81 2450 10 7839 king president 17-nov-81 5000 10