Subquery Concepts Chapter 8 Subqueries

1 1 Chapter 8 Subqueries Subquery Concepts A SELECT statement nested within the FROM, WHERE, or HAVING clause of another SQL statement The Subquery (inner query) executes once, beforethe main query The resultof the Subquery is passed to the main query (outer query)2 SELECT select_listFROM tableWHERE expr operator(SELECT select_listFROM table); Subquery Concepts Subqueries are an alternative to running a seriesof queries where the result of one query is the input for another subqueriesare useful when a query is based on unknown values Incremental Development what work should the subquerydo? Which writers have same freelancer status as K Cox?3 SELECT ln, fn, freelancerFROM writerWHERE freelancer ='Y';SELECT freelancerFROM writerWHERE writerid ='C200';Simple Subquery Which writers are the same freelancer status as Kelly Cox?

2 The displayed columns come from the _____ query4(SELECT freelancerFROM writerWHERE writerid ='C200')'Y'SELECT ln, fn, freelancerFROM writerWHERE freelancer=LN FN F------------------------------ -------------------- -Cox Kelly YEpstein Diane YJohnson Leroy W. YKim Chong YNilsson Tonya YSeeger Wilhelm Y2 Simple Subquery Practice Warm Up Show the name, salary and department number of each employee who earns less than the average * FROM ;Types of Scalar subqueryMain querySubqueryreturnsBUSLAW Multiple-row subqueryMain querySubqueryreturnsBUS Multiple-column subqueryMain querySubqueryreturnsGuidelines for Using Subqueries Enclose a Subquery in parentheses Place a Subquery to the right of a comparison operator Do not use an ORDER BY clause within a Subquery except in an inline view Use a single-row operatorwith a single-row Subquery = < > <> Use a multiple-row operatorwith a multi-row Subquery IN ANY/SOME ALL7IN Operator ( ) Condition is True if field s value equals any of the values in a comma separated list83 SubqueriesThat Return Multiple Rows Use a multi-row operator to handle results of a multi-row Subquery (IN, ANY/SOME, ALL)

3 What are the names of the writers who serve as contacts? Note that IN()auto distinctsand suppresses nulls9 SubqueriesThat Return Multiple Rows Use a multi-row operator to handle results of a multi-row Subquery (IN, ANY/SOME, ALL) What are the names of the writers who serve as contacts?10 SubqueriesThat Return Multiple Rows Use a multi-row operator to handle results of a multi-row Subquery (IN, ANY/SOME, ALL) Which students aren t enrolled in any section?11 Multi-Row Subquery Practice Which writers have written a BUS article? Show last name, first name and phone and sort the results by Multiple Subqueries Can nest one Subquery within another Subquery The innermost Subquery is executed first, its results are passed to next level up note: 3 no joins13 Nesting Multiple SubqueriesPractice What are the names of the instructors who have taught Java courses?

4 Incremental development14 SubqueriesThat Return Multiple Columns Use parentheses in the WHERE clause to designate the columns the number of columns and column order must match and data types must be compatible For each section, show the student with the highest project grade (pg329) what work does the subqueryperform?15 Subqueriesand Nulls Nulls are troublesome when they occur in a NOT IN Subquery when any value in the list is Null, the condition evaluates to unknownand no rows are returned! consider using a correlated subquerywith NOT EXISTS165 Correlated SubqueryConcepts Used when the subqueryneeds a value from each rowprocessed by the outer query the subqueryis executed once for each rowin the outer query the inner query refers to column(s) from a table in the outer query Show name, salary, and dept number of each employee who earns less than their department s average salary incremental development: we need each department's average salary17 Although serviceable, this correlated Subquery is inefficientsince it calculates each department s average salary for each employee in the Subquery Example Which sections have an enrollment that exceeds the capacity of the section?

5 (pg 349+) Note: columns from the outer queryare displayed18correlatedsubquerysolutionEqu ijoin Solution Which sections have an enrollment that exceeds the capacity of the section? An equijoin (inner join) can return a column from any of the joined tables with a Subquery approach, only columns from the outer query are displayed19equijoinsolutionEXISTS Operator Used to check whether a correlatedsubqueryreturns at least one row returns True if the subqueryreturns at least one row returns False if the subquerydoesn t return any rows Column(s) cited in the Subquery s SELECT are irrelevant Show the description of each type of article present in the article table An alternative to an inner join when you don t need to display columns from the other table20 SELECT descrFROM type tWHERE EXISTS(SELECT 'X'FROM article aWHERE = )

6 6 NOT EXISTS Operator Used to check whether a correlatedsubqueryreturns no rows returns True if the subquerydoesn t return any rows returns False if the subqueryreturns at least one row Column(s) cited in the Subquery s SELECT are irrelevant Show the description of each type of article not present in the article table NOT EXISTS correctly handles NOT INdoesn t21 SELECT descrFROM type tWHERE NOT EXISTS(SELECT 'X'FROM article aWHERE = )Inline View Concepts A Subquery in the FROM clause Used to break complex problems into simpler parts incremental development Creates a virtual table results are calculated on-the-flyand the result setis held in memory while statement is processed is not a persistent schema object (like a base tableis) Can have multiple inline views embedded within an SQL statement An inline view can have an alias that you use to qualify a reference any of its columns22 Lab View Example #1 Show each writer s name and number of articles written incremental development23 SELECT || ', ' || "Author", "Articles Written"FROM writer w,(SELECT writerid, COUNT(*) writer_artFROM articleGROUP BY writerid)aWHERE = BY DESC Notice the inline view (a) is explicitly joined to the writer table (w)Inline View Example #2 Show each writer s name, number of articles written, and their percentage of the total articles View Example #2 Show each writer s name, number of articles written, and their percentage of the total articles written.

7 Although inline view ais explicitly joined to the writer table w, inline view bis not joined to anor w what happens?25 SELECT || ', ' || "Author", "Articles Written",ROUND(100*( ),2) "% of Articles"FROM writer w,(SELECT writerid, COUNT(*) writer_artFROM articleGROUP BY writerid) a,(SELECT COUNT(*) total_artFROM article) bWHERE = BY DESC;Inline View Practice Show the name and salary of each employee who earns less than their department s average salary incremental development Enhancement: Also display the amount each person s salary falls below their department s average salary26 ROWNUM Pseudocolumn Assigns a sequential value to each row returned How are the results different when you add an ORDER BY? Query Concepts By default, SELECT queries return every row meeting the specified criteria (WHERE or HAVING) Top-N queries return only those rows which contain the nlargest (or smallest) column values eg: What are the 5 longestarticles?

8 Structure of a Top-N Query The inline viewselects and sorts the rows The outer query uses ROWNUMto limit the rows to the top-N28 SELECT [* | column_list] FROM (SELECT column_listFROM tableORDER BY Top-N_column)WHERE ROWNUM <= N8 Top-N Query Example What are the 5 shortestarticles? What does *represent in the outer query?29 Top-N Practice Show the 4 most heavily used locations ( , the locations that are used by the most sections). Modify the previous query to show only the 2 most heavily used locations. ROWNUM mechanism doesn t detect ties at the cutoff point!30 Top-N Query using RANK Use the RANK function to handle ties at the cut-off point31 ANY/SOME Operators Compares a value to each value in a list Condition is True if it is satisfied by anyof the values produced by the Subquery will return False when the Subquery returns no rows Must be preceded by =, !

9 =, >, <, <=, >= = ANY is equivalent to IN < ANY means less than the maximum > ANY means more than the minimum Which articles were written before anyof W. Seeger s articles?32 Lab Operator Compares a value to each value in a list The condition is True only if it is satisfied by everyvalue produced by the Subquery will return True if the Subquery returns no rows Must be preceded by =, !=, >, <, <=, >= > ALL means more than the maximum < ALL mean less than the minimum Which articles are longer than allof the articles by W Seeger?33