1 / 23

More SQL Nested and Union queries, and more

iSQLplus: http://uadisq01.uad.ac.uk:5560/isqlplus/. More SQL Nested and Union queries, and more. Reading: For example: Connolly/Begg (4th Ed) : 5.3.5 / 5.3.6 / 5.3.8 / 5.3.9 Any SQL book!!!. For most queries, we use the same sample data as before: Personnel, Branch, Transport tables.

adah
Télécharger la présentation

More SQL Nested and Union queries, and more

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. iSQLplus: http://uadisq01.uad.ac.uk:5560/isqlplus/ More SQLNested and Union queries, and more Reading: For example: Connolly/Begg (4th Ed) : 5.3.5 / 5.3.6 / 5.3.8 / 5.3.9 Any SQL book!!! For most queries, we use the same sample data as before: Personnel, Branch, Transport tables.

  2. Match up the set operations, diagrams and SQL words XY XY XY YX X X Y Y X X Y Y a. b. c. d. i. UNION ii. MINUS iii. INTERSECT iv. EXCEPT

  3. Set operations in SQL • Set operations are union (), intersect (), set difference () • Oracle SQL has corresponding operators UNION, INTERSECT, MINUS(standard SQL: EXCEPT) • use these to “glue together” two SELECT statements with union-compatible results • Can combine several using brackets like in maths • by default deletes duplicates - use UNION ALL to keep them in

  4. Reader Example {optional, and} Staff Student Library readers • each reader can be staff, student, both, or neither • Stored in three relations: • Reader(readerNo, name, address)contains all readers. staff also have a record in Staff, students in Student • Staff(readerNo*, department, email) • Student(readerNo*,matricNo, course, email)

  5. No semi-colon at end of first SELECT. Why? • Reader(readerNo, name, address)Staff(readerNo, department, email) • Student(readerNo,matricNo, course, email) Example: INTERSECT • Find all readers who are both staff and student • SQL: SELECT ReaderNoFROM StaffINTERSECT SELECT ReaderNoFROM Student;

  6. Reader(readerNo, name, address)Staff(readerNo, department, email) • Student(readerNo,matricNo, course, email) Example: MINUS • Find all readers who are not students • SQL: SELECT ReaderNo FROM Reader MINUSSELECT ReaderNo FROM Student; • How could you change this to get the names as well?

  7. Reminder: Basic SQL • SELECT <attributes> • May include aliases and aggregate functions • FROM <Tables> • List Tables and may use Join operations • WHERE <condition> • Use logical expressions and may Join tables • GROUP BY <aggregate statistics> • HAVING <aggregate condition> • ORDER BY <sorting attributes>

  8. Using IS NULL • To find missing/empty values use IS NULL • To exclude them use IS NOT NULL • Useful in LEFT / RIGHT / FULL JOINS to find values that don't have a match SELECT * FROM personnel WHERE bonus IS NULL; SELECT * FROM personnel WHERE bonus IS NOT NULL; Query 2-1.sql Query 2-2.sql Note: NULL is different from 0!!!!!

  9. Reminder Example “Find staff outside London who don't have a company car” SELECT surname, city FROM personnel p LEFT JOIN transport t ON p.snum=t.snum, branch b WHERE city <> 'LONDON' AND t.snum IS NULL AND p.div = b.div; Query 2-3.sql • Hints: • How many tables are required by the query? • Remember to join the tables

  10. Nested Queries • Queries can be used within other queries • usually in WHERE clause • can also be in • HAVING clause • or used in calculated columns in SELECT • expression in WHERE compares value with a query result • uses math operators =, >, <, >=, <=, <> and keywords ALL, ANY, IN, NOT IN, EXISTS, NOT EXISTS

  11. Danger! This only works if the subquery returns a single value! Example 1 • Find cars whose mileage is less than the Jaguar's. • Two steps: • Find mileage of the Jaguar • Compare other mileages with that one • Nested query (subquery) does all of this. Query 2-4.sql SELECT Regno, make FROM transport WHERE mileage < (SELECT mileage FROM transport WHERE make='JAGUAR');

  12. Example 2 • Find staff with lower than average salaries, and show how much lower they are SELECT snum, surname, salary-(SELECT AVG(salary)FROM personnel) AS difference FROM Personnel p WHERE salary < (SELECT AVG(salary) FROM personnel); Query 2-5.sql Note: need the subqueries because aggregate functions are not allowed in WHERE and cannot be mixed with non-aggregates in SELECT

  13. Nested Queries : Operators • If sub-query can return more than one result use an operator in the comparison (IN, NOT IN, ANY, ALL, EXISTS, NOT EXISTS) • IN • compares a value with a set and evaluates to True if the value is in the set and False otherwise • set can be a query result or a simple set, e.g. WHERE town IN ('Dundee', 'Perth', 'Aberdeen') • NOT IN

  14. Nested Queries: more operators • ANY • at least one result of the subquery must fulfil the condition • ALL • all results of the subquery must fulfil the comparison • EXISTS • returns True if there is at least one tuple in the subquery and False otherwise • NOT EXISTS • checks for empty sub-query

  15. Nested Examples: IN, ANY • Find all staff working in London SELECT snum, surname FROM personnel WHERE div IN (SELECT div FROM branch WHERE city='LONDON'); • or SELECT snum, surname FROM personnel WHERE div = ANY (SELECT div FROM branch WHERE city='LONDON'); Query 2-6.sql Query 2-7.sql

  16. Find staff earning more than those in branch 30 SELECT * FROM personnel WHERE div <> 30 and salary > (SELECT salary FROM personnel WHERE div=30); A C B SELECT * FROM personnel WHERE div != 30 and salary > ANY (SELECT salary FROM personnel WHERE div=30); SELECT * FROM personnel WHERE div <> 30 and salary > ALL (SELECT salary FROM personnel WHERE div=30); D Both A and C are correct Query 2-8.sql

  17. Nested Examples: ALL • branches which don’t have a director SELECT * FROM branch WHERE div <> ALL (SELECT div FROM personnel WHERE jobtitle = 'DIRECTOR'); Query 2-9.sql Note: no space between < and >

  18. How did this work? Subquery produces the result Outer Query takes this result and produces a list of branches which are none of these i.e. 20 10 30 40 50

  19. EXISTS / NOT EXISTS • branches which have no staff SELECT * FROM branch WHERE NOT EXISTS (SELECT * FROM personnel p WHERE p.div = branch.div); Query 2-10.sql Remember to specify join criterion

  20. How did it work? • DBMS evaluates sub-query separately for each value of p.div • If sub-query result is empty, NOT EXISTS is true,so this branch is in the result of the main query • If p.div=branch.div is omitted, sub-query is evaluated for all branches at once, sub-result not empty, so main query returns nothing

  21. Which of these queries also find branches with no staff? SELECT * FROM branch MINUS SELECT div FROM personnel; Query 2-11a.sql A SELECT b.* FROM personnel p RIGHT JOIN branch b ON p.div=b.div WHERE b.div IS NULL; B Query 2-11b.sql SELECT * FROM branch WHERE div NOT IN (SELECT div FROM personnel); C Query 2-11c.sql D Both B and C are correct but A is not E All queries are correct

  22. Reader(readerNo, name, address)Staff(readerNo, department, email) • Student(readerNo,matricNo, course, email) Back to the start • Find all readers who are not students • SQL: SELECT ReaderNo FROM Reader MINUSSELECT ReaderNo FROM Student; • So now, write another query that will find the readerno and name of all readers who are not students.

  23. Summary • Basic SQL SELECT • Basic query facilities • SELECT FROM WHERE GROUP BY HAVING ORDER BY • IS NULL / IS NOT NULL • Advanced SQL SELECT • Nested Queries • ALL, ANY, EXISTS, NOT EXISTS, IN, NOT IN • Set operators • UNION, INTERSECT, MINUS

More Related