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)