3-Lect-Intro SQL -updated

Main Textbook
Database Systems: The Complete Book
Hector Garcia-Molina
Jeffrey D. Ullman
Jennifer Widom

Alternative Textbook
Database Management Systems
Raghu Ramakrishnan
Johannes Gehrke

Goals of Course
To obtain a firm background in database systems, e.g.,
how to talk to database systems in a standard language
how to improve the efficiency of database systems
the theories behind database design and some algorithms behind database implementation
Mostly “basic stuff” about databases

What will NOT be taught
Advanced database technologies
Geographical information systems
Data mining
…
(This is an introductory course only)
Specific instructions on how to install and use a specific database system on a specific platform
Try the user manual or Google

Teaching Style
There will be a lot of in-lecture exercises
Questions will be welcomed
Lecture notes will be released on the Drive (at least several days before lectures)

Course Overview
What is a database?
A large collection of data organized especially for rapid search and retrieval (as by a computer)
What is a database system?
(more formally, a database management system, i.e., DBMS)
A management system that helps us retrieve information from databases

Database and DBMS
Database
Database
Management
System
User
What is the average annual income of a Kazakhstan tax payer?
KZT *****

Tables, Relations, Relational Model
Database
Database
Management
System
User
Income_Table

Tables, Relations, Relational Model
Database
Database
Management
System
User
Income_Table
What is the average annual income of a Kazakhstan tax payer?
???

Structured Query Language (SQL)
Database
Database
Management
System
User
Income_Table
SELECT avg(Annual_Income)
FROM Income_Table

Structured Query Language (SQL)
Database
Database
Management
System
User
Income_Table
SELECT avg(Annual_Income)
FROM Income_Table
More details about SQL will be
covered later today

Database Schema Design
Database
Database
Management
System
User
You should store XXX and YYY in two different tables, and blah blah blah..
Yes sir!

Database Schema Design
Assume that we want to capture parent-child relationships

Database Schema Design
Is one column enough?

Database Schema Design
Are two columns enough?
Assume that two columns are enough
Does everyone have two children?
Schema designs based on the
Entity-Relationship model

Course Content
SQL
Constraints and Triggers
Conceptual Design
Indices
Relation Algebra
Query Processing/Optimization
Concurrency Control
Recovery
Current trend (e.g., NOSQL)
Database Design
Database Implementation

What do you want from a DBMS?
Why do we need it?
Keep data around (persistent)
Answer queries (questions) about data
Update data
Requirements from high-end applications
Massive amounts of data (terabytes ~ petabytes)
High throughput (thousands ~ millions transactions/min)

The Relational Revolution
The Relational Revolution (1970’s)
IBM and Univ of Berkeley
A simple data model: Data is stored in relations (tables)
A declarative query language: SQL
Programmer specifies what answers a query should return, but not how the query is executed
DBMS picks the best execution strategy
Hide the physical organization of the database from applications
Provided only logical view of the data
Relational model is the dominating technology today
Graphs/Streams/Arrays are hot wanna-be!

“Relational databases are the foundation of western civilization.”
Bruce Lindsay
IBM Fellow
IBM Almaden Research Center

Structured Query Language (SQL)

Structured Query Language (SQL)
A declarative (computer) language for managing data in a relational database management system
Two parts
Data Definition Language (DDL)
Create/Alter/Delete tables
Will be discussed in the next week
Data Manipulation Language (DML)
Query one or more tables
Insert/Delete/Modify tuples in tables
Will be discussed in the following

Tables in SQL
Product
Table name
Attribute name
Tuple (i.e. row, record)
Key

Data Types in SQL
Character strings
CHAR(20)
VARCHAR(50)
…
Numbers
INT
FLOAT
…
Others
BOOLEAN
DATETIME
…
Product

Simple SQL Query
Product
SELECT *
FROM Product
WHERE Category = ‘Phone’
“selection”

Simple SQL Query
Product
SELECT *
FROM Product
WHERE Category <> ‘Phone’

Simple SQL Query
Product
SELECT *
FROM Product
WHERE Category = ‘Phone’ AND Price > 800

Simple SQL Query
Product
SELECT *
FROM Product
WHERE Category = ‘Tablet’ OR Price > 1000

Simple SQL Query (cont.)
Product
SELECT PName, Price, Manufacturer
FROM Product
WHERE Price > 800
“selection
and projection”

Details
SQL is NOT case sensitive (when it comes to keywords and names)
SELECT = Select = select
Product = product
Constants must use single quotes
‘abc’ – OK
“abc” – NOT OK

Patterns for Strings
Product
SELECT *
FROM Product
WHERE PName LIKE ‘iPh%’
% stands for “any string”

Patterns for Strings
Product
SELECT *
FROM Product
WHERE PName LIKE ‘%Ph%’
% stands for “any string”

Patterns for Strings
Product
SELECT *
FROM Product
WHERE PName LIKE ‘%P%e%’
% stands for “any string”

Patterns for Strings
Product
SELECT *
FROM Product
WHERE PName LIKE ‘_Phone 4’
_ stands for “any character”

Patterns for Strings
Product
SELECT *
FROM Product
WHERE PName LIKE ‘_Phone__’
_ stands for “any character”

Patterns for Strings
Product
SELECT *
FROM Product
WHERE PName NOT LIKE ‘_Phone__’

Eliminating Duplicates
Product
SELECT Category
FROM Product

Eliminating Duplicates (cont.)
Product
SELECT DISTINCT Category
FROM Product

Ordering the Results
SELECT PName, Price
FROM Product
WHERE Price < 800
ORDER BY PName
Product

Ordering the Results (cont.)
SELECT PName, Price
FROM Product
WHERE Price < 800
ORDER BY PName DESC
Product

Ordering the Results (cont.)
SELECT PName, Category
FROM Product
WHERE Price < 1000
ORDER BY Category, PName
Product

Ordering the Results (cont.)
SELECT PName, Category
FROM Product
WHERE Price < 1000
ORDER BY Category DESC,
PName
Product

Ordering the Results (cont.)
SELECT PName, Category
FROM Product
WHERE Price < 1000
ORDER BY Category DESC,
PName DESC
Product

Exercise
SELECT DISTINCT Category
FROM Product
ORDER BY Category
Product
?

Exercise
Product
SELECT DISTINCT Category
FROM Product
ORDER BY Category

Exercise
SELECT DISTINCT Category
FROM Product
ORDER BY Category
WHERE Price < 1000
Product
?

Exercise
“WHERE” should always proceed “ORDER BY”
SELECT DISTINCT Category
FROM Product
ORDER BY Category
WHERE Price < 1000
Product
Error!

Exercise
SELECT DISTINCT Category
FROM Product
ORDER BY PName
Product
?

Exercise
“ORDER BY” items must appear in the select list if “SELECT DISTINCT” is specified
SELECT DISTINCT Category
FROM Product
ORDER BY PName
Product
Error!

Joins
Company
Product
A user wants to know the names and prices of all products by Japan companies. How?

Joins
Company
Product
SELECT PName, Price
FROM Product, Company
WHERE Country = ‘Japan’
AND Manufacturer = CName

Joins
Find the names of the persons who work for companies in USA
SELECT PName
FROM Person, Company
WHERE Country = ‘USA’
AND WorksFor = CName
Company
Person

Joins
Find the names the persons who work for companies in USA, as well as their company addresses
SELECT PName, Address
FROM Person, Company
WHERE Country = ‘USA’
AND WorksFor = CName
Error!
Company
Person

Joins
Company
Person
Find the names the persons who work for companies in USA, as well as their company addresses
SELECT PName, Company.Address
FROM Person, Company
WHERE Country = ‘USA’
AND WorksFor = CName

Joins
Find the names the persons who work for companies in USA, as well as their company addresses
SELECT PName, Company.Address
FROM Person, Company
WHERE Country = ‘USA’
AND CName = CName
Error!
Company
Person

Joins
Find the names the persons who work for companies in USA, as well as their company addresses
SELECT PName, Company.Address
FROM Person, Company
WHERE Country = ‘USA’
AND Person.CName = Company.CName
Company
Person

Joins
Find the names the persons who work for companies in USA, as well as their company addresses
SELECT X.PName, Y.Address
FROM Person AS X, Company AS Y
WHERE Y.Country = ‘USA’
AND X.CName = Y.CName
Company
Person

Joins
Find the names the persons who work for companies in USA, as well as their company addresses
SELECT X.PName, Y.Address
FROM Person X, Company Y
WHERE Y.Country = ‘USA’
AND X.CName = Y.CName
Company
Person

Exercise
Company
Product
Exercise: Find the names of the companies in China that produce products in the ‘tablet’ category
SELECT DISTINCT CName
FROM Company, Product
WHERE Manufacturer = CName AND Country = ‘China’
AND Category = ‘Tablet’

Exercise
Company
Product
Exercise: Find the names of the companies in China that produce products in the ‘tablet’ or ‘phone’ category
SELECT DISTINCT CName
FROM Company, Product
WHERE Manufacturer = CName AND Country = ‘China’
AND (Category = ‘Tablet’ OR Category = ‘Phone’)

Exercise
Exercise: Find the manufacturers that produce products in both the ‘tablet’ and ‘phone’ categories
SELECT DISTINCT Manufacturer
FROM Product
WHERE Category = ‘Tablet’ AND Category = ‘Phone’
Error!
Product

Exercise
Product
Exercise: Find the manufacturers that produce products in both the ‘tablet’ and ‘phone’ categories
SELECT DISTINCT X.Manufacturer
FROM Product AS X, Product AS Y
WHERE X.Manufacturer = Y.Manufacturer AND X.Category = ‘Tablet’ AND Y.Category = ‘Phone’

Subqueries
A subquery is a SQL query nested inside a larger query
Queries with subqueries are referred to as nested queries
A subquery may occur in
SELECT
FROM
WHERE
SQL subquery
SQL subquery

A special subquery: Scalar Subquery
Example Query
From Sells(bar, beer, price), find the bars that serve Heineken for the same price Ku De Ta charges for Bud.
Scalar Subquery
return a single value which is then used in a comparison.
If query is written so that it expects a subquery to return a single value, and it returns multiple values or no values, a run-time error occurs.

Example Scalar Subquery
SELECT price
FROM Sells
WHERE bar = `Ku De Ta’
AND beer = `Bud’;
SELECT bar
FROM Sells
WHERE beer = `Heineken’
AND price = 7.90;
Sells
Find the price Ku De Ta charges for Bud.
Find the bars that serve Heineken at that price.

Example Scalar Subquery
SELECT bar
FROM Sells
WHERE beer = ‘Heineken’ AND
price = (SELECT price FROM Sells
WHERE bar = ‘Ku De Ta’
AND beer = ‘Bud’);

Subqueries in FROM
Find all products in the ‘phone’ category with prices under 1000
SELECT X.PName
FROM (SELECT * FROM Product
WHERE category = ‘Phone’) AS X
WHERE X.Price < 1000
Company
Product

Subqueries in FROM (cont.)
Find all products in the ‘phone’ category with prices under 1000
SELECT PName
FROM Product
WHERE Category = ‘Phone’ AND Price < 1000
This is a much more efficient solution
Company
Product

Subqueries in WHERE (cont.)
Find all companies that make some products with price < 100
SELECT DISTINCT CName
FROM Company AS X
WHERE X.CName IN
(SELECT Y.CName FROM Product AS Y
WHERE Y.Price < 100)
Company
Product

Subqueries in WHERE (cont.)
Find all companies that make some products with price < 100
SELECT DISTINCT CName
FROM Company AS X
WHERE EXISTS
(SELECT * FROM Product AS Y
WHERE X.CName = Y.Cname
AND Y.Price < 100)
Company
Product
A nested query is correlated with the outer query if it contains a reference to an attribute in the outer query.
A nested query is correlated with the outside query if it must be re-computed for every tuple produced by the outside query.

Subqueries in WHERE (cont.)
Find all companies that make some products with price < 100
SELECT DISTINCT CName
FROM Company AS X
WHERE X.CName IN
(SELECT * FROM Product AS Y
WHERE Y.Price < 100)
Company
Product
Error!
The number of attributes in the SELECT clause in the subquery must match the number of attributes compared to with the comparison operator.

Subqueries in WHERE (cont.)
Find all companies that make some products with price < 100
SELECT DISTINCT CName
FROM Company AS X
WHERE 100 > ANY
(SELECT Price FROM Product AS Y
WHERE X.CName = Y.Cname)
Company
Product

Subqueries in WHERE (cont.)
Find all companies that make some products with price < 100
SELECT DISTINCT CName
FROM Product
WHERE Price < 100
This is more efficient than the previous solutions
Company
Product

Operators in Subqueries
IN
IN
is
true if and only if the
tuple is a member of the
relation.
EXISTS
EXISTS(
) is true if and only if the
is not empty.
Returns true if the nested query has 1 or more tuples.
ANY
x = ANY(
) is a
boolean cond. meaning
that x equals at least one
tuple in the relation.
ALL
x <> ALL(
) is true if and only if for every tuple t in the relation, x is not equal to t.

Avoiding Nested Queries
In general, nested queries tend to be more inefficient than un-nested queries
query optimizers of DBMS do not generally do a good job at optimizing queries containing subqueries
Therefore, they should be avoided whenever possible
But there are cases where avoiding nested queries is hard…

Subqueries in WHERE (cont.)
Find all companies that do not make any product with price < 100
SELECT DISTINCT CName
FROM Company AS X
WHERE NOT EXISTS
(SELECT * FROM Product AS Y
WHERE X.CName = Y.Cname
AND Y.Price < 100)
Company
Product

Subqueries in WHERE (cont.)
Find all companies that do not make any product with price < 100
SELECT DISTINCT CName
FROM Company AS X
WHERE 100 <= ALL
(SELECT Price FROM Product AS Y
WHERE X.CName = Y.Cname)
Company
Product

Subqueries in WHERE (cont.)
Find all companies that does not make any products with price < 100
SELECT DISTINCT CName
FROM Company AS X
WHERE 100 <= ALL
(SELECT Price FROM Product AS Y
WHERE X.CName = Y.Cname)
Company
Product

Exercise
Find all drinkers that frequent some bar that serves some beer they like
SELECT DINSTINT F.Drinker
FROM Likes AS L, Frequent AS F, Serve AS S
WHERE L.Drinker = F.Drinker AND F.Bar = S.Bar AND L.Beer = S.Beer
Likes
Frequent
Serves

Exercise
Find all drinkers that frequent some bar that does not serve any beer they like
SELECT DISTINCT F.Drinker
FROM Frequent AS F, Serves AS S
WHERE F.Bar = S.Bar AND NOT EXIST
(SELECT *
FROM Likes as L
WHERE L.Beer = S.Beer AND L.Drinker = F.Drinker)
Likes
Frequent
Serves

Exercise
Find all drinkers that do not frequent any bar that serve some beer they like
SELECT DISTINCT F.Drinker
FROM Frequent AS F
WHERE NOT EXIST
(SELECT *
FROM Likes AS L, Serves AS S
WHERE L.Beer = S.Beer AND L.Drinker = F.Drinker
AND S.Bar = F.Bar)
Likes
Frequent
Serves

Roadmap --SQL
Table
SELECT
FROM
WHERE
ORDER BY
Joins
Subqueries
Aggregations
UNION, INTERSECT, EXCEPT
NULL
Outerjoin
Insert/Delete tuples
Create/Alter/Delete tables
View

Exercise
Find all drinkers that frequent some bar that does not serve any beer they like
SELECT DISTINCT F.Drinker
FROM Frequent AS F
WHERE NOT EXIST
(SELECT *
FROM Serves as S, Likes as L
WHERE L.Beer = S.Beer AND L.Drinker = F.Drinker
AND F.Bar = S.Bar)
Likes
Frequent
Serves