Chapter 5 More SQL CS 6360 Database Design Chris Irwin Davis Ph D Email cid021000 utdallas edu Phone 972 883 3574 O ce ECSS 4 705 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 2 More Complex SQL Retrieval Queries Additional features allow users to specify more complex retrievals from database Derived values Nested queries Joined tables Outer joins Aggregate functions and grouping 3 Selection Criteria in WHERE Clause FROM clause logically generates Cartesian Product of tables e g FROM T1 T2 T1 T2 WHERE clause filters based on selection criteria Each wide tuple of FROM clause is considered individually in sequence 4 Comparisons Involving NULL and Three Valued Logic Meanings of NULL Unknown value Unavailable or withheld value Not applicable attribute Each individual NULL value considered to be different and distinct from every other NULL value SQL uses a three valued logic TRUE FALSE and UNKNOWN Why significant 5 Comparisons Involving NULL and Three Valued Logic cont d 6 Comparisons Involving NULL and Three Valued Logic cont d 7 Comparisons Involving NULL and Three Valued Logic cont d SQL allows queries that check whether an attribute value is NULL IS NULL or IS NOT NULL 8 Comparisons Involving NULL and Three Valued Logic cont d SQL allows queries that check whether an attribute value is NULL IS NULL or IS NOT NULL 9 Nested Queries Nested in WHERE clause WHERE NOT attribute comp op subquery WHERE NOT attribute comp op ALL subquery WHERE NOT attribute comp op ANY subquery WHERE attribute NOT IN subquery WHERE attribute IN subquery Nested in FROM clause creating a View on the fly FROM subquery AS alias 10 IN Clause Comparison set 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 11 Nested Queries cont d UNION 12 Nested Queries cont d Use tuples of values in comparisons Place them within parentheses 13 Nested Queries cont d Use tuples of values in comparisons Place them within parentheses SELECT DISTINCT Essn FROM Works on WHERE Pno Hours IN SELECT Pno Hours FROM Works on WHERE Essn 123456789 14 IN Operator with Explicit Set SELECT FROM Department WHERE Dnumber IN 1 7 8 15 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 is hence equivalent to IN Other operators that can be combined with ANY or SOME and 16 Nested Queries cont d Avoid potential errors and ambiguities Create tuple variables aliases for all tables referenced in SQL query 17 Correlated Nested Queries Correlated nested query Whenever a condition in the WHERE clause of a nested query references some attribute of a relation declared in the outer query the two queries are said to be correlated We can understand a correlated query better by considering that the nested query is evaluated once for each tuple or combination of tuples in the outer query For example we can think of Q16 as follows For each EMPLOYEE tuple evaluate the nested query which retrieves the Essn values for all DEPENDENT tuples with the same sex and name as that EMPLOYEE tuple if the Ssn value of the EMPLOYEE tuple is in the result of the nested query then select that EMPLOYEE tuple 18 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 SQL function UNIQUE Q Returns TRUE if there are no duplicate tuples in the result of query Q 19 Alternate Query 16 Query 6 20 Explicit Sets and Renaming of Attributes in SQL Can use explicit set of values in WHERE clause 21 Explicit Sets and Renaming of Attributes in SQL Can use explicit set of values in WHERE clause 22 Explicit Sets and Renaming of Attributes in SQL Renaming of Attributes Use qualifier AS followed by desired new name Rename any attribute that appears in the result of a query 23 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 24 Joined Tables in SQL and Outer Joins cont d Specify different types of join NATURAL JOIN Various types of OUTER JOIN NATURAL JOIN on two relations R and S No join condition specified Implicit EQUIJOIN condition for each pair of attributes with same name from R and S 25 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 26 Joined Tables in SQL and Outer Joins cont d Outer joins LEFT JOIN Every tuple in left table must appear in result If no matching tuple padded with NULL values for attributes of right table RIGHT JOIN Not supported by SQLite FULL JOIN Not supported by MySQL SQLite Can be simulated with Left Join Right Join UNION 27 28 29 Aggregate Functions in SQL Used to summarize information from multiple tuples into a single tuple summary Grouping GROUP BY Create subgroups of tuples before summarizing Built in Aggregate Functions COUNT SUM MAX MIN and AVG Aggregate functions can be used in the SELECT clause or in a HAVING clause but not WHERE clause 30 Aggregate Functions in SQL cont d NULL values discarded when aggregate functions are applied to a particular column 31 Grouping The GROUP BY and HAVING Clauses Partition relation into subsets of tuples Based on grouping attribute s Apply function to each such group 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 32 Grouping The GROUP BY and HAVING Clauses cont d HAVING clause Provides a condition on the summary information 33 Discussion and Summary of SQL Queries 34 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 35 Specifying General Constraints as Assertions in SQL CREATE ASSERTION Specify a query that selects any tuples that violate the desired condition Each assertion is given a constraint name and is specified via a condition
View Full Document
Unlocking...