chapter 6 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Chapter 6 PowerPoint Presentation
Download Presentation
Chapter 6

Loading in 2 Seconds...

play fullscreen
1 / 186

Chapter 6 - PowerPoint PPT Presentation


  • 152 Views
  • Uploaded on

Chapter 6. Structured Query Language. 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. Introduction to SQL . IBM in the mid-1970s as SEQUEL

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 'Chapter 6' - Albert_Lan


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
chapter 6

Chapter 6

Structured Query Language

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

introduction to sql
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
introduction to sql4
Introduction to SQL
  • Four verbs
    • SELECT, UPDATE, DELETE, INSERT
    • Basic CRUD functionality
      • Create - INSERT
      • Read - SELECT
      • Update - UPDATE
      • Delete - DELETE
select
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 sql6
Introduction to SQL

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 select
Simple Retrieval (SELECT)

Retrieve rows from SUPPLIER for suppliers located in Finland

SELECT *

FROM SUPPLIER

WHERE SUPPLIER.SupplierCity = ‘FINLAND’;

simple retrieval select8
Simple Retrieval (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.

select10
SELECT

“What is known as success assumes nearly as many aliases as there are those who seek it.“

Stephen Birmingham

simple retrieval select11
Simple Retrieval (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 select12
Simple Retrieval (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 select13
Simple Retrieval (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 select14
Simple Retrieval (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 where
Simple Retrieval (WHERE)

The WHERE clause specifies a condition or conditions that restricts the rows return in the result set.

simple retrieval where16
Simple Retrieval (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 where17
Simple Retrieval (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 where18
Simple Retrieval (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 where19
Simple Retrieval (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 where20
Simple Retrieval (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 where21
Simple Retrieval (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 where22
Simple Retrieval (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 where23
Simple Retrieval (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 where24
Simple Retrieval (WHERE)

“All women become LIKE their mothers. That is their tragedy. No man does. That's his. “

Oscar Wilde

simple retrieval where25
Simple Retrieval (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 where26
Simple Retrieval (WHERE)

The LIKE clause performs character pattern matching.

Return all the Quarterly Productivity results:

WHERE Report LIKE ‘Q_ProductivityResult’

simple retrieval where27
Simple Retrieval (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 where28
Simple Retrieval (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:
  • The value is unknown
  • The value is not appropriate
  • The value is known to be blank
simple retrieval where29
Simple Retrieval (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 where30
Simple Retrieval (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 where31
Simple Retrieval (WHERE)

“ORDER is never observed; it is disorder that attracts attention because it is awkward and intrusive.”

Eliphas Levi ”

simple retrieval order by
Simple Retrieval (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 order by34
Simple Retrieval (ORDER BY)

Note that the sort fields don’t have to appear in the SELECT statement

simple retrieval order by35
Simple Retrieval (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 built in functions
Simple Retrieval (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 built in functions37
Simple Retrieval (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 built in functions38
Simple Retrieval (Built-in Functions)

What is the total sales for a specific product?

simple retrieval built in functions39
Simple Retrieval (Built-in Functions)

What is the highest and lowest UnitPrice in the Products table?

simple retrieval built in functions40
Simple Retrieval (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 built in functions41
Simple Retrieval (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 group by
Simple Retrieval (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 group by43
Simple Retrieval (GROUP BY)

CategoryID can be used to group the rows.

GROUP BY enables us to perform operations on those groups.

simple retrieval group by44
Simple Retrieval (GROUP BY)

Count the number of products in each category in the Products table.

simple retrieval having
Simple Retrieval (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 < search_condition > ]

simple retrieval date functions
Simple Retrieval (Date Functions)

“And summer's lease hath all too short a date.”

William Shakespeare

between
BETWEEN

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
Date functions

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 functions50
Date Functions

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
How about this one?

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
DatePart

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

GROUP BY DATEPART(MONTH, ORDERDATE)) T2

WHERE T1.MONTH=T2.MONTH

Syntax

DATEPART ( datepart , date )

division
Division

“If an integer dividend is divided by an integer divisor, the result is an integer that has any fractional part of the result truncated.”

Therefore, we had to CAST the dividend and divisor as REAL before doing the division.

SELECT T1.MONTH, NUMOFORDERS, NUMOFORDERS7,

CAST(NUMOFORDERS7 AS REAL)/CAST(NUMOFORDERS AS REAL) PercentbyMonth

simple retrieval project
Simple Retrieval (PROJECT)

Retrieve a list of Metals used in parts

SELECT PART.Metal

FROM PART;

Is this the correct result?

simple retrieval project55

Removed duplicate

Simple Retrieval (PROJECT)

Retrieve a list of Metals used in parts

SELECT DISTINCT PART.Metal

FROM PART;

multi table queries join 2 tables
Multi-table queries(JOIN) 2 tables

Retrieve the part name for all parts shipped in quantities equal to 200

SELECT DISTINCT PART.PartName

FROM PART, SHIPMENT

WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

multi table queries join 2 tables57

Conjunct #1 is a PK FK comparison

Conjunct #2 qualifies retrieval

Multi-table queries(JOIN) 2 tables

So how does this work?

SELECT DISTINCT PART.PartName

FROM PART, SHIPMENT

WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

multi table queries join 2 tables58
Multi-table queries(JOIN) 2 tables

SELECT DISTINCT PART.PartName

FROM PART, SHIPMENT

WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

Conceptually, we can understand the execution of the query in terms of row scans.

A Scan is a sequential inspection of many rows for the purpose of returning rows that meet a criteria.

The following demonstrations are a simplified version of the Nested Loop technique (as opposed to “merge sort” and “hashed join”)

multi table queries join 2 tables59

PK/FK comparison requires one scan of the FK set for each PK in the parent table.

Row is included in the view where PK=FK and Qty = 200

Multi-table queries(JOIN) 2 tables

SELECT DISTINCT PART.PartName

FROM PART, SHIPMENT

WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

multi table queries join 2 tables60
Multi-table queries(JOIN) 2 tables

SELECT DISTINCT PART.PartName

FROM PART, SHIPMENT

WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

(Scan 1)

(No Matches: TT)

(P1=P1  300=200)

(P1=P2  200=200)

.

.

.

(FK Scan)

(P1=P5  100=200)

multi table queries join 2 tables61

This removes duplicates in results

Multi-table queries(JOIN) 2 tables

SELECT DISTINCT PART.PartName

FROM PART, SHIPMENT

WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

(Scan 2)

(Two Matches: TT)

(P2=P1  300=200)

(P2=P2  200=200)

.

.

.

(FK Scan)

(P2=P5  100=200)

multi table queries join 2 tables62
Multi-table queries(JOIN) 2 tables

SELECT DISTINCT PART.PartName

FROM PART, SHIPMENT

WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

(Scan 3)

( No Matches: TT)

(P3=P1  300=200)

(P3=P2  200=200)

.

.

.

(FK Scan)

(P3=P5  100=200)

multi table queries join 2 tables63
Multi-table queries(JOIN) 2 tables

SELECT DISTINCT PART.PartName

FROM PART, SHIPMENT

WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

(Scan 4)

(And So On...)

( Two Matches: TT)

(P4=P1  300=200)

(P4=P2  200=200)

.

.

.

(FK Scan)

(P4=P5  100=200)

multi table queries join 2 tables64
Multi-table queries(JOIN) 2 tables

SELECT DISTINCT PART.PartName

FROM PART, SHIPMENT

WHERE PART.PartNumber = SHIPMENT.PartNumber AND SHIPMENT.quantity = 200;

(Scan 6)

How many row comparisons?

rows(PK-table) X rows(FK-table)

6 X 12 = 72

multi table queries join evaluation techniques
Multi-table queriesJOIN Evaluation Techniques
  • Three common methods used by DBMS optimizers to evaluate Joins
    • Nested Loop
    • Merge Scan
    • Hash Join
multi table queries join evaluation techniques66
Multi-table queriesJOIN Evaluation Techniques
  • Nested Loop
    • Essentially the preceding demo
    • One table defined as external and one table defined as internal
        • (External:Internal)
        • (1:M)
        • (Parent:Child)
    • If there isn’t an index on the FK, the internal table has to be opened for a scan for every row of the external table
multi table queries join evaluation techniques67
Multi-table queriesJOIN Evaluation Techniques
  • Merge Scan
    • Both tables have to be ordered by PK/FK
    • Parallel scans are executed on both tables
    • Qualifying rows are found by merging the order lists into groups or partitions
multi table queries join evaluation techniques68
Multi-table queriesJOIN Evaluation Techniques
  • Hash Join
    • Both tables are stored using hash function on join attributes (PK/FK)
    • Execute join on each partition
multi table queries join 3 tables
Multi-table queries(JOIN) 3 tables

Retrieve supplier name and part name for parts shipped in quantities less than 400.

SELECT DISTINCT SUPPLIER.SupplierName, PART.PartName

FROM SUPPLIER, PART, SHIPMENT

WHERE PART.PartNumber = SHIPMENT.PartNumber AND

SUPPLIER.SupplierNumber = SHIPMENT.SupplierNumber AND

SHIPMENT.Quantity < 400;

multi table queries join 3 tables70
Multi-table queries(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 < 400;

(Scan 1)

(One Match: TT T)

(P1=P1  S1=S1  400>300)

(P1=P2  S1=S1  400>200)

.

.

.

(FK Scan)

(P1=P5  S1=S4  400>100)

multi table queries join 3 tables71
Multi-table queries(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 < 400;

(Scan 2)

(No Matches: TT T)

(P1=P1  S2=S1  400>300)

(P1=P2  S2=S1  400>200)

.

.

.

(FK Scan)

(P1=P5  S2=S4  400>100)

multi table queries join 3 tables72

So we finish out the “P1” Supplier scan with no matches

Multi-table queries(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 < 400;

(Scan 2-5)

(No Matches: TT T)

.

.

.

multi table queries join 3 tables73
Multi-table queries(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 < 400;

(Scan 6)

(One Match: TT T)

(P2=P1  S1=S1  400>300)

(P2=P2  S1=S1  400>200)

.

.

.

(FK Scan)

(P2=P5  S1=S4  400>100)

multi table queries join 3 tables74
Multi-table queries(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 < 400;

(Scan 7)

(One Match: TT T)

(P2=P1  S2=S1  400>300)

(P2=P2  S2=S1  400>200)

.

.

.

(FK Scan)

(P1=P5  S2=S4  400>100)

multi table queries join 3 tables75
Multi-table queries(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 < 400;

(Scan 8)

(And so on...)

(One Match: TT T)

(P2=P1  S3=S1  400>300)

(P2=P2  S3=S1  400>200)

.

.

.

(FK Scan)

(P1=P5  S3=S4  400>100)

multi table queries join 3 tables76
Multi-table queries(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 < 400;

How many row comparisons?

6x5x12 = 360

multi table queries join 3 tables77
Multi-table queries(JOIN) 3 tables

Retrieve the supplier name and part name for all shipments with quantity greater than or equal to 200 for which the warehouse is located in the same city as the supplier

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 join let s recap

(Inner Table)

(Outer 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

Multi-table queries(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.

sub queries nested uncorrelated

Scan inner table

for

PartNumber = ‘P2’

(Inner Table)

(Outer Table)

Remove

non-qualifying rows

Data

FK Value

PK Value

Data

Data

FK Value

PK Value

Data

Data

FK Value

PK Value

Data

(Intermediate Table)

Data

FK Value

Data

FK Value

Data

FK Value

Then Join Outer with Intermediate

Data

FK Value

Data

FK value

Sub-Queries (nested)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.

sub queries nested introduction
Sub-Queries (nested)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 introduction81
Sub-Queries (nested)Introduction
  • General structure of uncorrelated sub-query

SELECT Item

FROM table1

WHERE Item IN [NOT IN]

(SELECT Item

FROM table2

WHERE predicate);

(Outer Query)

(Inner Query)

sub queries nested uncorrelated82
Sub-Queries (nested)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 uncorrelated83

Outer Result

Now join outer table to intermediate result

Intermediate

Sub-Queries (nested)Uncorrelated

SELECT DISTINCTROW SUPPLIER.SupplierName

FROM SUPPLIER

WHERE SUPPLIER.SupplierNumber IN

(SELECT SHIPMENT.SupplierNumber

FROM SHIPMENT

WHERE SHIPMENT.PartNumber = 'P2');

Execute inner query:

Scan SHIPMENT for ‘P2’

sub queries nested uncorrelated84

Outer Result

Now join outer table to intermediate result

Intermediate

Sub-Queries (nested)Uncorrelated

SELECT DISTINCTROW SUPPLIER.SupplierName

FROM SUPPLIER

WHERE SUPPLIER.SupplierNumber IN

(SELECT SHIPMENT.SupplierNumber

FROM SHIPMENT

WHERE SHIPMENT.PartNumber = 'P2');

sub queries nested uncorrelated85

Intermediate

Sub-Queries (nested)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

sub queries nested uncorrelated86
Sub-Queries (nested)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 uncorrelated 3 tables
Sub-Queries (nested)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 uncorrelated 3 tables88

Inner-most query first

PART is scanned for Metal=‘GALV’

Inner-most Intermediate result

Sub-Queries (nested)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'));

sub queries nested uncorrelated 3 tables89

Join SHIPMENT with Intermediate result of inner query

Inner-most Intermediate result

Intermediate

SHIPMENT Result

Join

Sub-Queries (nested)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'));

sub queries nested uncorrelated 3 tables90

Intermediate SHIPMENT Result

Join

Sub-Queries (nested)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'));

Join SUPPLIER with Intermediate result of inner query

sub queries nested uncorrelated 3 tables91

(Join)

(Join)

Sub-Queries (nested)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...

sub queries nested uncorrelated 3 tables92

SUPPLIER

PART

SHIPMENT

Sub-Queries (nested)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'));

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 uncorrelated 3 tables93
Sub-Queries (nested)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

(Join)

As a Join?

6*5*12 = 360

(Join)

sub queries nested an alternative formulation
Sub-Queries (nested)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 introduction95
Sub-Queries (nested)Introduction
  • General structure of correlated sub-query
  • Using Exists

SELECT Item

FROM table1

WHERE EXISTS [NOT EXISTS]

(SELECT Item

FROM table2

WHERE PK = FK AND

predicate);

(Outer Query)

(Inner Query)

sub queries nested introduction96
Sub-Queries (nested)Introduction
  • General structure of correlated sub-query
  • Using IN

SELECT Item

FROM table1

WHERE predicate IN

(SELECT Item

FROM table2

WHERE PK = FK);

(Outer Query)

(Inner Query)

sub queries nested correlated
Sub-Queries (nested)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 correlated98

This is what makes it correlated.

Sub-Queries (nested)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');

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 correlated99

Tests if intermediate result is nonempty (empty set)

Sub-Queries (nested)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');

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 correlated100

Intermediate

Sub-Queries (nested)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

sub queries nested correlated101

Intermediate

Sub-Queries (nested)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

sub queries nested correlated102

Intermediate

Sub-Queries (nested)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

sub queries nested correlated103

Intermediate

Sub-Queries (nested)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

sub queries nested correlated104
Sub-Queries (nested)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 correlated105
Sub-Queries (nested)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 correlated106

S1

S2

S3

S4

S5

Sub-Queries (nested)Correlated

SELECT DISTINCTROW SUPPLIER.SupplierName

FROM SUPPLIER

WHERE 'P1' IN

(SELECT SHIPMENT.PartNumber

FROM SHIPMENT

WHERE SUPPLIER.SupplierNumber =SHIPMENT.SupplierNumber);

sub queries nested correlated in having clause

Only has 1

Sub-Queries (nested)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);

sub queries nested correlated in having clause108

A sub-query on the same table

Correlated with P1 cursor in the PART table

Sub-Queries (nested)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);

sub queries nested correlated division
Sub-Queries (nested)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
division110
Division

Page 206

sub queries nested correlated division111
Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

  • How would you do it procedurally?
sub queries nested correlated division112

An inner loop that scrolls through Parts for every row in Suppliers

An outer loop that scrolls through Suppliers

Supplier Loop

An inner-most loop that scrolls through Shipment for every row in Part looking for PK=FK matches

Part Loop

Shipment Loop

PK=FK?

End Loop

End Loop

End Loop

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

sub queries nested correlated division113
Sub-Queries (nested)Correlated (DIVISION)

Priming read (Supplier)

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

Pseudo-code

sub queries nested correlated division114
Sub-Queries (nested)Correlated (DIVISION)

rstSupplier.MoveFirst

Do While Not rstSupplier.EOF

FoundPart = True

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

Visual Basic

Courtesy Dr. Landry*

* His original code was well documented...

sub queries nested correlated division115

SHIPMENT,

using cursor ‘S2’, ...

is scanned once for every row in PART.

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

SQL

SELECT SUPPLIER.SupplierNumber, SUPPLIER.SupplierName

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

Starting from the inner most query and working up...

sub queries nested correlated division116

using cursor ‘S1’.

For every row in SHIPMENT, ...

PART is scanned once, ...

Sub-Queries (nested)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

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 correlated division117

is joined with SUPPLIER

to retrieve SupplierName

The resulting set of SupplierNumbers from SHIPMENT using ‘S1’, ...

Sub-Queries (nested)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

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 correlated division118

S1

S2

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

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 correlated division119

Intermediate result

P1

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

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

(TRUE)

(S1=S1) AND (P1=P1)

sub queries nested correlated division120

Intermediate result

P1

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

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

(FALSE)

(S1=S1) AND (P2=P1)

sub queries nested correlated division121

Intermediate result

and so on...

P1

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

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

(FALSE)

(S1=S1) AND (P3=P1)

sub queries nested correlated division122

Is Intermediate result empty?

NOT(True) = False

Intermediate result

P1

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

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

(FALSE)

(S1=S1) AND (P5=P1)

sub queries nested correlated division123

So we don’t retrieve the PartNumber from PART

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

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

(FALSE)

(S1=S1) AND (P5=P1)

sub queries nested correlated division124

and so on...

Sub-Queries (nested)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

(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)));

(FALSE)

(S1=S1) AND (P1=P2)

sub queries nested correlated division125

Is Intermediate result empty?

NOT(True) = False

Intermediate result

P2

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

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

(FALSE)

(S1=S1) AND (P5=P2)

sub queries nested correlated division126

So we don’t retrieve the PartNumber from PART

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

And this cycle gets repeated for every row in PART

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

(FALSE)

(S1=S1) AND (P5=P2)

sub queries nested correlated division127

So we don’t retrieve the PartNumber from PART

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

And this cycle gets repeated for every row in PART

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

(FALSE)

(S1=S1) AND (P5=P6)

sub queries nested correlated division128

Once we’ve searched every row in PART,

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

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

(FALSE)

(S1=S1) AND (P5=P6)

sub queries nested correlated division129
Sub-Queries (nested)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 correlated division130

Intermediate result

S1

Sub-Queries (nested)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?

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

Yes. So retrieve the SupplierNumber

sub queries nested correlated division131

And repeat the entire sub-query again.

(TRUE)

(S1=S1) AND (P1=P1)

Sub-Queries (nested)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

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 correlated division132
Sub-Queries (nested)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

FROM PART

WHERE NOT EXISTS

(SELECT PartNumber

FROM SHIPMENT S2

WHERE S2.SupplierNumber = S1.SupplierNumber AND

S2.PartNumber = PART.PartNumber)));

(FALSE)

(S1=S2) AND (P1=P3)

sub queries nested correlated division133

Intermediate result

Sub-Queries (nested)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

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

So the innermost query does not return a PartNumber

sub queries nested correlated division134

Is Intermediate result empty?

NOT(False) = True

Intermediate result

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

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

Yes, so...

sub queries nested correlated division135
Sub-Queries (nested)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

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

Intermediate result

P3

sub queries nested correlated division136

Intermediate result

P3

P4

P5

P6

Sub-Queries (nested)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

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 correlated division137

Once we’ve searched every row in PART,

Intermediate result

P3

P4

P5

P6

Sub-Queries (nested)Correlated (DIVISION)

Retrieve all the suppliers that have shipped all parts in the PART table.

S1

S2

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 correlated division138

Intermediate result

P3

P4

P5

P6

Sub-Queries (nested)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

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 correlated division139

Intermediate result

P3

P4

P5

P6

Sub-Queries (nested)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?

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

No. So don’t retrieve the SupplierNumber ‘S2’

sub queries nested correlated division140

to get SupplierName

Sub-Queries (nested)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

(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)));

S1

sub queries nested correlated division141
Sub-Queries (nested)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

FROM SHIPMENT S1

WHERE NOT EXISTS

(SELECT PART.PartNumber

FROM PART

WHERE PART.PartNumber NOT IN

(SELECT PartNumber

FROM SHIPMENT S2

WHERE S2.SupplierNumber = S1.SupplierNumber)));

An alternative formulation using IN

sub queries nested correlated division142
Sub-Queries (nested)Correlated (DIVISION)

List the companyname from the Suppliers table in the NorthWind database for those suppliers who supply ALL products in Category 7.

union
UNION

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.

union144

Creates a new table and inserts the resulting rows from the query into it.

Here’s an example from a team project

UNION

select Flt_source, Flt_Destination

into temp

from Flight a

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

intersect
INTERSECT

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
DIFFERENCE
  • 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
Computed Fields

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
Outer Joins

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 joins150
Outer Joins

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 joins151
Outer Joins

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

Gudbrandsdalsost 15

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

This doesn’t show ALL productnames

outer joins152
Outer Joins

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 joins153
Outer Joins

Formulated as a LEFT OUTER JOIN:

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…}

Now we get ALL productnames

outer joins154
Outer Joins

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 sql155
Introduction to SQL
  • 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
data definition language
Data Definition Language
  • DDL
    • CREATE, DROP, ALTER
  • The simplicity of the Verbs belie the complexity of these commands, e.g.,
    • Oracle8i
create table
CREATE TABLE
  • 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 table158
CREATE TABLE

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 table159
CREATE TABLE

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 table160
CREATE TABLE

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 table161
CREATE TABLE

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 table162
CREATE TABLE

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 table163
CREATE TABLE

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 table164
CREATE TABLE

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
DROP TABLE
  • Schema Updates

DROP TABLE Customer

alter table
ALTER TABLE
  • Allows modifications of table structure

ALTER TABLE TableName

alter column AttributeName

add constraint

drop constraint

add column

drop column;

introduction to sql167
Introduction to SQL
  • Data Manipulation Language (DML)
    • SELECT, UPDATE, INSERT, DELETE
update
UPDATE
  • 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]

update169
UPDATE

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.
update170
UPDATE

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
update171
UPDATE

Set the credit limit of all customers to $10,000.

update172
UPDATE

Set the credit limit of customers with Status ‘1’ to $15,000.

update173

Partial Database Schema

UPDATE

Add $2,000 to the credit limit of those customers who have placed more than 8 orders.

Let’s try something a little bit harder...

update174

(Correlated subquery)

UPDATE

Add $2,000 to the credit limit of those customers who have placed more than 8 orders.

update175
UPDATE

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

update176
UPDATE

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
INSERT
  • General structure of INSERT syntax

INSERT

INTO table [(attribute1, attribute2,...)]

VALUES(constant, constant,...);

OR

INSERT

INTO table [(attribute1, attribute2,...)]

subquery;

[Optional parameters]

insert178
INSERT

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
insert179
INSERT

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
insert180
INSERT

INSERT

INTO table [(attribute1, attribute2,...)]

VALUES(constant, constant,...);

insert subquery

Notice that this comes from a different table

INSERTsubquery

INSERT INTO table [(column1, column2,...)]

subquery;

insert182
INSERT
  • 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
DELETE
  • General structure of DELETE syntax

DELETE

FROM table

[WHERE predicate]

OR

DELETE

FROM table

[subquery];

[Optional parameters]

delete184
DELETE

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
delete185
DELETE

DELETE

FROM table

[WHERE predicate]

How to delete the new records we just entered?

delete186
DELETE

DELETE

FROM table

[subquery];