Introductory SQL Example

-- Altered from original file of Max Hailperin
--    by Karl Knight on 2/26/2002 and 2/26/2003
--    by David Wolfe on 2/23/2004

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

-- You'll start by logging in to mysql using something like:
--    mysql -h mcs-jsp -u wolfe -p
-- where wolfe will be replaced by your user name.
-- You'll be prompted for a password.

-- Next, create a database with any name starting with your username
-- and an underscore:

create database wolfe_movies;
use wolfe_movies;

-- 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 * from movies;
      title      | director | year_made | id
-----------------+----------+-----------+----
 Amacord         |        1 |      1974 |  1
 The Big Easy    |        6 |      1987 |  2
 Boyz N the Hood |       13 |      1991 |  3
(3 rows)

select title from movies;
      title
-----------------
 Amacord
 The Big Easy
 Boyz N the Hood
(3 rows)

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 * from movies, persons;
      title      | director | year_made | id |   lname   |  fnames   | id
-----------------+----------+-----------+----+-----------+-----------+----
 Amacord         |        1 |      1974 |  1 | Fellini   | Federico  |  1
 Amacord         |        1 |      1974 |  1 | Noel      | Magali    |  2
 Amacord         |        1 |      1974 |  1 | Zanin     | Bruno     |  3
 Amacord         |        1 |      1974 |  1 | Maggio    | Pupella   |  4
 Amacord         |        1 |      1974 |  1 | Drancia   | Armando   |  5
 Amacord         |        1 |      1974 |  1 | Mcbride   | Jim       |  6
 Amacord         |        1 |      1974 |  1 | Quaid     | Dennis    |  7
 Amacord         |        1 |      1974 |  1 | Barkin    | Ellen     |  8
 Amacord         |        1 |      1974 |  1 | Beatty    | Ned       |  9
 Amacord         |        1 |      1974 |  1 | Persky    | Lisa Jane | 10
 Amacord         |        1 |      1974 |  1 | Goodman   | John      | 11
 Amacord         |        1 |      1974 |  1 | Ludlam    | Charles   | 12
 Amacord         |        1 |      1974 |  1 | Singleton | John      | 13
 Amacord         |        1 |      1974 |  1 | Gooding   | Cuba Jr.  | 14
 Amacord         |        1 |      1974 |  1 | Cube      | Ice       | 15
 Amacord         |        1 |      1974 |  1 | Fishburne | Larry     | 16
 Amacord         |        1 |      1974 |  1 | Ferrell   | Tyra      | 17
 Amacord         |        1 |      1974 |  1 | Chestnut  | Morris    | 18
 The Big Easy    |        6 |      1987 |  2 | Fellini   | Federico  |  1
 The Big Easy    |        6 |      1987 |  2 | Noel      | Magali    |  2
 The Big Easy    |        6 |      1987 |  2 | Zanin     | Bruno     |  3
 The Big Easy    |        6 |      1987 |  2 | Maggio    | Pupella   |  4
 The Big Easy    |        6 |      1987 |  2 | Drancia   | Armando   |  5
 The Big Easy    |        6 |      1987 |  2 | Mcbride   | Jim       |  6
 The Big Easy    |        6 |      1987 |  2 | Quaid     | Dennis    |  7
 The Big Easy    |        6 |      1987 |  2 | Barkin    | Ellen     |  8
 The Big Easy    |        6 |      1987 |  2 | Beatty    | Ned       |  9
 The Big Easy    |        6 |      1987 |  2 | Persky    | Lisa Jane | 10
 The Big Easy    |        6 |      1987 |  2 | Goodman   | John      | 11
 The Big Easy    |        6 |      1987 |  2 | Ludlam    | Charles   | 12
 The Big Easy    |        6 |      1987 |  2 | Singleton | John      | 13
 The Big Easy    |        6 |      1987 |  2 | Gooding   | Cuba Jr.  | 14
 The Big Easy    |        6 |      1987 |  2 | Cube      | Ice       | 15
 The Big Easy    |        6 |      1987 |  2 | Fishburne | Larry     | 16
 The Big Easy    |        6 |      1987 |  2 | Ferrell   | Tyra      | 17
 The Big Easy    |        6 |      1987 |  2 | Chestnut  | Morris    | 18
 Boyz N the Hood |       13 |      1991 |  3 | Fellini   | Federico  |  1
 Boyz N the Hood |       13 |      1991 |  3 | Noel      | Magali    |  2
 Boyz N the Hood |       13 |      1991 |  3 | Zanin     | Bruno     |  3
 Boyz N the Hood |       13 |      1991 |  3 | Maggio    | Pupella   |  4
 Boyz N the Hood |       13 |      1991 |  3 | Drancia   | Armando   |  5
 Boyz N the Hood |       13 |      1991 |  3 | Mcbride   | Jim       |  6
 Boyz N the Hood |       13 |      1991 |  3 | Quaid     | Dennis    |  7
 Boyz N the Hood |       13 |      1991 |  3 | Barkin    | Ellen     |  8
 Boyz N the Hood |       13 |      1991 |  3 | Beatty    | Ned       |  9
 Boyz N the Hood |       13 |      1991 |  3 | Persky    | Lisa Jane | 10
 Boyz N the Hood |       13 |      1991 |  3 | Goodman   | John      | 11
 Boyz N the Hood |       13 |      1991 |  3 | Ludlam    | Charles   | 12
 Boyz N the Hood |       13 |      1991 |  3 | Singleton | John      | 13
 Boyz N the Hood |       13 |      1991 |  3 | Gooding   | Cuba Jr.  | 14
 Boyz N the Hood |       13 |      1991 |  3 | Cube      | Ice       | 15
 Boyz N the Hood |       13 |      1991 |  3 | Fishburne | Larry     | 16
 Boyz N the Hood |       13 |      1991 |  3 | Ferrell   | Tyra      | 17
 Boyz N the Hood |       13 |      1991 |  3 | Chestnut  | Morris    | 18
(54 rows)

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
(1 row)

select fnames, lname from movies, persons
        where movies.director = persons.id 
          and title = 'Amarcord';
 fnames |  lname
--------+---------
 Jim    | Mcbride
(1 row)

jstudent=> select fnames, lname from movies, persons
jstudent->         where movies.director = persons.id
jstudent->           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

load data local infile '~wolfe/public/270/movies/persons.data' into table persons;
load data local infile '~wolfe/public/270/movies/movies.data'  into table movies;
load data local infile '~wolfe/public/270/movies/acts_in.data' into table 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)