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 m ov ie
Fie ld N a m e Typ e N ot e s
id INTEGER An a r b it r a r y u n iq u e id e n t ifie r .
t it le CHAR(70) Th e n a m e o f t h e film .
yr DECIMAL(4) Ye a r o f fir s t r e le a s e .
scor e FLOAT 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 .
vot es INTEGER Th e n u m b e r o f vo t e s c a s t fo r t h is film .
a ct or a ct or
Fie ld N a m e Typ e N ot e s
id INTEGER An a r b it r a r y u n iq u e id e n t ifie r .
nam e CHAR(30) Th e n a m e o f t h e a c t o r .
ca st in g ca st in g
Fie ld N a m e Typ e N ot e s
m ovieid INTEGER A r e fe r e n c e t o t h e m o vie t a b le .
act or id INTEGER 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
Problem: Select the year that Athens 2000
2004
sydney
At hens
hosted the Olympic games. 2008
2012
Biejing
London
Saad Bashir Alvi 12
Selecting records
Ga m e s
yr cit y
Problem: Select the year that Athens 2000
2004
sydney
At hens
hosted the Olympic games. 2008
2012
Biejing
London
Solution:
select yr, city from Games where city
= 'Athens';
yr cit y
2004 At hens
Saad Bashir Alvi 13
Select with GROUP BY
Ga m e s
Problem: Select the continents yr
2000
cit y
sydney
cont ine nt
Aust ralia
hosting the Olympics with the count 2004
2008
At hens
Biejing
Europe
Aisa
of the number of games held. 2012 London Europe
Saad Bashir Alvi 14
Select with GROUP BY
Ga m e s
Problem: Select the continents yr
2000
cit y
sydney
cont ine nt
Aust ralia
hosting the Olympics with the count 2004
2008
At hens
Biejing
Europe
Aisa
of the number of games held. 2012 London Europe
Solution:
select continent, count(yr) from Games group by
continent;
cont ine nt count ( yr )
Aust ralia 1
Asia 1
Europe 2
Saad Bashir Alvi 15
Select with aggregate functions
nam e r e gion ar e a populat ion gdp
Afghanist an Sout h Aisa 652225 2600000
Albania Europe 28728 320000 665600000
................
Database
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 ar e a populat ion gdp
Afghanist an Sout h Aisa 652225 2600000
Albania Europe 28728 320000 665600000
................
Database 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 count (name )
Africa 21
Am ericas 3
Asia 20
Australia 1
Europe 16
Middle East 10
North Am erica 3
South Am erica 6
Saad Bashir Alvi 23
Select with join
Ga m e s Cit y
yr cit y na m e count ry
Problem: 1896
1948
Athens
London
sydney
At hens
Aust ralia
Greece
We want to find the year and 2004 Athens
Biejing
London
China
UK
country where the games took 2008
2012
Biejing
London
place.
Saad Bashir Alvi 24
Select with join
Ga m e s Cit y
yr cit y na m e count ry
Problem: 1896
1948
Athens
London
sydney
At hens
Aust ralia
Greece
We want to find the year and 2004 Athens
Biejing
London
China
UK
country where the games took 2008
2012
Biejing
London
place.
Solution:
SELECT games.yr, city.country yr count ry
FROM games JOIN city ON 1896 Greece
(games.city = city.name) 1948
2004
UK
Greece
2008 China
2012 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)
t it le ar t ist
WHERE song = 'Alison' The Very Best Of Elvis Costello
Elvis Costello
And The Attraction
Saad Bashir Alvi 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 COUNT( * )
"Music from the Motion Picture ""Purple Rain""" 9
(What 's The St ory) Morning Glory? 12
..Baby One More Tim e [ ENHANCED CD] 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""" 1
(What 's The St ory) Morning Glory? 4
..Baby One More Tim e [ ENHANCED CD] 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
having count(*) > 2 song COUNT( * )
Angel 3
Best is yet t o com e 3
Changes 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
ON (album.asin=track.album) group by title having * )
t it le pr ice COUNT(
price/count(*) < .5 Angel 11.98 25
Best is yet t o com e 14.99 50
Changes 22.98 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 Elvis Pre s le y
The Man Who Shot Libert y Valance ohn Wayne
J
Mot hra 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 count ( * ) 43
1997 2
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
Saad Bashir Alvi Mot hra 8 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