/
Database Management Systems Database Management Systems

Database Management Systems - PowerPoint Presentation

lindy-dunigan
lindy-dunigan . @lindy-dunigan
Follow
439 views
Uploaded On 2015-10-11

Database Management Systems - PPT Presentation

Chapter 5 Advanced Queries 1 2 Objectives How can SQL be used to answer more complex questions Why are some business questions more difficult than others How do you find something that did not happen ID: 157118

select merchandise saleitem join merchandise select join saleitem sale category itemid cat dog query05 customer customerid table employee list

Share:

Link:

Embed:

Download Presentation from below link

Download Presentation The PPT/PDF document "Database Management Systems" is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.


Presentation Transcript

Slide1

Database Management Systems

Chapter 5Advanced Queries

1Slide2

2

Objectives

How can SQL be used to answer more complex questions?

Why are some business questions more difficult than others?

How do you find something that did not happen?

How is a

subquery

used for IN and NOT IN conditions?

What are the common uses for

subqueries

?

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? Slide3

Tables

3Slide4

OrganizationHarder Questions

Not In, LEFT JOINSubqueriesUNION, Multiple JOIN columns, Recursive JOINOther SQL Commands

DDL: Data Definition Language

DML: Data Manipulation LanguageOLAPMicrosoft SQL ServerOracleMicrosoft Access Crosstab

4Slide5

Harder QuestionsWhich items have not been sold

?Which items were not sold in July 2013?Which cat merchandise sold for more than the average sale price of cat merchandise?Compute the merchandise sales by category in terms of percentage of total sales

.

List all of the customers who bought something in March and who bought something in May. (Two tests on the same data!).List dog merchandise with a list price greater than the sale price of the cheapest cat product.Has one salesperson made all of the sales on a particular day?Use Not Exists to list customers who have not bought anything.Which merchandise has a list price greater than the average sale price of merchandise within that category?List all the managers and their direct reports.

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

.

Which employees sold merchandise from every category?

List

customers who adopted dogs and also bought cat products

.

5Slide6

LEFT JOIN Problem

6

Which merchandise items have not been sold?

ItemID Description1 Dog Kennel-Small2 Dog Kennel-Medium3 Dog Kennel-Large4 Dog Kennel-Extra Large5 Cat Bed-Small6 Cat Bed-Medium7 Dog Toy8 Cat Toy9 Dog Food-Dry-10 pound10 Dog Food-Dry-25 pound11 Dog Food-Dry-50 pound12 Cat Food-Dry-5 pound13 Cat Food-Dry-10 pound

14 Cat Food-Dry-25 pound15 Dog Food-Can-Regular

SaleID

ItemID

4 1

4 36

6 20

6 21

7 5

7 19

7 40

8 11

8 16

8 36

10 23

10 25

10 2610 27

SaleItem

Merchandise

INNER JOIN is a filter that returns ONLY rows that exist in both tables.

But

SaleItem

includes ONLY merchandise that HAS been sold.Slide7

LEFT JOIN Answer

7

Which merchandise items have not been sold?

SELECT Merchandise.ItemID, Merchandise.Description, SaleItem.SaleIDFROM MerchandiseLEFT

JOIN SaleItem

ON

Merchandise.ItemID

=

SaleItem.ItemID

WHERE

SaleItem.SaleID

Is

Null;

LEFT JOIN includes ALL rows from the table on the SQL left side and matching rows from the right-side table. RIGHT JOIN is similar.

ItemID

Description

1 Dog Kennel-Small

10

Dog Food-Dry-25 pound

11 Dog Food-Dry-50 pound

12 Cat Food-Dry-5 pound

13 Cat Food-Dry-10 pound

14 Cat Food-Dry-25 pound

15 Dog Food-Can-Regular

SaleID ItemID4 14 366 206 21

7 57 197 40

SaleItem

Merchandise

Query05_Fig03Slide8

LEFT JOIN Query

8

Note: LEFT/RIGHT depends on the SQL statement (Merchandise LEFT JOIN

SaleItem

). It is NOT set by the order of the tables in the display.Slide9

LEFT JOIN Result

9

Query1

ItemIDDescriptionSaleID

12

Cat Food-Dry-5 pound

13

Cat Food-Dry-10 poundSlide10

LEFT JOIN: Old Syntax

10

SELECT *

(SQL Server)FROM Merchandise, SaleItemWHERE Merchandise.ItemID *= SaleItemID.ItemIDAnd SaleItem.SaleID

Is Null

SELECT *

(Oracle)

FROM Merchandise,

SaleItem

WHERE

Merchandise.ItemID

=

SaleItemID.ItemID

(+)

And

SaleItem.SaleID

Is Null

Note that Oracle’s plus sign is on the opposite side from what you would expect.

You should

not

use this syntax for new queries. It is hard to read.

But you will likely encounter older queries in your work that use this syntax, so you need to recognize it and understand it is a LEFT join.Slide11

IN Function

11

SELECT *

FROM CustomerWHERE FirstName=N’Tim’ Or FirstName=N’David’ Or FirstName

=N’Dale’;

SELECT *

FROM Customer

WHERE

FirstName

IN

(

N’Tim

’,

N’David

’,

N’Dale

’);

The IN function compares a column to a set of values.

IN is easier to write. Items are joined with an “Or” condition.

Query05_Fig06a

Query05_Fig06bSlide12

Query Sets (IN)

12

SELECT * FROM Merchandise WHERE

ItemID

IN (1,2,3,4,5,6,7,8,9,10,11,14,15);

Query05_Fig07a

Field

ItemID

Description

QuantityOnHand

Table

Customer

Customer

SaleItem

Sort

Ascending

Ascending

Criteria

In (1,2,3,4,5,…)

Or

ItemID

Description

QuantityOnHand

ListPrice

Merchandise

List all

merchandise with the

ItemIDs

of (1,2,3,4,5,6,7,8,9,10,11,14,15).Slide13

IN Condition as a JOIN

13

SELECT *

FROM MerchandiseWHERE ItemID IN (SELECT ItemID FROM SaleItem); Match

ItemID values in the Merchandise table to those that were sold or listed in the

SaleItem

table.

Query05_Fig07bSlide14

NOT IN: Things that did not happen

14

Which merchandise items have not been sold?

Query05_Fig08SELECT *

FROM MerchandiseWHERE

ItemID

NOT IN

(SELECT

ItemID

FROM

SaleItem

);

ItemID

Description

1 Dog Kennel-Small

2 Dog Kennel-Medium

3 Dog Kennel-Large

4 Dog Kennel-Extra Large

5 Cat Bed-Small

6 Cat Bed-Medium

7 Dog Toy

8 Cat Toy9 Dog Food-Dry-10 pound10 Dog Food-Dry-25 pound11 Dog Food-Dry-50 pound12 Cat Food-Dry-5 pound

13 Cat Food-Dry-10 pound14 Cat Food-Dry-25 pound15 Dog Food-Can-Regular

Merchandise

Think of taking the main list (Merchandise) and subtracting the items from the second list (

SaleItem

). Then display the ones that are left.Slide15

Not Sold Conditions (Date Subquery)

15

Which items were not sold in July 2013?

Query05_Fig09

SELECT *FROM Merchandise

WHERE

ItemID

NOT IN

(SELECT

ItemID

FROM

SaleItem

INNER JOIN Sale ON

Sale.SaleID

=

SaleItem.SaleID

WHERE

SaleDate

BETWEEN

’01-JUL-2013’ AND ’31-JUL-2013’

);Slide16

Not Sold Conditions (Date LEFT JOIN--bad)

16

Which items were not sold in July 2013?

Query05_Fig10SELECT Merchandise.*

FROM Sale INNER JOIN (Merchandise

LEFT JOIN

SaleItem

ON

Merchandise.ItemID

=

SaleItem.ItemID

)

ON

Sale.SaleID

=

SaleItem.SaleID

WHERE

SaleDate

BETWEEN

’01-JUL-2013’

AND

’31-JUL-2013’;

Probably will not run and might not return desired results.

To work, the query must filter the

SaleItem rows FIRST, Then apply the LEFT JOIN.Slide17

Not Sold Conditions (Date LEFT JOIN--good)

17

Which items were not sold in July 2013?

JulyItems and Query05_Fig11

CREATE VIEW JulyItems

AS

SELECT

Sale.SaleID

,

ItemID

FROM Sale

INNER JOIN

SaleItem

ON

Sale.SaleID

=

SaleItem.SaleID

WHERE

SaleDate

BETWEEN

’01-JUL-2013’

AND

’31-JUL-2013’;

SELECT Merchandise.*FROM MerchandiseLEFT JOIN

JulyItems ON Merchandise.ItemID=

JulyItems.ItemIDWHERE JulyItems.Sale

Is Null;

The saved view forces the selection of July sale items to occur first.Then the LEFT JOIN applies those rows to the Merchandise table.Slide18

Subquery: Calculations 1

18

SELECT

Merchandise.ItemID, Merchandise.Description, Merchandise.Category, SaleItem.SalePriceFROM Merchandise INNER JOIN SaleItem ON

Merchandise.ItemID =

SaleItem.ItemID

WHERE

Merchandise.Category

=

N’Cat

AND

SaleItem.SalePrice

>

9

;

Query05_Fig12a

Which cat merchandise sold for more than the average sale price of cat merchandise?

If you know (guess) that the average price of cat merchandise is 9; then the query is easy.

So write the easy part first.Slide19

Subquery: Calculations 2

19

Query05_Fig12b

SELECT Merchandise.ItemID, Merchandise.Description

, Merchandise.Category

,

SaleItem.SalePrice

FROM Merchandise

INNER JOIN

SaleItem

ON

Merchandise.ItemID

=

SaleItem.ItemID

WHERE

Merchandise.Category

=

N’Cat

AND

SaleItem.SalePrice

>

9

;Which cat merchandise sold for more than the average sale price of cat merchandise?

SELECT

Merchandise.ItemID, Merchandise.Description,

Merchandise.Category, SaleItem.SalePrice

FROM Merchandise INNER JOIN SaleItem ON

Merchandise.ItemID = SaleItem.ItemID

WHERE Merchandise.Category=N’Cat

’ AND SaleItem.SalePrice > (SELECT

Avg

(

SaleItem.SalePrice

) AS

AvgOfSalePrice

FROM Merchandise

INNER JOIN

SaleItem

ON

Merchandise.ItemID

=

SaleItem.ItemID

WHERE

Merchandise.Category

=

N’Cat’);Slide20

Subquery Calculation Notes

20

When building

subqueries that use calculations:Write the query using a simple number.Write the subquery separately to compute the desired number. Test it!Isolate the original estimated number in the first query by putting it on a separate line.Delete the number and add parentheses ( ).Paste the subquery inside the parentheses.Slide21

Subquery for Percentages

21

Query05_Fig13

Compute the merchandise sales by category in terms of percentage of total sales.SELECT

Merchandise.Category, Sum([Quantity]*[

SalePrice

]) AS [Value],

Sum([Quantity]*[

SalePrice

])

/(SELECT Sum([Quantity]*[

SalePrice

]) FROM

SaleItem

)

As [

Pct

]

FROM Merchandise

INNER JOIN

SaleItem

ON

Merchandise.ItemID =

SaleItem.ItemIDGROUP BY Merchandise.Category;

Category Value Percentage

Bird $631.50 7.45063292035315E-02Cat $1,293.30 0.152587546411603

Dog $4,863.49 0.573809638983505Fish $1,597.50 0.188478006179955

Mammal $90.00 1.06184792214059E-02Slide22

Percentages with JOIN using Views

22

SELECT Category, Value, Value/

MainTotal AS PercentageFROM CategorySubtotals, TotalItemSales;Save the first view that computes subtotals.Create a second view to compute totals.Compute the percentages in a third query using a cross join.

CREATE VIEW

TotalItemSales

AS

SELECT Sum(Value) AS

MainTotal

FROM

CategorySubtotals

;

Query05_Fig14

CREATE VIEW

CategorySubtotals

AS

SELECT Category, Sum(Quantity*

SalePrice

) AS Value

FROM Merchandise

INNER JOIN

SaleItem

ON

Merchandise.ItemID

=

SaleItem.ItemID

GROUP BY

Merchandise.Category;Slide23

Sets of Data

23List all of the customers who bought something in March and in

May.

Try answering this question the “easy” but wrong way.

Query05_Fig15

SELECT

Customer.CustomerID

,

Customer.Phone

,

Customer.LastName

,

Sale.SaleDate

FROM Customer

INNER JOIN Sale ON

Customer.CustomerID

=

Sale.CustomerID

WHERE

Sale.SaleDate

Between

’01-MAR-2013’

And

’31-MAR-2013’

AND Sale.SaleDate

Between ’01-MAY-2013’ And ’31-MAY-2013’;

The WHERE clause checks the date on each row to see if it fall in March

AND in May. But no date can be in two months!Slide24

Two Sets using Subquery

24

SELECT

Customer.LastName, Customer.FirstNameFROM Customer INNER JOIN Sale ON Customer.CustomerID = Sale.CustomerIDWHERE (SaleDate Between

’01-MAR-2013’ And

‘31-MAR-2013’)

AND

Customer.CustomerID

IN

(SELECT

CustomerID

FROM Sale

WHERE (

SaleDate

Between ‘

01-MAY-2013’

And

’31-MAY-2013’)

);

List all of the customers who bought something in March and in

May.

Query05_Fig16

Customers in March and customers in May are two separate sets of data. Two separate SELECT statements are needed to answer the question.

This query combines those sets using a

subquery

.Slide25

25

Two Sets of Data Using JOIN

CREATE VIEW

MarchCustomers

AS

SELECT

CustomerID

FROM Sale

WHERE (

SaleDate

Between

‘01-MAR-2013’

And

‘31-MAR-2013’);

CREATE VIEW

MayCustomers

AS

SELECT

CustomerID

FROM Sale

WHERE (

SaleDate

Between

‘1-MAY-2013’

And

‘31-MAY-2013’);

SELECT

Customer.LastName,

Customer.FirstNameFROM Customer INNER JOIN

MarchCustomers ON Customer.CustomerID=

MarchCustomers.CustomerIDINNER JOIN MayCustomers

ON

MarchCustomers.CustomerID

=

MayCustomers.CustomerID

; Slide26

Subquery: Any

26

Query05_Fig18

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

. Probably easier to use MIN function in the

subquery

.

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.

List dog merchandise

with a list price greater than

the sale price of the cheapest cat product.

SELECT

Merchandise.ItemID

,

Merchandise.Description

,

Merchandise.Category

,

Merchandise.ListPrice

FROM Merchandise

WHERE

Category=

N'Dog

‘ AND

ListPrice

> ANY

(SELECT SalePrice

FROM Merchandise INNER JOIN SaleItem ON

Merchandise.ItemID=SaleItem.ItemID

WHERE Merchandise.Category=N'Cat

')

;Slide27

Subquery: All

27

Query05_Fig19

Has one salesperson made all of the sales on a particular day (Mar 28)?

SELECT Employee.EmployeeID

,

Employee.LastName

FROM Employee

WHERE

EmployeeID

= ALL

(SELECT

EmployeeID

FROM Sale

WHERE

SaleDate

= #

28-MAR-2013#)

;

ID

LastName

2 Gibson

Returns a match only if the employee made all of the sales on the specified date. (Or if there were no sales—all null values—on that date.)Slide28

Subquery: Exists

28

Use Not Exists to list customers who have not bought anything.

SELECT Customer.CustomerID, Customer.Phone, Customer.LastNameFROM CustomerWHERE NOT EXISTS

(SELECT SaleID

,

SaleDate

FROM

Sale WHERE

Sale.CustomerID

=

Customer.CustomerID

);

EXISTS tests for the existence of rows in the

subquery

.

The

subquery

can contain multiple columns because none of the returned values matter—only whether any values match the WHERE clause.

This example is better if you use a JOIN command, but it works and illustrates the Exists term.

Query05_Fig20Slide29

Correlated Subquery

29

Query05_Fig21

Which merchandise

has a list price greater than

the average sale price of merchandise within that category?

SELECT Merchandise1.ItemID, Merchandise1.Description, Merchandise1.Category, Merchandise1.ListPrice

FROM Merchandise AS Merchandise1

WHERE Merchandise1.ListPrice>

(

SELECT

Avg

(

SaleItem.SalePrice

) AS

AvgOfSalePrice

FROM Merchandise As Merchandise2 INNER JOIN

SaleItem

ON Merchandise2.ItemID =

SaleItem.ItemID

WHERE

Merchandise2.Category=Merchandise1.Category

);

The WHERE clause in the

subquery

depends on values in the outer query. The tables require aliases to tell them apart.Slide30

Correlated Subquery Potential Problem

30

Assume small query

100,000 rows

5 categories of 20,000 rows

100,000 * 20,000 = 1 billion rows to read!

1 Dog

$45.00

2 Dog

$65.00

3 Dog

$85.00

4 Dog

$110.00

5 Cat

$25.00

6 Cat

$35.00

7 Dog

$4.00

8 Cat

$3.00

9 Dog $7.50

Merchandise

Compute

Avg

:

$23.32

Compute

Avg

:

$23.32

Compute

Avg

:

$23.32

Compute

Avg

:

$23.32

Compute

Avg

:

$8.99

Recompute

average for every row in the main query!

MerchID

Category

ListPriceSlide31

More Efficient Solution: 2 queries

31

Compute the averages once and save query

JOIN saved query to main query

Two passes through table: 1 billion / 200,000 => 10,000

Bird

$37.60

Cat

$8.99

Dog

$23.32

Fish

$38.18

Mammal

$9.00

Category

AvgOfSalePrice

Saved Query

JOIN

Merchandise.Category

=

Query05_Fig23a.Category

Query05_Fig23a

1 Dog

$45.00

2 Dog

$65.00

3 Dog

$85.00

4 Dog

$110.00

5 Cat

$25.00

6 Cat

$35.00

7 Dog

$4.00

8 Cat

$3.00

9 Dog

$7.50

Merchandise

MerchID

Category

ListPriceSlide32

Uncorrelated Queries

32

SELECT

Merchandise.ItemID, Merchandise.Category, Merchandise.ListPrice, Query05_Fig23a.AvgOfSalePriceFROM Query05_Fig23a INNER JOIN Merchandise ON

Query05_Fig23a.Category = Merchandise.Category

WHERE

Merchandise.ListPrice

>[

Query05_Fig23a].[

AvgOfSalePrice

];

SELECT

Merchandise.Category

,

Avg

(

SaleItem.SalePrice

) AS

AvgOfSalePrice

FROM Merchandise

INNER

JOIN

SaleItem

ON

Merchandise.ItemID = SaleItem.ItemID

GROUP BY Merchandise.Category;

Query05_Fig23bSlide33

UNION Operator

33

SELECT EID, Name, Phone, Salary, ‘East’ AS Office

FROM

EmployeeEast

UNION

SELECT EID, Name, Phone, Salary, ‘West’ AS Office

FROM

EmployeeWest

EID Name Phone Salary Office

352 Jones 3352 45,000 East

876 Inez 8736 47,000 East

372

Stoiko

7632 38,000 East

890

Smythe

9803 62,000 West

361 Kim 7736 73,000 West

Offices in Los Angeles and New York.

Each has an Employee table (East and West).

Need to search data from both tables.

Columns in the two SELECT lines must match.Slide34

UNION, INTERSECT, EXCEPT

34

T1

T2

A

B

C

SELECT EID, Name

FROM

EmployeeEast

INTERSECT

SELECT EID, Name

FROM

EmployeeWest

List the name of any employee who has worked for both the East and West regions.

T1 UNION T2

A + B + C

T1 INTERSECT T2

B

T1

EXCEPT T2

A

Microsoft Access supports only UNION.

SQL Server supports all three.Slide35

Multiple JOIN Columns

35

AnimalID

Name

Category

Breed

DateBorn

Gender

. . .

Category

Breed

Breed

Animal

SELECT *

FROM Breed INNER JOIN Animal

ON

Breed.Category

=

Animal.Category

AND

Breed.Breed

=

Animal.Breed

Query05_Fig26

Sometimes need to JOIN tables on more than one column.

PetStore

: Category and Breed.Slide36

Reflexive Join

36

SELECT

Employee.EmployeeID

,

Employee.LastName

,

Employee.ManagerID

, E2.LastName

FROM Employee INNER JOIN Employee AS E2

ON

Employee.ManagerID

= E2.EmployeeID

EID Name . . . Manager

1

Reeves

11

2

Gibson

1

3

Reasoner

14

Hopkins 3

Employee

EID Name Manager

Name

1

Reeves

11

Smith

2

Gibson

1

Reeves

3

Reasoner

1

Reeves

SQL

Result

Query05_Fig20

Need to connect a table to itself.

Common example: Employee(EID,

LastName

, . . .,

ManagerID

)

A manager is also an employee.

Use a second copy of the table and an alias.Slide37

37

Recursive Joins (SQL 99 and 2003)

WITH RECURSIVE

EmployeeList

(

EmployeeID

, Title, Salary) AS

( SELECT

EmployeeID

, Title, 0.00

FROM Manages WHERE Title =

N‘CEO

’ -- starting level

UNION ALL

SELECT

Manages.EmployeeID

,

Manages.Title

,

Manages.Salary

FROM

EmployeeList

INNER JOIN Manages

ON

EmployeeList.EmployeeID

=

Manages.ManagerID )SELECT

EmployeeID, Count(Title), Sum(Salary)FROM EmployeeList

GROUP BY EmployeEID ;

List all of the

managers and

list everyone who reports to them.

Available in higher-end systems (SQL Server, Oracle, DB2, etc.), but each vendor uses a different, proprietary syntax.

See the Workbooks.

It

provides tree spanning capabilities.

Not available in Microsoft Access.

Variations are in

SQL Server and Oracle.Slide38

38

Recursive JOIN: SQL Server

WITH

DirectReports

(

EmployeeID

,

LastName

,

ManagerID

, Title, Level)

AS

(

--Root/anchor member (find employee with no manager)

SELECT

EmployeeID

,

LastName

,

ManagerID

, Title, 0 As Level

FROM Employee WHERE

ManagerID

=0 -- starting level

UNION ALL

-- Recursive members

SELECT

Employee.EmployeeID

, Employee.LastName

, Employee.ManagerID

, Employee.Title, Level +1

FROM Employee INNER JOIN

DirectReports

ON

Employee.ManagerID

=

DirectReports.EmployeeID

)

-- Now execute the common table expression

SELECT

ManagerID

,

EmployeeID

,

LastName

, Title, Level

FROM

DirectReports

ORDER BY Level,

ManagerID

,

LastNameSlide39

Recursive Query Results

ManagerID

EmployeeID

LastName

Title

Level

0

11

Smith

Owner

0

11

1

Reeves

Manager

1

1

2

Gibson

Manager

2

1

3

Reasoner

Manager

2

2

6

Eaton

Animal Friend

3

2

7

Farris

Animal Friend

3

2

5

James

Animal Friend

3

2

9

O’Connor

Animal Friend

3

2

10

Shields

Animal Friend

3

3

8

Carpenter

Worker

3

3

4

Hopkins

Worker

3

39Slide40

CASE Function

40

Select

AnimalID

,

CASE

WHEN Date()-

DateBorn

< 90 Then ‘Baby’

WHEN Date()-

DateBorn

>= 90

AND Date()-

DateBorn

< 270 Then ‘Young’

WHEN Date()-

DateBorn

>= 270

AND Date()-

DateBorn

< 365 Then ‘Grown’

ELSE ‘Experienced’

END

FROM Animal;

Not available in Microsoft Access. It is in SQL Server and Oracle.

Example

: Define age categories for the animals.

Less

than 3 months

Between 3 months and 9 months Between 9 months and 1 year Over 1 year

Convert age ranges into categories.Slide41

Inequality Join

41

AR(

TransactionID

,

CustomerID

, Amount,

DateDue

)

LateCategory

(

Category

,

MinDays

,

MaxDays

, Charge, …)

Month 30 90 3%

Quarter 90 120 5%

Overdue 120 9999 10%

SELECT *

FROM AR INNER JOIN

LateCategory

ON ((Date() -

AR.DateDue

) >=

LateCategory.MinDays

)

AND ((Date() -

AR.DateDue

) <

LateCategory.MaxDays

)

AccountsReceivable

Categorize by Days

Late

30, 90, 120+

Three

queries?

New table for business rules

Classify payments by number of days late.Slide42

Queries with “Every” Need EXISTS

42

List the employees who have sold animals from

every

category.

By hand: List the employees and the categories. Go through the SaleAnimal list and check off the animals they have sold.Slide43

Query With EXISTS

43

List the

Animal categories where merchandise has

not

been

sold by

an employee (#5).

SELECT Category

FROM Category

WHERE (Category <> 'Other') And Category NOT IN

(SELECT

Merchandise.Category

FROM Merchandise INNER JOIN (Sale INNER JOIN

SaleItem

ON

Sale.SaleID

=

SaleItem.SaleID

)

ON

Merchandise.ItemID

=

SaleItem.ItemID

WHERE

Sale.EmployeeID

= 5)

If this query returns any rows, then the employee has

not

sold every animal.

So list all the employees for whom the above query returns no rows:

SELECT

EmployeeID

,

LastName

FROM Employee

WHERE

NOT EXISTS

(above query slightly modified.)

Query05_Fig31Slide44

44

Query for Every

Query05_Fig32

SELECT

Employee.EmployeeID

,

Employee.LastName

FROM Employee

WHERE Not Exists

(SELECT Category

FROM Category

WHERE (

Category NOT IN (‘Other’, ‘Reptile’, ‘Spider’)

And Category NOT IN

(SELECT

Merchandise.Category

FROM Merchandise INNER JOIN (Sale INNER JOIN

SaleItem

ON

Sale.SaleID

=

SaleItem.SaleID

)

ON

Merchandise.ItemID

=

SaleItem.ItemID

WHERE Sale.EmployeeID =

Employee.EmployeeID) );

EmployeeID

LastName

2

Gibson

3

Reasoner

5

James

7

Farris

Which employees have merchandise sales from every category?Slide45

45

Simpler Query for Every

Sometimes it is easier to use Crosstab and the Count function.

But some systems do not have Crosstab, and sometimes the

lists would be too long. So you need to know both techniques.

Query05_Fig33

EID

LastName

Bird

Cat

Dog

Fish

Mammal

1

Reeves

 

4

15

6

 

2

Gibson

1

25

24

9

2

3

Reasoner

2

9

26

5

2

4

Hopkins

3

21

33

 

 

5

James

3

7

8

11

2

6

Eaton

1

2

8

 

1

7

Farris

1

4

24

1

1

8

Carpenter

3

1

11

5

 

9

O'Connor

 

5

10

3

1

10

Shields

1

 

5

 

 

11

Smith

 

1

 

 

 Slide46

SQL SELECT

46

SELECT DISTINCT

Table.Column

{AS alias} , . . .

FROM Table/Query

{INNER or LEFT}

JOIN Table/Query ON T1.ColA = T2.ColB

WHERE (condition)

GROUP BY Column

HAVING (group condition)

ORDER BY

Table.Column

{ Union second select }Slide47

SQL Mnemonic

47

Someone

From

Ireland

Will

Grow

Horseradish and

Onions

SELECT

FROM

INNER JOIN

WHERE

GROUP BY

HAVING

ORDER BY

SQL is picky about putting the commands in the proper sequence.

If you have to memorize the sequence, this mnemonic may be helpful.Slide48

SQL Data Definition

Create Schema Authorization dbName passwordCreate Table TableName (Column Type, . . .)Alter Table

Table

{Add, Column, Constraint, Drop}Drop {Table Table | Index Index On table}Create Index IndexName ON Table (Column {ASC|DESC})

48Slide49

Syntax Examples

49

CREATE TABLE Customer

(

CustomerID

INTEGER NOT NULL,

LastName

VARCHAR (20

),

more columns

);

ALTER TABLE Customer

DROP COLUMN

ZipCode

;

ALTER TABLE Customer

ADD COLUMN

CellPhone

CHAR(15); Slide50

SQL: Foreign Key

50

CREATE TABLE Order

(

OrderID

INTEGER NOT NULL,

OrderDate

DATE,

CustomerID

INTEGER

CONSTRAINT

pkorder

PRIMARY KEY (

OrderID

),

CONSTRAINT

fkorder

FOREIGN KEY (

CustomerID

)

REFERENCES Customer (

CustomerID

)

);

OrderID

OrderDate

CustomerID

CustomerID

LastName

FirstName

Address

Order

Customer

*Slide51

SQL Data Manipulation CommandsInsert Into target (column1 . . .) VALUES (value1 . . .)

Insert Into target (column1 . . .) SELECT . . . FROM. . .Delete From table WHERE conditionUpdate table SET Column=Value,. . . Where conditionNote the use of the Select and Where conditions.

Synatx is the same--only learn it once.

You can also use subqueries.

51Slide52

Copy Old Customer Data

52

INSERT INTO

OldCustomers

SELECT *

FROM

Customer

WHERE

CustomerID

IN

(SELECT

Sale.CustomerID

FROM

Customer

INNER

JOIN Sale ON

Customer.CustomerID

=

Sale.CustomerID

GROUP

BY

Sale.CustomerID

HAVING Max(

Sale.SaleDate

)<’01-Jul-2013’)

);Slide53

Delete Old Customer Data

53

DELETE

FROM

Customer

WHERE

CustomerID

IN

(

SELECT

Sale.CustomerID

FROM Customer

INNER JOIN Sale ON

Customer.CustomerID

=

Sale.CustomerID

GROUP BY

Sale.CustomerID

HAVING

Max(

Sale.SaleDate

)<

’01-Jul-2013’)

);Slide54

Update Example

54

UPDATE

Merchandise

SET

ListPrice

=

ListPrice

*

1.10

WHERE Category =

N‘Cat

;

UPDATE

Merchandise

SET

ListPrice

=

ListPrice

*

1.20

WHERE Category =

N‘Dog

;

Change the

ListPrice of Merchandise at the PetStore

.For cats, increase the ListPrice by 10%.For dogs, increase the ListPrice

by 20%.Typically use two similar UPDATE statements.With the CASE function, the statements can be combined.Slide55

55

Quality: Building Queries

Break questions into smaller pieces.

Test each query.

Check the SQL.

Look at the data.

Check computations

Combine into

subqueries

.

Use cut-and-paste to avoid errors.

Check for correlated

subqueries

.

Test sample data.

Identify different cases.

Check final query and

subqueries

.

Verify calculations.

Test SELECT queries before executing UPDATE queries.

Dogs and cat products on the same sale.

Dogs and cat products at different times.

Dogs and never any cat products.

Cat products and never any dogs.

Which customers who

adopted Dogs

also bought products for Cats

(at any time)?

Who

adopted dogs

?

Who bought cat products?Slide56

56

Quality Queries: Example

Which customers who

adopted Dogs

also bought products for Cats?

SELECT DISTINCT

Animal.Category

,

Sale.CustomerID

FROM Sale INNER JOIN Animal

ON

Animal.SaleID

=

Sale.SaleID

WHERE (

Animal.Category

='Dog')

AND

Sale.CustomerID

IN (

SELECT DISTINCT

Sale.CustomerID

FROM Sale INNER JOIN (Merchandise INNER JOIN

SaleItem

ON

Merchandise.ItemID

=

SaleItem.ItemID

)

ON Sale.SaleID = SaleItem.SaleID

WHERE (

Merchandise.Category

='Cat')

);

A. Which customers

adopted dogs

?

B. Which customers bought cat products?

Query05_Fig38Slide57

Programming Review: Variables

Integer2 bytes-32768 32767Long4 bytes

+/- 2,147,483,648

Single4 bytes+/- 3.402823 E 38+/- 1.401298 E-45Global, Const, StaticDouble8 bytes+/- 1.79769313486232 E 308+/- 4.94065645841247 E-324Currency8 bytes+/- 922,337,203,685,477.5808String & String*n

VariantAny data typeNull

57Slide58

Programming: Scope and Lifetime

ScopeWhere is the variable, and which procedures can access it?LifetimeWhen is the variable created, and when is it destroyed?

58

Form--Module Code

Sub Button1_Click()

Dim i1 As Integer

i1 = 3

End Sub

Sub Button2_Click()

Dim i1 As Integer

i1 = 7

End Sub

Form

Button1

Button2

Different procedures,

different variables.

Created and destroyed

each time the button

is clicked.Slide59

Programming: Global Variables

Wider scope and lifetimeCreated at a higher levelFormPublic moduleAccessible to any procedure in that form or module.

Declare it Global to make it available to any procedure.

59

Form--Module Code

Sub Button2_Click()

i2 = i2 + 7

End Sub

Form

Button1

Button2

Dim i2 As Integer

Variable is created

when form

is opened.

Clicking Button1 sets

the initial

value.

Clicking Button2

modifies the

value.

What if user clicks buttons in a different order?

Sub Button1_Click()

i2 = 20

End SubSlide60

Programming: Computations

Standard Math+ - * /\ Integer divide^ Exponentiation(2^3 = 2*2*2 = 8)

Mod

(15 Mod 4 = 3) (12 + 3 = 15)String& ConcatenationLeft, Right, MidTrim, LTrim, RTrimStringChr, AscLCase, UCaseInStrLenStrCompFormat

60

“Frank” & “Rose”

FrankRose

Left(“Jackson”,5)

“Jacks”

Trim(“ Maria “)

 “Maria”

Len(“

Ramanujan

”)  9

String(5,”a”)  “

aaaaa

InStr

(“8764 Main”,” “)  5Slide61

Programming: Standard Functions

NumericExp, LogAtn, Cos, Sin, Tan

Sqr

AbsSgnInt, FixRnd, Randomize

61

x = log

e

(e

x

)

Trigonometric functions

2 = 1.414

Abs(-35)

35

Sgn

(-35)

 -1

Int

(17.893)

 17

Rnd

()

0.198474

?

=30

92Slide62

Programming:Standard Functions: Date/Time

62

Date, Now, Time

DateAdd

,

DateDiff

“y”, “m”, “q” . . .

Firstweekday

1=Sunday,. . .

Can also be used to find number of Fridays, between two dates.

today

DateDue

02/19/10

03/21/10

DateDue

=

DateAdd

(“d”, 30, Date())Slide63

Programming:Standard Functions: Variant

VariantIsDateIsNumericVarTypeIsEmpty

IsNull

63Slide64

Programming: DebugStop

Ctrl-BreakF5: GoF8: Step throughS-F8: Step overBreakpoints

Immediate Window

? or PrintAny assignmentAny code

64Slide65

Programming:Output: Message Box

MsgBoxMessageTypeTitle

Types: Use Constants

vbOKOnly vbOKCancelvbAbortRetryIgnorevbYesNoCancelvbYesNo vbRetryCancelDefaultsvbDefaultButton1vbDefaultButton2vbDefaultButton3

IconsvbCritical

Stop sign

vbQuestion

Question mark

vbExclamation

Warning

vbInformation

Circle i

Responses

vbOK

vbCancel

vbAbort

vbRetry

vbIgnore

vbYes vbNo

65

MsgBox

"This is a message box",

vbYesNoCancel

+

vbInformation

, "Sample Box"Slide66

Programming:Input: InputBox

InputBoxPromptTitleDefault

X-

Pos, Y-PosPromptCannot change box sizeUse Chr(10) & Chr(13) for blank lines.Returns text or VariantCancel = zero string ““PositionsTwipsTwentieth of inch point

72 points1440 twips per inch

66

Dim

str

As String

str

=

InputBox

(

"Enter your name:",

"Sample Input", ,

5000, 5000)Slide67

Programming: ConditionsIf

If (Condition) Thenstatements for trueElsestatements for falseEnd If

IIF (Cond., True, False)

Select Case (expr)Case valuestatementsCase value2Case ElseEnd SelectConditions<, <=, >, >=, =, <>And, Or, Not, XorEqv, Imp (logic)

67

If (Condition1) Then

statements for true

Else

statements for false

If (Condition2) Then

statements for true

End If

End IfSlide68

ProgrammingSelect Example

68

Message Box

Could use repeated If statements

Better to use Select Case

response =

MsgBox

(…)

If (response ==

vbYes

) Then

‘ statements for Yes

Else

If (response ==

vbNo

) Then

‘ statements for No

Else

‘statements for Cancel

End If

End If

response =

MsgBox

(…)

Select Case response

Case

vbYes

‘ statements for Yes

Case

vbNo

‘ statements for No

Case

vbCancel

‘ statements for Cancel

End CaseSlide69

Programming: Loops

DoFor … NextFor Each

69

Do Until (x > 10)

‘ Statements

x = x + 1

Loop

Initialize value

Statements

Change value

Test condition

Do While (x <= 10)

‘ Statements

x = x + 1

Loop

Do

‘ Statements

x = x + 1

Loop Until (x > 10)

For x = 1 to 10

‘ Statements

Next xSlide70

Programming: Loops Again

DoDo {While | Until}Exit Do (optional)LoopDoLoop {While | Until}

For/Next

For counter = start To end Step incrementExit For (optional)Next counterFor/Each (objects)For Each element In group[Exit For] (optional)Next elementWith (objects)With objectEnd With

70Slide71

ProgrammingSubroutines and Functions

Sub name (var1 As . . ., var2, . . .)End SubFunction fname (var1 As . . .) As datatypefname = … ‘ returns a specific valueEnd Function

Variables are passed by reference

Changes made to the parameters in the subroutine are passed back to the caller.Unless you use ByValChanges are made to a copy of the parameter, but are not returned to the calling program.

71Slide72

Programming: Example Subroutine

72

Main program

StatusMessage

“Trying to connect.”

StatusMessage

“Verifying access.”

End main program

Sub

StatusMessage

(

Msg

As String)

‘ Display

Msg

, location, color

End SubSlide73

Programming: Parameter Types

73

Main

j = 3

DoSum j

‘ j is now equal to 8

Subroutine DoSum (j2 As Integer)

j2 = 8

End Sub

By Reference

Changes to data in the

subroutine are passed back.

Main

j = 3

DoSum

j

‘ j is still equal to 3

Subroutine

DoSum

(

ByVal

j2 As Integer)

j2 = 8

End Sub

By Value

Creates a copy of the

variable, so changes are

not returned.Slide74

ProgrammingArrays and User Types

ArraysDim array(sub, . . .) As typeDim iSorts(10) As IntegerSpecifying bounds:(lower To upper, . . .)

ReDim [Preserve] array .. .

Option Base 0 | 1v 2.0 arrays less than 64KBUser defined typesType Tnameename1 As typeename2 As typeEnd TypeDim var1 As Tnamevar1.ename1 = . . .var1.ename2 = . . .

74Slide75

Programming: Financial Functions

Fixed paymentsPV (rate, nper, pmt,

fv

, due)FV (rate, nper, pmt, pv, due)IPmt (rate, per, nper, pv, fv, due)NPer (rate, pmt,

pv, fv

, due)

Pmt

(rate,

nper

,

pv

,

fv,due

)

PPmt

(rate, per,

nper

,

pv

,

fv, due)Rate (

nper, pmt, pv,

fv, due, guess)rate interest rate per periodper specific period number

nper # of periodspv present valuefv future value

due 0=due at end, 1=due at startArraysNPV (rate, array)

IRR (array, guess)MIRR (array, finrate, re_rate)DepreciationDDB (cost, salv, life, period)

SLN (cost, salvage, life)SYD (cost, salv., life, period)

75Slide76

Programming: Text File Input/Output

Open filename As # file#Close # file#, ResetPrint #,Put, WriteSpc, TabGet, Input #, Line Input #EOF, LOFSeek # file#, position

ChDir, ChDirve

DirKill, (re)NameLock, UnlockCurDir, MkDir, RmDir

76Slide77

OLE: Object Linking & Embedding

CreateObject (class)“appname . objecttype”GetObject (file, class)Methods and syntax are defined by the software that exports the object.

Example

Dim obj As Objectset obj = CreateObject(“Word.Basic”)obj.Boldobj.Insert “text”obj.SaveAs “file”

77