Chapter 7 More SQL Chapter 7 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 Comparisons Involving NULL and Three Valued Logic cont d SQL uses a three valued logic TRUE FALSE and UNKNOWN 5 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 6 Nested Queries Nested Queries Nested in WHERE clause WHERE NOT attribute comp op WHERE NOT attribute comp op WHERE NOT attribute comp op WHERE attribute NOT IN subquery subquery ANY subquery ALL subquery Nested in FROM clause creating a Table on the fly FROM subquery AS alias 8 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 1 0 IN Operator with Explicit Set SELECT FROM Department WHERE Dnumber IN 1 7 8 1 1 Explicit Sets and Renaming of Attributes in SQL Can use explicit set of values in WHERE clause 1 2 Nested Queries cont d Retrieve the name of each employee who has at least one son Select Fname Lname From Employee Where Ssn IN Select Essn From Dependent Where relationship son the result of the subquery is 123456789 333445555 Each of these two ESSNs has at least one son Nested Queries cont d Use tuples of values in comparisons e g Pno Hours Place them within parentheses Query Retrieve the SSN of every employee who had worked on the same projects and the same number of hours as the employee with SSN 123456789 The result of the subquery is 1 32 5 2 7 5 1 4 Nested Queries cont d UNION 1 5 Nested Queries cont d Avoid potential errors and ambiguities Create tuple variables aliases for all tables referenced in SQL query 1 6 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 Other operators that can be combined with ANY or SOME equivalent to IN and 1 7 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 1 8 The EXISTS Functions in SQL EXISTS function Check whether the result of a nested query is empty or not No comparison with elements of result set EXISTS and NOT EXISTS Typically used in conjunction with a correlated nested query 1 9 Alternate Query 16 Query 6 2 0 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 2 2 Joins 23 Specifying Joined Tables in the FROM Clause of SQL The FROM clause in Q1 Joined table Permits users to specify a table resulting from a join operation in the FROM clause of a query Contains a single joined table JOIN may also be called INNER JOIN 24 NATURAL JOIN NATURAL JOIN on two relations R and S No join condition specified Is equivalent to an implicit EQUIJOIN condition for each pair of attributes with the same name from R and S Rename attributes of one relation so it can be joined with another using NATURAL JOIN Q1B SELECT Fname Lname Address FROM EMPLOYEE NATURAL JOIN DEPARTMENT WHERE Dname Research The above works with EMPLOYEE Dno DEPT Dno as an implicit join condition 25 INNER and OUTER Joins 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 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 attributes of left table 26 SQL Inner Joins SELECT S name E classid FROM S JOIN E ON S sid E sid S S name Jones Smith Brown E S sid 11111 22222 33333 E classid E sid History105 11111 11111 DataScience1 94 French150 English10 22222 44444 S name E classid Jones History105 Jones DataScienc e194 French150 Smith 27 SQL Inner Joins SELECT S name E classid FROM S JOIN E ON S sid E sid S S name Jones Smith Brown S sid 11111 22222 33333 S name E classid Jones History105 Jones DataScienc e194 French150 Smith E E classid E sid 11111 History105 11111 DataScienc e194 French150 English10 22222 44444 Unmatched keys 28 What kind of Join is this SELECT S name E classid FROM S E ON S sid E sid S S name Jones Smith Brown S sid 11111 22222 33333 S name E classid Jones History105 Jones DataScienc e194 French150 NULL Smith Brown E E classid E sid 11111 History105 11111 DataScienc e194 French150 English10 22222 44444 29 SQL Joins SELECT S name E classid FROM S LEFT OUTER JOIN E ON S sid E sid S S name Jones Smith Brown S sid 11111 22222 33333 S name E classid Jones History105 Jones DataScienc e194 French150 NULL Smith Brown E E classid E sid 11111 History105 11111 DataScienc e194 French150 English10 22222 44444 30 SQL Joins SELECT S name E classid FROM S RIGHT OUTER JOIN E ON S sid E sid S S name Jones Smith Brown S sid 11111 22222 33333 S name E classid Jones History105 Jones DataScienc e194 French150 English10 Smith NULL E E classid E sid 11111 History105 11111 DataScienc e194 French150 English10 22222 44444 31 SQL Joins SELECT S name E classid FROM S RIGHT OUTER JOIN E ON S sid E sid S S name Jones Smith Brown S sid 11111 22222 33333 S name E classid Jones History105 Jones DataScienc e194 French150 English10 Smith NULL E E classid E sid 11111 History105 11111 DataScienc e194 French150 English10 22222 44444 32 SQL Joins SELECT S name E classid FROM S JOIN E ON S sid E sid S S name Jones Smith Brown S sid 11111 22222 33333 S name E classid Jones History105 Jones DataScienc e194 French150