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)