DDL – subqueryObjectivesWhat is a subquery?When is a subquery needed?Which query will be evaluated first?What kind of results do queries return?Which clauses does a subquery can appear in?How to write a subquery?Creating Nested QueriesSlide 10Nested Subquery ExampleAliases & In/SubqueriesSlide 13Slide 14Correlated subqueryAn correlated nested querySlide 17Subquery and joinSlide 19SQL - Subquery InsertsThe only limitation is one’s imagination.1DDL – subquerySen Zhang2Objectives•What is a subquery? •Learn how to create nested SQL queries•Read sample scripts and book for different kinds of subquery.3What is a subquery?•A subquery is a standard select query that is nested within a select, update, insert, or delete command.•Used to select results based on the result of a query•A subquery can even contain subqueries within itself, i.e. subqueries can be nested to more than one level (nested subqueries). Theoretically speaking, you can nest subqueries to an infinite number of levels of depth.4When is a subquery needed?•When a certain information that is not directly available from any physical table must be answered using a select statement before a larger question can be addressed, we need a subquery.•For example, to write a select statement to find out whose salary is above the average, you must first find out what “average salary” is, which itself requires a select statement already.5Which query will be evaluated first?•Inner subquery will be evaluated first, then outer query.6What kind of results do queries return?•The result of a subquery could be –a table of one row and one column, thus a scalar value,–a table of one single column, thus a list of item–a table of a single row of one single record, thus a tuple –a table of multiple columns and multiple records, thus a relation7Which clauses does a subquery can appear in?•Depending on the evaluating result of a subquery, the subquery can be used to provide data for different clauses –From–Where–Having8How to write a subquery?•put a select statement in a pair of parenthesis.•It can be used as a value, a list, or a table generated on the fly9Creating Nested Queries•Consists of a main query and one or more subqueries.–Main query: first query that appears in the SELECT command–Subquery retrieves values that the main query’s search condition must match10Creating Nested Queries•Being aware of what will be returned by a nested query is very important.•Nested queries can return single or multiple values–To match single values use = operator–To match multiple values use IN operator11Nested Subquery Example12Aliases & In/Subqueries•Let us find out average salary of each individual department •SELECT avg(salary), dno from employee group by dno;•This gives: AVG(SALARY) DNO----------- ---------- 53766.75 1 88447 4 101508.5 513•Notice that, in the previous slide, the “min(salary)" appears the Select lines as the column header on the report. •Avg(salary) may not be a good field name used by outerquery, therefore we can assign alias name to avg(salary) as following•SELECT avg(salary) avgsalary, dno from employee group by dno;•This gives: AVGSALARY DNO---------- ---------- 53766.75 1 88447 4 101508.5 514select lname, fname, t1.salary, ssn, t1.dno from employee t1,(select min(salary) avgsalary, dno from employee group by dno) t2where t1.dno=t2.dno andt1.salary=t2.avgsalary;15Correlated subquery•A correlated subquery is a subquery that uses values from the outer query. •With a correlated subquery, the database must run the subquery for each evaluation because it is based on the outer query’s data.•The outer query knows nothing about the inner query except its results. For that reason, the outer query cannot reference any columns in the subquery. However, the subquery has access to the outer query and can reference outer query columns, thus the correlated subquery.16An correlated nested query•Retrieve the name of each employee who has a dependent with the same first name and same sex as the employee.•Select e.fname, e.lnamefrom employee ewhere e.ssn in (select essnfrom dependent where e.fname=dependent_nameand e.sex=sex);17•In this example, the subquery references the sex in the outer query. The value of the sex changes by row of the outer query, so the database must rerun the subquery for each row comparison. •This has a significant performance impact on the execution time of the query, and for that reason, correlated subqueries should be avoided if possible.18Subquery and join•In general, a query written with nested select-from-where blocks and using the = or IN comparsion operators can always be expressed as a single block query. •For example, the previous correlated query can be rewritten as –Select e.fname, e.lnameFrom employee e, dependent dwhere e.ssn=d.essn and e.sex=d.sex ande.fname=d.dependent_name;19•Nested subqueries are slower than joins and should be used sparingly, so if you can use join to solve the problem, probably you do not want to use subquery solution.•A nested query may or may not have a equivalent flat joining query solution.20SQL - Subquery Inserts•Subqueries can be used to pull old data from your database and insert it into new tables. (We have homework problem for it.)•For instance if we opened up a third store and we wanted to place the same manager over 3 stores we could do this by pulling the manager's information using a subquery and then inserting the records. Also note that this form of insert will insert all cases where the subquery is true, therefore several rows may or may not be inserted depending upon how your table is set up.•SQL Code:–INSERT INTO employees3 (id,Lastname,Firstname,Title) (SELECT id,Lastname,Firstname,Title FROM employees WHERE Title='manager');21The only limitation is one’s imagination.•With complete mastery of a subqueries you can now see the power of the SQL language. •The SQL language is capable of nearly all things imaginable.•Please read the sample script discussed in class and read book for more query