CSC 321.01, Class 11: Modeling, continued
- Notes and news
- Upcoming work
- Extra credit
- SQL basics
News / Etc.
- Apologies for the late start today.
- Please let me know when you notice problems on the Web site. I think I’ve fixed most of them. (Okay, something is massively wrong with the generation of readings in the schedule, but that turns out to be a programming problem I don’t have time to address.)
- I’ll be generating today’s lab live today. Sorry!
- Reading journal for class 12 due Monday night.
- The last programming project to be posted tonight, due a week from Tuesday.
Good things to do
Note: I do not do extra credit in two-credit classes.
- Convocation today.
- CS table Tuesday
- CS extras Thursday: Grad school
Common problem: We want to model data in our program.
A model is a collection of names/types that describe some kind of data.
Traditionally, we think about grouping these fields together in an object or in a table in which each row is one “thing” and the columns correspond to the fields.
We then want to do things with the tables
- I want all of the faculty members whose specialization is Vision
- I want all of the movies whose rating is PG
- I want all of the movies released before 1970
- I want all of the movies released before 1970 whose rating is PGa
We often want to ask compound things, too
- I want the release dates of all the movies that Sam Rebelsky reviewed.
How do you implement this? Lots of strategies, most bad.
Solution: Build programs whose only job is to manage tabular data. The programs that manage this are Database Management Systems.
Note: Common syntax is probably important. SQL. Standard/structured query language, designed for working with data in the “related tables” format. (“Relational databases; “relation” is another name for “table”)
- SELECT columns FROM table
- SELECT columns FROM table WHERE restrictions
- SELECT columns FROM table1,table2 WHERE restrictions
- SELECT reviews.reviewername FROM movies,reviews WHERE movie.moviename = ‘Star Wars’ AND review.movieid = movie.movieid
- UPDATE table SET column=value WHERE …
- INSERT columns VALUES values INTO table
- CREATE TABLE name … (Sam always looks this one up)
These kinds of things are popular among the many popular implementations of SQL (MySQL, Oracle, SQLite, PostgeSQL,
There are also other models of data that are less standardized and less focused on tables.
Unfortunately, SQL misses many things you want to do.
- List all the tables in the database.
- Determine what columns are in a table.
- Write a script
Open your C9 account and navigate to your first Rotten Potatoes instance.
Start the command-line database manager with
.helpto determine what commands are available.
Determine what tables are available by typing
.tables. (The command is different in every DBMS.)
Determine the structure of the movies table by typing
Get all the movie data with
SELECT * FROM movies;
Get all the movie titles with
SELECT title FROM movies;
Get all the movie titles plus ids plus ratings. Separate the fields with commas.
Get all the movie titles plus ids plus ratings where rating is PG
Get all the movie titles plus ids plus ratings plus year where year is at least (pick a year).
Get all the movie titles plus ids plus ratings where year is at least (pick a year). [Note, we are selecting by year, but not displaying year.]
- Add two more movies, specifying everything besides id. (You will need
to look up the INSERT syntax to get it right.)
INSERT into TABLE (fields) VALUES (values);
Try to add a movie with a duplicate id.
- Create a new table,
reviews, that contains the following columns. (You will need to look up the CREATE TABLE syntax to get it right. You may also want to use
.schema moviesfor ideas)
movieid, an integer which references an entry in the movie table,
reviewer, a string
review, a string.
Verify that you created the table correctly with
Generate four sample reviews for at least two different movies
Try the command
SELECT * from movies,reviewsto see what you get.
- Figure out how to select just the reviews for one movie.
What do I expect you to take away from all of this?
- You can master the basics of SQL in about two hours.
- You will, however, miss some subtleties, such as how to set up tables that automatically generate and check keys.
- Some of the commands are really powerful and therefore really
- It takes a bit of time to understand the subtleties of join
- But there are lots of good articles
- Spend three (eight hour) days of your life learning this stuff. It’s valuable.
- When you are working in rails, it’s all done for you.