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
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
Problem: Select the year that Athens
hosted the Olympic games.
Saad Bashir Alvi 12
Selecting records
Problem: Select the year that Athens
hosted the Olympic games.
Solution:
select yr, city from Games where city
= 'Athens';
Saad Bashir Alvi 13
Select with GROUP BY
Problem: Select the continents
hosting the Olympics with the count
of the number of games held.
Saad Bashir Alvi 14
Select with GROUP BY
Problem: Select the continents
hosting the Olympics with the count
of the number of games held.
Solution:
select continent, count(yr) from Games group by
continent;
Saad Bashir Alvi 15
Select with aggregate functions
Database
bbc(name, region, area, population, gdp)
Problem: Give the total GDP of 'Africa'
Saad Bashir Alvi 16
Select with aggregate functions
Database bbc(name,
region, area, population, gdp)
Problem: Give the total GDP of 'Africa'
Solution:
select sum(gdp) from bbc where region =
'Africa'
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
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'
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 23
Select with join
Problem:
We want to find the year and
country where the games took
place.
Saad Bashir Alvi 24
Select with join
Problem:
We want to find the year and
country where the games took
place.
Solution:
SELECT games.yr, city.country
FROM games JOIN city ON
(games.city = city.name)
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 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'
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
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
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
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
price/count(*) < .5
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')
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
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 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
title order by count(actor.id) desc
Saad Bashir Alvi 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