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 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 Nested in FROM clause creating a View on the fly FROM subquery AS alias 11 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 12 Nested Queries cont d UNION 13 Nested Queries cont d Use tuples of values in comparisons Place them within parentheses 14 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 15 IN Operator with Explicit Set SELECT FROM Department WHERE Dnumber IN 1 7 8 16 Explicit Sets and Renaming of Attributes in SQL Can use explicit set of values in WHERE clause 17 Explicit Sets and Renaming of Attributes in SQL Can use explicit set of values in WHERE clause 18 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 19 Nested Queries cont d Avoid potential errors and ambiguities Create tuple variables aliases for all tables referenced in SQL query 20 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 21 The EXISTS Functions in SQL EXISTS function Check whether the result of a nested query is empty or not No comparison with result set EXISTS and NOT EXISTS Typically used in conjunction with a correlated nested query 22 Alternate Query 16 Query 6 23 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 24 Joins 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 26 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 27 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 28 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 29 Aggregate Functions GROUP BY and HAVING 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 31 GROUP BY and HAVING in SQL Used to summarize information from multiple tuples into a single tuple summary Grouping GROUP BY Create subgroups of tuples before summarizing Aggregate functions can be used in the SELECT clause or in a HAVING clause but not WHERE clause 32 Aggregate Functions in SQL cont d NULL values discarded when aggregate functions are applied to a particular column 33 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 34 Grouping The GROUP BY and HAVING Clauses cont d HAVING clause Provides a condition on the summary information 35 Discussion and Summary of SQL Queries 36 Views Views Virtual Tables in SQL Concept of a view in SQL Single table derived from other tables Considered to be a virtual table 38 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 39 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 40 View
View Full Document
Unlocking...