SQL Joins, Subqueries, and Programming Techniques, Slides of Introduction to Database Management Systems

Download SQL Joins, Subqueries, and Programming Techniques and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Database Management Systems

1

Objectives

• How can SQL be used to answer more complex questions?
• Why are some business questions more difficult than others?
• What common uses for subqueries?
• How do you find something that did not happen?
• What are correlated subqueries?
• What tricky problems arise and how do you handle them in SQL?
• What are the SQL data definition commands?
• What SQL commands alter the data stored in tables?
• How do you know if your query is correct?

2

Organization

• Harder Questions

• Subqueries

• Not In, LEFT JOIN

• UNION, Multiple JOIN columns, Recursive JOIN

• Other SQL Commands

– DDL: Data Definition Language

– DML: Data Manipulation Language

• OLAP

– Microsoft SQL Server

– Oracle

– Microsoft Access Crosstab

4

Harder Questions

• Which cats sold for more than the

average sale price of cats?

• List all customers who purchased one

of the following items (1, 2, 30, 32,

• List all customers who bought items

for cats.

• List all of the customers who bought

something in March and who bought

something in May. (Two tests on the

same data!)

• Lists animals that sold for more than

the sale price of the cheapest cat.

• Has one salesperson made all of the

sales on a particular day?

• Which animals sold for more than the

average price of similar animals?

• List all the managers and their direct

reports.

• Convert age ranges into categories.
• Classify payments by number of days

late.

• Which employees have sold animals

from every category?

• List customers who bought dogs and

also bought cat products.

5

Query Sets (IN)

List all customers (Name) who purchased one of the following items: 1, 2, 30, 32, 33.

7

SELECT Customer.LastName, Customer.FirstName, SaleItem.ItemID FROM (Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID WHERE (SaleItem.ItemID In (1,2,30,32,33)) ORDER BY Customer.LastName, Customer.FirstName;

Query05_Fig

SaleID SaleDate EmployeeID CustomerID

Field LastName FirstName ItemID Table Customer Customer SaleItem

Sort Ascending Ascending Criteria In (1,2,30,32,33) Or

CustomerID Phone FirstName LastName

Customer Sale

SaleID ItemID Quantity SalePrice

SaleItem

Using IN with a Sub-query

List all customers who bought items for cats.

8

SELECT Customer.LastName, Customer.FirstName, SaleItem.ItemID FROM (Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerID) INNER JOIN SaleItem ON Sale.SaleID = SaleItem.SaleID WHERE (SaleItem.ItemID In (SELECT ItemID FROM Merchandise WHERE Category=‘Cat’) );

Query05_Fig

Separate Lists with Joined Views

10

CREATE VIEW MarchCustomers AS

SELECT CustomerID

FROM Sale

WHERE (SaleDate Between 3/1/2007 And 3/31/2007);

CREATE VIEW MayCustomers AS

SELECT CustomerID

FROM Sale

WHERE (SaleDate Between 5/1/2007 And 5/31/2007);

SELECT Customer.LastName, Customer.FirstName

FROM Customer

INNER JOIN MarchCustomers ON

Customer.CustomerID=MarchCustomers.CustomerID

INNER JOIN MayCustomers ON

MarchCustomers.CustomerID=MayCustomers.CustomerID;

Query05_Fig

SubQuery (ANY, ALL)

Any: value is compared to each item in the list. If it is True for any of

the items, the statement is evaluated to True.

All: value is compared to each item in the list. If it is True for every item

in the list, the statement is evaluated to True (much more restrictive than any.

11

SELECT Animal.AnimalID, Name, SalePrice, ListPrice

FROM Animal

INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID

WHERE (SalePrice > ANY

(SELECT ListPrice

FROM Animal

INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID

WHERE Category="Cat"))

AND (Category="Cat")

Query05_Fig

SubQuery: NOT IN (Data)

13

ID Name Category Breed 2 Fish Angel 4 Gary Dog Dalmation 5 Fish Shark 6 Rosie Cat Oriental Shorthair 7 Eugene Cat Bombay 8 Miranda Dog Norfolk Terrier 9 Fish Guppy 10 Sherri Dog Siberian Huskie 11 Susan Dog Dalmation 12 Leisha Dog Rottweiler

ID SaleID SalePrice 2 35 $10. 4 80 $156. 6 27 $173. 7 25 $251. 8 4 $183. 10 18 $150. 11 17 $148.

Animal SaleAnimal

Which animals have not been sold?

Left Outer Join

AnimalID 2 4 12 19 25 34 88 181

Name

Simon Leisha Gene Vivian Rhonda Brandy

Category Fish Dog Dog Dog Dog Dog Dog Fish

14

SELECT Animal.AnimalID, Animal.Name, Animal.Category FROM Animal LEFT JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE (SaleAnimal.SaleID Is Null);

SaleID AnimalID SalePrice

Field AnimalID SaleID Name Category

Table Animal SaleAnimal Animal Animal

Sort

Criteria Is Null

Or

AnimalID Name Category Breed

Animal SaleAnimal

Query05_Fig

Which animals have not been sold?

LEFT JOIN includes all rows from left table (Animal)

But only those from right table (SaleAnimal) that match a row in

Animal.

Rows in Animal without matching data in Sale Animal will haveDocsity.com

Older Syntax for Left Join

• Which animals have not been sold?

16

SELECT ALL

FROM Animal, SaleAnimal

WHERE Animal.AnimalID *= SaleAnimal.AnimalID

And SaleAnimal.SaleID Is Null;

SELECT ALL

FROM Animal, SaleAnimal

WHERE Animal.AnimalID = SaleAnimal.AnimalID (+)

And SaleAnimal.SaleID Is Null;

Old Oracle syntax— note that the (+) symbol is on the reversed side.

SubQuery for Computation

• Don’t know the average, so use a subquery to look it up.
• Watch parentheses.

17

SELECT SaleAnimal.AnimalID, Animal.Category, SaleAnimal.SalePrice FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE ((Animal.Category=‘ Cat’ ) AND (SaleAnimal.SalePrice> ( SELECT AVG(SalePrice) FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE (Animal.Category=‘ Cat ’) ) ) );

SaleID AnimalID SalePrice

Field AnimalID Name Category SalePrice

Table Animal Animal Animal SaleAnimal

Sort Descending

Criteria 3 > (SELECT Avg(SalePrice) FROM Animal INNER JOIN SaleAnimal ON Animal.AnimalID = SaleAnimal.AnimalID WHERE Animal.Category = ‘Cat’)

Or

AnimalID Name Category Breed

Animal SaleAnimal

Query05_Fig

Correlated SubQuery (Avoid)

List the Animals that have sold for a price higher than the average for

animals in that Category.

• Match category in subquery with top level
  • Rename tables (As)
• Correlated Subquery
  • Recompute subquery for every row in top level--slow!
  • Better to compute and save Subquery, then use in join.

19

SELECT A1.AnimalID, A1.Name, A1.Category, SaleAnimal.SalePrice FROM Animal As A1 INNER JOIN SaleAnimal ON A1.AnimalID = SaleAnimal.AnimalID WHERE (SaleAnimal.SalePrice> (SELECT Avg(SaleAnimal.SalePrice) FROM Animal As A2 INNER JOIN SaleAnimal ON A2.AnimalID = SaleAnimal.AnimalID WHERE (A2.Category = A1.Category) ) ) ORDER BY SaleAnimal.SalePrice DESC;

Query05_Fig

Correlated Subquery Problem

• Assume small query
  • 100,000 rows
  • 5 categories of 20,000 rows
• 100,000 * 20,000 = 1 billion rows to read!

20

Fish $10. Dog $156. Fish $19. Cat $173. Cat $251. Dog $183. Fish $1. Dog $150. Dog $148.

Category SalePrice

Animal + SaleAnimal

Compute Avg: $37.

Compute Avg: $174.

Compute Avg: $37.

Compute Avg: $169.

Compute Avg: $169.

Recompute average

for every row in the

main query!