Introductory SQL Example (MCS-270, Spring 2001)
create table movies (title varchar(40) not null,
director int not null references persons,
year_made int not null,
id int primary key);
create table persons (lname varchar(40) not null,
fnames varchar(40),
id int primary key);
create table acts_in (actor int not null references persons,
movie int not null references movies);
insert into movies values('Amacord', 1, 1974, 1);
insert into persons values('Fellini', 'Federico', 1);
insert into persons values('Noel', 'Magali', 2);
insert into persons values('Zanin', 'Bruno', 3);
insert into persons values('Maggio', 'Pupella', 4);
insert into acts_in values(2, 1);
insert into acts_in values(3, 1);
insert into acts_in values(4, 1);
-- and similarly for three other movies and associated persons
select title from movies where year_made = 1991;
title
---------------
Boyz N the Hood
(1 row)
select title from movies where year_made >= 1985 and year_made <= 1995;
title
---------------
The Big Easy
Boyz N the Hood
(2 rows)
select title, year_made from movies
where year_made >= 1985 and year_made <= 1995;
title |year_made
-------------------+---------
The Big Easy | 1987
Boyz N the Hood | 1991
(2 rows)
select year_made from movies where title = 'The Godfather';
year_made
---------
1972
(1 row)
select title, fnames, lname, year_made
from movies, persons
where movies.director = persons.id;
title |fnames |lname |year_made
---------------+------------+---------+---------
Amacord |Federico |Fellini | 1974
The Big Easy |Jim |McBride | 1987
The Godfather |Francis Ford|Coppola | 1972
Boyz N the Hood|John |Singleton| 1972
(4 rows)
select fnames, lname from movies, persons
where movies.director = persons.id
and title = 'The Godfather';
fnames |lname
------------+-------
Francis Ford|Coppola
(1 row)
select fnames, lname from movies, persons
where movies.director = persons.id
and title = 'Amarcord';
fnames|lname
------+-----
(0 rows)
update movies set title = 'Amarcord' where title = 'Amacord';
select fnames, lname from movies, persons
where movies.director = persons.id
and title = 'Amarcord';
fnames |lname
--------+-------
Federico|Fellini
(1 row)
select fnames, lname
from movies, persons, acts_in
where movies.id = acts_in.movie
and persons.id = acts_in.actor
and title = 'The Big Easy';
fnames |lname
---------+-------
Dennis |Quaid
Ellen |Barkin
Ned |Beatty
Lisa Jane|Persky
John |Goodman
Charles |Ludlam
(6 rows)
delete from movies;
delete from persons;
delete from acts_in;
\copy movies from /Net/solen/home/m/a/max/MC39/movies
\copy persons from /Net/solen/home/m/a/max/MC39/persons
\copy acts_in from /Net/solen/home/m/a/max/MC39/acts_in
select title, year_made from movies
where year_made >= 1985 and year_made <= 1990;
title |year_made
-------------------+---------
The Big Easy | 1987
Blood Simple | 1985
A Fish Called Wanda| 1988
House of Games | 1987
(4 rows)
select distinct fnames, lname
from persons, acts_in as a1, acts_in as a2
where a1.actor = a2.actor
and a1.movie <> a2.movie
and a1.actor = persons.id;
fnames |lname
-------+------
Claude |Rains
Edward |Fox
Lindsay|Crouse
Orson |Welles
(4 rows)
select title from movies, persons, acts_in
where movies.id = movie
and persons.id = actor
and fnames = 'Claude' and lname = 'Rains';
title
------------------
Casablanca
Lawrence of Arabia
(2 rows)