Chapter 5 More SQL Complex Queries Triggers Views and Schema Modification Copyright 2011 Pearson Education Inc Publishing as Pearson Addison Wesley Chapter 5 Outline More Complex SQL Retrieval Queries Specifying Constraints as Assertions and Actions as Triggers Views Virtual Tables in SQL Schema Change Statements in SQL Copyright 2011 Ramez Elmasri and Shamkant Navathe More Complex SQL Retrieval Queries Additional features allow users to specify more complex retrievals from database Nested queries joined tables outer joins aggregate functions and grouping Copyright 2011 Ramez Elmasri and Shamkant Navathe Comparisons Involving NULL Meanings of NULL Unknown value Unavailable or withheld value Not applicable attribute Each individual NULL value considered to be different from every other NULL value Copyright 2011 Ramez Elmasri and Shamkant Navathe Comparisons Involving NULL cont d SQL allows queries that check whether an attribute value is NULL IS or IS NOT NULL Copyright 2011 Ramez Elmasri and Shamkant Navathe Nested Queries Tuples and Set Multiset Comparisons Nested queries Complete select from where blocks within WHERE clause of another query Outer query Comparison operator IN Compares value v with a set or multiset of values V Evaluates to TRUE if v is one of the elements in V Copyright 2011 Ramez Elmasri and Shamkant Navathe Nested Queries cont d Copyright 2011 Ramez Elmasri and Shamkant Navathe Nested Queries cont d Use tuples of values in comparisons Place them within parentheses Copyright 2011 Ramez Elmasri and Shamkant Navathe Nested Queries cont d Use 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 IN Other operators that can be combined with ANY or SOME and Copyright 2011 Ramez Elmasri and Shamkant Navathe Correlated Nested Queries Correlated nested query Evaluated once for each tuple in the outer query Copyright 2011 Ramez Elmasri and Shamkant Navathe The EXISTS and UNIQUE Functions in SQL EXISTS function Check whether the result of a correlated nested query is empty or not EXISTS and NOT EXISTS Typically used in conjunction with a correlated nested query Copyright 2011 Ramez Elmasri and Shamkant Navathe Explicit Sets and Renaming of Attributes in SQL Can use explicit set of values in WHERE clause Use qualifier AS followed by desired new name Rename any attribute that appears in the result of a query Copyright 2011 Ramez Elmasri and Shamkant Navathe Joined Tables in SQL and Outer Joins Joined table Permits users to specify a table resulting from a join operation in the FROM clause of a query The FROM clause in Q1A Contains a single joined table Copyright 2011 Ramez Elmasri and Shamkant Navathe Joined Tables in SQL and Outer Joins cont d Inner join Default type of join in a joined table Tuple is included in the result only if a matching tuple exists in the other relation Copyright 2011 Ramez Elmasri and Shamkant Navathe Joined Tables in SQL and Outer Joins cont d LEFT OUTER JOIN Every tuple in left table must appear in result If no matching tuple Padded with NULL values for attributes of right table RIGHT OUTER JOIN Every tuple in right table must appear in result If no matching tuple Padded with NULL values for the attributes of left table FULL OUTER JOIN Copyright 2011 Ramez Elmasri and Shamkant Navathe Aggregate Functions in SQL Used to summarize information from multiple tuples into a single tuple summary Built in aggregate functions COUNT SUM MAX MIN and AVG Functions can be used in the SELECT clause or in a HAVING clause Grouping Create subgroups of tuples before summarizing Copyright 2011 Ramez Elmasri and Shamkant Navathe Aggregate Functions in SQL cont d NULL values discarded when aggregate functions are applied to a particular column Copyright 2011 Ramez Elmasri and Shamkant Navathe Grouping The GROUP BY and HAVING Clauses Partition relation into subsets of tuples Based on grouping attribute s Apply function to each subgroup independently GROUP BY clause Specifies grouping attributes If NULLs exist in grouping attribute Separate group created for all tuples with a NULL value in grouping attribute Copyright 2011 Ramez Elmasri and Shamkant Navathe Results of GROUP BY Copyright 2011 Ramez Elmasri and Shamkant Navathe Results of GROUP BY and HAVING Copyright 2011 Ramez Elmasri and Shamkant Navathe Results of GROUP BY and HAVING Copyright 2011 Ramez Elmasri and Shamkant Navathe Grouping The GROUP BY and HAVING Clauses cont d HAVING clause Provides a condition on the summary information Copyright 2011 Ramez Elmasri and Shamkant Navathe Discussion and Summary of SQL Queries Copyright 2011 Ramez Elmasri and Shamkant Navathe Specifying Constraints as Assertions and Actions as Triggers CREATE ASSERTION Specify additional types of constraints outside scope of built in relational model constraints CREATE TRIGGER Specify automatic actions that database system will perform when certain events and conditions occur Copyright 2011 Ramez Elmasri and Shamkant Navathe Specifying General Constraints as Assertions in SQL CREATE ASSERTION Specify a query that selects any tuples that violate the desired condition Copyright 2011 Ramez Elmasri and Shamkant Navathe Introduction to Triggers in SQL CREATE TRIGGER statement Used to monitor the database Typical trigger has three components Event s Condition Action Copyright 2011 Ramez Elmasri and Shamkant Navathe Views Virtual Tables in SQL Concept of a view in SQL Single table derived from other tables Considered to be a virtual table Copyright 2011 Ramez Elmasri and Shamkant Navathe Specification of Views in SQL CREATE VIEW command Give table name list of attribute names and a query to specify the contents of the view Copyright 2011 Ramez Elmasri and Shamkant Navathe Specification of Views in SQL cont d Specify SQL queries on a view View always up to date Responsibility of the DBMS and not the user DROP VIEW command Dispose of a view Copyright 2011 Ramez Elmasri and Shamkant Navathe Schema Change Statements in SQL Schema evolution commands Can be done while the database is operational Does not require recompilation of the database schema Copyright 2011 Ramez Elmasri and Shamkant Navathe The DROP Command DROP command Used to drop named schema elements such as tables domains or constraint Drop behavior options CASCADE and RESTRICT Example DROP SCHEMA COMPANY CASCADE Copyright 2011 Ramez Elmasri and Shamkant Navathe The
View Full Document
Unlocking...