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)