Database Systems
CSE 303
Lecture 03: SQL
2016
Single Table Query
CSE 303: Ashikur Rahman 1
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
CSE 303: Ashikur Rahman 2
Table name Attribute names
Tables in SQL
Product
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
Tuples or rows CSE 303: Ashikur Rahman 3
SQL Query
Basic form: (plus many many more bells and whistles)
SELECT <attributes>
FROM <one or more relations>
CSE 303: Ashikur Rahman 4
Simplest 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
Show all entries from product table
CSE 303: Ashikur Rahman 5
Simplest SQL Query
Product 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 *
FROM Product
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
“selection” MultiTouch $203.99 Household Hitachi
CSE 303: Ashikur Rahman 6
Single Column Selection
Show all product categories
Product 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 category
FROM Product
Category
Gadgets
“selection” and Gadgets
“projection” Photography
CSE 303: Ashikur Rahman Household 7
Single Column Selection
(Eliminating Duplicates)
Product 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 DISTINCT category
FROM Product
Category
Gadgets
Show all product categories
Photography
Household
CSE 303: Ashikur Rahman 8
In summary..
Category
SELECT DISTINCT category Gadgets
FROM Product Photography
Household
Compare to:
Category
Gadgets
SELECT category Gadgets
FROM Product Photography
Household
CSE 303: Ashikur Rahman 9
Multi-column Selection
List all products along with their price and manufacturers
Product 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 pname, price, manufacturer
FROM Product
PName Price Manufacturer
Gizmo $19.99 GizmoWorks
Powergizmo $29.99 GizmoWorks
SingleTouch $149.99 Canon
CSE 303: Ashikur Rahman
MultiTouch $203.99 Hitachi 10
Notation
Input Schema
Product(PName, Price, Category, Manfacturer)
SELECT pname, price, manufacturer
FROM Product
Answer(PName, Price, Manfacturer)
CSE 303: Ashikur Rahman Output Schema 11
Duplicates from multiple column
Maker Model Type
A 1001 pc
A 1002 pc
A 1003 pc SELECT maker, type
A 2004 laptop
FROM Product
A 2005 laptop
A 2006 laptop
B 1004 pc
B 1005 pc
B 1006 pc
B 2007 laptop
C
…
….
1007
…
…
pc
…
…
?
CSE 303: Ashikur Rahman 12
Duplicates from multiple column
Maker Type
A pc
A pc
A pc SELECT maker, type
A laptop
FROM Product
A laptop
A laptop
B pc
B pc
B pc
B laptop
C pc
… …
…. …
CSE 303: Ashikur Rahman 13
Duplicates from multiple column
Maker Type
A pc
A pc
A pc SELECT DISTINCT maker, type
A laptop
FROM Product
A laptop
A laptop
B pc
B pc
B pc
B laptop
C pc
… …
…. …
CSE 303: Ashikur Rahman 14
Duplicates from multiple column
Maker Type
A pc
A laptop
B pc
B laptop
C pc
… …
…. …
… …
…. …
… …
…. …
… …
…. …
CSE 303: Ashikur Rahman 15
Simple Predicates
3
SELECT <attributes>
1 FROM <one or more relations>
2 WHERE <conditions>
CSE 303: Ashikur Rahman 16
Semantics
3
SELECT a1,…,ak
1 FROM R
2 WHERE conditions
Answer = {}
for each x in R do
if conditions satisfied then
Answer = Answer {(a1,…,ak)}
return Answer
CSE 303: Ashikur Rahman 17
Number Range Selection
Show all product’s info that will cost more than 100 dollars
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
Answer = {}
SELECT * for each x in R do
FROM Product if conditions satisfied then
WHERE price > 100 Answer = Answer {(a1,…,ak)}
return Answer
PName Price Category Manufacturer
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
CSE 303: Ashikur Rahman 18
Number Range Operators
• Greater than “>”
• Less than “<”
• Greater than or equal to “>=”
• Less than or equal to “<=”
• Two forms of not-equal-to
“!=” and “< >”
CSE 303: Ashikur Rahman 19
BETWEEN-AND operator
Show
Show all
all product’s info in the price range $100 to $200 (inclusive)
product categories
Product 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 *
FROM Product
WHERE price BETWEEN 100 AND 200
PName Price Category Manufacturer
BETWEEN-AND SingleTouch $149.99 Photography Canon
is inclusive CSE 303: Ashikur Rahman 20
String Matching
Show all household product’s info
Product 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 pname, price, manufacturer
FROM Product
WHERE category =‘Household’
Matches even category is
PName Price Manufacturer
fixed length CHAR data type
MultiTouch $203.99 Hitachi
right padded with blank CSE 303: Ashikur Rahman 21
The LIKE operator
• s LIKE p: pattern matching on strings
• p may contain two special symbols:
(1) % any sequence of characters (zero or more)
(2) _ any single character (exactly one)
CSE 303: Ashikur Rahman 22
The LIKE operator
Show all product’s info that has ‘mo’ as a substring of the product name
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 *
FROM Products
WHERE pname LIKE ‘%mo%’
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
CSE 303: Ashikur Rahman 23
The LIKE operator
Show all product’s info where the product name ends with ‘Touch’
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 *
FROM Products
WHERE pname LIKE ‘%Touch’
PName Price Category Manufacturer
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
CSE 303: Ashikur Rahman 24
The LIKE operator
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 *
FROM Products
WHERE manufacturer LIKE ‘G_z_oWorks’
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
CSE 303: Ashikur Rahman 25
Searching for three special
symbols
• ’ ‘Baby’s World’
• % ‘10%’
• _ ‘USA_Canada’
CSE 303: Ashikur Rahman 26
Searching for three special
symbols
• ’ ‘Baby’s World’
Search for all company names having apostrophe s in the
name?
Use double apostrophe
SELECT *
FROM Company
WHERE cname LIKE ‘%’’s%’
CSE 303: Ashikur Rahman 27
Searching for three special
symbols
• % ‘10%’
Search for all interest rates having percent at the end?
Use ESCAPE characters
SELECT *
FROM Bank
WHERE interestRates LIKE ‘%\%’ ESCAPE ‘\’
CSE 303: Ashikur Rahman 28
Searching for three special
symbols
• ‘_’ ‘USA_Canada’
Search for all country names having underscore inside?
Use ESCAPE characters
SELECT *
FROM Company
WHERE country LIKE ‘%\_%’ ESCAPE ‘\’
CSE 303: Ashikur Rahman 29
LIKE does not pad with blank
before matching
SELECT pname, price, manufacturer
FROM Product
WHERE category =‘Household’
Not equivalent
SELECT pname, price, manufacturer
FROM Product
WHERE category LIKE ‘Household’
CSE 303: Ashikur Rahman 30
The NOT LIKE operator
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 *
FROM Products
WHERE manufacturer NOT LIKE ‘%mo%’
PName Price Category Manufacturer
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
CSE 303: Ashikur Rahman 31
Combining Predicates
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
Show all household products having prices more than $100
CSE 303: Ashikur Rahman 32
Combining Predicates
Show all manufacturer that make photography or household items
Product 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 DISTINCT maker
FROM Product
WHERE category =‘Photography’
OR category =‘Household’
Manufacturer
Canon
CSE 303: Ashikur Rahman Hitachi
33
Combining Predicates
Show all manufacturer that make both photography and household items
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
WRONG! Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
SELECT DISTINCT maker
FROM Product
WHERE category =‘Photography’
AND category =‘Household’
PName Price Category Manufacturer
CSE 303: Ashikur Rahman 34
Combining Predicates
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Product
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
SELECT *
FROM Product
WHERE pname LIKE ‘%Giz%’
OR category LIKE ‘%hold’
AND price > 100
Pname Price Category Manufacturer
AND has higher
Gizmo $19.99 Gadgets GizmoWorks
precedence over OR MultiTouch $203.99 Household Hitachi
35
CSE 303: Ashikur Rahman0
Combining Predicates
PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Product
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
SELECT *
FROM Product
WHERE (pname LIKE ‘%Giz%’
OR category LIKE ‘%hold’)
AND price > 100
Pname Price Category Manufacturer
MultiTouch $203.99 Household Hitachi
CSE 303: Ashikur Rahman 36
Ordering the Results
PName Price Category Manufacturer
Default order is Ascending Gizmo $19.99 Gadgets GizmoWorks
Use DESC for descending Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
SELECT pname, price, manufacturer
FROM Product
WHERE price > 50
ORDER BY pname
Pname Price Category Manufacturer
MultiTouch $203.99 Household Hitachi
SingleTouch $149.99 Photography Canon
CSE 303: Ashikur Rahman 37
ORDER BY: Sorting the Results
SELECT PName, Price, Manufacturer
FROM Product
WHERE Category=‘gizmo’ AND Price > 50
ORDER BY Price, PName
Ties are broken by the Ordering is ascending,
second attribute on the unless you specify the
ORDER BY list, etc. DESC keyword.
CSE 303: Ashikur Rahman 38
IN operator CDCollection
artist album tracks company year
Find all albums U2 The Unforgettable 10 Island 1984
launched in 1984 or U2 Rattle and Hum 17 Island 1988
1996. U2 Achtung Boys 12 Island 1991
Underworld Second Toughest 8 Junior 1996
SELECT album
FROM CDCollection
WHERE year IN (1984, 1996)
SELECT album artist album tracks company year
U2 The Unforgettable 10 Island 1984
FROM CDCollection Underworld Second Toughest 8 Junior 1996
WHERE year =1984
OR year =1996 CSE 303: Ashikur Rahman 39
NOT IN operator
artist album tracks company year
U2 The Unforgettable 10 Island 1984
CDCollection
U2 Rattle and Hum 17 Island 1988
U2 Achtung Boys 12 Island 1991
Underworld Second Toughest 8 Junior 1996
SELECT *
FROM CDCollection
WHERE year NOT IN (1984, 1996)
SELECT * artist album tracks company year
FROM CDCollection U2 Rattle and Hum 17 Island 1988
WHERE year < > 1984 U2 Achtung Boys 12 Island 1991
AND year < > 1996 CSE 303: Ashikur Rahman
40
