SQL Tutorial
Saad Bashir Alvi
1
Topics to be covered
CREATE
INSERT UPDATE SELECT ALTER
DROP
Saad Bashir Alvi
2
First Example
Movie Database
movies actors
casting
Saad Bashir Alvi
3
Tables of Movie Database
m ov ie
Fie ld N a m e
id t it le yr scor e vot es
m ov ie
Typ e
INTEGER CHAR(70) FLOAT INTEGER
N ot e s
An a r b it r a r y u n iq u e id e n t ifie r . Th e n a m e o f t h e film . Ave r a g e o f a ll t h e vo t e s c a s t fo r t h e film . Th e n u m b e r o f vo t e s c a s t fo r t h is film .
DECIMAL(4) Ye a r o f fir s t r e le a s e .
a ct or
Fie ld N a m e
id nam e
a ct or
Typ e
INTEGER CHAR(30)
N ot e s
An a r b it r a r y u n iq u e id e n t ifie r . Th e n a m e o f t h e a c t o r .
ca st in g
Fie ld N a m e
m ovieid act or id
ca st in g
Typ e
INTEGER INTEGER
N ot e s
A r e fe r e n c e t o t h e m o vie t a b le . A r e fe r e n c e t o t h e a c t o r t a b le .
Saad Bashir Alvi
4
Topics to be covered
CREATE
INSERT UPDATE SELECT ALTER
DROP
Saad Bashir Alvi
5
Creating Database
create table movie (id int NOT NULL primary key, title varchar(70), yr decimal(4), score float, votes integer); create table actor(id int NOT NULL primary key, name varchar(30)); create table casting(movieid int, actorid int, ord integer, primary key (movieid, actorid));
Saad Bashir Alvi
6
Topics to be covered
CREATE
INSERT
UPDATE SELECT ALTER
DROP
Saad Bashir Alvi
7
Populating Database
insert into table movie(id, title, yr, score, votes) values (1, “Lione King”, 2001, 5, 20000); insert into actor(id, name) values (1, “Sambda”); insert into casting(movieid, actorid, ord) values (1, 1, 5);
Saad Bashir Alvi
8
Topics to be covered
CREATE
INSERT
UPDATE
SELECT ALTER
DROP
Saad Bashir Alvi
9
Updating Record
update table movie set title = “Lion King” where id = 1; update table actor set name = “simba” where id = 1; update table casting set ord = 1 where movieid = 1 and actorid = 1;
Saad Bashir Alvi
10
Topics to be covered
CREATE
INSERT UPDATE
SELECT
ALTER
DROP
Saad Bashir Alvi
11
Selecting records
Ga m e s
yr cit y sydney At hens Biejing London
Problem: Select the year that Athens hosted the Olympic games.
2000 2004 2008 2012
Saad Bashir Alvi
12
Selecting records
Ga m e s
yr cit y sydney At hens Biejing London
Problem: Select the year that Athens hosted the Olympic games. Solution: select yr, city from Games where = 'Athens';
yr
2004
2000 2004 2008 2012
city
cit y
At hens
Saad Bashir Alvi
13
Select with GROUP BY
Ga m e s
Problem: Select the continents
hosting the Olympics with the count of the number of games held.
yr 2000 2004 2008 2012
cit y sydney At hens Biejing London
cont ine nt Aust ralia Europe Aisa Europe
Saad Bashir Alvi
14
Select with GROUP BY
Ga m e s
Problem: Select the continents
hosting the Olympics with the count of the number of games held.
yr 2000 2004 2008 2012
cit y sydney At hens Biejing London
cont ine nt Aust ralia Europe Aisa Europe
Solution:
select continent, count(yr) from Games group by continent;
cont ine nt count ( yr )
Aust ralia Asia Europe 1 1 2
Saad Bashir Alvi
15
Select with aggregate functions
nam e r e gion
Sout h Aisa Europe
ar e a
652225 28728
populat ion
2600000 320000
gdp
665600000
Database
Afghanist an Albania ................
bbc(name, region, area, population, gdp)
Problem: Give the total GDP of 'Africa'
Saad Bashir Alvi
16
Select with aggregate functions
nam e r e gion
Sout h Aisa Europe
ar e a
652225 28728
populat ion
2600000 320000
gdp
665600000
Database
Afghanist an Albania ................
bbc(name,
region, area, population, gdp)
Problem: Give the total GDP of 'Africa' Solution: select sum(gdp) from bbc where region = 'Africa'
sum ( gdp)
410196200000
Saad Bashir Alvi
17
Select with aggregate functions
Database
bbc(name, region, area, population, gdp)
Problem: How many countries have an area of
at least 1000000
Saad Bashir Alvi
18
Select with aggregate functions
Database
bbc(name, region, area, population, gdp)
Problem: How many countries have an area of
at least 1000000
Solution:
select count(name) from bbc where area >= 1000000
count ( nam e )
29
Saad Bashir Alvi
19
Select with aggregate functions
Database
bbc(name, region, area, population, gdp)
Problem: What is the total population of
('France','Germany','Spain')
Saad Bashir Alvi
20
Select with aggregate functions
Database
bbc(name, region, area, population, gdp)
Problem: What is the total population of
('France','Germany','Spain')
Solution:
select sum(population) from bbc where name = 'France' or name = 'Germany' or name = 'Spain'
sum ( populat ion)
187300000
Saad Bashir Alvi
21
Select with aggregate functions
Database
bbc(name, region, area, population, gdp)
Problem: For each region show the region and
number of countries with populations of at least 10 million.
Saad Bashir Alvi
22
Select with aggregate functions
Database
bbc(name, region, area, population, gdp)
Problem: For each region show the region and
number of countries with populations of at least 10 million.
Solution:
select region, count(name) from bbc where population >= 10000000 group by region
re gion
Africa
Am ericas Asia Australia Europe Middle East North Am erica South Am erica
count (name )
21
3 20 1 16 10 3 6
Saad Bashir Alvi
23
Select with join
Ga m e s
yr cit y Athens London Athens Biejing London
Cit y
na m e sydney At hens Biejing London count ry Aust ralia Greece China UK
Problem:
We want to find the year and country where the games took place.
1896 1948 2004 2008 2012
Saad Bashir Alvi
24
Select with join
Ga m e s
yr cit y Athens London Athens Biejing London
Cit y
na m e sydney At hens Biejing London count ry Aust ralia Greece China UK
Problem:
We want to find the year and country where the games took place.
1896 1948 2004 2008 2012
Solution:
SELECT games.yr, city.country FROM games JOIN city ON (games.city = city.name)
yr 1896 1948 2004 2008 2012 count ry Greece UK Greece China UK
Saad Bashir Alvi
25
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: Find the title and artist who recorded
the song 'Alison'
Saad Bashir Alvi
26
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: Find the title and artist who recorded
the song 'Alison'
Solution:
SELECT title, artist
FROM album JOIN track ON (album.asin=track.album) WHERE song = 'Alison'
Saad Bashir Alvi
t it le
ar t ist
Elvis Costello
The Very Best Of Elvis Costello And The Attraction
27
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: Show the song for each track on the
album 'Blur'
Saad Bashir Alvi
28
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: Show the song for each track on the
album 'Blur'
Solution:
select song FROM album JOIN track ON (album.asin=track.album) where title = 'Blur'
song
Beetlebum
Song 2 Count ry sad ballad m an
Saad Bashir Alvi
.....................
29
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: For each album show the title and the
total number of track.
Saad Bashir Alvi
30
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: For each album show the title and the
total number of track.
Solution:
SELECT title, COUNT(*) FROM album JOIN track ON (asin=album) GROUP BY title
t it le
"Music from the Motion Picture ""Purple Rain""" (What 's The St ory) Morning Glory? ..Baby One More Tim e [ ENHANCED CD] .....................
COUNT( * )
9
12 11
Saad Bashir Alvi
31
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: For each album show the title and the
total number of tracks containing the word 'Heart'.
Saad Bashir Alvi
32
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: For each album show the title and the
total number of tracks containing the word 'Heart'.
Solution:
SELECT title, COUNT(*) FROM album JOIN track ON (asin=album) where song like "%Heart%" GROUP BY title t it le COUNT( * )
"Music from the Motion Picture ""Purple Rain""" (What 's The St ory) Morning Glory? ..Baby One More Tim e [ ENHANCED CD] ..................... 1 4 2
Saad Bashir Alvi
33
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: Find the songs that appear on more
than 2 albums. Include a count of the number of times each shows up.
Saad Bashir Alvi
34
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: Find the songs that appear on more
than 2 albums. Include a count of the number of times each shows up.
Solution:
select song, count(*) FROM album JOIN track ON (album.asin=track.album) group by song song COUNT( * ) having count(*) > 2
Angel Best is yet t o com e Changes 3 3 3
Saad Bashir Alvi
.....................
35
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: A "good value" album is one where the
price per track is less than 50 cents. Find the good value album - show the title, the price and the number of tracks.
Saad Bashir Alvi
36
Select with join
Database
album(asin, title, artist, price, release, label, rank) track(album, dsk, posn, song)
Problem: A "good value" album is one where the
price per track is less than 50 cents. Find the good value album - show the title, the price and the number of tracks.
Solution:
select title, price, count(*) FROM album JOIN track t it le pr ice COUNT( ON (album.asin=track.album) group by title having * ) Angel 11.98 25 price/count(*) < .5
Best is yet t o com e Changes ..................... 14.99 22.98 50 46
Saad Bashir Alvi
37
Select with join
Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the films in which 'Harrison Ford' has
appeared
Saad Bashir Alvi
38
Select with join
Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the films in which 'Harrison Ford' has
appeared
Solution:
select title from movie join casting on id = movieid where actorid = (select id from actor where name = 'Harrison Ford') t it le
What Lies Beneath Random Heart s Air Force One
Saad Bashir Alvi
.....................
39
Select with join
Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the films together with the leading star
for all 1962 films
Saad Bashir Alvi
40
Select with join
Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the films together with the leading star
for all 1962 films
Solution:
select title, name from movie, actor, casting where yr = '1962' and ord = 1 and movie.id = casting.movieid and actor.id = casting.actoridnam e t it le
Kid Galahad The Man Who Shot Libert y Valance Mot hra Elvis Pre s le y J ohn Wayne Frankie Sakai
Saad Bashir Alvi .....................
41
Select with join
Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: Which were the busiest years for 'John
Travolta'. Show the number of movies he made for each year.
Saad Bashir Alvi
42
Select with join
Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: Which were the busiest years for 'John
Travolta'. Show the number of movies he made for each year.
Solution:
select yr, count(*) from movie, casting, actor where actor.id = casting.actorid and movie.id = casting.movieid and actor.name = 'John Travolta' group by yr order by count(*) desc limit 1
Saad Bashir Alvi
yr
1997
count ( * )
2
43
Select with join
Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the 1978 films by order of cast list
size.
Saad Bashir Alvi
44
Select with join
Database movie(id, title, yr, score, votes, director) actor(id, name) casting(movieid, actorid, ord) Problem: List the 1978 films by order of cast list
size.
Solution:
select title, count(actor.id) from movie, actor, casting where actor.id = casting.actorid and movie.id = casting.movieid and yr = 1978 group by t it le count ( * ) title order by count(actor.id) desc Kid Galahad 16
The Man Who Shot Libert y Valance 13 8
Saad Bashir Alvi Mot hra
.....................
45
Topics to be covered
CREATE
INSERT UPDATE SELECT
ALTER
DROP
Saad Bashir Alvi
46
ALTER
ALTER TABLE actor add column age integer; ALTER TABLE actor change age newage integer; ALTER TABLE actor drop column age;
Saad Bashir Alvi
47
Topics to be covered
CREATE
INSERT UPDATE SELECT ALTER
DROP
Saad Bashir Alvi
48
DROP
drop table movie;
Saad Bashir Alvi
49
Thanks and Good luck for your exams
Saad Bashir Alvi
50