Introductory SQL Example (MCS-270, Spring 2000)
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 /Net/solen/home/m/a/max/MC39/movie_info
\copy movie_actors from /Net/solen/home/m/a/max/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)