�ݺ�ߣ

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
movie movie
Field Name Type Notes
id INTEGER An arbitrary unique identifier.
title CHAR(70) The name of the film.
yr DECIMAL(4) Year of first release.
score FLOAT Average of all the votes cast for the film.
votes INTEGER The number of votes cast for this film.

actor actor
id INTEGER An arbitrary unique identifier.
name CHAR(30) The name of the actor.

casting casting
movieid INTEGER A reference to the movie table.
actorid INTEGER A reference to the actor table.

Saad Bashir Alvi 4



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



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



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



CREATE

INSERT

UPDATE

SELECT

ALTER

DROP

Saad Bashir Alvi 11

Selecting records
Games
yr city


Problem: Select the year that Athens 2000
2004
sydney
Athens

hosted the Olympic games. 2008
2012
Biejing
London

Saad Bashir Alvi 12

Selecting records
Games
yr city


Problem: Select the year that Athens 2000
2004
sydney
Athens

hosted the Olympic games. 2008
2012
Biejing
London


Solution:
select yr, city from Games where city = 'Athens';

yr city
2004 Athens

Saad Bashir Alvi 13

Select with GROUP BY
Games

Problem: Select the continents
yr city continent

2000 sydney Australia

hosting the Olympics with the count 2004
2008
Athens
Biejing
Europe
Aisa

of the number of games held. 2012 London Europe

Saad Bashir Alvi 14

Select with GROUP BY
Games

Problem: Select the continents
yr city continent

2000 sydney Australia

hosting the Olympics with the count 2004
2008
Athens
Biejing
Europe
Aisa

of the number of games held. 2012 London Europe


Solution:
select continent, count(yr) from Games group by
continent;

continent count(yr)
Australia 1
Asia 1

Europe 2

Saad Bashir Alvi 15

Select with aggregate functions
name region area population gdp
Afghanistan South 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

name region area population gdp
Afghanistan South 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


Database

Problem: How many countries have an area of
at least 1000000

Saad Bashir Alvi 18


Database

Problem: How many countries have an area of
at least 1000000

Solution:
select count(name) from bbc where area >=
1000000

count(name)
29

Saad Bashir Alvi 19


Database

Problem: What is the total population of
('France','Germany','Spain')

Saad Bashir Alvi 20


Database

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(population)
187300000

Saad Bashir Alvi 21


Database

Problem: For each region show the region and
number of countries with
populations of at least 10 million.

Saad Bashir Alvi 22


Database

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 region
Africa
count(name)
21
Americas 3
Asia 20
Australia 1
Europe 16
Middle East 10
North America 3
South America 6

Saad Bashir Alvi 23

Select with join
Games City
yr city name country

Problem: 1896 Athens sydney Australia

Athens Greece
1948 London

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
Games City
yr city name country

Problem: 1896 Athens sydney Australia

Athens Greece
1948 London

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 country

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

Problem: Find the title and artist who recorded
the song 'Alison'

Solution:
SELECT title, artist

FROM album JOIN track

ON (album.asin=track.album) title artist

WHERE song = 'Alison' The Very Best Of Elvis Costello
Elvis Costello
And The Attraction

Saad Bashir Alvi 27

Select with join

Database

Problem: Show the song for each track on the
album 'Blur'

Saad Bashir Alvi 28

Select with join

Database

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
Country sad ballad man

Saad Bashir Alvi ..................... 29

Select with join

Database

Problem: For each album show the title and the
total number of track.

Saad Bashir Alvi 30

Select with join

Database

total number of track.

Solution:
SELECT title, COUNT(*) FROM album JOIN track
ON (asin=album) GROUP BY title
title COUNT(*)
"Music from the Motion Picture ""Purple Rain""" 9
(What's The Story) Morning Glory? 12

..Baby One More Time [ENHANCED CD] 11
.....................

Saad Bashir Alvi 31

Select with join

Database

total number of tracks containing the word 'Heart'.

Saad Bashir Alvi 32

Select with join

Database

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 title COUNT(*)
"Music from the Motion Picture ""Purple Rain""" 1

(What's The Story) Morning Glory? 4

..Baby One More Time [ENHANCED CD] 2
.....................

Saad Bashir Alvi 33

Select with join

Database

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

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 to come 3
Changes 3
.....................
Saad Bashir Alvi 35

Select with join

Database

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

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 COUNT(*)
title price

having price/count(*) < .5 Angel 11.98 25

Best is yet to come 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
actor(id, name)

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') title
What Lies Beneath

Random Hearts

Air Force One
Saad Bashir Alvi ..................... 39

Select with join

Database
actor(id, name)

Problem: List the films together with the leading star
for all 1962 films

Saad Bashir Alvi 40

Select with join

Database
actor(id, name)

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 name
title
Kid Galahad Elvis Presley

The Man Who Shot Liberty Valance John Wayne

Mothra Frankie Sakai
Saad Bashir Alvi ..................... 41

Select with join

Database
actor(id, name)

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
actor(id, name)

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
actor(id, name)

Problem: List the 1978 films by order of cast list
size.

Saad Bashir Alvi 44

Select with join

Database
actor(id, name)

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 count(*)
title order by count(actor.id) desc
Kid Galahad 16

The Man Who Shot Liberty Valance 13

Saad Bashir Alvi Mothra 8
45
.....................



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



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

�ݺ�ߣ

Sql presentation

More Related Content

Sql presentation