Introductory SQL Example (MC39, Spring 1999)

create table movie_info (title varchar(40), director varchar(40),
                         year_made int, id int primary key);

create table movie_actors (id int, actor varchar(40));

insert into movie_info values('Amacord', 'Federico Fellini', 1974, 1);
insert into movie_actors values(1, 'Magali Noel');
insert into movie_actors values(1, 'Bruno Zanin');
insert into movie_actors values(1, 'Pupella Maggio');

-- and then similarly for three other movies and their actors

select * from movie_info, movie_actors where movie_info.id = movie_actors.id;

title          |director            |year_made|id|id|actor           
---------------+--------------------+---------+--+--+----------------
Amacord        |Federico Fellini    |     1974| 1| 1|Magali Noel     
Amacord        |Federico Fellini    |     1974| 1| 1|Bruno Zanin     
Amacord        |Federico Fellini    |     1974| 1| 1|Pupella Maggio  
Amacord        |Federico Fellini    |     1974| 1| 1|Armando Drancia 
The Big Easy   |Jim McBride         |     1987| 2| 2|Dennis Quaid    
The Big Easy   |Jim McBride         |     1987| 2| 2|Ellen Barkin    
The Big Easy   |Jim McBride         |     1987| 2| 2|Ned Beatty      
The Big Easy   |Jim McBride         |     1987| 2| 2|Lisa Jane Persky
The Big Easy   |Jim McBride         |     1987| 2| 2|John Goodman    
The Big Easy   |Jim McBride         |     1987| 2| 2|Charles Ludlam  
The Godfather  |Francis Ford Coppola|     1972| 3| 3|Marlon Brando   
The Godfather  |Francis Ford Coppola|     1972| 3| 3|Al Pacino       
The Godfather  |Francis Ford Coppola|     1972| 3| 3|James Caan      
The Godfather  |Francis Ford Coppola|     1972| 3| 3|Robert Duvall   
The Godfather  |Francis Ford Coppola|     1972| 3| 3|Diane Keaton    
Boyz N the Hood|John Singleton      |     1991| 4| 4|Cuba Gooding Jr.
Boyz N the Hood|John Singleton      |     1991| 4| 4|Ice Cube        
Boyz N the Hood|John Singleton      |     1991| 4| 4|Larry Fishbrune 
Boyz N the Hood|John Singleton      |     1991| 4| 4|Tyra Ferrell    
Boyz N the Hood|John Singleton      |     1991| 4| 4|Morris Chesnut  
(20 rows)

select actor from movie_info, movie_actors 
       where movie_info.id = movie_actors.id and title = 'The Big Easy';

actor           
----------------
Dennis Quaid    
Ellen Barkin    
Ned Beatty      
Lisa Jane Persky
John Goodman    
Charles Ludlam  
(6 rows)

select title from movie_info where year_made = 1991;

title          
---------------
Boyz N the Hood
(1 row)

select title from movie_info where year_made >= 1985 and year_made <= 1990;

title       
------------
The Big Easy
(1 row)

select director from movie_info where title='Amarcord';

director
--------
(0 rows)

update movie_info set title='Amarcord' where title='Amacord';

select director from movie_info where title='Amarcord';

director        
----------------
Federico Fellini
(1 row)

select year_made from movie_info where title='The Godfather';

year_made
---------
     1972
(1 row)

select title from movie_info, movie_actors 
       where movie_info.id = movie_actors.id
         and movie_actors.actor = 'Robert Duvall';

title        
-------------
The Godfather
(1 row)

delete from movie_info;
delete from movie_actors;

\copy movie_info from MC39/movie_info
\copy movie_actors from MC39/movie_actors

select title, year_made from movie_info
       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 a1.actor from movie_actors as a1, movie_actors as a2
       where a1.actor = a2.actor and a1.id <> a2.id;

actor         
--------------
Claude Rains  
Edward Fox    
Lindsay Crouse
Orson Welles  
(4 rows)

select title from movie_info, movie_actors
       where movie_info.id = movie_actors.id
       and movie_actors.actor = 'Claude Rains';

title             
------------------
Casablanca        
Lawrence of Arabia
(2 rows)