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