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
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
Open up a bash terminal window, and
cd to the
From here, run
sqlite3 testdb, which will start up a new sqlite3 session.
You should see a prompt that looks like:
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.
If not, check with the instructor.
Now that you’ve exited
sqlite3, you are bash in bash (you should see the
$ prompt again).
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.
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
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
directortable, once we insert some rows.
Creating this schema will involve writing two
CREATE SQL statements.
Now it’s time to add some data to the tables we just created!
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
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.
For each of the bullet points below, write a SQL
SELECT query that gets only the described information out of the
An now, a few from the
director, only if the director is older than 55