PowerPoint PresentationChapter 5 OutlineMore Complex SQL Retrieval QueriesComparisons Involving NULL and Three-Valued LogicComparisons Involving NULL and Three-Valued Logic (cont’d.)Slide 6Nested Queries, Tuples, and Set/Multiset ComparisonsNested Queries (cont’d.)Slide 9Slide 10Slide 11Correlated Nested QueriesThe EXISTS and UNIQUE Functions in SQLExplicit Sets and Renaming of Attributes in SQLJoined Tables in SQL and Outer JoinsJoined Tables in SQL and Outer Joins (cont’d.)Slide 17Aggregate Functions in SQLAggregate Functions in SQL (cont’d.)Grouping: The GROUP BY and HAVING ClausesResults of GROUP BYResults of GROUP BY and HAVINGSlide 23Grouping: The GROUP BY and HAVING Clauses (cont’d.)Discussion and Summary of SQL QueriesSpecifying Constraints as Assertions and Actions as TriggersSpecifying General Constraints as Assertions in SQLIntroduction to Triggers in SQLViews (Virtual Tables) in SQLSpecification of Views in SQLSpecification of Views in SQL (cont’d.)View Implementation, View Update, and Inline ViewsView ImplementationView Implementation (cont’d.)View Update and Inline ViewsView Update and Inline Views (cont’d.)Schema Change Statements in SQLThe DROP CommandThe ALTER CommandThe ALTER Command (cont’d.)SummaryCopyright © 2011 Pearson Education, Inc. Publishing as Pearson Addison-WesleyChapter 5More SQL: Complex Queries, Triggers, Views, and Schema ModificationCopyright © 2011 Ramez Elmasri and Shamkant NavatheChapter 5 OutlineMore Complex SQL Retrieval QueriesSpecifying Constraints as Assertions and Actions as TriggersViews (Virtual Tables) in SQLSchema Change Statements in SQLCopyright © 2011 Ramez Elmasri and Shamkant NavatheMore Complex SQL Retrieval QueriesAdditional features allow users to specify more complex retrievals from database:Nested queries, joined tables, outer joins, aggregate functions, and groupingCopyright © 2011 Ramez Elmasri and Shamkant NavatheComparisons Involving NULLand Three-Valued LogicMeanings of NULLUnknown valueUnavailable or withheld valueNot applicable attributeEach individual NULL value considered to be different from every other NULL valueSQL uses a three-valued logic:TRUE, FALSE, and UNKNOWNCopyright © 2011 Ramez Elmasri and Shamkant NavatheComparisons Involving NULLand Three-Valued Logic (cont’d.)Copyright © 2011 Ramez Elmasri and Shamkant NavatheComparisons Involving NULLand Three-Valued Logic (cont’d.)SQL allows queries that check whether an attribute value is NULLIS or IS NOT NULLCopyright © 2011 Ramez Elmasri and Shamkant NavatheNested Queries, Tuples,and Set/Multiset ComparisonsNested queriesComplete select-from-where blocks within WHERE clause of another queryOuter queryComparison operator INCompares value v with a set (or multiset) of values V Evaluates to TRUE if v is one of the elements in VCopyright © 2011 Ramez Elmasri and Shamkant NavatheNested Queries (cont’d.)Copyright © 2011 Ramez Elmasri and Shamkant NavatheNested Queries (cont’d.)Use tuples of values in comparisons Place them within parenthesesCopyright © 2011 Ramez Elmasri and Shamkant NavatheUse other comparison operators to compare a single value v = ANY (or = SOME) operator •Returns TRUE if the value v is equal to some value in the set V and is hence equivalent to INOther operators that can be combined with ANY (or SOME): >, >=, <, <=, and <>Nested Queries (cont’d.)Copyright © 2011 Ramez Elmasri and Shamkant NavatheNested Queries (cont’d.)Avoid potential errors and ambiguitiesCreate tuple variables (aliases) for all tables referenced in SQL queryCopyright © 2011 Ramez Elmasri and Shamkant NavatheCorrelated Nested QueriesCorrelated nested query Evaluated once for each tuple in the outer queryCopyright © 2011 Ramez Elmasri and Shamkant NavatheThe EXISTS and UNIQUE Functions in SQLEXISTS function Check whether the result of a correlated nested query is empty or notEXISTS and NOT EXISTS Typically used in conjunction with a correlated nested querySQL function UNIQUE(Q)Returns TRUE if there are no duplicate tuples in the result of query QCopyright © 2011 Ramez Elmasri and Shamkant NavatheExplicit Sets and Renaming of Attributes in SQLCan use explicit set of values in WHERE clauseUse qualifier AS followed by desired new nameRename any attribute that appears in the result of a queryCopyright © 2011 Ramez Elmasri and Shamkant NavatheJoined Tables in SQL and Outer JoinsJoined tablePermits users to specify a table resulting from a join operation in the FROM clause of a queryThe FROM clause in Q1A Contains a single joined tableCopyright © 2011 Ramez Elmasri and Shamkant NavatheJoined Tables in SQL and Outer Joins (cont’d.)Inner joinDefault type of join in a joined tableTuple is included in the result only if a matching tuple exists in the other relationCopyright © 2011 Ramez Elmasri and Shamkant NavatheJoined Tables in SQL and Outer Joins (cont’d.)LEFT OUTER JOIN Every tuple in left table must appear in resultIf no matching tuple•Padded with NULL values for attributes of right tableRIGHT OUTER JOINEvery tuple in right table must appear in resultIf no matching tuple•Padded with NULL values for the attributes of left tableFULL OUTER JOINCopyright © 2011 Ramez Elmasri and Shamkant NavatheAggregate Functions in SQLUsed to summarize information from multiple tuples into a single-tuple summaryBuilt-in aggregate functions COUNT, SUM, MAX, MIN, and AVGFunctions can be used in the SELECT clause or in a HAVING clauseGrouping Create subgroups of tuples before summarizingCopyright © 2011 Ramez Elmasri and Shamkant NavatheAggregate Functions in SQL (cont’d.)NULL values discarded when aggregate functions are applied to a particular columnCopyright © 2011 Ramez Elmasri and Shamkant NavatheGrouping: The GROUP BY and HAVING ClausesPartition relation into subsets of tuplesBased on grouping attribute(s)Apply function to each such group independentlyGROUP BY clause Specifies grouping attributesIf NULLs exist in grouping attribute Separate group created for all tuples with a NULL value in grouping attributeCopyright © 2011 Ramez Elmasri and Shamkant NavatheResults of GROUP BYCopyright © 2011 Ramez Elmasri and Shamkant NavatheResults of GROUP BY and HAVINGCopyright © 2011 Ramez Elmasri and Shamkant NavatheResults of GROUP BY and
View Full Document
Unlocking...