banner and the sql select statement part four multiple connected select statements
Download
Skip this Video
Download Presentation
Banner and the SQL Select Statement: Part Four (Multiple Connected Select Statements)

Loading in 2 Seconds...

play fullscreen
1 / 64

Banner and the SQL Select Statement: Part Four (Multiple Connected Select Statements) - PowerPoint PPT Presentation


  • 247 Views
  • Uploaded on

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). Outline. The Goal The Concepts A First Example

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Banner and the SQL Select Statement: Part Four (Multiple Connected Select Statements)' - livana


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
banner and the sql select statement part four multiple connected select statements

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)

outline
Outline
  • The Goal
  • The Concepts
    • A First Example
    • Single Table Selects
    • Joins
    • Multiple Connected Select Statements
a first example
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
single table selects
Single Table Selects
  • Outline
    • WHERE clause: single condition, multiple conditions
    • Lab 3:
    • Order By; Aggregate Functions
    • Lab 4:
    • Group By; Having
    • Lab 5:
joins
Joins
  • Outline
    • Why multiple tables?
    • Inner Joins
    • Lab 6:
    • Outer joins
    • Lab 7:
multiple connected select statements
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
set operators
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)
set operators franz
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
set operators9
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.
example tables franz
Example Tables (franz)

Relation (stvrelt) table (13 rows)

Legacy (stvlgcy) table (10 rows)

union franz
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.
union franz12
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]

union franz13
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.

union all franz
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]

union all franz15
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

intersect franz
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).
intersect franz17
INTERSECT (franz)

The syntax is as follows:

[SQL Statement 1]INTERSECT[SQL Statement 2]

intersect franz18
INTERSECT (franz)

SELECT stvrelt_code, stvrelt_desc

FROM stvrelt

INTERSECT

SELECT stvlgcy_code, stvlgcy_desc

FROM stvlgcy

minus franz
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.
minus franz20
MINUS (franz)

The syntax is as follows:

[SQL Statement 1]MINUS[SQL Statement 2]

minus franz21
MINUS (franz)

SELECT stvrelt_code, stvrelt_desc

FROM stvrelt

MINUS

SELECT stvlgcy_code, stvlgcy_desc

FROM stvlgcy

set operators franz22
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.
set operators franz23
SET Operators (franz)

SELECT stvlgcy_code, stvlgcy_desc

FROM stvlgcy

MINUS

SELECT stvrelt_code, stvrelt_desc

FROM stvrelt

set operators franz24
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.
laboratory eight
Laboratory Eight
  • Objectives:
    • Develop competence with set operators
  • Steps:
    • First Query
laboratory eight26
Laboratory Eight

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

laboratory eight27
Laboratory Eight

Answer:

SELECT sprtele_phone_number

FROM sprtele

MINUS

SELECT sprtele_phone_number

FROM sprtele

WHERE sprtele_area_code = 828

subquery
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.
subquery29
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
slide30
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‘

subquery31
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)
subquery32
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
special operators for subqueries franz
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.
in franz
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%’)
not in franz
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)

any franz
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)

all franz
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)

correlated subquery
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.

correlated subquery39
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
exists franz
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)

exists just checks for the existence of rows returned by the subquery not the actual values
‘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.

slide42
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)

not exists franz
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)

a multi condition where clause subquery franz
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)

single value subquery
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.
single value subquery franz
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."

slide47
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...

single value subquery48
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
single value subquery franz49
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).
single value subquery franz50
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].
single value subquery franz51
Single Value Subquery (franz)

SELECT product_type_id, AVG(price)

FROM products

GROUP BY product_type_id

slide52
Single Value Subquery (franz)

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

slide53
Single Value Subquery (franz)

From the product_type_id average prices,

select the ones that are less than $26.22.

HAVINGAVG(price) <

(SELECT MAX(AVG(price))

FROM products

GROUP BY product_type_id)

our original query franz
Our original query (franz)

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)

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

laboratory nine
Laboratory Nine
  • Objectives:
    • Develop competence with subqueries
  • Steps:
    • First Query
    • Second Query
laboratory nine56
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.

laboratory nine57
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)

laboratory nine58
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.

laboratory nine59
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)

a query development methodology
A Query Development Methodology
  • Problem: queries can be complicated
      • => easy to introduce bugs
      • => difficult to find bugs
        • => complexity of the select statement
        • => large size of input table
        • => 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
a query development methodology61
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!
a query development methodology62
A Query Development Methodology
  • Logic Errors
    • Your query has legal syntax and does not cause any illegal operations => 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)
a query development methodology63
A Query Development Methodology
  • Incremental Development:
    • Don’t 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
a query development methodology64
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
ad