CSc 250: Lab 10

This lab was designed to be completed during lab-time for cs250. This lab is not graded for correctness. However, you should work through all of the problems, as it will help you on assignments and exams. If you cannot complete the lab in the alloted time, finish at home.

This particular lab was designed to be completed on the Mac lab machines in GS 930

All Problems

You will be writing a set of SQL statements that create tables, populate tables, query tables, and remove entries from tables. All of your SQL should be put into a single file names lab10.sql.

Problem 1 - Warm-up

Open up a bash terminal window, and cd to the Desktop directory. From here, run sqlite3 testdb, which will start up a new sqlite3 session. You should see a prompt that looks like:

sqlite>

This indicates that you are running sqlite3, and it is waiting for you to issue SQL queries.

From here run the following CREATE SQL statement:

CREATE TABLE movie (title TEXT, director TEXT, year INT);

Then, insert a few entries into this new table by running:

INSERT INTO movie VALUES ('Batman Begins', 'Christopher Nolan', 2005); 
INSERT INTO movie VALUES ('The Dark Knight', 'Christopher Nolan', 2008); 
INSERT INTO movie VALUES ('The Dark Knight Rises', 'Christopher Nolan', 2012); 

Finally, run the following SELECT query to get all of the rows and columns from this table:

SELECT * FROM movie;

Now, exit the sqlite3 session by typing .exit and hitting enter. At this point you should see a new file named testdb created on the Desktop. Do you? If not, check with the instructor.

Now that you’ve exited sqlite3, you are bash in bash (you should see the $ prompt again). Run sqlite3 testdb again, and at the prompt re-run the SELECT * FROM movie; query. Do you see the same result you saw the first time you ran it? You should, and this indicated that the database will correctly save what you store in it in-between sessions. Exit sqlite3 again.

Problem 2 - Creating a Schema

In this problem, you will write the Schema for a movie database.

From the desktop, run sqlite3 moviedb, which will create a new (empty) sqlite3 database file named moviedb. In this database you should create two tables to store information about movies and their associated directors.

Create one table named director should should have the following attributes (columns):

• first_name - The director’s first name
• last_name - The director’s last name
• age - The director’s age
• director_id - A unique ID for each director

Then create a table named movie. It should have the following attributes (columns):

• title - The title of the movie
• year - The release year
• rt_rating - The rotten tomatoes rating (a number ranging from 0-100)
• movie_id - A unique ID. Each movie should have a different ID.
• director_id - The ID of the director. This will reference IDs from the director table, once we insert some rows.

Creating this schema will involve writing two CREATE SQL statements.

Problem 3 - Adding some Data

Now it’s time to add some data to the tables we just created!

Write INSERT statements to insert at least 7 rows into each table. Make sure each of the rows have realistic an unique information in them. If you need to, google for information like the rotten tomatoes rating, movie release year, director ages, etc. Also, make sure that the IDs from the director_id column in the movie table matches the associated director_id from the director table. Look up each movie’s rotten tomato rating online to fill in the values for that column.

Once you’ve inserted the rows, run SELECT * FROM movie; and SELECT * FROM director; to make sure the information is in the database tables.

Problem 4 - Query the Data

For each of the bullet points below, write a SQL SELECT query that gets only the described information out of the movie table:

• Get only the titles for all rows in movie
• Get only the titles and rotten tomato ratings for all rows in movie
• Get only the titles and rotten tomato ratings for all rows whose rotten tomato rating is greater-than 80 in movie

An now, a few from the director table:

• Get only the first name and last name for all rows in director
• Get only the first name and last name for all rows in director, only if the director is older than 55