1 / 9

Database Refactor: Complex Sub-Queries

Learn complex database sub-query techniques for finding student names, pairs of IDs with the same name, and more using EXISTS and NOT EXISTS.

jcindy
Download Presentation

Database Refactor: Complex Sub-Queries

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 8Database Redesign We only discuss complex sub-queries

  2. Referring to itself • Find the student names used by more than one student • First try Select name From S Group By Name Having count(*) > 1 • Second Try Select s.name Form S, S as S2 Where s.name = s2.name and s.sid <> s2.SID

  3. Referring to itself (2) • Find the pair of SIDs using the same name Select S.Name, S.SID, S2.SID Form S, S as S2 Where s.name = s2.name and s.sid < s2.SID Note: you cannot use subquery or “Group By” to do this one

  4. EXISTS and NOT EXISTS • EXISTS and NOT EXISTS are specialized forms of subqueries • An EXISTS condition is true if any row in the subquery meets the specified conditions • A NOT EXISTS condition is true only if all rows in the subquery do not meet the specified condition • Double use of NOT EXISTS can be used to find rows that have some specified condition to every row of a table

  5. Example: EXISTS • List the names of students who take at least one class SELECT Name FROM S WHERE EXISTS (SELECT * FROM E WHERE SID =StudentNumber ); • What is the difference? SELECT Name FROM S WHERE SID in (SELECT StudentNumber FROM E );

  6. Example: NOT EXISTS • List the names of students who take no class SELECT Name FROM S WHERE NOT EXISTS (SELECT * FROM E WHERE SID =StudentNumber ); • What is the difference? SELECT Name FROM S WHERE SID NOT in (SELECT StudentNumber FROM E );

  7. Example: NOT EXISTS -- ALL • List the names of students who take all classes SELECT Name FROM S WHERE SID in (SELECT StudentNumber FROM E group by student number Having count(*) = (select count(*) from C) );

  8. Example: NOT EXISTS – ALL (2) • List the names of students who take all classes • List the names of students for whom there does not exist a class the students are not taking SELECT Name FROM S WHERE Not Exists (SELECT * FROM C where not exists (select * from E where c.name = className and Sid = studentnumber );

  9. Example: NOT EXISTS – ALL (3) • List the names of students who take all classes taking by PAKERs SELECT Name FROM S WHERE Not Exists (SELECT * FROM E as E2 where studentnumber in (select SID from S where S.name = ‘Paker’) and not exists (select * from E where E2.Classname = E.className and Sid = studentnumber );

More Related