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 1UNIONSQL 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 1UNION ALLSQL Statement 2

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 1INTERSECTSQL 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 1MINUSSQL 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
• and p_cnt gt 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
  (, ltgt, gt, lt, lt, gt) 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 in a 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 , ltgt, gt, lt, lt, or gt
  operator before ANY in the query.

SELECT employee_id, last_name FROM
employees WHERE salary lt ANY (SELECT
low_salary FROM salary_grades)
37
ALL (franz)

• Compare a value with all values in a list.
• You have to place an , ltgt, gt, lt, lt, or gt
  operator before ALL in the query.
• SELECT employee_id, last_name
• FROM employees
• WHERE salary gt 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 (, gt,
  )?
• 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 , gt, lt, gt, lt. 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
• HAVING AVG(price) lt
• (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
• gt the query is uncorrelated
• gt 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.

51
Single Value Subquery
(franz)

SELECT product_type_id, AVG(price) FROM
products GROUP BY product_type_id
52
Single Value Subquery
(franz)

• Among these average prices, we locate the
  maximum price out of all the averages which is
  26.22.

53

Single Value Subquery
(franz)
From the product_type_id average prices, select
the ones that are less than 26.22.
HAVING AVG(price) lt (SELECT MAX(AVG(price))
FROM products GROUP BY product_type_id)
54
Our original query
(franz)

• SELECT product_type_id, AVG(price)
• FROM products
• GROUP BY product_type_id
• HAVING AVG(price) lt
• (SELECT MAX(AVG(price))
• FROM products
• GROUP BY product_type_id)

Break it down into smaller steps. Then put the
larger query together.
55
Laboratory Nine

• Objectives
• Develop competence with subqueries
• Steps
• First Query
• Second Query

56
Laboratory Nine

• First Query
• Problem Find the phone numbers of the people who
  do not live in the area code 828 region.
• Requirement Use an uncorrelated subquery.

57
Laboratory Nine

• Solution
• SELECT s1.sprtele_phone_number
• FROM sprtele s1
• WHERE s1.sprtele_pidm NOT IN
• (SELECT s2.sprtele_pidm
• FROM sprtele s2
• WHERE s2.sprtele_area_code 828)

58
Laboratory Nine

• Second Query
• Problem Find the phone numbers of the people who
  do not live in the area code 828 region.
• Requirement Use an correlated subquery.

59
Laboratory Nine

• Solution
• SELECT s1.sprtele_phone_number
• FROM sprtele s1
• WHERE NOT EXISTS
• (SELECT s2.sprtele_pidm
• FROM sprtele s2
• WHERE s1.sprtele_pidm s2.sprtele_pidm and
  s2.sprtele_area_code 828)

60
A Query Development Methodology

• Problem queries can be complicated
• gt easy to introduce bugs
• gt difficult to find bugs
• gt complexity of the select statement
• gt large size of input table
• gt limited number of instances of input table
  tested
• Solution
• be aware of types of bugs
• incremental development
• multiple small input data sets with correct
  output known

61
A Query Development Methodology

• Types of Bugs
• Compile-time Banner emits error message about
  illegal syntax when query is submitted
• Run-time Banner emits error message when query
  is executing that an illegal operation occurred
• Logic No error message!

62
A Query Development Methodology

• Logic Errors
• Your query has legal syntax and does not cause
  any illegal operations gt result table is
  generated
• But your query is not doing what you think it is
  doing
• Hard to detect (the result table may be the
  correct result table for this particular input
  table instance)

63
A Query Development Methodology

• Incremental Development
• Dont try to come up with a complete solution
  (select statement) all at once.
• Develop a select statement for a small part of
  the query
• test that partial solution using all the input
  data sets to make sure it works
• if it does not work, edit it, and try again
• Extend the select statement to include more of
  the complete query and repeat

64
A Query Development Methodology

• Multiple small input data sets
• Develop instances of the input table that are
• small (so understandable)
• test all the cases for the possible real input
• to check the tentative partial select statement
  is correct
• usually artificial (made just for testing) to
  satisfy the previous constraints