M5L2-SQL-NestedSubqueries.pdf

SQL – Nested Subqueries Slides Modified by Dianne Foreback Database System Concepts, 7th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re-use Readings • Section 3.8 – 3.10 in Database System Concepts, 7th Ed. By Silberschatz, Korth and Sudarshan 1 Nested Subqueri...

SQL – Nested Subqueries Slides Modified by Dianne Foreback Database System Concepts, 7th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re-use Readings • Section 3.8 – 3.10 in Database System Concepts, 7th Ed. By Silberschatz, Korth and Sudarshan 1 Nested Subqueries • • SQL provides a mechanism for the nesting of subqueries. A subquery is a select-from-where expression that is nested within another query. The nesting can be done in the following SQL query select A1, A2, ..., An from r1, r2, ..., rm where P as follows: ✓ From clause: ri can be replaced by any valid subquery ✓ Where clause: P can be replaced with an expression of the form: B <operation> (subquery) B is an attribute and <operation> to be defined later. ✓ Select clause: Ai can be replaced be a subquery that generates a single value. 2 SQL Nested Subqueries Topics ▪ Set Membership o in o not in ▪ Set Comparison o some o all o exists ▪ Subqueries in the “FROM” clause o with clause o scalar subquery ▪ Modification to DB with nested subqueries o delete from o insert into o update 3 Set Membership with “IN” select * from section order by year, semester • Find courses offered in Fall 2017 and in Spring 2018: returns “CS-101” select distinct course_id from section where semester = 'Fall' and year= 2017 and course_id in (select course_id from section where semester = 'Spring' and year= 2018); • • room_ time_ course_id sec_id semester year building number slot_ id CS-101 1 Fall 2017 Packard CS-347 1 Fall 2017 Taylor PHY-101 1 Fall 2017 Watson EE-181 1 Spring 2017 Taylor 3128 C CS-190 1 Spring 2017 Taylor 3128 E CS-190 2 Spring 2017 Taylor 3128 A BIO-101 1 Summer 2017 Painter 514 B CS-101 1 Spring 2018 Packard 101 F CS-315 1 Spring 2018 Watson 120 D CS-319 1 Spring 2018 Watson 100 B CS-319 2 Spring 2018 Taylor FIN-201 1 Spring 2018 Packard 101 B HIS-351 1 Spring 2018 Painter 514 C 2018 Packard 101 D 2018 Painter 514 A Is the keyword “distinct” necessary? MU-199 1 Spring BIO-301 1 Summer ▪ Yes. E.g., if CS-101 in section 1 and 2 for Fall of 2017 then the resulting relation would return two tuples Could the query be rewritten with a set “operation”? Should distinct be in this query? (select course_id from section where semester = 'Fall' and year = 2017) intersect (select course_id from section where semester = 'Spring' and year = 2018) 101 H 3128 A 100 A 3128 C 4 Set Membership – “NOT IN” instructor query result name Brandt Califieri Crick El Said Gold Katz Kim Singh Srinivasan Wu • Name all instructors whose name is neither “Mozart” nor Einstein” select distinct name from instructor where name not in ('Mozart', 'Einstein') 5 Set Membership with “NOT IN” example 2 • • Find courses offered in Fall 2017 but not in Spring 2018 What will this return? select distinct course_id from section where semester = 'Fall' and year= 2017 and course_id not in (select course_id from section where semester = 'Spring' and year= 2018); • room_ time_ course_id sec_id semester year building number slot_ id CS-101 1 Fall 2017 Packard CS-347 1 Fall 2017 Taylor PHY-101 1 Fall 2017 Watson EE-181 1 Spring 2017 Taylor 3128 C CS-190 1 Spring 2017 Taylor 3128 E CS-190 2 Spring 2017 Taylor 3128 A BIO-101 1 Summer 2017 Painter 514 B CS-101 1 Spring 2018 Packard 101 F CS-315 1 Spring 2018 Watson 120 D CS-319 1 Spring 2018 Watson 100 B CS-319 2 Spring 2018 Taylor FIN-201 1 Spring 2018 Packard 101 B HIS-351 1 Spring 2018 Painter 514 C MU-199 1 Spring 2018 Packard 101 D BIO-301 1 Summer 2018 Painter 514 A 101 H 3128 A 100 A 3128 C Is the keyword “distinct” necessary? ▪ Yes. E.g. if ‘PHY-101’ were in section 1 and 2 for Fall of 2017 then the resulting relation would return two tuples 6 SQL Nested Subqueries Topics ▪ Set Membership o in o not in ▪ Set Comparison o some o all o exists ▪ Subqueries in the “FROM” clause o with clause o scalar subquery ▪ Modification to DB with nested subqueries o delete from o insert into o update 8 Set Comparison – “some” Clause • Find names of instructors with salary greater than that of some (at least one) instructor in the Biology department. instructor select distinct T.name from instructor as T, instructor as S where T.salary > S.salary and S.dept_name = 'Biology'; • Same query using > some clause select name from instructor where salary > some (select salary result from instructor name where dept_name = 'Comp. Sci.') Brandt Crick order by name; • Can you think of other ways to do this? Einstein Gold Katz Kim Singh Wu 9 Definition of “some” Clause • F <comp> some r   t  r such that (F <comp> t ) Where <comp> can be:    =  0 5 6 ) = true (5 < some 0 5 ) = false (5 = some 0 5 ) = true (5  some 0 5 ) = true (since 0  5) (5 < some (read: 5 < some tuple in the relation) (= some)  in However, ( some)  not in 10 Set Comparison – “all” Clause • Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department. instructor select name from instructor where salary > all (select salary from instructor where dept_name = 'Comp. Sci.') order by name; result name Einstein 11 Definition of “all” Clause • F <comp> all r   t  r (F <comp> t) (5 < all 0 5 6 ) = false (5 < all 6 10 ) = true (5 = all 4 5 ) = false (5  all 4 6 ) = true (since 5  4 and 5  6) ( all)  not in However, (= all)  in 12 Test for Empty Relations • • • The exists construct returns the value true if the argument subquery is nonempty. exists r  r  Ø not exists r  r = Ø 13 Use of “exists” Clause • Yet another way of specifying the query “Find all courses taught in both the Fall 2017 semester and in the Spring 2018 semester” select course_id from section as S where semester = 'Fall' and year = 2017 and exists (select * from section as T where semester = 'Spring' and year= 2018 and S.course_id = T.course_id); • • Correlation name – variable S in the outer query Correlated subquery – the inner query 14 3 Queries with Same Result Find all courses taught in both the Fall 2017 semester and in the Spring 2018 semester” select * from section order by year, semester --exists course_id sec_id semester year building select course_id CS-101 1 Fall 2017 Packard from section as S CS-347 1 Fall 2017 Taylor where semester = 'Fall' and year = 2017 PHY-101 1 Fall 2017 Watson EE-181 1 Spring 2017 Taylor and exists (select * CS-190 1 Spring 2017 Taylor from section as T CS-190 2 Spring 2017 Taylor where semester = 'Spring’ BIO-101 1 Summer 2017 Painter and year= 2018 CS-101 1 Spring 2018 Packard and S.course_id = T.course_id); CS-315 1 Spring 2018 Watson --in CS-319 1 Spring 2018 Watson select distinct course_id CS-319 2 Spring 2018 Taylor from section FIN-201 1 Spring 2018 Packard 1 Spring 2018 Painter where semester = 'Fall' and year= 2017 HIS-351 MU-199 1 Spring 2018 Packard and course_id in BIO-301 1 Summer 2018 Painter (select course_id from section where semester = 'Spring' and year= 2018); --intersect result (select course_id from section course_id where semester = 'Fall' and year = 2017) CS-101 intersect (select course_id from section where semester = 'Spring' and year = 2018) room_ time_ number slot_ id 101 H 3128 A 100 A 3128 C 3128 E 3128 A 514 B 101 F 120 D 100 B 3128 C 101 B 514 C 101 D 514 A 15 Use of “not exists” Clause • Find all students who have taken all courses offered in the Biology department. all courses offered by “Biology” select distinct S.ID, S.name from student as S where not exists ( (select course_id from course except where dept_name = 'Biology') except (select T.course_id all courses a student took from takes as T where S.ID = T.ID)); We want the empty set! • First nested query lists all courses offered in Biology • Second nested query lists all courses a particular student took • • Note that X –Y = Ø  X Y Note: Cannot write this query using = all and its variants 16 SQL Nested Subqueries Topics ▪ Set Membership o in o not in ▪ Set Comparison o some o all o exists ▪ Subqueries in the “FROM” clause o with clause o scalar subquery ▪ Modification to DB with nested subqueries o delete from o insert into o update 17 Subqueries in the From Clause • • • SQL allows a subquery expression to be used in the from clause Find the average instructors’ salaries of those departments where the average salary is greater than $42,000.” select dept_name, Note that we do not need to use the having clause select dept_name, avg_salary from --create a temp relation (select dept_name, avg (salary) from instructor group by dept_name) --names temp relation dept_avg with 2 attr --with names of dept_name and avg_salary as dept_avg (dept_name, avg_salary) where avg_salary > 42000; dept_name avg_salary Biology 72000 Comp. Sci. 77333 Elec. Eng. 80000 Finance 85000 History 61000 Physics 91000 avg(salary) as avg_salary from instructor group by dept_name dept_name avg_salary Biology 72000 Comp. Sci. 77333 Elec. Eng. 80000 Finance 85000 History 61000 Music 40000 Physics 91000 18 With Clause • • The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs. Find all departments with the maximum budget with tempMaxBudgetRelation (max_budget) as (select max(budget) from department) select department.dept_name from department, tempMaxBudgetRelation where department.budget = tempMaxBudgetRelation.max_budget; 19 With Clause • • The with clause provides a way of defining a temporary relation whose definition is available only to the query in which the with clause occurs. Find all departments with the maximum budget with tempMaxBudgetRelation (max_budget) as tempMaxBudgetRelation (select max(budget) max_budget from department) 120000 select department.dept_name from department, tempMaxBudgetRelation where department.budget = tempMaxBudgetRelation.max_budget; can use the temp relation in the query 20 Complex Queries using With Clause • Find all departments where the total salary is greater than the average of the total salary at all departments with dept_total (dept_name, dept_tot_salary) as (select dept_name, sum(salary) from instructor group by dept_name), dept_total_avg(avg_tot_salary) as (select avg(dept_tot_salary) from dept_total) select dept_name from dept_total, dept_total_avg where dept_total.dept_tot_salary > dept_total_avg.avg_tot_salary; 21 Complex Queries using With Clause • Find all departments where the total salary is greater than the average of the total salary at all departments with dept_total (dept_name, dept_tot_salary) as (select dept_name, sum(salary) from instructor group by dept_name), dept_total_avg(avg_tot_salary) as (select avg(dept_tot_salary) from dept_total) select dept_name from dept_total, dept_total_avg where dept_total.dept_tot_salary > dept_total_avg.avg_tot_salary; dept_total dept_name dept_tot_salary Biology 72000 Comp. Sci. 232000 Elec. Eng. 80000 Finance 170000 History 122000 Music 40000 Physics 182000 avg_tot_salary 74833 dept_name Comp. Sci. Finance Physics 22 Scalar Subquery • • • Scalar subquery is one which is used where a single value is expected Runtime error if subquery returns more than one result tuple List all departments along with the number of instructors in each department dept_name num_instructors select dept_name, Biology 1 ( select count(*) Comp. Sci. 3 from instructor Elec. Eng. 1 where department.dept_name Finance 2 = instructor.dept_name) History 2 as num_instructors Music 1 from department Physics 2 order by dept_name; -- the above query demo scalar in a subquery but it could be -- rewritten as follows providing name cannot be null select dept_name, count(name) as num_instructors from instructor group by dept_name order by dept_name; 23 SQL Nested Subqueries Topics ▪ Set Membership o in o not in ▪ Set Comparison o some o all o exists ▪ Subqueries in the “FROM” clause o with clause o scalar subquery ▪ Modification to DB with nested subqueries o delete from o insert into o update 24 Deletion • Delete all instructors delete from instructor; • Delete all instructors from the Finance department delete from instructor where dept_name= 'Finance’; • Delete all tuples in the instructor relation for those instructors associated with a department located in the Watson building. delete from instructor where dept name in (select dept name from department where building = 'Watson'); CAUTION! Before issuing a delete, test with its corresponding select statement. 25 Insertion • • Add a new tuple to course insert into course values ('CS-437', 'Database Systems', 'Comp. Sci.', 4); or equivalently insert into course (course_id, title, dept_name, credits) values ('CS-437', 'Database Systems', 'Comp. Sci.', 4); • Add a new tuple to student with tot_creds set to null insert into student values ('3003', 'Green', 'Finance', null); 26 Insertion (Cont.) • Make each student in the Music department who has earned more than 144 credit hours an instructor in the Music department with a salary of $18,000. insert into instructor select ID, name, dept_name, 18000 from student where dept_name = 'Music' and total_cred > 144; • The select from where statement is evaluated fully before any of its results are inserted into the relation. ▪ Otherwise queries like the following would cause problem insert into table1 select * from table1 27 Updates • Give a 5% salary raise to all instructors update instructor set salary = salary * 1.05 • Give a 5% salary raise to those instructors who earn less than 70000 update instructor set salary = salary * 1.05 where salary < 70000; • Give a 5% salary raise to instructors whose salary is less than average update instructor set salary = salary * 1.05 where salary < (select avg (salary) from instructor); 28 Updates (Cont.) • Increase salaries of instructors whose salary is over $100,000 by 3%, and all others by a 5% ▪ Write two update statements: update instructor set salary = salary * 1.03 where salary > 100000; update instructor set salary = salary * 1.05 where salary <= 100000; • The order is important ▪ Can be done better using the case statement (next slide) 29 Case Statement for Conditional Updates • Same query as before but with case statement update instructor set salary = case when salary <= 100000 then salary * 1.05 else salary * 1.03 end 30 Study Guide • Try the queries in this presentation to gain a better understanding. • Understand how to formulate queries with the set membership of "in" and "not in" • Understand how to formulate queries with the set comparison of "some", "all" and "exists" • Subqueries can exist in the "select", "from" and the "where" clause - how to formulate these queries and evaluate • What does the "with clause" do • Be able to modify the database with nested subqueires using "delete from", "insert into" and "update" • Practice Exercises and Solutions to practice exercises are available https://www.db-book.com/Practice-Exercises/index-solu.html for the following problems: 3.1d,e,f,g 3.3b,c 3.4b 3.5a,b 3.8a 3.9b,d,f,g 3.10a,b • You can test your queries online at https://www.db-book.com/universitylab-dir/sqljs.html 32 Thank You ! Questions ? 33