Transcript www.is.inf.uni

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
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
vot es
INTEGER
a ct or
N ot e s
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 .
a ct or
Fie ld N a m e
Typ e
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
N ot e s
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

Problem: Select the year that Athens
hosted the Olympic games.
Saad Bashir Alvi
yr
cit y
2000
sydney
2004
At hens
2008
Biejing
2012
London
12
Selecting records
Ga m e s


Problem: Select the year that Athens
hosted the Olympic games.
Solution:
select yr, city from Games where
= 'Athens';
Saad Bashir Alvi
yr
cit y
2000
sydney
2004
At hens
2008
Biejing
2012
London
city
yr
cit y
2004
At hens
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.
Saad Bashir Alvi
yr
cit y
cont ine nt
2000
sydney
Aust ralia
2004
At hens
Europe
2008
Biejing
Aisa
2012
London
Europe
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
cit y
cont ine nt
2000
sydney
Aust ralia
2004
At hens
Europe
2008
Biejing
Aisa
2012
London
Europe
Solution:
select continent, count(yr) from Games group by
continent;
cont ine nt count ( yr )
Saad Bashir Alvi
Aust ralia
1
Asia
1
Europe
2
15
Select with aggregate functions

Database
nam e
r e gion
ar e a
populat ion
gdp
Afghanist an
Albania
................
Sout h Aisa
Europe
652225
28728
2600000
320000
665600000
bbc(name, region, area, population, gdp)

Problem: Give the total GDP of 'Africa'
Saad Bashir Alvi
16
Select with aggregate functions

Database
nam e
r e gion
ar e a
populat ion
gdp
Afghanist an
Albania
................
Sout h Aisa
Europe
652225
28728
2600000
320000
665600000
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
Saad Bashir Alvi
re gion
count (name )
Africa
21
Am ericas
Asia
Australia
Europe
Middle East
North Am erica
South Am erica
3
20
1
16
10
3
6
23
Select with join
Ga m e s

Problem:
We want to find the year and
country where the games took
place.
Saad Bashir Alvi
Cit y
yr
cit y
na m e
count ry
1896
Athens
sydney
Aust ralia
1948
London
At hens
Greece
2004
Athens
Biejing
China
London
UK
2008
Biejing
2012
London
24
Select with join
Ga m e s

Problem:
We want to find the year and
country where the games took
place.

Cit y
yr
cit y
na m e
count ry
1896
Athens
sydney
Aust ralia
1948
London
At hens
Greece
2004
Athens
Biejing
China
London
UK
2008
Biejing
2012
London
Solution:
SELECT games.yr, city.country
FROM games JOIN city ON
(games.city = city.name)
Saad Bashir Alvi
yr
count ry
1896
Greece
1948
UK
2004
Greece
2008
China
2012
UK
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
The Very Best Of Elvis Costello
And The Attraction
Elvis Costello
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
song
COUNT( * )
having count(*) > 2
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
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
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.actorid
t it le
nam e
Kid Galahad
Elvis Pre s le y
The Man Who Shot Libert y Valance
John Wayne
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 ( * )
1997
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
Saad Bashir Alvi Mot hra
.....................
13
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