PowerPoint PresentationChapter 5 OutlineMore Complex SQL Retrieval QueriesComparisons Involving NULLComparisons Involving NULL (cont’d.)Nested Queries, Tuples, and Set/Multiset ComparisonsNested Queries (cont’d.)Slide 8Slide 9Correlated 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 15Aggregate Functions in SQLAggregate Functions in SQL (cont’d.)Grouping: The GROUP BY and HAVING ClausesResults of GROUP BYResults of GROUP BY and HAVINGSlide 21Grouping: 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.)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 NULLMeanings of NULLUnknown valueUnavailable or withheld valueNot applicable attributeEach individual NULL value considered to be different from every other NULL valueCopyright © 2011 Ramez Elmasri and Shamkant NavatheComparisons Involving NULL (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 hence is equivalent to INOther operators that can be combined with ANY (or SOME): >, >=, <, <=, and <>Nested Queries (cont’d.)Copyright © 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 queryCopyright © 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 subgroup 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 HAVINGCopyright © 2011 Ramez Elmasri and Shamkant NavatheGrouping: The GROUP BY and HAVING Clauses (cont’d.)HAVING clauseProvides a condition on the summary informationCopyright © 2011 Ramez Elmasri and Shamkant NavatheDiscussion and Summary of SQL QueriesCopyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying Constraints as Assertions and Actions as TriggersCREATE ASSERTIONSpecify additional types of constraints outside scope of built-in relational model constraintsCREATE TRIGGERSpecify automatic actions that database system will perform when certain events and conditions occurCopyright © 2011 Ramez Elmasri and Shamkant NavatheSpecifying General Constraints as Assertions in SQLCREATE ASSERTION
View Full Document