1 / 16

SQL Subqueries and Basic Operations

This chapter introduces SQL subqueries and basic operations such as data definition, query structure, set operations, null values, aggregate functions, and nested subqueries. It also covers database modification with nested subqueries.

brewerd
Download Presentation

SQL Subqueries and Basic Operations

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Chapter 3Introduction to SQL(4) 1st Semester, 2017 Sanghyun Park

  2. Outline • History • Data Definition • Basic Query Structure • Additional Basic Operations • Set Operations • Null Values • Aggregate Functions • Nested Subqueries • Database Modification

  3. 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, …, Anfrom r1, r2, .., rmwhere P • Ai can be replaced by a subquery that generates a single value • ri can be replaced by any valid subquery • P can be replaced with an expression of the form: B <operation> (subquery)where B is an attribute and <operation> will be defined later

  4. Subqueries in Where Clause • A common use of subqueries is to perform tests: • For set membership • For set comparison • For set cardinality

  5. Set Membership • Find courses offered both in Fall 2009 and in Spring 2010selectdistinct course_idfrom sectionwhere semester = ‘Fall’ and year = 2009 and course_id in (select course_id from section where semester = ‘Spring’ and year = 2010); • Find courses offered in Fall 2009 but not in Spring 2010selectdistinct course_idfrom sectionwhere semester = ‘Fall’ and year = 2009 and course_id not in (select course_id from section where semester = ‘Spring’ and year = 2010);

  6. Set Comparison – “some” Clause • Find names of instructors with salary greater than that of some (at least one) instructor in the Biology departmentselectdistinct T.namefrom instructor as T, instructor as Swhere T.salary > S.salary and S.dept_name = ‘Biology’ • Same query using > some clauseselect namefrom instructorwhere salary > some (select salary from instructor where dept_name = ‘Biology’);

  7. Set Comparison – “all” Clause • Find names of instructors whose salary is greater than the salary of all instructors in the Biology departmentselect namefrom instructorwhere salary > all (select salary from instructor where dept_name = ‘Biology’);

  8. Test for Empty Relations • The exists construct returns the value true if the argument subquery is nonempty • exists r  r ≠ Ø • not exists r  r = Ø

  9. Use of “exists” Clause • Yet another way of specifying the query “Find all courses taught both in Fall 2009 and in Spring 2010”select course_idfrom section as Swhere semester = ‘Fall’ and year = 2009 andexists (select * from section as T where semester = ‘Spring’ and year = 2010and S.course_id = T.course_id);

  10. Use of “not exists” Clause • Find all students who have taken all courses offered in the Biology department selectdistinct S.ID, S.namefrom student as Swherenotexists ( (select course_id from course where dept_name = ‘Biology’ )except (select T.course_idfrom takes as Twhere S.ID = T.ID ) );

  11. Test for Absence of Duplicate Tuples • The unique construct tests whether a subquery has any duplicate tuples in its result • The unique construct evaluates to “true” if a given subquery contains no duplicates • Find all courses that were offered at most once in 2009 select T.course_idfrom course as Twhereunique (select R.course_id from section as R where T.course_id = R.course_id and R.year = 2009 );

  12. Subqueries in From Clause • 생략 (textbook 참조)

  13. Subqueries in Select Clause • 생략 (textbook 참조)

  14. Database Modification - Delete • Delete all instructors from the Finance dept:delete from instructorwheredept_name = ‘Finance’ • Delete all instructors whose salary is less than the average salary of instructors:delete from instructorwhere salary< (select avg (salary)from instructor)

  15. Database Modification - Insert • Add a new tuple to course: insert into coursevalues (‘CS-437’, ‘Database Systems’, ‘Comp. Sci’, 4) or equivalentlyinsert into course(course_id, title, credits, dept_name)values (‘CS-437’, ‘Database Systems’, 4, ‘Comp. Sci’) • Add all instructors to the student relation with tot_creds set to 0:insert into studentselect ID, name, dept_name, 0from instructor

  16. Database Modification - Update • Increase salaries of instructors whose salary is over $100,000 by 3% and all others receive 5% raise: update instructorset salary = salary  1.03where salary > 100000; update instructorset salary = salary  1.05where salary  100000; • The order is important • Can be done better using the case statementupdate instructorset salary =casewhen salary  100000 then salary *1.05else salary * 1.03end;

More Related