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
--    by Max Hailperin on 2005-01-31

-- 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.

-- You'll start by logging in to MySQL using something like:
--    mysql -h mcs-mysql.gac.edu -u max -p
-- where max will be replaced by your user name.
-- You'll be prompted for a password; this is your MySQL password, not
-- your usual login password.

-- Alternatively, you could use the web-based interface linked from
-- the course web page.  Some of the actions shown here as SQL commands
-- can be done in point-and-click fashion in that interface.

-- If this is the first time you are doing this example, and so you
-- don't have a database yet to do it in, you need to create one (again,
-- replace max with your own name):

create database max_movies;

-- Whether the database already existed or is newly created, connect
-- to it (again, replace max with your own name):

use max_movies;

-- If you you did this example before, and still have the tables around,
-- you can get rid of the old versions:

drop table acts_in;
drop table movies;
drop table persons;

-- First, the table definitions.  Note that they must be defined in
-- this order, because of the foreign key constraints:

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

create table movies (title varchar(40) not null,
                     director int not null,
                     year_made int not null,
                     id int,
                     primary key (id),
                     foreign key (director) references persons(id));

create table acts_in (actor int,
                      movie int,
                      primary key (actor, movie),
                      foreign key (actor) references persons(id),
                      foreign key (movie) references movies(id));

-- 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 |
+-----------------+----------+-----------+----+

select title from movies;
+-----------------+
| title           |
+-----------------+
| Amacord         |
| The Big Easy    |
| Boyz N the Hood |
+-----------------+
3 rows in set (0.00 sec)

select title from movies where year_made = 1991;
+-----------------+
| title           |
+-----------------+
| Boyz N the Hood |
+-----------------+
1 row in set (0.00 sec)

select title from movies where year_made >= 1985 and year_made <= 1995;
+-----------------+
| title           |
+-----------------+
| The Big Easy    |
| Boyz N the Hood |
+-----------------+
2 rows in set (0.01 sec)

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 in set (0.00 sec)

select year_made from movies where title = 'The Big Easy';
+-----------+
| year_made |
+-----------+
|      1987 |
+-----------+
1 row in set (0.00 sec)

-- select with two tables will form a complete cross-product

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

-- To limit outselves to just the combinations that make sense
--  where the person is the movie's director), we can impose
--  a constraint that the director number matches the person's
--  id number.  The below *almost* does that, but has an error:

select title, fnames, lname, year_made 
       from movies, persons 
       where director = id;
ERROR 1052 (23000): Column 'id' in where clause is ambiguous

-- The solution is to start naming the tables we are joining
--  together in each select, and then refer to the columns
--  more specifically, saying which table they are from:

select title, fnames, lname, year_made 
       from movies m, persons p
       where m.director = p.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 in set (0.00 sec)

select fnames, lname from movies m, persons p
       where m.director = p.id 
         and m.title = 'The Big Easy';
+--------+---------+
| fnames | lname   |
+--------+---------+
| Jim    | Mcbride |
+--------+---------+
1 row in set (0.00 sec)

select fnames, lname from movies m, persons p
        where m.director = p.id 
          and m.title = 'Amarcord';
Empty set (0.00 sec)

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

select fnames, lname from movies m, persons p
        where m.director = p.id 
          and m.title = 'Amarcord';
+----------+---------+
| fnames   | lname   |
+----------+---------+
| Federico | Fellini |
+----------+---------+
1 row in set (0.00 sec)

select fnames, lname 
       from movies m, persons p, acts_in a
       where m.id = a.movie 
         and p.id = a.actor 
         and m.title = 'The Big Easy';
+-----------+---------+
| fnames    | lname   |
+-----------+---------+
| Dennis    | Quaid   |
| Ellen     | Barkin  |
| Ned       | Beatty  |
| Lisa Jane | Persky  |
| John      | Goodman |
| Charles   | Ludlam  |
+-----------+---------+
6 rows in set (0.01 sec)

-- Delete everything 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 '/Net/solen/home/w/o/wolfe/public/270/movies/persons.data' into table persons;
load data local infile '/Net/solen/home/w/o/wolfe/public/270/movies/movies.data'  into table movies;
load data local infile '/Net/solen/home/w/o/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 in set (0.00 sec)

select distinct fnames, lname 
       from persons p, acts_in a1, acts_in a2 
       where a1.actor = a2.actor 
         and a1.movie <> a2.movie
         and a1.actor = p.id;
+---------+--------+
| fnames  | lname  |
+---------+--------+
| Claude  | Rains  |
| Orson   | Welles |
| Edward  | Fox    |
| Lindsay | Crouse |
+---------+--------+
4 rows in set (0.00 sec)

select title from movies m, persons p, acts_in a
       where m.id = a.movie
         and p.id = a.actor 
         and p.fnames = 'Claude' and p.lname = 'Rains';
+--------------------+
| title              |
+--------------------+
| Casablanca         |
| Lawrence of Arabia |
+--------------------+
2 rows in set (0.00 sec)