ASSOSA UNIVERSITY

College of Computing and Informatics

DEPARTMENT OF COMPUTER SCIENCE

Fundamental Database System

Chapter 7: SQL Language

1
 SQL Introduction
 SQL is Structured Query Language, which is a computer
language for storing, manipulating and retrieving data stored in a
relational database.
 SQL is the standard language for Relational Database System.
 All the Relational Database Management Systems (RDMS) like
MySQL, MS Access, Oracle, Sybase, Informix, Postgres and
SQL Server use SQL as their standard database language.
 Many standards out there:
 ANSI SQL, SQL92 (a.k.a. SQL2), SQL99 (a.k.a. SQL3),
….
 Vendors support various subsets: watch for fun discussions in
class!

2
 Why WE CARE SQL?
 SQL is widely popular because it offers the following
advantages:
Allows users to access and describe data in the relational
database management systems.
Allows users to define the data in a database and manipulate
that data.
Allows to embed within other languages using SQL modules,
libraries & pre-compilers.
Allows users to create and drop databases and tables.
Allows users to create view, stored procedure, functions in a
database.
Allows users to set permissions on tables, procedures and views.

3
 SQL
 Data Definition Language (DDL)
 Create/alter/delete tables and their attributes
 Following lectures...
 Data Manipulation Language (DML)
 Query one or more tables – discussed next !
 Insert/delete/modify tuples in tables
 Data control language(DCL)
 Grant, revoke

4
 SQL Constraints
 Constraints are the rules enforced on data columns on a table.
 These are used to limit the type of data that can go into a table.
 Following are some of the most used constraints available in SQL.
 NOT NULL Constraint: Ensures that a column cannot have a NULL value.
 DEFAULT Constraint: Provides a default value for a column when none is
specified.
 UNIQUE Constraint: Ensures that all the values in a column are different.
 PRIMARY key: Uniquely identifies each row/record in a database table.
 FOREIGN key: Uniquely identifies a row/record in any another database
table.
 CHECK Constraint: The CHECK constraint ensures that all values in a
column satisfy certain conditions.
 INDEX: Used to create and retrieve data from the database very quickly.

5
 Data Integrity
 The following categories of data integrity exist with each
RDBMS:
Entity Integrity: There are no duplicate rows in a table.

Domain Integrity: Enforces valid entries for a given column by

restricting the type, the format, or the range of values.

Referential integrity: Rows cannot be deleted, which are used by

other records.

User-Defined Integrity: Enforces some specific business rules that

do not fall into entity, domain or referential integrity.

6
 Working with table structures
CREATE TABLE: Create a new table in the database.

ALTER TABLE: Modify the structure of an existing table.

DROP TABLE: Remove the tables permanently.

TRUNCATE TABLE: Delete all data in a big table fast and efficiently.

7
Tables in SQL
Table name s
t e n ame
Product Att ribu

PName Price Category Manufacturer

Gizmo $19.99 Gadgets GizmoWorks

Powergizmo $29.99 Gadgets GizmoWorks

SingleTouch $149.99 Photography Canon

MultiTouch $203.99 Household Hitachi

e s or
l
Tup ows
r
8
Tables Explained
 The schema of a table is the table name and its attributes:
Product(PName, Price, Category, Manfacturer)
 A key is an attribute whose values are unique;
we underline a key
Product(PName, Price, Category, Manfacturer)

9
Data Types in SQL
 Atomic types:
 Characters: CHAR(20), VARCHAR(50)
 Numbers: INT, BIGINT, SMALLINT, FLOAT
 Others: MONEY, DATETIME, …
 Every attribute must have an atomic type
 Hence tables are flat
 Why ?

10
Tables Explained
 A tuple = a record
 Restriction: all attributes are of atomic type

 A table = a set of tuples

 Like a list…
 …but it is unorderd:
no first(), no next(), no last().

11
SQL Query
 Basic form: (plus many more bells and whistles)
SELECT
SELECT <attributes>
<attributes>
FROM
FROM <one
<oneorormore
morerelations>
relations>
WHERE
WHERE <conditions>
<conditions>

12
 Simple SQL Query
Product Manufactu
PName Price Category
rer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT
SELECT **
FROM
FROM Product
Product
WHERE
WHERE category=‘Gadgets’
category=‘Gadgets’

PName Price Category Manufacturer

“selection” Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks

13
 Simple SQL Query
PName Price Category Manufacturer
Product Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT
SELECT PName,
PName,Price,
Price,Manufacturer
Manufacturer
FROM
FROM Product
Product
WHERE
WHERE Price
Price>>100
100

“selection” and PName Price Manufacturer

“projection”
SingleTouch $149.99 Canon
MultiTouch $203.99 Hitachi

14
 Notation
Input Schema

Product(PName, Price, Category, Manfacturer)

SELECT
SELECT PName,
PName,Price,
Price,Manufacturer
Manufacturer
FROM
FROM Product
Product
WHERE
WHERE Price
Price>>100
100
Answer(PName, Price, Manfacturer)

Output Schema

15
 Details
 Case insensitive:
 Same: SELECT Select select
 Same: Product product
 Different: ‘Seattle’ ‘seattle’
 Constants:
 ‘abc’ - yes
 “abc” - no

16
The LIKE operator

SELECT
SELECT **
FROM
FROM Products
Products
WHERE
WHERE PName
PNameLIKE
LIKE‘%gizmo%’
‘%gizmo%’

 s LIKE p: pattern matching on strings

 p may contain two special symbols:
 % = any sequence of characters
 _ = any single character

17
 Eliminating Duplicates

Category
SELECT
SELECT DISTINCT
DISTINCTcategory
category Gadgets
FROM
FROM Product
Product Photography
Household

Compare to:

Category
SELECT
SELECT category
categoryFROM
FROM Gadgets
Product
Product Gadgets
Photography
Household

18
 Ordering the Results
SELECT
SELECT pname,
pname,price,
price,manufacturer
manufacturer
FROM
FROM Product
Product
WHERE
WHERE category=‘gizmo’
category=‘gizmo’AND
ANDprice
price>>50
50
ORDER
ORDERBYBY price,
price,pname
pname

 Ties are broken by the second attribute on the ORDER BY list,

etc.

 Ordering is ascending, unless you specify the DESC keyword.

19
 POP up question
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT
SELECT DISTINCT
FROM
DISTINCTcategory
FROM Product
category
ProductORDER
ORDERBY BYcategory
category ?
SELECT
SELECT Category
ORDER
ORDERBY
CategoryFROM
BY PName
PName
FROM Product
Product
?
?
SELECT
SELECT DISTINCT
DISTINCTcategory
categoryFROM
FROM
Product
ProductORDER
ORDERBYBYPName
PName
20
 Keys and Foreign Keys
Company e y
K
CName StockPrice Country

GizmoWorks 25 USA r e ign

Fo
Canon 65 Japan key

Hitachi 15 Japan

PName Price Category Manufacturer

Product
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

21
 Joins

 Product
Product(pname,
(pname, price,
price,category,
category,manufacturer)
manufacturer)

 Company
Company(cname,
(cname,stockPrice,
stockPrice,country)
country)

 Find all products under $200 manufactured in Japan;

return their names and prices.
Join
between Product
and Company
SELECT
SELECT PName,
PName,Price
Price
FROM
FROM Product,
Product,Company
Company
WHERE
WHERE Manufacturer=CName
Manufacturer=CNameAND
ANDCountry=‘Japan’
Country=‘Japan’
AND
ANDPrice
Price<=
<=200
200

22
 Joins
Product Company
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks Cname StockPrice Country
Powergizmo $29.99 Gadgets GizmoWorks GizmoWorks 25 USA
SingleTouch $149.99 Photography Canon Canon 65 Japan
MultiTouch $203.99 Household Hitachi Hitachi 15 Japan

SELECT
SELECT PName,
PName,Price
Price
FROM
FROM Product,
Product,Company
Company PName Price
WHERE
WHERE Manufacturer=CName
Manufacturer=CNameAND
ANDCountry=‘Japan’
Country=‘Japan’ SingleTouch $149.99
AND
ANDPrice
Price<=
<=200
200

23
 More Joins
 Product (pname, price, category, manufacturer)
 Company (cname, stockPrice, country)

 Find all Chinese companies that manufacture products both in the

‘electronic’ and ‘toy’ categories.

SELECT
SELECT cname
cname

FROM
FROM

WHERE
WHERE

24
 A Subtlety about Joins
 Product (pname, price, category, manufacturer)
 Company (cname, stockPrice, country)
 Find all countries that manufacture some product in the ‘Gadgets’ category .

SELECT
SELECT Country
Country
FROM
FROM Product,
Product,Company
Company
WHERE
WHERE Manufacturer=CName
Manufacturer=CNameAND
ANDCategory=‘Gadgets’
Category=‘Gadgets’

USA
Unexpected duplicates USA

25
 A Subtlety about Joins

Product Company
Name Price Category Manufacturer Cname StockPrice Country
Gizmo $19.99 Gadgets GizmoWorks GizmoWorks 25 USA
Powergizmo $29.99 Gadgets GizmoWorks Canon 65 Japan
SingleTouch $149.99 Photography Canon
Hitachi 15 Japan
MultiTouch $203.99 Household Hitachi

SELECT
SELECT Country
Country
FROM
FROM Product,Company
Product, Company
WHERE
WHERE Manufacturer=CNameAND
Manufacturer=CName ANDCategory=‘Gadgets’
Category=‘Gadgets’

Country
What is ??
the problem ? ??
What’s the
solution ?
26
Tuple Variables
 Person(pname, address, worksfor)
 Company(cname, address)
SELECT
SELECT DISTINCT
DISTINCTpname,
pname,address
address Which
FROM
FROM Person,
Person,Company
Company address ?
WHERE
WHERE worksfor
worksfor==cname
cname

SELECT
SELECT DISTINCT
[Link],
[Link],[Link]
[Link]
FROM
FROM Person,
Person,Company
Company
WHERE
WHERE [Link]
[Link]==[Link]
[Link]

SELECT
SELECT DISTINCT
[Link],
[Link],[Link]
[Link]
FROM
FROM Person
PersonASASx,x,Company
CompanyAS ASyy
WHERE
WHERE [Link]
[Link]==[Link]
[Link]
27
 Meaning (Semantics) of SQL Queries
SELECT
SELECTaa11,,aa22,,…,
…,aakk
FROM
FROM RR11AS ASxx11,,RR22AS
ASxx22,,…,
…,RRnnAS
ASxxnn
WHERE
WHERE Conditions
Conditions

Answer
Answer=={} {}
for
forxx11in
inRR11do
do
for
forxx22in
inRR22dodo
…..
…..
for
forxxnnin
inRRnndo
do
ififConditions
Conditions
then
thenAnswer Answer
Answer==Answer {(a
{(a11,…,a
,…,akk)}
)}
return
returnAnswer
Answer

28
 An Unintuitive Query
SELECT
SELECT DISTINCT
DISTINCTR.A
R.A
FROM
FROM R,
R,S,
S,TT
WHERE
WHERE R.A=S.A
R.A=S.A OR
OR R.A=T.A
R.A=T.A

What does it compute ?

Computes R Ç (S È T) But what if S = f ?

29
 Subqueries Returning Relations
 Company(name, city)
 Product(pname, maker)
 Purchase(id, product, buyer)

 Return cities where one can find companies that manufacture

products bought by Customer name Aster.

SELECT
SELECT [Link]
[Link]
FROM
FROM Company
Company
WHERE
WHERE [Link]
[Link] ININ
(SELECT
([Link]
[Link]
FROM
FROM Purchase
Purchase, ,Product
Product
WHERE
[Link]=[Link]
[Link]=[Link]
AND
ANDPurchase
[Link]
.buyer==‘Aster‘);
‘Aster‘);
30
 Subqueries Returning Relations
Is it equivalent to this ?

SELECT
SELECT [Link]
[Link]
FROM
FROM Company,
Company,Product,
Product,Purchase
Purchase
WHERE
WHERE [Link]=
[Link]=[Link]
[Link]
AND
AND [Link]
[Link] ==[Link]
[Link]
AND
AND [Link]
[Link]==‘Joe
‘JoeBlow’
Blow’

Beware of duplicates !

31
 Removing Duplicates
SELECT
SELECTDISTINCT
[Link]
[Link]
FROM
FROM Company
Company
WHERE
WHERE [Link]
[Link] IN
IN
(SELECT
([Link]
[Link]
FROM
FROM Purchase
Purchase, ,Product
Product
WHERE
[Link]=[Link]
[Link]=[Link]
AND
ANDPurchase
[Link]
.buyer==‘Aster‘);
‘Aster‘);

 Now they are equivalent

SELECT
SELECTDISTINCT
[Link]
[Link]
FROM
FROM Company,
Company,Product,
Product,Purchase
Purchase
WHERE
WHERE [Link]=
[Link]=[Link]
[Link]
AND
AND [Link]
[Link] ==[Link]
[Link]
AND
AND [Link]
[Link]=‘Aster’
=‘Aster’

32
 Subqueries Returning Relations
You can also use: s > ALL R
s > ANY R
EXISTS R

Product ( pname, price, category, maker)

Find products that are more expensive than all those produced By “Gizmo-Works”

SELECT
SELECT name
name
FROM
FROM Product
Product
WHERE
WHERE price
price>> ALL
ALL(SELECT
(SELECTprice
price
FROM
FROM Purchase
Purchase
WHERE maker=‘Gizmo-Works’)
WHERE maker=‘Gizmo-Works’)

33
 Correlated Queries
 Movie (title, year, director, length)
Find movies whose title appears more than once.
correlation

SELECT
SELECTDISTINCT
DISTINCTtitle
title
FROM
FROM Movie
MovieAS
ASxx
WHERE
WHERE year
year<>
<>ANY
ANY
(SELECT
(SELECT year
year
FROM
FROM Movie
Movie
WHERE
WHERE title
title== [Link]);
[Link]);

Note
 scope of variables
 this can still be expressed as single SFW
34
 Complex Correlated Query
Product ( pname, price, category, maker, year)
 Find products (and their manufacturers) that are more expensive
than all products made by the same manufacturer before 1972

SELECT
SELECTDISTINCT
DISTINCT pname,
pname,maker
maker
FROM
FROM Product
ProductAS
ASxx
WHERE
WHERE price
price>>ALL
ALL (SELECT
(SELECT price
price
FROM
FROM ProductAS
Product ASyy
WHERE
WHERE [Link]
[Link]==[Link]
[Link]
[Link]
[Link]<<1972);
1972);

Very powerful ! Also much harder to optimize.

35
 Aggregation
• SQL supports several aggregation operations:
Sum, count, min, Max, avg

SELECT
SELECT avg(price)
avg(price)
FROM
FROM Product
Product
WHERE
WHERE maker=“Toyota”
maker=“Toyota”

SELECT
SELECT count(*)
count(*)
FROM
FROM Product
Product
WHERE
WHERE year
year>>1995
1995

• Except count, all aggregations apply to a single attribute

36
 Aggregation: Count
COUNT applies to duplicates, unless otherwise stated:
same as Count(*)

SELECT
SELECT Count(category)
Count(category) FROM
FROM Product
Product WHERE
WHERE year
year>>1995
1995

We probably want:

SELECT
SELECT Count(DISTINCT
Count(DISTINCTcategory)
category)FROM
FROM Product
ProductWHERE
WHERE year
year>>
1995
1995

37
 More Examples
Purchase(product, date, price, quantity)

t do ?
ha e a n
W m
y
the
SELECT
SELECT Sum(price
Sum(price**quantity)
quantity)FROM
FROM Purchase
Purchase

SELECT
SELECT Sum(price
Sum(price**quantity)
quantity)FROM
FROM Purchase
PurchaseWHERE
WHERE product
product==‘bagel’
‘bagel’

38
 Simple Aggregations
Purchase
Product Date Price Quantity
Bagel 10/21 1 20
Banana 10/3 0.5 10
Banana 10/10 1 10
Bagel 10/25 1.50 20

SELECT
SELECT Sum(price
Sum(price**quantity)
quantity)FROM
FROM Purchase
PurchaseWHERE
WHERE product
product==
‘bagel’
‘bagel’

50 (= 20+30)

39
 Grouping and Aggregation
Purchase(product, date, price, quantity)

Find total sales after 10/1/2005 per product.

SELECT
SELECT product,
product,Sum(price*quantity)
Sum(price*quantity)AS
ASTotalSales
TotalSales
FROM
FROM Purchase
Purchase
WHERE
WHERE date
date>>‘10/1/2005’
‘10/1/2005’
GROUP
GROUPBY
BY product
product

Let’s see what this means…

40
Grouping and Aggregation
1. Compute the FROM and WERE clauses.

2. Group by the attributes in the GROUPBY

3. Compute the SELECT clause: grouped attributes and

aggregates.

41
 1&2. FROM-WHERE-GROUPBY

Product Date Price Quantity

Bagel 10/21 1 20

Bagel 10/25 1.50 20

Banana 10/3 0.5 10

Banana 10/10 1 10

42
 3. SELECT

Product Date Price Quantity Product TotalSales

Bagel 10/21 1 20
Bagel 10/25 1.50 20 Bagel 50
Banana 10/3 0.5 10
Banana 15
Banana 10/10 1 10

SELECT
SELECT product,
product,Sum(price*quantity)
Sum(price*quantity)AS
ASTotalSales
TotalSales
FROM
FROM Purchase
Purchase
WHERE
WHERE date
date>>‘10/1/2005’
‘10/1/2005’
GROUP
GROUPBYBY product
product

43
 GROUP BY v.s. Nested Quereis
SELECT
SELECT product,
product,Sum(price*quantity)
Sum(price*quantity)AS
ASTotalSales
TotalSales
FROM
FROM Purchase
Purchase
WHERE
WHERE datedate>>‘10/1/2005’
‘10/1/2005’
GROUP
GROUPBYBY product
product

SELECT
SELECTDISTINCT
DISTINCT [Link],
[Link],(SELECT
(SELECTSum([Link]*[Link])
Sum([Link]*[Link])
FROM
FROM Purchase
Purchaseyy
WHERE
[Link]
[Link]==[Link]
[Link]
AND
[Link]
[Link]>>‘10/1/2005’)
‘10/1/2005’)
AS
ASTotalSales
TotalSales
FROM
FROM Purchase
Purchasexx
WHERE
WHERE [Link]
[Link]>>‘10/1/2005’
‘10/1/2005’

44
 Another Example
 SELECT
SELECT product,
product,
sum(price
sum(price**quantity)
quantity)AS
ASSumSales
SumSales
max(quantity)
max(quantity)AS
ASMaxQuantity
MaxQuantity
FROM
FROM Purchase
Purchase
GROUP
GROUPBY BYproduct
product

What does
it mean ?

45
 HAVING Clause
 Same query, except that we consider only products that
had at least 100 buyers.

SELECT
SELECT product,
product,Sum(price
Sum(price**quantity)
quantity)
FROM
FROM Purchase
Purchase
WHERE
WHERE date
date>>‘10/1/2005’
‘10/1/2005’
GROUP
GROUPBY
BYproduct
product
HAVING
HAVING Sum(quantity)
Sum(quantity)>>30
30

 HAVING clause contains conditions on aggregates.

46
General form of Grouping and Aggregation
SELECT
SELECT SS
FROM
FROM RR1,…,R
1,…,Rnn

WHERE
WHERE C1
C1
GROUP
GROUPBY
BYaa11,…,a
,…,akk
HAVING
HAVING C2’
C2’
Why ?

S = may contain attributes a1,…,ak and/or any aggregates but NO

OTHER ATTRIBUTES

C1 = is any condition on the attributes in R 1,…,Rn

C2 = is any condition on aggregate expressions 47

 General form of Grouping and Aggregation
SELECT
SELECT SS
FROM
FROM RR1,…,R
1,…,Rn
n
WHERE
WHERE C1C1
GROUP
GROUPBYBYaa1,…,a
1,…,ak
k
HAVING
HAVING C2C2

Evaluation steps:
1. Evaluate FROM-WHERE, apply condition C1
2. Group by the attributes a1,…,ak
3. Apply condition C2 to each group (may have aggregates)
4. Compute aggregates in S and return the result

48
 Advanced SQLizing
1. Getting around INTERSECT and EXCEPT

2. Quantifiers

3. Aggregation v.s. subqueries

49
 INTERSECT and EXCEPT: not in SQL
Server
1. INTERSECT and EXCEPT:
duplicates,
If R , S h a v e n o
without
then can write
W ?)
subqueries (HO

(SELECT
(SELECTR.A,
R.A,R.B
R.B SELECT
SELECTR.A,
R.A,R.B
R.B
FROM
FROM R) R) FROM
FROM RR
INTERSECT
INTERSECT WHERE
WHERE
(SELECT
(SELECTS.A,
S.A,S.B
S.B EXISTS(SELECT
EXISTS(SELECT**
FROM
FROM S) S) FROM
FROMSS
WHERE
WHERER.A=S.A
R.A=[Link]
andR.B=S.B)
R.B=S.B)

(SELECT SELECT
SELECTR.A,
R.A,R.B
(SELECTR.A,
R.A, R.B
R.B FROM
FROM RR
R.B
FROM WHERE
FROM R)R) WHERE
EXCEPT NOT
NOT EXISTS(SELECT
EXISTS(SELECT**
EXCEPT
(SELECT FROM
FROMSS
(SELECTS.A,
S.A,S.B
S.B
FROM WHERE
WHERER.A=S.A
R.A=[Link]
andR.B=S.B)
FROM S)S) R.B=S.B)
50
 2. Quantifiers
 Product ( pname, price, company)
 Company( cname, city)

Find all companies that make some products with price < 100

SELECT
SELECTDISTINCT
DISTINCT [Link]
[Link]
FROM
FROM Company,
Company,Product
Product
WHERE
WHERE [Link]
[Link]==[Link]
[Link]
[Link]
[Link]<<100
100

Existential: easy ! 
51
 2. Quantifiers
 Product ( pname, price, company)
 Company( cname, city)

Find all companies that make only products with price < 100

same as:

Find all companies s.t. all of their products have price < 100

Universal: hard ! 

52
 2. Quantifiers
1. Find the other companies: i.e. s.t. some product  100
SELECT
SELECTDISTINCT
DISTINCT [Link]
[Link]
FROM
FROM Company
Company
WHERE
WHERE [Link]
[Link]
IN(SELECT
([Link]
[Link]
FROM
FROMProduct
Product
WHERE
[Link]
[Link]>=
>=100
100

2. Find all companies s.t. all their products have price < 100
SELECT
SELECTDISTINCT
DISTINCT [Link]
[Link]
FROM
FROM Company
Company
WHERE
WHERE [Link]
[Link] NOTININ(SELECT
([Link]
[Link]
FROM
FROMProduct
Product
WHERE
[Link]
[Link]>=
>=100
100

53
 3. Group-by v.s. Nested Query
 Author(login,name)
 Wrote(login,url)
 Find authors who wrote ³ 10 documents:
is
 Attempt 1: with nested queries T hi s y
b
SQL ice
v
a no

SELECT
SELECTDISTINCT
[Link]
[Link]
FROM
FROM Author
Author
WHERE
WHERE count(SELECT
count([Link]
[Link]
FROM
FROMWrote
Wrote
WHERE
[Link]=[Link])>
[Link]=[Link])>10
10

54
 3. Group-by v.s. Nested Query
 Find all authors who wrote at least 10 documents:
is
 Attempt 2: SQL style (with GROUP BY) This y
b
SQL ert
p
an ex

SELECT
[Link]
[Link]
FROM Author,
Author,Wrote
Wrote
WHERE
WHERE [Link]=[Link]
[Link]=[Link]
GROUPBY [Link]
[Link]
HAVING
HAVING count([Link])
count([Link])>>10
10

No need for DISTINCT: automatically from GROUP BY

55
 3. Group-by v.s. Nested Query
 Author(login,name)
 Wrote(login,url)
 Mentions(url,word)

Find authors with vocabulary ³ 10000 words:

SELECT
SELECT [Link]
[Link]
FROM
FROM Author,
Author,Wrote,
Wrote,Mentions
Mentions
WHERE
WHERE [Link]=[Link]
[Link]=[Link]
[Link]=[Link]
[Link]=[Link]
GROUP
GROUPBY
BY [Link]
[Link]
HAVING
HAVING count(distinct
count([Link])
[Link])>>10000
10000

56
 Two Examples
 Store(sid, sname)
 Product(pid, pname, price, sid)

Find all stores that sell only products with price > 100

same as:

Find all stores s.t. all their products have price > 100)

57
 SELECT
[Link]
[Link]
FROM
FROM Store,
Store,Product
Product
WHERE
WHERE [Link]
[Link]==[Link]
[Link]
GROUP Why both ?
GROUPBY
BY [Link],
[Link],[Link]
[Link]
HAVING
HAVING100
100<<min([Link])
min([Link])

SELECT
[Link]
[Link]
FROM
FROM Store
Store
WHERE
WHERE
nt…

100
100<<ALL
ALL(SELECT
([Link]
[Link]
FROM product
ale

FROM product
WHERE
[Link]
[Link]==[Link])
uiv

[Link])
eq
st

SELECT
[Link]
[Link]
mo

FROM
FROM Store
Store
Al

WHERE
WHERE [Link]
[Link]
NOTININ
(SELECT
([Link]
[Link]
FROM Product
FROM Product
WHERE
WHERE [Link]
[Link]<=
<=100)
100)

58
 Two Examples
 Store(sid, sname)
 Product(pid, pname, price, sid)

For each store, find its most expensive product?

59
 Two Examples
This is easy but doesn’t do what we want:
SELECT
[Link],
[Link],max([Link])
max([Link])
FROM
FROM Store,
Store,Product
Product
WHERE
WHERE [Link]
[Link]==[Link]
[Link]
GROUP
GROUPBY
BY [Link],
[Link],[Link]
[Link]
Better:

But may SELECT

[Link],
[Link],[Link]
[Link]
return FROM
FROM Store,
Store,Product
Productxx
multiple WHERE
WHERE [Link]
[Link]==[Link]
[Link]
and
product names [Link]
[Link]>=
>=
per store ALL
ALL(SELECT
([Link]
[Link]
FROM
FROMProduct
Productyy
WHERE
[Link]
[Link]==[Link])
[Link])

60
 Two Examples
Finally, choose some pid arbitrarily, if there are many with highest price:

SELECT
[Link],
[Link], max([Link])
max([Link])
FROM
FROM Store,
Store,Product
Productxx
WHERE
WHERE [Link]
[Link]==[Link]
[Link]
and
[Link]
[Link]>=
>=
ALL
ALL(SELECT
([Link]
[Link]
FROM
FROMProduct
Productyy
WHERE
[Link]
[Link]==[Link])
[Link])
GROUP
[Link]
[Link]

61
 NULLS in SQL
 Whenever we don’t have a value, we can put a NULL
 Can mean many things:
 Value does not exist
 Value exists but is unknown
 Value not applicable
 Etc.
 The schema specifies for each attribute if can be null
(nullable attribute) or not
 How does SQL cope with tables that have NULLs ?

62
 Null Values
 If x= NULL, then 4*(3-x)/7 is still NULL

 If x= NULL, then x=“Joe” is UNKNOWN

 In SQL there are three boolean values:
FALSE = 0
UNKNOWN = 0.5
TRUE =1

63
 Null Values
 C1 AND C2 = min(C1, C2)
 C1 OR C2 = max(C1, C2)
 NOT C1 = 1 – C1

E.g., age=20, height=NULL, weight=200

SELECT
SELECT**
FROM
FROMPerson
Person
WHERE
WHERE (age
(age<<25)
25)AND
AND
(height
(height>>66OR
ORweight
weight>>190)
190)

Rule in SQL: include only tuples that yield TRUE

64
 Null Values
Unexpected behavior:

SELECT
SELECT**
FROM
FROM Person
Person
WHERE
WHERE age
age<<25
25 OR
OR age
age>=
>=25
25

Some Persons are not included !

65
 Null Values
 Can test for NULL explicitly:
 x IS NULL
 x IS NOT NULL

SELECT
SELECT**
FROM
FROM Person
Person
WHERE
WHERE age
age<<25
25 OR
OR age
age>=
>=25
25OR
ORage
ageIS
ISNULL
NULL

 Now it includes all Persons

66
 Outerjoins
Explicit joins in SQL = “inner joins”:
 Product(name, category)
 Purchase(prodName, store)

SELECT
[Link],
[Link],[Link]
[Link]
FROM
FROM Product
ProductJOIN
JOINPurchase
PurchaseON
ON
[Link]
[Link]==[Link]
[Link]

Same as:
SELECT
[Link],
[Link],[Link]
[Link]
FROM
FROM Product,
Product,Purchase
Purchase
WHERE
WHERE [Link]
[Link]==[Link]
[Link]

But Products that never sold will be lost !

67
 Outerjoins
Left outer joins in SQL:
 Product(name, category)
 Purchase(prodName, store)

SELECT
[Link],
[Link],[Link]
[Link]
FROM
FROM Product LEFT OUTERJOIN
Product LEFT OUTER JOINPurchase
PurchaseON
ON
[Link]
[Link]==[Link]
[Link]

68
Product Purchase
Name Category ProdName Store

Gizmo gadget Gizmo Wiz

Camera Photo Camera Ritz

OneClick Photo Camera Wiz

Name Store

Gizmo Wiz

Camera Ritz

Camera Wiz

OneClick NULL
69
 Application
Compute, for each product, the total number of sales in ‘September’
 Product(name, category)
 Purchase(prodName, month, store)

SELECT
[Link],
[Link],count(*)
count(*)
FROM
FROM Product,
Product,Purchase
Purchase
WHERE
WHERE [Link]
[Link]==[Link]
[Link]
and
and [Link]
[Link]==‘September’
‘September’
GROUP
[Link]
[Link]

What’s wrong ?

70
 Application
Compute, for each product, the total number of sales in ‘September’
 Product(name, category)
 Purchase(prodName, month, store)

SELECT
[Link],
[Link],count(*)
count(*)
FROM
FROM Product
ProductLEFT
LEFTOUTER
OUTERJOINJOINPurchase
PurchaseON
ON
[Link]
[Link]==[Link]
[Link]
and
and [Link]
[Link]==‘September’
‘September’
GROUP
GROUPBY
[Link]
[Link]

Now we also get the products who sold in 0 quantity

71
 Outer Joins
 Left outer join:
 Include the left tuple even if there’s no match
 Right outer join:
 Include the right tuple even if there’s no match
 Full outer join:
 Include the both left and right tuples even if there’s no match

72
 Modifying the Database
 Three kinds of modifications
 Insertions
 Deletions
 Updates
 Sometimes they are all called “updates”.

73
 Insertions
General form:
INSERT
INSERT INTO
INTO R(A1,….,
R(A1,….,An)
An) VALUES
VALUES (v1,….,
(v1,….,vn)
vn)

Example: Insert a new purchase to the database:

INSERT
INSERT INTO
INTO Purchase(buyer,
Purchase(buyer,seller,
seller,product,
product,store)
store)
VALUES
VALUES (‘Joe’,
(‘Joe’,‘Fred’,
‘Fred’,‘wakeup-clock-espresso-machine’,
‘wakeup-clock-espresso-machine’,
‘The
‘TheSharper
SharperImage’)
Image’)

 Missing attribute  NULL.

 May drop attribute names if give them in order.

74
 Insertion: an Example
 Product(name,
Product(name,listPrice,
listPrice,category)
category)
 Purchase(prodName,
Purchase(prodName,buyerName,
buyerName,price)
price)

 prodName is foreign key in [Link]

 Suppose database got corrupted and we need to fix it:
Product Purchase
prodName buyerName price
name listPrice category
camera John 200
gizmo 100 gadgets gizmo Smith 80
camera Smith 225

Task: insert in Product all prodNames from Purchase

76
 Insertion: an Example
INSERT
INSERT INTO
INTO Product(name)
Product(name)
SELECT
SELECT DISTINCT
DISTINCT prodName
prodName
FROM Purchase
FROM Purchase
WHERE
WHERE prodName
prodName NOT
NOTININ(SELECT
(SELECT name
nameFROM
FROM Product)
Product)

name listPrice category

gizmo 100 Gadgets

camera - -

77
 Insertion: an Example
INSERT
INSERT INTO
INTO Product(name,
Product(name,listPrice)
listPrice)

SELECT
SELECT DISTINCT
DISTINCT prodName,
prodName,price
price
FROM
FROM Purchase
Purchase
WHERE
WHERE prodName
prodName NOT
NOTININ(SELECT
(SELECT name
nameFROM
FROM Product)
Product)

name listPrice category

Depends on the
implementation
gizmo 100 Gadgets

camera 200 -

camera ?? 225 ?? -

78
 Deletions
Example:
DELETE
DELETE FROM
FROM PURCHASE
PURCHASEWHERE
WHERE seller
seller==‘Joe’
‘Joe’
AND
AND
product
product==‘Brooklyn
‘BrooklynBridge’
Bridge’

 Factoid about SQL: there is no way to delete only a single

occurrence of a tuple that appears twice in a relation.

79
 Updates
Example:
UPDATE
UPDATE PRODUCT
PRODUCT
SET
SET price
price==price/2
price/2
WHERE
WHERE [Link]
[Link] IN IN
(SELECT
(SELECTproduct
product
FROM Purchase
FROM Purchase
WHERE
WHERE DateDate=‘Oct,
=‘Oct,25,
25,1999’);
1999’);

80
.

Thank You!

81