MCS-170 Project 8: Databases Using MySQL
- Part II
Due: In Class
Project Task
In part I of this lab, you created a small movie database using the
MySQL server
in the MCS department.
In this lab, you will use a more extensive movie database to
investigate the process of constructing queries on the database.
During the lab you
will be asked a series of questions.
Record your answers and turn them in to the instructor at the end of
the lab period.
In Lab
To start using MySQL open a shell window. (Click on the Fedora icon in
the lower left corner, choose "Run Command" from the popup menu, type
"Konsole," and hit
"Run.")
A window will popup on the screen
labeled "Shell-Konsole." Type the following command, replacing
"username" with your individual username (your email name).
mysql -h mcs-mysql.gac.edu -u username -p
You'll be asked for your password, which
will be "gustavus".
The first thing you need to do is connect to the movie database created
in part I of this lab. To do this type in the following, replacing
'username' with
your own username that you used to login.
Now we will re-define the tables for our movie database. First,
we delete everything from our three tables:
delete from acts_in;
delete from movies;
delete from persons;
Next we load in data from external files. For example, the file persons.data contains a list of names
in the format of the persons
table. Each field is separated by a space. There are three such
external files for each of our three tables. We load the data from
these files into our tables as follows:
load data local infile '/Net/solen/home1/h/v/hvidsten/www-docs/courses/MC170/movies/persons.data' into table persons;
load data local infile '/Net/solen/home1/h/v/hvidsten/www-docs/courses/MC170/movies/movies.data' into table movies;
load data local infile '/Net/solen/home1/h/v/hvidsten/www-docs/courses/MC170/movies/acts_in.data' into table acts_in;
We can create queries for this new data just as we did in part I of
this project. For
example, the query
mysql> select title, year_made from movies
where year_made >= 1985 and year_made <= 1990;
returns the table for all movies in the database made between 1985 and
1990:
+---------------------+-----------+
| title | year_made |
+---------------------+-----------+
| The Big Easy | 1987 |
| Blood Simple | 1985 |
| A Fish Called Wanda | 1988 |
| House of Games | 1987 |
+---------------------+-----------+
This simple query needs to access just the movies table, as the title and
year_made fields are contained in that table, and the 'where' row
constraints refer only to fields (year_made) from the same table.
EXERCISE 1: Construct
a query to return the list of movies made in either the 1950's or the
1970's. (Group logical clauses with parentheses) Test out your query on
the database and record the query.
In contrast, let's suppose we wanted to construct a query for the
director of 'Gone with the Wind'. Since the director information
is in the persons table, and
the movie title is in the movies
table, we
first need to join
the movies and persons table, and then extract the
row we want. We will display the first and last name fields of
the director, so these will be the fields selected in the select
statement. Also, we need to restrict the rows of the join to
those rows where the director id in the movies table matches the id of the persons table. Finally, we get
the exact row we want by restricting to movies whose 'title' field
matches 'Gone with the Wind';
select firstnames, lastname from movies m, persons p
where m.director = p.id
and m.title = 'Gone with the Wind';
+------------+-------------+
| firstnames | lastname |
+------------+-------------+
| Victor | Fleming |
+------------+-------------+
EXERCISE 2:
What does the following query do?
select title from movies m, persons p
where m.director = p.id
and p.firstnames = 'Orson' and p.lastname = 'Welles';
That is, explain what table data is created by this command. Run the
command and record your result.
We can also do more sophisticated row testing, in particular we can do
string matching, using the like
comparison function. The command
select * from movies where title
like '%the%';
performs a matching test, returning those movies where the title
contains a string matching the string 'the'. The string we want
to match is enclosed by the two % signs. The result of executing this
query will be the following table.
+--------------------------+----------+-----------+----+
|
title
| director | year_made | id |
+--------------------------+----------+-----------+----+
| The Big
Easy
| 116 |
1987 | 2 |
| Boyz N the
Hood
| 130 |
1991 | 3 |
| The
Godfather
| 73
| 1972 | 5 |
| Gone with the
Wind
| 227 |
1939 | 9 |
| The Manchurian Candidate
| 122 |
1962 | 11 |
|
Othello
| 181 |
1952 | 13 |
| After the
Rehearsal
| 94
| 1984 | 16 |
| The Cotton
Club
| 73
| 1984 | 20 |
| The Crying
Game
| 174 |
1992 | 21 |
| The Day of the
Jackal | 77
| 1973 | 22 |
| The
Dresser
| 191 |
1984 | 24 |
| The
Exorcist
| 231 |
1973 | 26 |
+--------------------------+----------+-----------+----+
EXERCISE 3:
Construct a query that will return all of the movies that have an actor
with first name containing 'james'. Test out your query on the database
and record your query and results.
EXERCISE 4: Come up
with your own query that either uses a join operation or a string
matching operation different from the examples above. Test out your
query on the database and record your results.
EXERCISE 5: What did
you find most confusing in
working with queries (the select
command)?
Project Report:
Hand in your answers to the exercises above.
Back to
MCS
170
home page