Introductory SQL Example (MCS-270, Spring 2002)

-- Altered from original file of Max Hailperin
--    by Karl Knight on 2/26/2002

-- This file describes a possible interaction in psql, setting up
-- a movie database which you are connecting to.  The file contains
-- queries which can be executed in a psql session (you can actually
-- cut-and-paste them in, should you wish), along with the output that
-- would be reported by psql.  I am assuming that you are starting
-- with an empty database (ie, no tables yet defined).


-- First, the table definitions.  Note that they must be defined in
-- this order, because of the references.

create table persons (lname varchar(40) not null,
                      fnames varchar(40),
                      id int primary key);

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 acts_in (actor int not null references persons,
                      movie int not null references movies);

-- The data for Amacord.  Note that we must insert into persons first,
-- then into movies, and finally acts_in, again because of references.

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 persons values('Drancia', 'Armando', 5);

insert into movies values('Amacord', 1, 1974, 1);

insert into acts_in values(2, 1);
insert into acts_in values(3, 1);
insert into acts_in values(4, 1);
insert into acts_in values(5, 1);

-- The other movies in the initial database:

-- The Big Easy

insert into persons values('Mcbride', 'Jim', 6);
insert into persons values('Quaid', 'Dennis', 7);
insert into persons values('Barkin', 'Ellen', 8);
insert into persons values('Beatty', 'Ned', 9);
insert into persons values('Persky', 'Lisa Jane', 10);
insert into persons values('Goodman', 'John', 11);
insert into persons values('Ludlam', 'Charles', 12);

insert into movies values('The Big Easy', 6, 1987, 2);

insert into acts_in values(7, 2);
insert into acts_in values(8, 2);
insert into acts_in values(9, 2);
insert into acts_in values(10, 2);
insert into acts_in values(11, 2);
insert into acts_in values(12, 2);

-- Boyz in the Hood

insert into persons values('Singleton', 'John', 13);
insert into persons values('Gooding', 'Cuba Jr.', 14);
insert into persons values('Cube', 'Ice', 15);
insert into persons values('Fishburne', 'Larry', 16);
insert into persons values('Ferrell', 'Tyra', 17);
insert into persons values('Chestnut', 'Morris', 18);

insert into movies values('Boyz N the Hood', 13, 1991, 3);

insert into acts_in values(14, 3);
insert into acts_in values(15, 3);
insert into acts_in values(16, 3);
insert into acts_in values(17, 3);
insert into acts_in values(18, 3);

-- Some example queries follow:

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 Big Easy';

 year_made 
-----------
      1987
(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
 Boyz N the Hood | John     | Singleton |      1991
(3 rows)

select fnames, lname from movies, persons 
       where movies.director = persons.id 
         and title = 'The Big Easy';

 fnames |  lname  
--------+---------
 Jim    | Mcbride

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 evreything from the three tables.  Notice again, we
-- need to do it in this order becauses of the references.

delete from acts_in;
delete from movies;
delete from persons;

-- Copy in from tab-separated data fields

\copy persons from /Net/solen/home1/k/a/karl/public/mcs270/movies/persons.data
\copy movies from /Net/solen/home1/k/a/karl/public/mcs270/movies/movies.data
\copy acts_in from /Net/solen/home1/k/a/karl/public/mcs270/movies/acts_in.data

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)