Structured Query Language
(SQL)
Ask and ye shall receive. The Bible
Class Outline
 What is SQL?
 What are the basic SQL select statements and
what is the order in which they are to be used?
Which are optional?
 Create SQL statements that






use expressions and functions to convert data into
information
use multiple criteria for searching
produce output in a specific order
provide summary data from groups of records
display data from two tables
show data that uses the results of another query as
criteria
SQL

SQL is a non-procedural data access language that is used
primarily in programming by embedding it in other languages

SQL is not a user-level language

SQL accepts one or more relations as input and produces a single
relation as output

SQL provides functions for data definition (creates database and
table structures), data management (enter, edit, delete data), and
data query (convert data into information)
our focus

developed in mid 1970s by IBM; endorsed by ANSI (American
National Standards Institute) as the language of choice for
manipulating relational databases

language used by DB2, SQL/DS, ORACLE, INGRES, SYBASE,
dBase, Paradox, Access (each with its own dialect)

computer systems are able to exchange data by passing SQL
requests and responses to one another
General SQL Query Syntax
required
optional,
must be in
this order
if any or
all are
used
SELECT*
FROM
columns to extract
tables containing columns
WHERE
that
GROUP BY
groups
HAVING
search criteria to restricts rows
are returned
summarizes query results by
search criteria to restrict groups
that
are returned
sorts results by one or more
ORDER BY
columns
• Preceding is the order in which clauses should appear
• Order of processing is as follows: From, Where, Group by, Having,
Order by, Select
Projections
Employee
EmpID
27
44
35
37
99
Name
Rodney Jones
Goro Azuma
Francine Moire
Anne Abel
Mary Chen
SELECT Name, Salary
FROM Employee
Name
Salary
Rodney Jones
3000
Goro Azuma
1500
Francine Moire
2500
Anne Abel
1500
Mary Chen
5000
Office
Toronto
Tokyo
Brussels
Tokyo
Brussels
Salary
3000
1500
2500
1500
5000
SELECT Office, EmpID, Name
FROM Employee
Office
Toronto
Tokyo
Brussels
Tokyo
Brussels
EmpID
27
44
35
37
99
Name
Rodney Jones
Goro Azuma
Francine Moire
Anne Abel
Mary Chen
Unique Projections
Employee
EmpID
27
44
35
37
99
Name
Rodney Jones
Goro Azuma
Francine Moire
Anne Abel
Mary Chen
SELECT DISTINCT Name, Salary
FROM Employee
Name
Salary
Rodney Jones
3000
Goro Azuma
1500
Francine Moire
2500
Anne Abel
1500
Mary Chen
3000
Office
Toronto
Tokyo
Brussels
Tokyo
Brussels
Salary
3000
1500
2500
1500
3000
SELECT DISTINCT Office, Salary
FROM Employee
Office
Toronto
Tokyo
Brussels
Brussels
Salary
3000
1500
2500
3000
appears
only once
Selections
Employee
EmpID
27
44
35
37
99
Name
Rodney Jones
Goro Azuma
Francine Moire
Anne Abel
Mary Chen
SELECT EmpID, Name, Office, Salary
FROM Employee
WHERE Office = ‘Brussels’
EmpID
35
99
Office
Toronto
Tokyo
Brussels
Tokyo
Brussels
Salary
3000
1500
2500
1500
5000
SELECT *
FROM Employee
WHERE Office = ‘Brussels’
Name
Office
Francine Moire Brussels
Mary Chen
Brussels
Salary
2500
5000
Combining Selections and Projections
Employee
EmpID
27
44
35
37
99
Name
Rodney Jones
Goro Azuma
Francine Moire
Anne Abel
Mary Chen
SELECT Name, Salary
FROM Employee
WHERE Office = ‘Brussels’
Name
Salary
Francine Moire
2500
Mary Chen
5000
Office
Toronto
Tokyo
Brussels
Tokyo
Brussels
Salary
3000
1500
2500
1500
5000
SELECT Name, Office, Salary
FROM Employee
WHERE EmpID = 35
Name
Office
Francine Moire Brussels
Salary
2500
Single quotes necessary around text and dates (but not values) in criteria.
Comparison Search Conditions
Equality and Inequality Operators
Equal to
Not equal to
Greater than
Less than
Less than or equal to
Greater than or equal to
SELECT Name, Salary
FROM Employee
WHERE Salary >= 2500
Name
Salary
Rodney Jones
3000
Francine Moire
2500
Mary Chen
5000
=
<> (or !=)
>
<
<=
>=
Employee
EmpID
27
44
35
37
99
Name
Rodney Jones
Goro Azuma
Francine Moire
Anne Abel
Mary Chen
Office
Toronto
Tokyo
Brussels
Tokyo
Brussels
Salary
3000
1500
2500
1500
5000
SELECT EmpID, Name, Office
FROM Employee
WHERE Name <> ‘Anne Abel’
EmpID
27
44
35
99
Name
Rodney Jones
Goro Azuma
Francine Moire
Mary Chen
Office
Toronto
Tokyo
Brussels
Brussels
Comparison Search Conditions
Comparison Operators
Equal to any member of the list
IN(list )
Greater than or equal to one value,
and less than or equal to another
Matches the following pattern
BETWEEN low AND high
a string of zero or more characters
a string of one character
% (MS Access uses *)
(MS Access uses ?)
Missing value
Reverses preceding operators
LIKE
IS NULL
NOT
Examples of Search Conditions
SELECT Name, Office, Salary
FROM Employee
WHERE Name Like ‘%am’
SELECT Name, Office, Salary
FROM Employee
WHERE Office is Null
SELECT Name, Office, Salary
FROM Employee
WHERE Name Like ‘Ab_l’
SELECT Name, Office, Salary
FROM Employee
WHERE Office NOT IN (‘Toronto’)
SELECT Name, Salary
FROM Employee
WHERE Office IN (‘Brussels’, ‘Tokyo’)
SELECT Name, Office, Salary
FROM Employee
WHERE Salary between 2000 and 3000
Compound Comparison Search Conditions
AND means
that ALL
conditions
must be met
OR means
SELECT Name, Office, Salary
that ANY
FROM Employee
condition
WHERE Office IN (‘Toronto’) or Name = ‘Anne Abel’
may be met
when
SELECT Name, Salary
operators
FROM Employee
are
WHERE (Office IN (‘Brussels’, ‘Tokyo’) or Salary is Null)
combined,
and HireDate <= ‘7/15/99’
brackets
are
SELECT Name, Office, Salary
evaluated
FROM Employee
first
WHERE (Salary between 2000 and 3000 and Office <> ‘Tokyo’) (inner to
outer)
or (Name like ‘Gor% and EmpID > 20)
SELECT Name, Office, Salary
FROM Employee
WHERE Name NOT Like ‘%Abel’ and Salary >= 3100
Sorting
Employee
EmpID
27
44
35
37
99
Name
Rodney Jones
Goro Azuma
Francine Moire
Anne Abel
Mary Chen
Office
Toronto
Tokyo
Brussels
Tokyo
Tokyo
Salary
1500
1500
2500
3000
1500
SELECT Name, Salary
FROM Employee
WHERE Office = ‘Tokyo’
ORDER BY Salary, Name
SELECT Name, Office, Salary
FROM Employee
WHERE Salary >= 2000
ORDER BY EmpID DESC
Name
Goro Azuma
Mary Chen
Anne Abel
Name
Office
Anne Abel
Tokyo
Francine Moire Brussels
Salary
1500
1500
3000
Salary
3000
2500
Expressions using Arithmetic
SELECT ItemName, Price*1.15
FROM Product
SELECT Name, Salary, Commission/Salary
FROM Salesperson
WHERE Commission > .05*Salary
current
system
date
SELECT ContactName, CompanyName
FROM Customer
WHERE Paid is null and OrderDate >= ‘1/1/99’
ORDER BY SysDate-InvoiceDate
SELECT Name, (Sysdate-Birthdate)/365
FROM Employee
ORDER BY Name
SELECT EmpID, Hiredate+90
FROM Employee
ORDER BY Name
SQL Built-in Functions
Employee
EmpID
27
44
35
37
99
SELECT Count(*)
FROM Employee
Count(*)
5
Name
Rodney Jones
Goro Azuma
Francine Moire
Anne Abel
Mary Chen
Office
Toronto
Tokyo
Brussels
Tokyo
Tokyo
Salary HireDate
3000 12-May-92
1500
15-Mar-93
2500 22-Dec-89
1500
1-Aug-97
5000 17-Aug-95
SELECT Count(Distinct Office)
FROM Employee
CountDistinctOffice
SELECT Max(HireDate)
FROM Employee
MaxOfHireDate
1-Aug-97
3
SELECT Min(Name)
FROM Employee
MinOfName
Anne Abel
SELECT Sum(Salary)
FROM Employee
SumOfSalary
13500
SELECT Avg(Salary)
FROM Employee
AvgOfSalary
2700
Aggregate Functions and Grouping
Employee
EmpID
27
44
35
37
99
Name
Rodney Jones
Goro Azuma
Francine Moire
Anne Abel
Mary Chen
Office
Brussels
Tokyo
Brussels
Tokyo
Tokyo
Salary
3000
4000
2500
2000
5000
Status
part time
full time
full time
full time
full time
HireDate
12-May-92
15-Mar-93
22-Dec-89
1-Aug-97
17-Aug-95
SELECT Office, Count(*)
FROM Employee
GROUP BY Office
SELECT Office, Status, Max(Salary)
FROM Employee
GROUP BY Office, Status
Office Count(*)
Brussels
2
Tokyo
3
Office
Brussels
Brussels
Tokyo
Status
full time
part time
full time
MaxOfSalary
2500
3000
5000
To view the groupings, you must also select them!
More Grouping Functions
Product
ProdID
ProdDesc
Category
801
Shur-Lock U-Lock
Accessories
802
Price
Quantity
SupplierID
75.00
5
2
SpeedRite Cyclecomputer
Components
60.00
20
1
803
SteelHead MicroshellAccessories
Helmet
40.00
40
3
804
SureStop 133-MB Brakes
Components
25.00
10
2
805
Diablo ATM MountainComponents
Bike
1,200.00
2
2
806
Ultravision Helmet Mount
Accessories
Mirrors
7.45
10
SELECT Category, Avg(Price), Min(Quantity), Sum(Price*Quantity)
FROM Product
WHERE SupplierID in (1, 2) or SupplierID is null
GROUP BY Category
ORDER BY Avg(Price)
Category
Accessories
Components
AvgOfPrice MinOfQuantity SumOfPriceQuantity
41.2
5
449.5
428.3
2
3850
Restrict Groups with “Having”
Product
ProdID
ProdDesc
Category
801
Shur-Lock U-Lock
Accessories
802
Price
Quantity
SupplierID
75.00
5
2
SpeedRite Cyclecomputer
Components
60.00
20
1
803
SteelHead MicroshellAccessories
Helmet
40.00
40
3
804
SureStop 133-MB Brakes
Components
25.00
10
2
805
Diablo ATM MountainComponents
Bike
1,200.00
2
2
806
Ultravision Helmet Mount
Accessories
Mirrors
7.45
10
SELECT Category, Avg(Price), Min(Quantity), Sum(Price*Quantity)
FROM Product
WHERE SupplierID in (‘1’, ‘2’) or SupplierID is null
GROUP BY Category
HAVING Min(Quantity) < 5
Category
Components
AvgOfPrice MinOfQuantity SumOfPriceQuantity
428.3
2
3850
The ‘WHERE’ clause is always evaluated before the ‘HAVING’ clause.
Another ‘Having’ example
Employee
EmpID
27
44
35
37
99
25
10
Name
Rodney Jones
Goro Azuma
Francine Moire
Anne Abel
Mary Chen
Brigit Sanchez
Joki Singh
Office
Brussels
Tokyo
Brussels
Tokyo
Tokyo
Toronto
Brussels
SELECT Office, Max(Salary)
FROM Employee
WHERE Status = ‘full-time’
GROUP BY Office
HAVING Count(*) > 1
ORDER BY Office
Salary
3000
4000
2500
2000
5000
10000
1000
Status
part time
full time
full time
full time
full time
full time
full time
HireDate
12-May-92
15-Mar-93
22-Dec-89
1-Aug-97
17-Aug-95
3-Jan-00
3-Jan-00
Office MaxOfSalary
Brussels
2500
Tokyo
5000
Subqueries
Product
ProdID
ProdDesc
Category
Price
Qty
SupID
801
Shur-Lock U-Lock
Accessories 75
5
2
802
SpeedRite Cyclecomputer
Components 60
20
1
803
SteelHead Microshell
Accessories
Helmet 40
40
3
804
SureStop 133-MB
Components
Brakes
25
10
2
805
Diablo ATM Mountain
Accessories
Bike 1,200
2
2
SELECT ProdID, Price, Qty
FROM Product
WHERE SupID IN
(SELECT SupplierID
FROM Supplier
WHERE City = ‘London’)
ProdID
Price
Qty
Subquery is always evaluated
before the main query.
Supplier
SupplierID
1
2
3
SupplierName
City
Bikes-R-Us
London
Small moter suppliers
Toronto
All Bikes AllwaysLondon
SELECT ProdID
FROM Product
WHERE Qty >
(SELECT Avg(Qty)
FROM Product
WHERE SupID IN (‘1’, ‘2’))
ProdID
802
60
20
802
803
40
40
804
Nested Subqueries
Agent
AgentID AgentName
1
Anne Abel
2
Goro Azuma
3
Mary Chen
Commission
PolicyPlan
AgentID PolicyNo. Comm PlanCodePolicyNo. Category
Area
45
3
FG
5%
5
1
100
98
5
SR
7%
9
1
200
45
9
JR
3%
3
3
100
5%
7
2
45
9
FR
Determine the number of policies sold in area 100 of type 45:
SELECT Count(Unique PolicyNum)
FROM PolicyPlan
WHERE PlanCode = ‘45’ and PolicyNum IN
(SELECT PolicyNum
FROM Commission
WHERE AgentNum IN
(SELECT AgentNum
FROM Agent
WHERE Area = 100))
CountOfPolicyNo.
2
Join (Natural Join)
Product
ProdID
ProdDesc
Category
Price
Qty
SupID
801
Shur-Lock U-Lock
Accessories
75.00
5
2
802
SpeedRite Cyclecomputer
Components
60.00
20
1
803
SteelHead MicroshellAccessories
Helmet
40.00
40
3
804
SureStop 133-MB Brakes
Components
25.00
10
2
805
Diablo ATM MountainAccessories
Bike
1,200.00
2
2
Supplier
SupplierID
SupplierName
1
Bikes-R-Us
2
Small moter suppliers
3
All Bikes Allways
SELECT Product ProdID, Product ProdDesc, Supplier SupplierName
FROM Product, Supplier
WHERE Product SupID = Supplier SupplierID
Table names
ProdID
ProdDesc
SupplierName
801
Shur-Lock U-Lock
Small motor suppliers
802
SpeedRite Cyclecomputer
Bikes-R-Us
803
SteelHead Microshell Helmet
All Bikes Allways
804
SureStop 133-MB Brakes
Small motor suppliers
805
Diablo ATM Mountain Bike
Small motor suppliers
required in
Select
statement only
if there’s a
possibility of
ambiguity
Renaming Attributes and Relations with an Alias
Product
ProdID
ProdDesc
Category
Price
Qty
SupID
801
Shur-Lock U-Lock Accessories
75.00
5
2
802
SpeedRite Cyclecomputer
Components
60.00
20
1
803
SteelHead Microshell
Accessories
Helmet
40.00
40
3
804
SureStop 133-MB Brakes
Accessories
25.00
10
2
805
Diablo ATM Mountain
Bike
Bike
1,200.00
2
2
Supplier
SupplierID
SupplierName
1
Bikes-R-Us
2
Small moter suppliers
3
All Bikes Allways
SELECT SupplierName, avg(price) “Average Price”, count(*) “# of Items”
FROM Product P, Supplier S
WHERE P SupID = S SupplierID
alias
and Category = ‘Accessories’
GROUP BY SupplierName SupplierName
Average Price # of Items
ORDER BY SupplierID
Small motor suppliers
50
2


All Bikes Allways
40
1
‘Exists’ and ‘not exists’ in Subqueries
Dependent
Employee
EmpID
27
44
35
Name
Office Sex
Rodney Jones Brussels M
Goro Azuma
Tokyo
M
Francine Moire Brussels F
EmpID
27
27
35
35
35
DepName
Jane Jones
Jenny Jones
Mary Moire
Melvin Moire
Minnie Moire
DepSex
F
F
F
M
F
Which employees have dependents of Which employees have no
dependents?
the same sex as themselves?
SELECT Name
FROM Employee E
WHERE EXISTS
(SELECT *
FROM Dependent D
WHERE E.EmpID = D.EmpID
and E.Sex = D.DepSex)
Name
Francine Moire
SELECT Name
FROM Employee E
WHERE NOT EXISTS
(SELECT *
FROM Dependent D
WHERE E.EmpID = D.EmpID)
Name
Goro Azuma
Descargar

instruct.uwo.ca