Classroom Session 6 (Advance Queries - Nested) - SELECT distinct FROM WHERE GROUP BY HAVING ORDER BY Advance Queries 1 Nested Queries(Sub Queries 2

Classroom Session 6 (Advance Queries - Nested) - SELECT...

This preview shows page 1 - 3 out of 6 pages.

SELECT distinct FROM WHERE GROUP BY HAVING ORDER BY ----------------------------------------------------------- Advance Queries ------------------- 1. Nested Queries (Sub Queries) 2. Correlated Queries 3. Joins 4. Hierarchical Queries 5. Set operators EMP EMPNO ENAME SAL DEPTNO --------------------------- 7788 Scott 800 10 8765 Allen 1200 10 9898 Smith 1200 20 -- 1200 DEPT DEPTNO DNAME LOC --------------------------- 10 SALES PUNE 20 MARKETING MUMBAI 30 HR PUNE 40 ENG PUNE 1. Nested Queries (Sub Queries) --------------------------------------------------------- Nested queries contain multiple queries within one command. Q : Find details of employees working for SALES department SELECT * FROM emp where deptno=? (SELECt deptno FROM dept WHERE dname='SALES') In nested we have 1. One or more inner query : It will feed or provide to result outer query. Inner query are executed first 2. Single outer query : Will consume result from inner query Sub queries or nested mostly used when predicate(WHERE condition) value is not know to user eg: Display list of employees working in same department as of SCOTT SELECT * FROM emp WHERE deptno=(SELECT deptno FROM emp WHERE ename='SCOTT') SELECT * FROM emp WHERE deptno=10 Nested queries can be categorized as 1. Single row sub queries : If inner query return one record
Image of page 1
2. Multiple rows sub queries : If inner query return multiple recordsSELECT * FROM empWHERE deptno=(SELECT deptno -- Single row subqueryFROM empWHERE ename='SCOTT')SELECT * FROM empWHERE deptno IN (SELECT deptno -- Multiple rows subqueryFROM empWHERE ename='SCOTT')Display list of employees working for same department as of SCOTT and earning more than scottdeptno? scott
Image of page 2
Image of page 3

You've reached the end of your free preview.

Want to read all 6 pages?

  • Summer '11
  • UNknown
  • Column, Query language, emp, Display list

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture