CSc 250: Lab 10

This lab was designed to be completed during lab-time for cs250. 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. However, with some tweaks, you can accomplish roughly the same in Windows.

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

And 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

Write one more query that requires a join. This query should list the name of each movie with the name of each director next to it.

Problem 5

Assume a python program named lab-11-A.py exists in the same directory as the moviedb file (Desktop). lab-11-A.py has the following contents:

import sys
import sqlite3
conn = sqlite3.connect('moviedb')
rows = conn.execute("SELECT * FROM director")
for row in rows:
    print( str(row[0]) + ' | ' +str(row[2]))

What will this program print when run? Try to figure out the answer, then create the program and run it to check.

Problem 6

Assume a python program named lab-11-B.py exists in the same directory as the moviedb file (Desktop). lab-11-B.py has the following contents:

import sys
import sqlite3
conn = sqlite3.connect('moviedb')
rows = conn.execute("SELECT year, title, rt_rating FROM movie WHERE rt_rating > 70")
for row in rows:
    print( str(row[1]) + ' | ' +str(row[2]))

What will this program print when run? Try to figure out the answer, then create the program and run it to check.

Problem 7

Now, let’s write a very simple python program that accesses a table in this database. In the same directory that you created moviedb, create a python program named getmovies.py. This program will:

• Import sqlite3
• Connect to the moviedb database
• Executes SELECT * FROM movie;
• Loops through all of the rows that are returned and prints them out

If you forgot how to connect to a database and run a query from python, look at the previous problems or the lecture notes!

Assuming the database was populated correctly, the results of running this query should be:

$ python3 getmovies.py
King Kong | 2005 | 84 | 1 | 4
Flags of Our Fathers | 2006 | 73 | 2 | 3
Man of Steel | 2013 | 55 | 3 | 1
Super 8 | 2011 | 82 | 4 | 5
Open Range | 2003 | 79 | 5 | 7
The Kings Speech | 2010 | 95 | 6 | 2
Hacksaw Ridge | 2016 | 87 | 7 | 6

Problem 8

Write a python program named addmovies.py that uses the sqlite3 module to insert 3 new rows into the movie table using INSERT statements. You can add whichever 3 movies you want. Though, remember to choose movie_id values that have not already been used by another row! After writing the program, run it.

In bash, cd to the directory with the moviedb file in it and run sqlite3 moviedb to open it up. Run SELECT * FROM movies; and make sure the new rows appear!

Submit at least 4 problems to D2L

Solutions