Subqueries - Subqueries Chapter 6 Objectives After...

This preview shows page 1 - 12 out of 51 pages.

The preview shows page 11 - 12 out of 51 pages.
SubqueriesChapter 6ObjectivesAfter completing this lesson, you should be able to do thefollovving:Describe the types of problems thatsubqueries can solveDefine subqueriesList the types of subqueriesWrite single-row and multiple-rowsubqueries1
Using a Subquery to Solve a Problem"Who has a salary greater than Jones'?""Which employees have a salary greaterthan Jones' salary?""What is Jones' salary?"2
SubqueriesSELECTselect_listFROMtableWHEREexpr operator( SELECTselect_ListFROMtable);The subquery (inner query) executesonce before the main query.The result of the subquery is used bythe main query (outerquery).A subquery is a SELECT statement that is embedded in a clause of anotherSELECT statement. You can build powerful statements out of simple ones byusing subqueries. They can be very useful when you need to select rows from atable with a condition that depends on the data in the table itself.You can place the subquery in a number of SQL clauses•WHERE clauss•HAVING clauseFROM clauseln the syntax;operatorincludes a comparison operatör such as>,=, or INNote:Comparison operutors fall iııto two classes: single-row operators( > , = ,>= ,< ,< > ,<= )and multiple-row operators ( IN , ANY , ALL ).3
Using a SubquerySELECT enameFROM EMPWHEREsal>( SELECT salFROM empWHERE empno=7566);ENAMEFORDSCOTTKINGFORD4
Using a SubquerySELECT ename, sal, deptno, jobFROM EMPWHERE job =( SELECT jobFROM empWHERE empno=7369);ENAMESALDEPTNOJOBADAMS110020 CLERKJAMES95030 CLERKMILLER130010 CLERKSMITH80020 CLERKADAMS110020 CLERKJAMES95030 CLERKMILLER130010 CLERK7 rows selected.5
SELECT ename, sal, deptnoFROM EMPWHERE sal IN( SELECT MIN(sal)FROM empGROUP BY deptno );ENAMESALDEPTNOJAMES95030SMITH80020MILLER1300106
SELECT empno, ename, jobFROM empWHERE sal < ANY( SELECT salFROM empWHERE job = 'CLERK' );EMPNOENAMEJOB7369 SMITHCLERK7900 JAMESCLERK7876 ADAMSCLERK7521 WARDSALESMAN7654 MARTINSALESMAN7
SELECT empno, ename, jobFROM empWHERE sal < ANY( SELECT salFROM empWHERE job = 'CLERK' )AND job <> 'CLERK' ;EMPNOENAMEJOB7521 WARDSALESMAN7654 MARTINSALESMAN8
SELECT empno, ename, jobFROM empWHERE sal > ALL( SELECT salFROM empWHERE job = 'CLERK' )AND job <> 'CLERK' ;EMPNOENAMEJOB7499 ALLENSALESMAN7566 JONESMANAGER7698 BLAKEMANAGER7782 CLARKMANAGER7788 SCOTTANALYST7839 KINGPRESIDENT7844 TURNERSALESMAN7902 FORDANALYST8 rows selected.9
SELECT empno, ename, jobFROM empWHERE sal > ALL( SELECT AVG(sal)FROM empGROUP BY deptno ) ;EMPNOENAMEJOB7566 JONESMANAGER7788 SCOTTANALYST7839 KINGPRESIDENT7902 FORDANALYST10
Guidelines for Using SubqueriesEnclose subqueries in parentheses.Place subqueries on the right side of thecomparison operator.Do not add an ORDER BY clause to asubquery.Use single-row operators with single-row subqueries.Use multiple-row operators withmultiple-row subqueries.

Upload your study docs or become a

Course Hero member to access this document

Upload your study docs or become a

Course Hero member to access this document

End of preview. Want to read all 51 pages?

Upload your study docs or become a

Course Hero member to access this document

Term
Spring
Professor
Bailey
Tags
Oracle Database, Sal, emp, Ename

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture