Cơ sở dữ liệu - Chapter 9: Advanced query formulation with sql

Chapter 9Advanced Query Formulation with SQLOutline Outer join problemsType I nested queriesType II nested queries and difference problemsNested queries in the FROM clauseDivision problemsNull value effectsOuter Join OverviewJoin excludes non matching rowsPreserving non matching rows is important in some business situations Outer join variationsFull outer joinOne-sided outer join Right Outer JoinOuter Join OperatorsLeft Outer JoinJoinMatched rows using the join conditionUnmatched rows of the left tableUnmatched rows of the right tableFull outer joinFull Outer Join Example University Database LEFT JOIN and RIGHT JOIN KeywordsExample 1 (Access) SELECT OfferNo, CourseNo, Offering.FacSSN, FacFirstName, FacLastName FROM Offering LEFT JOIN Faculty ON Offering.FacSSN = Faculty.FacSSN WHERE CourseNo LIKE 'IS*' Example 2 (Oracle) SELECT OfferNo, CourseNo, Offering.FacSSN, FacFirstName, FacLastName FROM Faculty RIGHT JOIN Offering ON Offering.FacSSN = Faculty.FacSSN WHERE CourseNo LIKE 'IS%' Full Outer Join Example IExample 3 (SQL:2003 and Oracle 9i/10g) SELECT FacSSN, FacFirstName, FacLastName, FacSalary, StdSSN, StdFirstName, StdLastName, StdGPA FROM Faculty FULL JOIN Student ON Student.StdSSN = Faculty.FacSSN Full Outer Join Example IIExample 4 (Access) SELECT FacSSN, FacFirstName, FacLastName, FacSalary, StdSSN, StdFirstName, StdLastName, StdGPA FROM Faculty RIGHT JOIN Student ON Student.StdSSN = Faculty.FacSSN UNION SELECT FacSSN, FacFirstName, FacLastName, FacSalary, StdSSN, StdFirstName, StdLastName, StdGPA FROM Faculty LEFT JOIN Student ON Student.StdSSN = Faculty.FacSSN Mixing Inner and Outer Joins IExample 5 (Access) SELECT OfferNo, Offering.CourseNo, OffTerm, CrsDesc, Faculty.FacSSN, FacLastName FROM ( Faculty RIGHT JOIN Offering ON Offering.FacSSN = Faculty.FacSSN ) INNER JOIN Course ON Course.CourseNo = Offering.CourseNo WHERE Course.CourseNo LIKE 'IS*'Type I Nested QueriesQuery inside a queryUse in WHERE and HAVING conditionsSimilar to a nested procedureExecutes one timeNo reference to outer queryAlso known as non-correlated or independent nested query Type I Nested Query Examples IExample 6 (Access): List finance faculty who teach IS courses. SELECT FacSSN, FacLastName, FacDept FROM Faculty WHERE FacDept = 'FIN' AND FacSSN IN ( SELECT FacSSN FROM Offering WHERE CourseNo LIKE 'IS*' ) Type I Nested Query Examples IIExample 7 (Oracle): List finance faculty who teach 4 unit IS courses.SELECT FacSSN, FacLastName, FacDept FROM Faculty WHERE FacDept = 'FIN' AND FacSSN IN ( SELECT FacSSN FROM Offering WHERE CourseNo LIKE 'IS%' AND CourseNo IN ( SELECT CourseNo FROM Course WHERE CrsUnits = 4 ) ) DELETE ExampleUse Type I nested queries to test conditions on other tablesUse for UPDATE statements alsoExample 8: Delete offerings taught by Leonard Vince. DELETE FROM Offering WHERE Offering.FacSSN IN ( SELECT FacSSN FROM Faculty WHERE FacFirstName = 'Leonard' AND FacLastName = 'Vince' )Type II Nested QueriesSimilar to nested loopsExecutes one time for each row of outer queryReference to outer queryAlso known as correlated or variably nested queryUse for difference problems not joins Type II Nested Query Example for a Difference ProblemExample 9: Retrieve MS faculty who are not teaching in winter 2006. SELECT FacSSN, FacLastName, FacDept FROM Faculty WHERE FacDept = 'MS' AND NOT EXISTS ( SELECT * FROM Offering WHERE OffTerm = 'WINTER' AND OffYear = 2006 AND Faculty.FacSSN = Offering.FacSSN )Limited Formulations for Difference ProblemsType I nested query with NOT IN conditionOne-sided outer join with IS NULL conditionDifference operation using MINUS (EXCEPT) operator Type I Difference FormulationExample 10: Retrieve MS faculty who are not teaching in winter 2006. SELECT FacSSN, FacLastName, FacDept FROM Faculty WHERE FacDept = 'MS' AND FacSSN NOT IN ( SELECT FacSSN FROM Offering WHERE OffTerm = 'WINTER' AND OffYear = 2006 ) One-Sided Outer Join Difference FormulationExample 11: Retrieve MS faculty who have never taught a course (research faculty). SELECT FacSSN, FacLastName, FacDept FROM Faculty LEFT JOIN Offering ON Faculty.FacSSN = Offering.FacSSN WHERE FacDept = 'MS' AND Offering.FacSSN IS NULL MINUS Operator Difference FormulationExample 12 (Oracle): Retrieve faculty who are not studentsSELECT FacSSN AS SSN, FacFirstName AS FirstName, FacLastName AS LastName, FacCity AS City, FacState AS State FROM Faculty MINUSSELECT StdSSN AS SSN, StdFirstName AS FirstName, StdLastName AS LastName, StdCity AS City, StdState AS State FROM StudentNested Queries in the FROM ClauseMore recent introduction than nested queries in the WHERE and HAVING clausesConsistency in language designWherever table appears, table expression can appearSpecialized usesNested aggregatesMultiple independent aggregate calculations Nested FROM Query ExampleExample 13: Retrieve the course number, course description, the number of offerings, and the average enrollment across offering. SELECT T.CourseNo, T.CrsDesc, COUNT(*) AS NumOfferings, Avg(T.EnrollCount) AS AvgEnroll FROM (SELECT Course.CourseNo, CrsDesc, Offering.OfferNo, COUNT(*) AS EnrollCount FROM Offering, Enrollment, Course WHERE Offering.OfferNo = Enrollment.OfferNo AND Course.CourseNo = Offering.CourseNo GROUP BY Course.CourseNo, CrsDesc, Offering.OfferNo) T GROUP BY T.CourseNo, T.CrsDescDivide OperatorMatch on a subset of valuesSuppliers who supply all partsFaculty who teach every IS courseSpecialized operatorTypically applied to associative tables representing M-N relationships Division Example COUNT Method for Division ProblemsCompare the number of rows associated with a group to the total number in the subset of interest Type I nested query in the HAVING clauseExample 14: List the students who belong to all clubs. SELECT StdNo FROM StdClub GROUP BY StdNo HAVING COUNT(*) = ( SELECT COUNT(*) FROM Club ) Typical Division ProblemsCompare to an interesting subset rather than entire table Use similar conditions in outer and nested queryExample 15: List the students who belong to all social clubs. SELECT Student1.StdNo, SName FROM StdClub, Club, Student1 WHERE StdClub.ClubNo = Club.ClubNo AND Student1.StdNo = StdClub.StdNo AND CPurpose = 'SOCIAL' GROUP BY Student1.StdNo, SName HAVING COUNT(*) = ( SELECT COUNT(*) FROM Club WHERE CPurpose = 'SOCIAL' )Advanced Division ProblemsCount distinct values rather than rowsFaculty who teach at least one section of selected course offeringsOffering table has duplicate CourseNo valuesUse COUNT(DISTINCT column)Use stored query or nested FROM query in Access Advanced Division Problem ExampleExample 16: List the SSN and the name of faculty who teach at least one section of all of the fall 2005, IS courses.SELECT Faculty.FacSSN, FacFirstName, FacLastName FROM Faculty, Offering WHERE Faculty.FacSSN = Offering.FacSSN AND OffTerm = 'FALL' AND CourseNo LIKE 'IS%' AND OffYear = 2005 GROUP BY Faculty.FacSSN, FacFirstName, FacLastName HAVING COUNT(DISTINCT CourseNo) = ( SELECT COUNT(DISTINCT CourseNo) FROM Offering WHERE OffTerm = 'FALL' AND OffYear = 2005 AND CourseNo LIKE 'IS%' )Null Value EffectsSimple conditionsCompound conditionsGrouping and aggregate functionsSQL:2003 standard but implementation may varySimple ConditionsSimple condition is null if either left-hand or right-hand side is null.Discard rows evaluating to false or nullRetain rows evaluating to trueRows evaluating to null will not appear in the result of the simple condition or its negation Compound ConditionsAggregate FunctionsNull values ignoredEffects can be subtleCOUNT(*) may differ from Count(Column)SUM(Column1) + SUM(Column2) may differ from SUM(Column1 + Column2) Grouping EffectsRows with null values are grouped togetherGrouping column contains null valuesNull group can be placed at beginning or end of the non-null groupsSummaryAdvanced matching problems not common but important when necessaryUnderstand outer join, difference, and division operatorsNested queries important for advanced matching problemsLots of practice to master query formulation and SQL Oracle 8i Notation for One-Sided Outer JoinsExample A1 (Oracle 8i) SELECT OfferNo, CourseNo, Offering.FacSSN, FacFirstName, FacLastName FROM Faculty, Offering WHERE Offering.FacSSN = Faculty.FacSSN (+) AND CourseNo LIKE 'IS%'Example A2 (Oracle 8i) SELECT OfferNo, CourseNo, Offering.FacSSN, FacFirstName, FacLastName FROM Faculty, Offering WHERE Faculty.FacSSN (+) = Offering.FacSSN AND CourseNo LIKE 'IS%' Full Outer Join Example IIIExample A3 (Oracle 8i) SELECT FacSSN, FacFirstName, FacLastName, FacSalary, StdSSN, StdFirstName, StdLastName, StdGPA FROM Faculty, Student WHERE Student.StdSSN = Faculty.FacSSN (+) UNION SELECT FacSSN, FacFirstName, FacLastName, FacSalary, StdSSN, StdFirstName, StdLastName, StdGPA FROM Faculty, Student WHERE Student.StdSSN (+) = Faculty.FacSSN Mixing Inner and Outer Joins IIExample A4 (Oracle 8i) SELECT OfferNo, Offering.CourseNo, OffTerm, CrsDesc, Faculty.FacSSN, FacLastName FROM Faculty, Course, Offering WHERE Offering.FacSSN = Faculty.FacSSN (+) AND Course.CourseNo = Offering.CourseNo AND Course.CourseNo LIKE 'IS%'