Banner and the SQL Select Statement: Part Four (Multiple Connected Select Statements)

1. Banner and the SQL Select Statement: Part Four (Multiple Connected Select Statements) Mark Holliday Department of Mathematics and Computer Science Western Carolina University 18 November 2005 (updated: 18 November 2005)

2. Outline • The Goal • The Concepts • A First Example • Single Table Selects • Joins • Multiple Connected Select Statements

3. A First Example • Outline • The Relational Model: Single Table • Lab 1: TOAD, Schema Browser • Some Structured Query Language (SQL) Basics • Lab 2: TOAD, SQL Editor

4. Single Table Selects • Outline • WHERE clause: single condition, multiple conditions • Lab 3: • Order By; Aggregate Functions • Lab 4: • Group By; Having • Lab 5:

5. Joins • Outline • Why multiple tables? • Inner Joins • Lab 6: • Outer joins • Lab 7:

6. Multiple Connected Select Statements • Outline • Set Operators • Lab 8: • Subqueries • Use directly: FROM clause • Use as a set: new operators • Use as a single value: aggregate functions • Lab 9: • A Query Development Methodology

7. SET Operators Intuition: • A SQL Select statement returns a table • A table is a set • we can put a set operator in between two select statements to create a new set (that is, table)

8. SET Operators (franz) Types of Set Operators : • UNION – return all rows, exclusing duplicates • UNION ALL -- returns all rows, including duplicates • INTERSECT -- returns rows retrieved in both queries • MINUS -- returns remaining rows when results of second query are subtracted from the first query

9. Set Operators Two restrictions of set operators are: • The two tables must contain the same number of columns. • All corresponding columns in the two tables need to be of the same data type.

10. Example Tables (franz) Relation (stvrelt) table (13 rows) Legacy (stvlgcy) table (10 rows)

11. UNION (franz) • The purpose of the SQL UNION command is to combine the results of two queries. • In this respect, UNION is similar to JOIN • they are both used to combine related information from multiple tables.

12. UNION (franz) When using UNION, only distinct values are selected (similar to SELECT DISTINCT). The syntax is as follows: [SQL Statement 1]UNION[SQL Statement 2]

13. UNION (franz) SELECT stvrelt_code, stvrelt_desc  varchar(1); varchar(30) FROM stvrelt  Relation table UNION SELECT stvlgcy_code, stvlgcy_desc  varchar(1); varchar(30) FROM stvlgcy  Legacy table Note: The default resultant set from UNION is DISTINCT rows.

14. UNION ALL (franz) • The difference between UNION ALL and UNION is that, • while UNION only selects distinct values, • UNION ALL selects all values. The syntax for UNION ALL is as follows: [SQL Statement 1]UNION ALL[SQL Statement2]

15. UNION ALL (franz) Here is our example using Banner tables: SELECT stvrelt_code, stvrelt_desc FROM stvrelt UNION ALL SELECT stvlgcy_code, stvlgcy_desc FROM stvlgcy

16. INTERSECT (franz) Similar to the UNION command, INTERSECT operates on two SQL statements. The difference is that, while UNION essentially acts as an OR operator • (value is selected if it appears in either the first or the second statement), the INTERSECT command acts as an AND operator • (value is selected only if it appears in both statements).

17. INTERSECT (franz) The syntax is as follows: [SQL Statement 1]INTERSECT[SQL Statement 2]

18. INTERSECT (franz) SELECT stvrelt_code, stvrelt_desc FROM stvrelt INTERSECT SELECT stvlgcy_code, stvlgcy_desc FROM stvlgcy

19. MINUS (franz) • The MINUS operates on two SQL statements. • It takes all the results from the first SQL statement, and then subtract out the ones that are present in the second SQL statement to get the final answer. • If the second SQL statement includes results not present in the first SQL statement, such results are ignored.

20. MINUS (franz) The syntax is as follows: [SQL Statement 1]MINUS[SQL Statement 2]

21. MINUS (franz) SELECT stvrelt_code, stvrelt_desc FROM stvrelt MINUS SELECT stvlgcy_code, stvlgcy_desc FROM stvlgcy

22. SET Operators (franz) A note of special importance: • When using the various SQL SET operators that we have covered, • it is especially important that you understand your data! In the last example, • if we reversed the order of the MINUS operator, • we would have completely different results.

23. SET Operators (franz) SELECT stvlgcy_code, stvlgcy_desc FROM stvlgcy MINUS SELECT stvrelt_code, stvrelt_desc FROM stvrelt

24. SET Operators (franz) would return FIVE rows. • Starting from the “stvlgcy” table, we would look at “stvrelt”. • Any records in “stvrelt” that were not in “stvlgcy” would be ignored. • The records in “stvrelt” that were the same as “stvlgcy” would be removed. • The items grayed out in the “stvlgcy” table would be selected.

25. Laboratory Eight • Objectives: • Develop competence with set operators • Steps: • First Query

26. Laboratory Eight Problem: Find the phone numbers of the people who do not live in the area code 828 region.

27. Laboratory Eight Answer: SELECT sprtele_phone_number FROM sprtele MINUS SELECT sprtele_phone_number FROM sprtele WHERE sprtele_area_code = 828

28. Subquery Intuition: • The Set Operators used so far (UNION, UNION ALL, INTERSECT, MINUS) operate on output tables (i.e. sets) but only in between select statement • Question: Can we use set operations so as to operate on an output table within another select statement? • Answer: Yes! • The select statement inside the outer select statement is called a subquery or a nested query.

29. Subquery • Where in a select statement can we nest a subquery (i.e. where do we use a set)? • The FROM clause since the FROM clause lists tables • just have one of those tables be generated from the subquery

30. FROM Clause Subquery (franz) Suppose we want to select non-busineses from ‘spriden’, including a count of addresses, where there is more than one address type for that pidm: Example of a subquery in the FROM clause: SELECT spriden_id, spriden_last_name, spriden_first_name, p_cnt FROM spriden, (SELECT spraddr_pidm, count(spraddr_atyp_code) p_cnt FROM spraddr GROUP BY spraddr_pidm) WHERE spriden_pidm = spraddr_pidm andp_cnt > 1 and spriden_entity_ind = 'P‘

31. Subquery • Can we use a subquery anywhere else? • Yes, but we need some help. • The conditions in WHERE, and HAVING clauses we have seen all use • comparison operators that work on single values (=, <>, >, <, <=, >=) or • multiple values only in restricted ways (e.g. LIKE)

32. Subquery • We need operators to compare a value with a set of values • the set of values will be the output table of the subquery • the resulting expressions can be conditions in the WHERE and HAVING clauses • Introduce new operators that work with subqueries • IN, NOT IN, ANY, ALL, EXISTS, NOT EXISTS

33. Special operators for subqueries (franz) • IN/NOT IN -- Check to see if a value is in a specified list of values returned in the subquery. • ANY -- Compare a value with any value in a list. • ALL -- Compare a value with all values ina list. • EXISTS/NOT EXISTS --Check for the existence of rows returned by a subquery.

34. IN (franz) Check to see if a value is in a specified list of values returned in the subquery • SELECT product_id, name • FROM products • WHERE product_id IN • (SELECT product_id • FROM product • WHERE name LIKE‘%e%’)

35. NOT IN (franz) Check to see if a value is not in a specified list of values returned in the subquery. SELECT product_id, name FROM products WHERE product_id NOT IN (SELECT product_id FROM purchases)

36. ANY (franz) • Compare a value with any value in a list. • You have to place an “=, <>, >, <, <=, or >=“ operator before ANY in the query. SELECT employee_id, last_name FROM employees WHERE salary < ANY (SELECT low_salary FROM salary_grades)

37. ALL (franz) • Compare a value with all values in a list. • You have to place an “=, <>, >, <, <=, or >=“ operator before ALL in the query. SELECT employee_id, last_name FROM employees WHERE salary >ALL (SELECT high_salary FROM salary_grades)

38. Correlated Subquery If the table variable declared in the outer query is used in the subquery • the subquery is said to be correlated (otherwise, it is uncorrelated) In an uncorrelated subquery, the subquery is just evaluated once during the outer query.

39. Correlated Subquery In a correlated subquery, the set of rows output from the subquery can vary for each value of the outer table variable. • the subquery is reevaluated for each value of the outer table variable. • EXISTS and NOT EXISTS tend to be used in correlated subqueries • as in the examples following

40. EXISTS (franz) Check for the existence of rows returned by a subquery. SELECT employee_id, last_name FROM employees outer WHERE EXISTS (SELECT employee_id FROM employees inner WHERE inner.manager_id = outer.employee_id)

41. ‘EXISTS’ just checks for the existence of rows returned by the subquery, not the actual values. EXISTS (franz) To make your query run faster, you can just return a literal value.

42. EXISTS (franz) A re-write of our previous example: SELECT employee_id, last_name FROM employees outer WHERE EXISTS (SELECT 1 FROM employees inner WHERE inner.manager_id = outer.employee_id)

43. NOT EXISTS (franz) Retrieve products that have not been purchased: SELECT product_id, name FROM products outer WHERE NOT EXISTS (SELECT 1 FROM purchases inner WHERE inner.product_id = outer.product_id)

44. A Multi-Condition WHERE Clause Subquery (franz) The WHERE clause can have conditions besides the one using the subquery. Suppose we want to select non-busineses from ‘spriden’ that do NOT have a record in ‘spbpers’: SELECT spriden_id, spriden_last_name, spriden_first_name FROM spriden WHERE spriden_entity_ind = 'P' AND spriden_pidm not in (SELECT spbpers_pidm FROM spbpers)

45. Single Value Subquery • Question: Can we do even more with subqueries? • More Specific Question: Can we use the output table generated by a subquery in conditions that use the single value comparison operators (=, >, …)? • Answer: Yes! (sometimes) • Some SQL select statements are guaranteed to return an output table that is a set with only one value. • Which ones? Those with an aggregate function in the SELECT clause.

46. Single Value Subquery (franz) SELECT "column_name1" FROM "table_name" WHERE "column_name2" [Comparison Operator](SELECT AGGREGATE FUNCTION("column_name1“) FROM "table_name"WHERE [Condition]) [Comparison Operator] can be =, >, <, >=, <=. or "LIKE."

47. Single Value Subquery and HAVING Clause Subquery (franz) This is not an example from Banner, but from a made-up table. SELECT product_type_id, AVG(price) FROM products GROUP BY product_type_id HAVINGAVG(price) < (SELECT MAX(AVG(price)) FROM products GROUP BY product_type_id) The following data illustrates this subquery example...

48. Single Value Subquery • The same table alias is not used in both the outer query and the subquery • => the query is uncorrelated • => the subquery only needs to be evaluated once

49. Single Value Subquery (franz) • In the products table, there are multiple rows for each product_type_id. • Each row has a price, along with other information (which is not shown in our example).

50. Single Value Subquery (franz) • For each product_type_id in the products table: • we sum the prices (and divide by the total number of prices) • then determine the average price for product_type_id. • Each row has an average price, unique to the product_type_id [GROUP BY].