Chapter 6
Structured Query Language
Chapter 6
SQL
The relational operators define
permissible data manipulation
functions. They are not a
specification for a data access
language. These operators imply a
certain functionality.
Chapter 6
Introduction to SQL
• IBM in the mid-1970s as SEQUEL
• SQL is a standard
– slight variations among implementations
• data access language that is
embedded in application programs
• result of an SQL statement is a
relation
– Transform-oriented language
Chapter 6
Introduction to SQL
• Four verbs
– SELECT, UPDATE, DELETE, INSERT
– Basic CRUD functionality
• Create - INSERT
• Read - SELECT
• Update - UPDATE
• Delete - DELETE
Chapter 6
SELECT
“We must SELECT the illusion which
appeals to our temperament, and
embrace it with passion, if we want
to be happy.”
Cyril Connolly
Introduction to SQL Chapter 6
General Form:
SELECT [DISTINCT] Item(s)
FROM table(s)
[WHERE predicate]
[GROUP BY field(s) [HAVING predicate]]
[ORDER BY field(s)];
[Optional parameters]
Simple Retrieval Chapter 6
(SELECT)
Retrieve rows from SUPPLIER for suppliers located in Finland
SELECT *
FROM SUPPLIER
WHERE SUPPLIER.SupplierCity = ‘FINLAND’;
Simple Retrieval Chapter 6
(SELECT)
SELECT *
FROM SUPPLIER
WHERE SUPPLIER.SupplierCity = ‘FINLAND’;
Do you think using * (all columns) is, in general, a good practice?
In applications?
For exploring a table yes, but embedding it in an application is risky.
Order of columns can change, column names can change.
It‟s usually best to enumerate the column names in the SELECT.
Chapter 6
Chapter 6
SELECT
“What is known as success
assumes nearly as many aliases
as there are those who seek it.“
Stephen Birmingham
Simple Retrieval Chapter 6
(SELECT)
Often you want to present the columns in the result not in their native
format, but in an alternative, more application specific format.
We do this with a column Alias.
Simple Retrieval Chapter 6
(SELECT)
SELECT OrderDetail.OrderID
FROM [Order Details] AS OrderDetail
WHERE OrderDetail.UnitPrice = 14
We can also use a Table Alias
In this case, we can now reference the Order Details table
in the query without the troublesome [ ]‟s.
Simple Retrieval Chapter 6
(SELECT)
I often use Aliases to abbreviate long object
names.
As a short hand, it is convenient; but it makes the
query much less readable and maintainable.
Meaningful aliases, just like meaningful identifiers
in programming, is important.
Simple Retrieval Chapter 6
(SELECT)
SELECT Orders.OrderID, [Order Details].OrderDate
FROM Orders, [Order Details]
WHERE Order.OrderID=[Order Details].OrderID
AND [Order Details].UnitPrice = 14
Becomes…
SELECT O.OrderID, O.OrderDate
FROM Orders O, [Order Details] OD
WHERE O.OrderID=OD.OrderID
AND OD.UnitPrice = 14
Simple Retrieval Chapter 6
(WHERE)
The WHERE clause specifies a condition or conditions that
restricts the rows return in the result set.
NOT
T F
F T
AND OR
T T T T T T
T F F T F T
F T F F T T
F F F F F F
Simple Retrieval Chapter 6
(WHERE)
WHERE clauses can be as complex as they need to be.
List the OrderID, OrderDate, and RequiredDate of orders for
employee 5, shipped to the USA, for either customers SAVEA
or RATTC, that has a freight cost exceeding $50.
SELECT OrderID, OrderDate, RequiredDate
FROM Orders
WHERE EmployeeID = 5
AND ShipCountry = 'USA'
AND (CustomerID = 'SAVEA' OR CustomerID = 'RATTC')
AND Freight > 50
Simple Retrieval Chapter 6
(WHERE)
An alternative means of expressing the OR clause below is
with the IN() clause.
SELECT OrderID, OrderDate, RequiredDate
FROM Orders
WHERE EmployeeID = 5
AND ShipCountry = 'USA'
AND (CustomerID = 'SAVEA' OR CustomerID = 'RATTC')
AND Freight > 50
Becomes…
SELECT OrderID, OrderDate, RequiredDate
FROM Orders
WHERE EmployeeID = 5
AND ShipCountry = 'USA'
AND CustomerID IN ('SAVEA','RATTC')
AND Freight > 50
Simple Retrieval Chapter 6
(WHERE)
The IN() clause is an example of an uncorrelated subquery.
The expression evaluates to TRUE if the test expression
matches any values in the list.
test_expression [NOT] IN (value1, value2,…valueN)
The list can be “hard-coded” as below, or it can be the result of a SELECT
statement. The only restriction is that the SELECT statement has to return
an enumerated list, not a matrix (a list of values versus a table).
SELECT OrderID, OrderDate, RequiredDate
FROM Orders
WHERE EmployeeID = 5
AND ShipCountry = 'USA'
AND CustomerID IN ('SAVEA','RATTC')
AND Freight > 50
Simple Retrieval Chapter 6
(WHERE)
test_expression [NOT] IN (value1, value2,…valueN)
The list can be “hard-coded” as below, or it can be the result of a SELECT
statement. The only restriction is that the SELECT statement has to return
an enumerated list, not a matrix (a list of values versus a table).
SELECT OrderID, OrderDate, RequiredDate
FROM Orders
WHERE EmployeeID = 5
AND ShipCountry = 'USA'
AND CustomerID IN (SELECT CustomerID
FROM Customers
WHERE Condition = value)
AND Freight > 50
We’re going to spend a great deal more time on uncorrelated subqueries later…
Simple Retrieval Chapter 6
(WHERE)
Speaking of complex WHERE clauses, how would you answer this
question?
List the EmployeeID, OrderID, and OrderDate for Employees 1 and 2
where they have orders placed on the same day.
Simple Retrieval Chapter 6
(WHERE)
How would you output the data such that the employee records were
sequential rather than side-by-side?
List the EmployeeID, OrderID, and OrderDate for Employees 1 and 2
where they have orders placed on the same day.
Simple Retrieval Chapter 6
(WHERE)
“The difference BETWEEN the
right word and the almost right
word is the difference BETWEEN
lightning and a lightning bug. “
Mark Twain
Simple Retrieval Chapter 6
(WHERE)
The BETWEEN clause specifies a range of values to test
against the test expression.
test_expression [NOT] BETWEEN begin_value AND end_value
Simple Retrieval Chapter 6
(WHERE)
“All women become LIKE their
mothers. That is their tragedy. No
man does. That's his. “
Oscar Wilde
Simple Retrieval Chapter 6
(WHERE)
The LIKE clause performs character pattern matching.
match_expression [NOT] LIKE pattern
List the CustomerID and Phone number of customers that have a phone
number with area code (503).
Simple Retrieval Chapter 6
(WHERE)
The LIKE clause performs character pattern matching.
Wildcard character Description
% Any string of zero or more characters.
_ (underscore) Any single character.
[] Any single character within the specified range ([a-f]) or set ([abcdef]).
[^] Any single character not within the specified range ([^a-f]) or set ([^abcdef]).
Return all the Quarterly Productivity results:
WHERE Report LIKE ‘Q_ProductivityResult’
Simple Retrieval Chapter 6
(WHERE)
A word about NULL…
“The very impossibility in which I find
myself to prove that God is not,
discovers to me his existence.”
Voltaire
Simple Retrieval Chapter 6
(WHERE)
NULL implies that an attribute value has not been supplied. It
is not empty string or zero. It has no existence.
Permitting NULL “values” in your database introduces
ambiguity regarding the “existence” of a value.
Null can mean:
1. The value is unknown
2. The value is not appropriate
3. The value is known to be blank
Simple Retrieval Chapter 6
(WHERE)
DBMS’s provide a NULL function to check for NULL values.
In SQL Server this function is IS [NOT] NULL
List the Employee name of the employee who doesn’t report
to anyone.
Simple Retrieval Chapter 6
(WHERE)
List the Employee name of the employee who doesn’t report
to anyone. Should this be NULL?
What does it mean that Fuller has a Null value for ReportsTo?
Simple Retrieval Chapter 6
(WHERE)
“ORDER is never observed; it is disorder
that attracts attention because it is
awkward and intrusive.”
Eliphas Levi ”
Simple Retrieval Chapter 6
(ORDER BY)
Although the order of rows and columns is
unimportant in a “true” relation, the order of the
result set is.
ORDER BY specifies the sort for the result set.
ORDER BY { order_by_expression [ ASC | DESC ]
ORDER BY can sort on multiple columns.
Simple Retrieval Chapter 6
(ORDER BY)
Simple Retrieval Chapter 6
(ORDER BY)
Note that the sort fields don’t have to appear in the SELECT statement
Simple Retrieval Chapter 6
(ORDER BY)
The optimizer first did a scan of
the available indexes. SQL
Server automatically creates an
index on the primary key.
If the data are to be sorted by a
field frequently, you would want
to create and index on that field
to improve sorting performance.
Simple Retrieval Chapter 6
(Built-in Functions)
Also known as aggregate functions, these functions perform
standard calculations on rows or groups of rows of records.
Aggregate functions return a single value.
With the exception of COUNT, aggregate functions ignore
NULLs.
Some of the functions in SQL Server are:
AVG, COUNT, MAX, MIN, SUM
Simple Retrieval Chapter 6
(Built-in Functions)
Display the average product UnitPrice.
SELECT AVG(UnitPrice) AverageUnitPrice FROM Products
Display The number of orders placed by a specific customer.
Simple Retrieval Chapter 6
(Built-in Functions)
What is the total sales for a specific product?
Simple Retrieval Chapter 6
(Built-in Functions)
What is the highest and lowest UnitPrice in the Products
table?
Simple Retrieval Chapter 6
(Built-in Functions)
List the ProductID and it’s UnitPrice for the product that has
the highest UnitPrice.
Why doesn’t this work?
Simple Retrieval Chapter 6
(Built-in Functions)
Here’s a more challenging one.
List the average number of years the employees in the employee table with job_lvl
greater than 200 have been with the publisher (assuming no one has retired). That
is, compute the difference between the hire_date and the current date in years and
take the average.
Simple Retrieval Chapter 6
(GROUP BY)
It is often the case that we want to perform aggregate
functions on groups of records.
GROUP BY divides a table into groups. Groups can consist of
column names or results or computed columns.
For example, if you order the Products table in the Northwind
database, you can see that the rows can be group by
CategoryID
Simple Retrieval Chapter 6
(GROUP BY)
CategoryID can be used to
group the rows.
GROUP BY
enables us to
perform
operations on
those groups.
Simple Retrieval Chapter 6
(GROUP BY)
Count the number of products in each category in the
Products table.
Simple Retrieval Chapter 6
(HAVING)
The HAVING clause specifies a search condition for a group
or an aggregate.
HAVING is usually used with the GROUP BY clause. When
GROUP BY is not used, HAVING behaves like a WHERE
clause.
[ HAVING ]
Chapter 6
Simple Retrieval Chapter 6
(Date Functions)
“And summer's lease hath all too
short a date.”
William Shakespeare
BETWEEN Chapter 6
Use BETWEEN to pull rows in a given date range. For example, in the
Northwind database, list the product names of the products ordered in the
first week of January 1997.
SELECT PRODUCTNAME
FROM PRODUCTS
WHERE PRODUCTID IN
(SELECT PRODUCTID
FROM ORDERS, [ORDER DETAILS] ORDERDETAIL
WHERE ORDERS.ORDERID=ORDERDETAIL.ORDERID
AND ORDERDATE
BETWEEN '1997-01-01' AND '1997-01-07')
Date functions Chapter 6
But how to get this output?
List the orders in the month of January 1997 that shipped later than 10 days
from the order date.
ORDERID ORDERDATE SHIPPEDDATE Days
------- ---------------------- ----------------------- -------
10400 1997-01-01 00:00:00.000 1997-01-16 00:00:00.000 15
10405 1997-01-06 00:00:00.000 1997-01-22 00:00:00.000 16
10407 1997-01-07 00:00:00.000 1997-01-30 00:00:00.000 23
Date Functions Chapter 6
List the orders in the month of January 1997 that shipped later than 10 days
from the order date.
SELECT ORDERS.ORDERID, ORDERDATE, SHIPPEDDATE,
DATEDIFF(DAY,ORDERDATE, SHIPPEDDATE)
FROM ORDERS
WHERE ORDERDATE BETWEEN '1997-01-01' AND '1997-01-07' AND
DATEDIFF(DAY,ORDERDATE, SHIPPEDDATE) > 10
Syntax
DATEDIFF ( datepart , startdate , enddate )
How about this one? Chapter 6
For the year 1997, show the percentage of orders that shipped within 7 days
by month.
MONTH NUMOFORDERS NUMOFORDERS7 PercentbyMonth
----------- ----------- ------------ ------------------------
1 88 46 52.272725
2 83 51 61.445785
3 103 52 50.485438
4 105 58 55.238098
5 46 22 47.826087
6 30 19 63.333333
7 55 34 61.818182
8 58 40 68.965518
9 60 31 51.666665
10 64 44 68.75
11 59 34 57.627118
12 79 42 53.16456
DatePart Chapter 6
For the year 1997, show the percentage of orders that
shipped within 7 days by month.
SELECT T1.MONTH, NUMOFORDERS, NUMOFORDERS7,
CAST(NUMOFORDERS7 AS REAL)/CAST(NUMOFORDERS AS REAL)*100
PercentbyMonth
FROM (SELECT DATEPART(MONTH, ORDERDATE) MONTH, COUNT(*)
NUMOFORDERS
FROM ORDERS
GROUP BY DATEPART(MONTH, ORDERDATE)) T1,
(SELECT DATEPART(MONTH, ORDERDATE) MONTH, COUNT(*) NUMOFORDERS7
FROM ORDERS
WHERE DATEDIFF(DAY, ORDERDATE, SHIPPEDDATE) 300)
(P1=P2 S1=S1 400>200)
.
(FK Scan) .
.
(P1=P5 S1=S4 400>100)
Multi-table queries Chapter 6
(JOIN) 3 tables
SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName
FROM SUPPLIER, PART, SHIPMENT
WHERE PART.PartNumber = SHIPMENT.PartNumber AND
SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.Quantity 300)
(P1=P2 S2=S1 400>200)
.
(FK Scan) .
.
(P1=P5 S2=S4 400>100)
Multi-table queries Chapter 6
(JOIN) 3 tables
SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName
FROM SUPPLIER, PART, SHIPMENT
WHERE PART.PartNumber = SHIPMENT.PartNumber AND
SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.Quantity 300)
(P2=P2 S1=S1 400>200)
.
(FK Scan) .
.
(P2=P5 S1=S4 400>100)
Multi-table queries Chapter 6
(JOIN) 3 tables
SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName
FROM SUPPLIER, PART, SHIPMENT
WHERE PART.PartNumber = SHIPMENT.PartNumber AND
SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.Quantity 300)
(P2=P2 S2=S1 400>200)
.
(FK Scan) .
.
(P1=P5 S2=S4 400>100)
Multi-table queries Chapter 6
(JOIN) 3 tables
SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName
FROM SUPPLIER, PART, SHIPMENT
WHERE PART.PartNumber = SHIPMENT.PartNumber AND
SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.Quantity 300)
(P2=P2 S3=S1 400>200)
.
(FK Scan) .
.
(P1=P5 S3=S4 400>100)
Multi-table queries Chapter 6
(JOIN) 3 tables
SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName
FROM SUPPLIER, PART, SHIPMENT
WHERE PART.PartNumber = SHIPMENT.PartNumber AND
SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.Quantity = 200 AND
SUPPLIER.SupplierCity = PART.PartCity);
Multi-table queries Chapter 6
(JOIN) Let‟s recap...
For each row in the outer table (Parent), every row in the inner table (Child) is
scanned for a PK/FK match and any qualifying predicate.
In your WHERE clause, you must have a PK=FK statement for every
Parent/Child relationship involved in the query.
(Outer Table) (Inner Table)
Data FK Value
PK Value Data
Data FK Value
PK Value Data
(PK=FK Scan) Data FK Value
PK Value Data
Data FK Value
Data FK Value
Outer
Table
Inner
Table
Sub-Queries (nested) Chapter 6
Uncorrelated
So... Joins are really just the combination of the PRODUCT and SELECT
relational operators.
But in the case where you have a qualifying predicate such as PartNumber=„P2‟,
why do a PRODUCT of the outer table with the entire inner table?
Why not eliminate some of the rows from the inner table first with a simple
query and then do a PRODUCT of the resulting (and smaller) table?
Recall, that the result of a relational operation is a table. So we can Join the
outer table with the intermediate table resulting from the inner or sub-query.
Scan inner table
(Outer Table) (Inner Table)
for
Remove Data FK Value PartNumber = „P2‟
PK Value Data
non-qualifying
Data FK Value
PK Value Data
rows
Data FK Value
PK Value Data (Intermediate Table)
Data FK Value
Data FK Value
Data FK Value
Then Join Data FK Value
Outer with Data FK value
Intermediate
Sub-Queries (nested) Chapter 6
Introduction
• Nested queries can be either correlated or
uncorrelated
– correlated:inner query depends on row that is
currently being examined in the outer query
– uncorrelated: inner query performed independently
of outer query
• The nested or sub-query usually appears in the
WHERE clause of a query
• Sub-queries can also appear in the FROM and
HAVING clause
Sub-Queries (nested) Chapter 6
Introduction
• General structure of uncorrelated sub-query
SELECT Item
(Outer Query)
FROM table1
WHERE Item IN [NOT IN]
(SELECT Item
FROM table2 (Inner Query)
WHERE predicate);
Sub-Queries (nested) Chapter 6
Uncorrelated
Retrieve supplier names for suppliers who supply part P2
SELECT DISTINCTROW SUPPLIER.SupplierName
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT
WHERE SHIPMENT.PartNumber = 'P2');
Sub-Queries (nested) Chapter 6
Uncorrelated
SELECT DISTINCTROW SUPPLIER.SupplierName Outer Result
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT
WHERE SHIPMENT.PartNumber = 'P2');
Now join outer table to Execute inner query:
intermediate result Scan SHIPMENT for „P2‟
Intermediate
Sub-Queries (nested) Chapter 6
Uncorrelated
SELECT DISTINCTROW SUPPLIER.SupplierName Outer Result
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT
WHERE SHIPMENT.PartNumber = 'P2');
Now join outer table to
intermediate result
Intermediate
Sub-Queries (nested) Chapter 6
Uncorrelated
SELECT DISTINCTROW SUPPLIER.SupplierName
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT
WHERE SHIPMENT.PartNumber = 'P2');
How many row comparisons? If formulated as a join?
12 + (5 x 4) = 32 (5 x 12) = 60
Intermediate
Sub-Queries (nested) Chapter 6
Uncorrelated
SELECT DISTINCTROW SUPPLIER.SupplierName
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT
WHERE SHIPMENT.PartNumber = 'P2');
Formulated as a Join
SELECT DISTINCT SUPPLIER.SupplierName
FROM SUPPLIER, SHIPMENT
WHERE SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.PartNumber=‘P2’;
Use a join if you need columns from multiple tables. If, as in the example
above, you need columns from only one table, use either a join or a
subquery.
A subquery may include the GROUP BY and HAVING clauses, but not the
ORDER BY and UNION.
Sub-Queries (nested) Chapter 6
Uncorrelated (3 tables)
Retrieve supplier name and city for all suppliers who supply at least one
galvanized part.
SELECT DISTINCTROW SUPPLIER.SupplierName, SUPPLIER.SupplierCity
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT
WHERE SHIPMENT.PartNumber IN
(SELECT PART.PartNumber
FROM PART
WHERE METAL = 'GALV'));
Sub-Queries (nested) Chapter 6
Uncorrelated (3 tables)
SELECT DISTINCTROW SUPPLIER.SupplierName, SUPPLIER.SupplierCity
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT Inner-most query first
WHERE SHIPMENT.PartNumber IN
(SELECT PART.PartNumber
FROM PART PART is scanned for
WHERE METAL = 'GALV')); Metal=„GALV‟
Inner-most
Intermediate result
Sub-Queries (nested) Chapter 6
Uncorrelated (3 tables)
SELECT DISTINCTROW SUPPLIER.SupplierName, SUPPLIER.SupplierCity
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN Join SHIPMENT with Intermediate
(SELECT SHIPMENT.SupplierNumber result of inner query
FROM SHIPMENT
WHERE SHIPMENT.PartNumber IN
(SELECT PART.PartNumber
FROM PART Inner-most
WHERE METAL = 'GALV')); Intermediate result
Intermediate
SHIPMENT Result
Join
Sub-Queries (nested) Chapter 6
Uncorrelated (3 tables)
SELECT DISTINCTROW SUPPLIER.SupplierName, SUPPLIER.SupplierCity
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT Join SUPPLIER with Intermediate
WHERE SHIPMENT.PartNumber IN result of inner query
(SELECT PART.PartNumber
FROM PART Intermediate
WHERE METAL = 'GALV')); SHIPMENT Result
Join
Sub-Queries (nested) Chapter 6
Uncorrelated (3 tables)
SELECT DISTINCTROW SUPPLIER.SupplierName, SUPPLIER.SupplierCity
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT
WHERE SHIPMENT.PartNumber IN
(SELECT PART.PartNumber
FROM PART
WHERE METAL = 'GALV'));
In Summary...
(Join)
(Join)
Sub-Queries (nested) Chapter 6
Uncorrelated (3 tables)
SELECT DISTINCTROW SUPPLIER.SupplierName, SUPPLIER.SupplierCity
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT SUPPLIER PART
WHERE SHIPMENT.PartNumber IN
(SELECT PART.PartNumber
FROM PART
WHERE METAL = 'GALV'));
SHIPMENT
Written as a Join
SELECT DISTINCTROW SUPPLIER.SupplierName,
SUPPLIER.SupplierCity
FROM SUPPLIER, SHIPMENT, PART
WHERE SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.PartNumber = PART.PartNumber AND
PART.METAL = 'GALV';
Sub-Queries (nested) Chapter 6
Uncorrelated (3 tables)
SELECT DISTINCTROW SUPPLIER.SupplierName, SUPPLIER.SupplierCity
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SHIPMENT.SupplierNumber
FROM SHIPMENT
WHERE SHIPMENT.PartNumber IN
(SELECT PART.PartNumber
FROM PART
WHERE METAL = 'GALV'));
How many row comparisons for subquery?
6 + (2*12) + (3*5) = 45
As a Join? (Join)
6*5*12 = 360
(Join)
Sub-Queries (nested) Chapter 6
An alternative formulation
SELECT T1.Item …, T2.Item…
FROM table1 T1, (SELECT Item
FROM table2
WHERE predicate) As T2
WHERE T1.PK = T2.FK;
In this case, the inner table is filtered by the
predicate and then joined to the outer table using
the alias T2. This overcomes the shortcoming of the
IN method by allowing attributes from the inner
table to be included in the outer SELECT. See
handout for examples from class.
Sub-Queries (nested) Chapter 6
Introduction
• General structure of correlated sub-query
• Using Exists
SELECT Item
(Outer Query)
FROM table1
WHERE EXISTS [NOT EXISTS]
(SELECT Item
FROM table2 (Inner Query)
WHERE PK = FK AND
predicate);
Sub-Queries (nested) Chapter 6
Introduction
• General structure of correlated sub-query
• Using IN
SELECT Item (Outer Query)
FROM table1
WHERE predicate IN
(SELECT Item
(Inner Query)
FROM table2
WHERE PK = FK);
Sub-Queries (nested) Chapter 6
Correlated
Retrieve supplier names for suppliers who supply part P1
SELECT SUPPLIER.SupplierName
FROM SUPPLIER
WHERE EXISTS
(SELECT SHIPMENT.PartNumber
FROM SHIPMENT
WHERE SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.PartNumber = 'P1');
Sub-Queries (nested) Chapter 6
Correlated
Retrieve supplier names for suppliers who supply part P1
SELECT SUPPLIER.SupplierName
FROM SUPPLIER
WHERE EXISTS
(SELECT SHIPMENT.PartNumber
FROM SHIPMENT
WHERE SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.PartNumber = 'P1');
This is what makes it correlated.
The inner query is executed once for every row in the
SUPPLIER table. That is, the value for SupplierNumber
is passed by value into the sub-query.
Sub-Queries (nested) Chapter 6
Correlated
Retrieve supplier names for suppliers who supply part P1
SELECT SUPPLIER.SupplierName
FROM SUPPLIER Tests if intermediate result is
WHERE EXISTS nonempty (empty set)
(SELECT SHIPMENT.PartNumber
FROM SHIPMENT
WHERE SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.PartNumber = 'P1');
For each nonempty intermediate result, the SupplierName
is selected.
The EXISTS test will be performed for each row in the
SUPPLIER table.
Sub-Queries (nested) Chapter 6
Correlated
SELECT SUPPLIER.SupplierName
FROM SUPPLIER
WHERE EXISTS
(SELECT SHIPMENT.PartNumber
FROM SHIPMENT
WHERE SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.PartNumber = 'P1');
Execute nested query
EXISTS?
Yes
Intermediate
Sub-Queries (nested) Chapter 6
Correlated
SELECT SUPPLIER.SupplierName
FROM SUPPLIER
WHERE EXISTS
(SELECT SHIPMENT.PartNumber
FROM SHIPMENT
WHERE SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.PartNumber = 'P1');
Execute nested query
EXISTS?
Yes
Intermediate
Sub-Queries (nested) Chapter 6
Correlated
SELECT SUPPLIER.SupplierName
FROM SUPPLIER
WHERE EXISTS
(SELECT SHIPMENT.PartNumber
FROM SHIPMENT
WHERE SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.PartNumber = 'P1');
Execute nested query
EXISTS?
No
Intermediate
Sub-Queries (nested) Chapter 6
Correlated
SELECT SUPPLIER.SupplierName
FROM SUPPLIER
WHERE EXISTS
(SELECT SHIPMENT.PartNumber
FROM SHIPMENT
WHERE SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.PartNumber = 'P1');
EXISTS is
False for
S4 and S5
Intermediate
Sub-Queries (nested) Chapter 6
Correlated
Retrieve supplier names for suppliers who DO NOT supply part P1
SELECT SUPPLIER.SupplierName
FROM SUPPLIER
WHERE NOT EXISTS
(SELECT SHIPMENT.PartNumber
FROM SHIPMENT
WHERE SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND
SHIPMENT.PartNumber = 'P1');
Sub-Queries (nested) Chapter 6
Correlated
Using “IN”
SELECT DISTINCTROW SUPPLIER.SupplierName
FROM SUPPLIER
WHERE 'P1' IN
(SELECT SHIPMENT.PartNumber
FROM SHIPMENT
WHERE SUPPLIER.SupplierNumber =SHIPMENT.SupplierNumber);
It‟s still correlated.
But now, the intermediate result will be a list of
PartNumbers for the current Supplier.
If „P1‟ is IN that resulting set, the SupplierName is
selected.
Sub-Queries (nested) Chapter 6
Correlated
SELECT DISTINCTROW SUPPLIER.SupplierName
FROM SUPPLIER
WHERE 'P1' IN
(SELECT SHIPMENT.PartNumber
FROM SHIPMENT
WHERE SUPPLIER.SupplierNumber =SHIPMENT.SupplierNumber);
S1 S2 S3 S4
S5
Sub-Queries (nested) Chapter 6
Correlated (in HAVING clause)
Find the average Weight of PARTs for each City that has at least two parts.
SELECT P1.PartCity, AVG (Weight) AS Average
FROM PART P1
GROUP BY P1.PartCity
HAVING 1 < (SELECT COUNT(*)
FROM PART P2
WHERE P1.PartCity=P2.PartCity);
Only has 1
Sub-Queries (nested) Chapter 6
Correlated (in HAVING clause)
Find the average Weight of PARTs for each City that has at least two parts.
SELECT P1.PartCity, AVG (Weight) AS Average
FROM PART P1
GROUP BY P1.PartCity
HAVING 1 < (SELECT COUNT(*)
FROM PART P2
WHERE P1.PartCity=P2.PartCity);
A sub-query on the same table
Correlated with P1 cursor in the PART table
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART
table.
• As stated in the definition of the DIVISION
operator, this is a general operation for any
case where you want to know if a particular
instance in one table corresponds to every
instance in another table.
• For example
– List employees that have passed all the exams for
MS certification
– List students that have taken all courses for
graduation
Division
Page 206
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART
table.
• How would you do it procedurally?
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART
table.
An inner loop that scrolls
An outer loop that scrolls through Parts for every
through Suppliers row in Suppliers
Supplier Loop
Part Loop An inner-most loop that
scrolls through Shipment
Shipment Loop
for every row in Part
PK=FK? looking for PK=FK matches
End Loop
End Loop
End Loop
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Priming read (Supplier) Pseudo-code
While NOT EOF Supplier
Initialize exit condition to TRUE
Priming read (Part)
While (NOT EOF Part) AND (PK=FK found)
Set exit condition to FALSE
Priming read (Shipment)
While (NOT EOF Shipment) AND (NOT PK=FK found)
Evaluate PK=FK
Set found boolean
Move cursor (Shipment)
End While
Move cursor (Part)
End While
If supply all parts, print Supplier Info
Move cursor (Supplier)
End While
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
rstSupplier.MoveFirst
Do While Not rstSupplier.EOF Visual Basic
FoundPart = True Courtesy Dr. Landry*
rstParts.MoveFirst
Do While (Not rstParts.EOF) And FoundPart
FoundPart = False
rstShipment.MoveFirst
Do While Not rstShipment.EOF And (Not FoundPart)
FoundPart = (rstShipment.Fields("PartNumber") =
rstParts.Fields("PartNumber")) _
And (rstShipment.Fields("SupplierNumber") =
rstSupplier.Fields("SupplierNumber"))
rstShipment.MoveNext
Loop
rstParts.MoveNext
Loop
If FoundPart Then
With rstSupplier
picBox.Print .Fields("SupplierNumber"), .Fields("SupplierName"), .Fields("SupplierCity")
End With
End If
Loop * His original code was well documented...
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
SELECT SUPPLIER.SupplierNumber, SUPPLIER.SupplierName
SQL
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
Starting from the inner
(SELECT SupplierNumber
most query and working up...
FROM SHIPMENT S1
WHERE NOT EXISTS SHIPMENT, using cursor „S2‟, ...
(SELECT PART.PartNumber
FROM PART
is scanned once for every
WHERE NOT EXISTS
row in PART.
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
SELECT SUPPLIER.SupplierNumber, SUPPLIER.SupplierName
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
using cursor „S1‟.
FROM SHIPMENT S1
WHERE NOT EXISTS For every row in SHIPMENT, ...
(SELECT PART.PartNumber
FROM PART PART is scanned once, ...
WHERE NOT EXISTS
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
SELECT SUPPLIER.SupplierNumber, SUPPLIER.SupplierName
FROM SUPPLIER
is joined with SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
to retrieve SupplierName
(SELECT SupplierNumber
FROM SHIPMENT S1 The resulting set of
WHERE NOT EXISTS SupplierNumbers from
(SELECT PART.PartNumber SHIPMENT using „S1‟, ...
FROM PART
WHERE NOT EXISTS
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Uses two cursors into
SHIPMENT
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
FROM PART
WHERE NOT EXISTS
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Intermediate
SELECT SUPPLIER.SupplierNumber
result
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
P1
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(TRUE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P1=P1)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Intermediate
SELECT SUPPLIER.SupplierNumber
result
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
P1
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(FALSE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P2=P1)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Intermediate
SELECT SUPPLIER.SupplierNumber
result
FROM SUPPLIER
and so
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
P1 on...
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(FALSE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P3=P1)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Is Intermediate
result empty?
NOT(True) = False
Intermediate
SELECT SUPPLIER.SupplierNumber
result
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
P1
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(FALSE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P5=P1)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
So we don‟t retrieve
the PartNumber from
PART
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(FALSE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P5=P1)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Now we re-run the inner-
most query for the next
value in PART
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN and so
(SELECT SupplierNumber on...
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(FALSE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P1=P2)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Is Intermediate
result empty?
NOT(True) = False
Intermediate
SELECT SUPPLIER.SupplierNumber
result
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
P2
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(FALSE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P5=P2)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
So we don‟t retrieve
the PartNumber from
PART And this
cycle gets
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
repeated for
WHERE SUPPLIER.SupplierNumber IN every row in
(SELECT SupplierNumber PART
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(FALSE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P5=P2)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
So we don‟t retrieve
the PartNumber from
PART And this
cycle gets
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
repeated for
WHERE SUPPLIER.SupplierNumber IN every row in
(SELECT SupplierNumber PART
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(FALSE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P5=P6)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Once we‟ve searched
every row in PART,
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(FALSE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P5=P6)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
for the first row in
SHIPMENT, we evaluate
the WHERE clause
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
FROM PART
WHERE NOT EXISTS
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Did the inner query return
an empty set?
Intermediate
SELECT SUPPLIER.SupplierNumber
result
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
S1
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
FROM PART Yes. So retrieve the
WHERE NOT EXISTS SupplierNumber
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Now we move to the next
row in SHIPMENT
SELECT SUPPLIER.SupplierNumber And repeat
FROM SUPPLIER the entire
WHERE SUPPLIER.SupplierNumber IN
sub-query
(SELECT SupplierNumber
again.
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(TRUE)
FROM PART
WHERE NOT EXISTS
(S1=S1) AND (P1=P1)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
However... when we get to
this point in the execution
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
(FALSE)
FROM PART
WHERE NOT EXISTS
(S1=S2) AND (P1=P3)
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
there will be no instances
of (S2=S2) AND (P3=P3)
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER Intermediate
WHERE SUPPLIER.SupplierNumber IN
result
(SELECT SupplierNumber
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
So the innermost query does
FROM PART
WHERE NOT EXISTS
not return a PartNumber
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Is Intermediate
result empty?
NOT(False) = True
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER Intermediate
WHERE SUPPLIER.SupplierNumber IN
result
(SELECT SupplierNumber
FROM SHIPMENT S1
WHERE NOT EXISTS
(SELECT PART.PartNumber
FROM PART
Yes, so...
WHERE NOT EXISTS
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
the PART sub-query will return
the PartNumber that S2
doesn‟t supply
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER Intermediate
WHERE SUPPLIER.SupplierNumber IN
result
(SELECT SupplierNumber
FROM SHIPMENT S1
P3
WHERE NOT EXISTS
(SELECT PART.PartNumber
FROM PART
WHERE NOT EXISTS
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
At this point, the PART sub-
query will have returned the
set of Parts not supplied by S2.
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER Intermediate
WHERE SUPPLIER.SupplierNumber IN result
(SELECT SupplierNumber
FROM SHIPMENT S1 P3
WHERE NOT EXISTS
(SELECT PART.PartNumber P4
FROM PART
WHERE NOT EXISTS
P5
(SELECT PartNumber P6
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Once we‟ve searched
every row in PART,
Intermediate
result
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
P3
(SELECT SupplierNumber
P4
FROM SHIPMENT S1
WHERE NOT EXISTS P5
(SELECT PART.PartNumber
FROM PART
P6
WHERE NOT EXISTS
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
for the Supplier „S2‟, we
evaluate the WHERE
clause Intermediate
result
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
P3
(SELECT SupplierNumber
P4
FROM SHIPMENT S1
WHERE NOT EXISTS P5
(SELECT PART.PartNumber
FROM PART
P6
WHERE NOT EXISTS
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
Did the PART sub-query
return an empty set?
Intermediate
result
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
P3
(SELECT SupplierNumber
P4
FROM SHIPMENT S1
WHERE NOT EXISTS P5
(SELECT PART.PartNumber
FROM PART
P6
WHERE NOT EXISTS
(SELECT PartNumber
No. So don‟t retrieve
FROM SHIPMENT S2 the SupplierNumber „S2‟
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
S1 S2
And so finally... the
SupplierNumber result is
joined with SUPPLIER
SELECT SUPPLIER.SupplierNumber
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
S1
(SELECT SupplierNumber
FROM SHIPMENT S1 to get SupplierName
WHERE NOT EXISTS
(SELECT PART.PartNumber
FROM PART
WHERE NOT EXISTS
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber AND
S2.PartNumber = PART.PartNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
Retrieve all the suppliers that have shipped all parts in the PART table.
SELECT SUPPLIER.SupplierNumber, SUPPLIER.SupplierName
FROM SUPPLIER
WHERE SUPPLIER.SupplierNumber IN
(SELECT SupplierNumber An alternative formulation
FROM SHIPMENT S1 using IN
WHERE NOT EXISTS
(SELECT PART.PartNumber
FROM PART
WHERE PART.PartNumber NOT IN
(SELECT PartNumber
FROM SHIPMENT S2
WHERE S2.SupplierNumber = S1.SupplierNumber)));
Sub-Queries (nested) Chapter 6
Correlated (DIVISION)
List the companyname from the Suppliers table in the NorthWind database
for those suppliers who supply ALL products in Category 7.
UNION Chapter 6
Retrieve customer info for customers who have NOT had their order shipped
to their headquarters city (i.e., City=ShipCity).
• UNION stacks the results of one query onto a
second query
SELECT attribute(s)
FROM table
UNION
SELECT attribute(s)
FROM table;
You can have multiple UNIONs in one
query.
UNION Chapter 6
Here‟s an example from a team project
select Flt_source, Flt_Destination
into temp Creates a new table and inserts the
from Flight a resulting rows from the query into it.
where (a.Flt_source=‘MOB’) and (a.Flt_Destination=‘SJC’)
UNION
select a.Flt_source, a.Flt_Destination
from Flight a, Flight b
where ((a.Flt_Destination=b.Flt_Source)and
((a.Flt_source=‘MOB’) and (a.Flt_Destination=‘SJC’))
UNION
select b.Flt_source, b.Flt_Destination
from Flight a, Flight b
where ((a.Flt_Destination=b.Flt_Source) and
((a.Flt_source=‘MOB’) and (a.Flt_Destination=‘SJC’))
Create a combined mailing list for Employees,
UNION Customers, and Suppliers Chapter 6
INTERSECT Chapter 6
Intersection returns the rows that appear
in both queries
• INTERSECT is a set operator like
UNION
SELECT attribute(s)
FROM table
INTERSECT
SELECT attribute(s)
FROM table;
DIFFERENCE Chapter 6
• Difference returns the rows that do NOT
appear in both queries
• EXCEPT is a set operator like UNION and
INTERSECT
SELECT attribute(s)
FROM table
EXCEPT
SELECT attribute(s)
FROM table;
Computed Fields Chapter 6
Select all parts and compute shipping cost per unit. Assume cost is 0.001 per
unit of weight.
SELECT PART.PartNumber, WEIGHT*0.001 AS Cost
FROM PART;
Outer Joins Chapter 6
Returns rows where PK=FK AND non-matched rows in either or both of the
tables joined.
BASIC STRUCTURE:
SELECT ITEMS
FROM TABLE1 LEFT [RIGHT] OUTER JOIN TABLE2
ON TABLE1.PK=TABLE2.FK
Where LEFT refers to the table listed first (to the left of the OUTER JOIN clause).
Outer Joins Chapter 6
List all product names and the total quantity ordered for the first week of
January 1997. An inner join:
SELECT PRODUCTNAME, SUM(T1.QUANTITY) TOTAL
FROM PRODUCTS P,
(SELECT DISTINCT PRODUCTID, ORDERDATE, QUANTITY
FROM ORDERS O, [ORDER DETAILS] OD
WHERE O.ORDERID=OD.ORDERID AND ORDERDATE BETWEEN '1997-01-01' AND '1997-01-07') T1
WHERE P.PRODUCTID=T1.PRODUCTID
GROUP BY PRODUCTNAME
Outer Joins Chapter 6
List all product names and the total quantity ordered for the first week of
January 1997. Would result in:
PRODUCTNAME TOTAL
---------------------------------------- -----------
Aniseed Syrup 50
Boston Crab Meat 2
Chai 10
Chocolade 70
Flotemysost 75
Gnocchi di nonna Alice 70 This doesn‟t show ALL
Gudbrandsdalsost 15 productnames
Gumbär Gummibärchen 30
Inlagd Sill 5
Louisiana Fiery Hot Pepper Sauce 20
Maxilaku 60
Nord-Ost Matjeshering 18
Pavlova 21
Queso Cabrales 30
Rössle Sauerkraut 42
Singaporean Hokkien Fried Mee 40
Sir Rodney's Scones 30
Steeleye Stout 35
Thüringer Rostbratwurst 21
Tunnbröd 60
Vegie-spread 65
Outer Joins Chapter 6
Formulated as a LEFT OUTER JOIN:
SELECT PRODUCTNAME, SUM(T1.QUANTITY) TOTAL
FROM PRODUCTS P LEFT OUTER JOIN
(SELECT DISTINCT PRODUCTID, ORDERDATE, QUANTITY
FROM ORDERS O, [ORDER DETAILS] OD
WHERE O.ORDERID=OD.ORDERID AND ORDERDATE BETWEEN '1997-01-01' AND '1997-01-07') T1
ON P.PRODUCTID=T1.PRODUCTID
GROUP BY PRODUCTNAME
The basic structure is:
SELECT PRODUCTNAME, SUM(T1.QUANTITY) TOTAL
FROM PRODUCTS P LEFT OUTER JOIN T1 ON P.PK=T1.FK
GROUP BY PRODUCTNAME
Outer Joins Chapter 6
Formulated as a LEFT OUTER JOIN:
PRODUCTNAME TOTAL
---------------------------------------- -----------
Alice Mutton NULL
Aniseed Syrup 50
Boston Crab Meat 2
Camembert Pierrot
Carnarvon Tigers
NULL
NULL
Now we get ALL
Chai 10 productnames
Chang NULL
Chartreuse verte NULL
Chef Anton's Cajun Seasoning NULL
Chef Anton's Gumbo Mix NULL
Chocolade 70
Côte de Blaye NULL
Escargots de Bourgogne NULL
Filo Mix NULL
Flotemysost 75
Geitost NULL
Genen Shouyu NULL
Gnocchi di nonna Alice 70
Gorgonzola Telino NULL
Grandma's Boysenberry Spread NULL
Gravad lax NULL
Guaraná Fantástica NULL
{AND SO ON…}
Outer Joins Chapter 6
An outer join won‟t always solve the problem.
PRODUCTNAME TOTAL
---------------------------------------- -----------
Alice Mutton NULL
Aniseed Syrup 50
Boston Crab Meat 2
Camembert Pierrot NULL
Carnarvon Tigers NULL
Chai 10
Chang NULL
Chartreuse verte NULL
Chef Anton's Cajun Seasoning NULL
Chef Anton's Gumbo Mix NULL
Chocolade 70
Côte de Blaye NULL
Escargots de Bourgogne NULL
Filo Mix NULL
Flotemysost 75
Geitost NULL
Genen Shouyu NULL
Gnocchi di nonna Alice 70
Gorgonzola Telino NULL
Grandma's Boysenberry Spread NULL
Gravad lax NULL
Guaraná Fantástica NULL
{AND SO ON…}
Introduction to SQL Chapter 6
• To review:
• Four verbs
• SELECT, UPDATE, DELETE, INSERT
– Basic CRUD functionality
• Create - INSERT
• Read - SELECT
• Update - UPDATE
• Delete – DELETE
– So far we‟ve only looked at SELECT
– Before we do DML, let‟s look at DDL
Chapter 6
Data Definition Language
• DDL
– CREATE, DROP, ALTER
• The simplicity of the Verbs belie the
complexity of these commands, e.g.,
– Oracle8i
CREATE TABLE Chapter 6
• CREATE TABLE defines a base table in the
database schema.
• General structure of CREATE statement
CREATE TABLE TableName
(AttributeName Domain [Default][Constraint]
[, AttributeName Domain ...]
[other constraints])
[Optional parameters]
CREATE TABLE Chapter 6
CREATE TABLE TableName
(AttributeName Domain [Default][Constraint]
[, AttributeName Domain ...]
[other constraints])
• Domain :set of values an attribute can have
• SQL provides six “families” of elementary
domains
– Character (strings)
– Bit (0,1)
– Exact Numeric (integer, smallint, decimal ($))
– Approximate numeric (floating point)
– Date and Time
– Temporal intervals
• User Defined
CREATE TABLE Chapter 6
CREATE TABLE TableName
(AttributeName Domain [Default][Constraint]
[, AttributeName Domain ...]
[other constraints])
• [Default] the value that is assigned
automatically when the row is inserted into the
table unless specified in the INSERT INTO
statement.
CREATE TABLE Customer
(CustomerID char(5),
Status char(10) Default ‘Active’)
CREATE TABLE Chapter 6
CREATE TABLE TableName
(AttributeName Domain [Default][Constraint]
[, AttributeName Domain ...]
[other constraints])
• Intra-relational constraints [Constraint]
• For example
– NOT NULL
– UNIQUE
– PRIMARY KEY (implies NOT NULL and UNIQUE)
CREATE TABLE Customer
(CustomerID char(5) primary key,
CustomerName char(50) not null,
SocialSecurity Char(9) not null unique,
Status char(10) Default ‘Active’)
CREATE TABLE Chapter 6
CREATE TABLE TableName
(AttributeName Domain [Default][Constraint]
[, AttributeName Domain ...]
[other constraints])
• Compound primary key
CREATE TABLE Customer
(FirstName char(20),
LastName char(20) not null,
SocialSecurity Char(9) not null unique,
Status char(10) Default ‘Active’
primary key (FirstName, LastName)
CREATE TABLE Chapter 6
CREATE TABLE TableName
(AttributeName Domain [Default][Constraint]
[, AttributeName Domain ...]
[other constraints])
• Inter-relational constraints [other constraints]
• Referential Integrity
– references TableName(AttributeName)
– Foreign key (AtttributeName1, AttributeName2,...)
– references TableName(Attribute1, Attribute2,...)
CREATE TABLE Chapter 6
CREATE TABLE TableName
(AttributeName Domain [Default][Constraint]
[, AttributeName Domain ...]
[other constraints])
• Referential Integrity
CREATE TABLE Customer
(CustomerID char(5) primary key,
CustomerName char(50) not null,
ClerkID char(4)
references clerk(ClerkID),
Status char(10) Default ‘Active’)
CREATE TABLE Chapter 6
CREATE TABLE TableName
(AttributeName Domain [Default][Constraint]
[, AttributeName Domain ...]
[other constraints])
• Referential Integrity
CREATE TABLE Customer
(CustomerID char(5) primary key,
CustomerName char(50) not null,
DeptID char(4),
BuildingID char (5),
Status char(10) Default ‘Active’
foreign key (DeptID, BuildingID)
references DeptBuilding(DeptID,BuildingID))
DROP TABLE Chapter 6
• Schema Updates
DROP TABLE Customer
ALTER TABLE Chapter 6
• Allows modifications of table structure
ALTER TABLE TableName
alter column AttributeName
add constraint
drop constraint
add column
drop column;
Chapter 6
Introduction to SQL
• Data Manipulation Language (DML)
– SELECT, UPDATE, INSERT, DELETE
UPDATE Chapter 6
• General structure of UPDATE syntax
UPDATE table
SET attribute = expression [,attribute = expression]
WHERE predicate;
OR
UPDATE table
SET attribute = expression [,attribute = expression]
subquery;
[Optional parameters]
UPDATE Chapter 6
UPDATE table
SET attribute = expression [,attribute = expression]
WHERE predicate;
• The update command makes it possible to update
one or more attributes of the rows of table that
satisfy some condition.
• If no predicate is given, the operation is
performed on all rows.
UPDATE Chapter 6
UPDATE table
SET attribute = expression [,attribute = expression]
WHERE predicate;
• The new value can be one of the following:
– the result of a computed value on the attributes of
the table
– the result of an SQL query
– the null value
– or the default value for the domain
UPDATE Chapter 6
Set the credit limit of all customers to $10,000.
UPDATE Chapter 6
Set the credit limit of customers with Status ‘1’ to
$15,000.
UPDATE Chapter 6
Add $2,000 to the credit limit of those customers who
have placed more than 8 orders.
Partial
Database
Let‟s
Schema try something a little bit harder...
UPDATE Chapter 6
Add $2,000 to the credit limit of those customers who
have placed more than 8 orders.
(Correlated subquery)
UPDATE Chapter 6
Add $2,000 to the credit limit of those customers who
have placed more than 8 orders.
If there‟s a count for the current
customerid, then retrieve it in the
outer query
UPDATE Chapter 6
Add $2,000 to the credit limit of those customers who
have placed more than 8 orders.
Uses IN to identify the customerids
that need to be retrieved in the
outer query.
INSERT Chapter 6
• General structure of INSERT syntax
INSERT
INTO table [(attribute1, attribute2,...)]
VALUES(constant, constant,...);
OR
INSERT
INTO table [(attribute1, attribute2,...)]
subquery;
[Optional parameters]
INSERT Chapter 6
INSERT
INTO table [(attribute1, attribute2,...)]
VALUES(constant, constant,...);
• The left to right order of attribute and
constant must match.
• Attribute list is optional
– if not present, assumed entire list is being inserted
• Do not have to include all attributes in VALUES
list
INSERT Chapter 6
INSERT
INTO table [(attribute1, attribute2,...)]
VALUES(constant, constant,...);
• If a value (constant) is not provided
– uses default value, or failing this
– the NULL value
– If can‟t be NULL, insert is rejected
• Much better to always specify a value for each
attribute in embedded SQL
INSERT Chapter 6
INSERT
INTO table [(attribute1, attribute2,...)]
VALUES(constant, constant,...);
INSERT Chapter 6
subquery
INSERT INTO table [(column1, column2,...)]
subquery;
Notice that this comes from a
different table
INSERT Chapter 6
• The two forms of INSERT have two different
applications
• The first form is typically used in programs to
insert data from a user interface (a form).
• The second form is typically used to create
intermediate tables from data already in the
database.
DELETE Chapter 6
• General structure of DELETE syntax
DELETE
FROM table
[WHERE predicate]
OR
DELETE
FROM table
[subquery];
[Optional parameters]
DELETE Chapter 6
DELETE
FROM table
[WHERE predicate]
• Eliminates rows from a table based on a condition
• If the WHERE clause is not specified, the command
deletes all records.
• How are these two different?
DELETE FROM Customer1
DROP TABLE Customer1
• DELETE doesn‟t change schema
DELETE Chapter 6
DELETE
FROM table
[WHERE predicate]
How to delete the new records we
just entered?
DELETE Chapter 6
DELETE
FROM table
[subquery];