After Online Session 8, we have an SQLite database of stock price data named NASDAQ_200505.db. Its schema is:
EndOfDayData(symbol, date, open, high, low, close, volume)
with data that look like this:
AMAT|20050506|15.56|15.66|15.33|15.63|20778700
Now we can write queries in SQL to interrogate the data.
Write SQL queries to answer these requests:
(Spoiler alert: the answers come next...).
When we learned about selection operators in relational algebra, we saw that we can write arbitrary conditions on the attributes of a relation. SQL supports all the usual arithmetic and boolean operators we need to write conditions.
Our first query involves comparing the high and low prices for each entry in the table:
$ sqlite3 NASDAQ_200505.db
sqlite> SELECT * FROM EndOfDayData WHERE high > 2*low;
PRTL|20050502|1.47|1.61|0.73|0.77|9296200
EPMN|20050504|0.14|0.71|0.14|0.66|185800
INSG|20050504|0.5|1.29|0.5|0.7|1090000
CALGZ|20050505|0.09|0.09|0.04|0.04|1000
GCFBW|20050505|0.12|0.55|0.1|0.49|46800
CALGZ|20050506|0.09|0.76|0.07|0.3|974500
The second requires a compound condition that examines three columns in each tuple:
sqlite> SELECT * FROM EndOfDayData
...> WHERE (volume > 100000) AND (close > 1.5*open);
EPMN|20050504|0.14|0.71|0.14|0.66|185800
CALGZ|20050506|0.09|0.76|0.07|0.3|974500
May 4, 2005 (*) was quite a day for EPMN investors. The price
ranged widely, went way up in the end, and traded a lot of
shares.
(*) May the Fourth be with you!
These two queries produce relations with six and two tuples, respectively. Just how rare are these phenomena? SQL can help us figure that out with one of its aggregation operators:
sqlite> SELECT COUNT(*) from EndOfDayData;
16420
So, only 6 trading days out of 16,420 involve stocks whose high
price ever reached twice its low price, and only 2 trading days
out of the 16,420 traded more than 100,000 shares and closed up
more than 25%. Rare, indeed.
We'll talk more about SQL's aggregation operators soon!
In recent weeks, we have learned about relational algebra, its most important operators, and how to use them to build expressions that query a relational database. We are now ready to write SQL programs that implement our queries against real databases.
In last week's online session, we learned how to import data from CSV text files into SQLite databases. This gives us the ability to work with larger and more interesting databases, such as the NASDAQ database we used in the opening exercise. We are limited mostly by our own curiosity... There are a lot of free and open source data files available on the web.
This week, we turn our attention to SQL as a programming language. We have already used SQL statements of this form repeatedly:
SELECT attributes FROM relation WHERE condition;
as a way to implement relational queries of this form:
πattributes(σcondition(relation))
Notice that, in SQL, the "select" clause acts like projection,
while the "where" clause acts like selection. Programming
languages are odd sometimes...
But SQL has many more features than we have seen thus far. Some of these features implement more of the relational algebra we have learned. Others enable programmers to perform other computational tasks that are useful when querying data and presenting results. We will see that SQL is a programming language: not a general-purpose language, but one designed well for the specific task of writing complete database programs.
In this session, we will explore some of the ways that SQL supports basic programming tasks. We will then look at a feature that allows us to implement relational algebra's renaming operator using local names called aliases.
Before we start, let's prepare the database we will use in our examples.
This section creates the database we use as a running example in this session. It teaches you a little new SQL and a little new SQLite. This material is optional. If you'd like to skip it, if only for now, download the database file now and jump to the next section.
With SQLite's .import command, we can rapidly build relational databases from text files. I refer to the data and queries from Homework 3 as examples in today's lecture, so I used what we learned about .import last week and a couple of extensions to build a movies database.
First, I created CSV files for the data listed in the assignment: [ movie.csv | actor.csv | stars_in.csv | director.csv ]. These files include a header line with names for the columns.
Then I used .import to bring the data into tables named Movie, Actor, StarsIn, and Director:
sqlite> .mode csv
sqlite> .import movie.csv Movie
sqlite> .import actor.csv Actor
sqlite> .import stars_in.csv StarsIn
sqlite> .import director.csv Director
Notice that I did not create tables first. When the target table
does not already exist, .import treats the first line of
the CSV file as a header line, defines the table, and names the
columns for us. That saved me typing the schema and the
CREATE TABLE statement.
Unfortunately, SQLite doesn't know anything about the data in the file except the column headers. As a result, it generates schema that with the most general types possible:
sqlite> .schema Movie
CREATE TABLE Movie(
"title" TEXT,
"director" TEXT,
"year" TEXT,
"rating" TEXT
);
I wanted better schema, so I exported the auto-generated schema to an SQL file so that I could edit them:
sqlite> .output homework03-schema.sql
sqlite> .schema
sqlite> .quit
The .output dot command tells SQLite where to write
its output for the commands that follow. By default, it writes to
standard output. My command instructs it to write the output of
the .schema command to a file named
homework03-schema.sql.
I then edited the schemas to look the way I wanted them, including type information. For example, I modified the Movie schema to this:
CREATE TABLE Movie(
title VARCHAR(20),
director VARCHAR(20),
year SMALLINT,
rating DECIMAL(2,1)
);
Note the datatype I used for the rating, which is a decimal value
on a scale of 1-10, with one digit after the decimal point.
DECIMAL is one of the datatypes supported by SQL.
Wikipedia has
a list of the standard types,
but it doesn't have any detail about them. I'll create a more
detailed description soon and add it to the Course Resources
page.
I ended up with a file of schemas that looked just as I wanted them. I then used .import again to re-import the data and create my database file. If you'd like, here is a complete SQL file for the database with all create and insert statements needed to build the database.
The end result is homework03.db, the database we will use throughout this session to illustrate the new features of SQL we learn. Download it now and follow along in a new SQLite session!
In today's opening exercise, we wrote conditions using simple boolean and arithmetic operators:
... WHERE (volume > 100000) AND (close > 1.5*open);
I think you'll find that SQL supports all the basic math and
logic that you need to manipulate numbers and compare values.
Sometimes, we'd like to compute summary values for a query, such as the total for a set of values. SQL provides a small set of aggregation operators that replace simple loops in a more general language. We saw one in the discussion of our opening exercise, COUNT. Here's another example:
sqlite> SELECT COUNT(*) FROM Movie;
4
We can also count results from a narrower query:
sqlite> SELECT COUNT(director) FROM Movie WHERE rating > 7.5;
3
This may not be the answer we expected, because only two directors have movies rated over 7.5. We have seen this phenomenon before, in Session 7, when a relation of genres contained duplicates. In relational algebra, relations are sets, but SQL implements relations as bags, not sets.
A bag is like a set, but it keeps track of how many of each item there are. It's a cool data structure with a lot of uses! If you'd like to learn more, read this short section on Wikipedia.
In Session 7, we learned about the modifier DISTINCT, which enforces set behavior. We can use it here, too, to find the number of unique tuples in a result:
sqlite> SELECT COUNT(DISTINCT director) FROM Movie
...> WHERE rating > 7.5;
2
sqlite> SELECT COUNT(actor) FROM StarsIn;
6
sqlite> SELECT COUNT(DISTINCT actor) FROM StarsIn;
3
COUNT is one of five aggregation operators in SQL. The others are SUM, AVG, MAX, and MIN. They work much as you might guess from their names:
sqlite> SELECT MIN(year) FROM Movie; # year of oldest movie
1987
sqlite> SELECT AVG(rating) FROM Movie; # the average rating
7.7
These SELECT statements do not return relations. They return scalar values: a single value in one of the datatypes supported by the language. Scalars are useful as the answers to top-level queries such as these, and they will also be useful as parts of larger queries. (Coming soon...)
SQL has lots of features that enable us to write programs more suitable to our needs. For now, let's consider three briefly.
Sometimes, we'd like to change a column of values returned by a query. For example, movie ratings are on a 1-10 scale, but we might want to convert them to a scale of 1-5 stars. So we want to see the ratings cut in half. We can do that sort of arithmetic on an item in the list of attributes we give to SELECT:
sqlite> SELECT rating/2 FROM Movie;
4.1
3.8
3.7
3.8
This statement requires to several steps in order. We can think of it projecting rating from the Movie relation, and then applying the /2 operation to each tuple in the result.
SQL returns a relation for each query. Our DBMS presents that result to us in some form. By default, SQLite uses a list format with column values separated by | characters:
sqlite> SELECT title, rating/2 FROM Movie;
Fargo|4.1
Raising Arizona|3.8
Spiderman|3.7
Wonder Boys|3.8
The name here is self-explanatory but, once separated from the
query, the rating is just a number. Maybe we'd like to include
in our output a label that augments the number. SQL allows us
to create a constant column that acts like a label:
sqlite> SELECT title, rating/2, 'of five stars' FROM Movie;
Fargo|4.1|of five stars
Raising Arizona|3.8|of five stars
Spiderman|3.7|of five stars
Wonder Boys|3.8|of five stars
Adding a column with a constant value is a feature of SQL. Most DBMSes give us the ability to format output in a more pleasing fashion. SQLite gives us several such features. One very useful one is column mode:
sqlite> .mode column
sqlite> SELECT title, rating/2, 'of five stars' FROM Movie;
title rating/2 'of five stars'
--------------- -------- ---------------
Fargo 4.1 of five stars
Raising Arizona 3.8 of five stars
Spiderman 3.7 of five stars
Wonder Boys 3.8 of five stars
It presents output in columnar format and adds column headers.
In this case, one of our columns is computed and another is a
constant, so SQLite uses those expressions for the headers.
Later in the session, we'll see how to improve those column
names.
SQL provides the ability to do several kinds of pattern matching in the conditions we write. The most basic uses the form
s LIKE p
where s is a value, interpreted as a string, and p
is a pattern.
For example, we could find all the movies made in the 2000s by selecting all movies where year &ge 2000, using a boolean comparison. But we can also use a pattern:
sqlite> SELECT * FROM Movie WHERE year LIKE '20__';
title director year rating
----------- -------- ---- ------
Spiderman Raimi 2002 7.4
Wonder Boys Hanson 2000 7.6
The pattern '20__' looks for exact matches: strings that
start with '20' and contain two more characters.
We can also match arbitrary strings. For example, we can find all movies whose titles start with 'Wonder', regardless of length, using this pattern:
sqlite> SELECT * FROM Movie WHERE year LIKE 'Wonder%s';
title director year rating
----------- -------- ---- ------
Wonder Boys Hanson 2000 7.6
The pattern 'Wonder%s' would also match
'Wonder Woman', if it were in the database.
We have only begun to scratch the surface of what SQL provides as a programming language, even for these features. We'll learn more as time goes by, and look features we might want on the fly. But now let's move on to a feature that both implements relational algebra and expands the scope of the programs we can write.
Recall our example earlier where we converted ratings to a 1-5 scale using a computed attribute:
sqlite> SELECT title, rating/2 FROM Movie;
title rating/2
--------------- --------
Fargo 4.1
Raising Arizona 3.8
Spiderman 3.7
Wonder Boys 3.8
The new column doesn't have a name in the table, so it uses the
expression as the attribute name.
SQL allows us to assign a name to an attribute using an AS clause:
sqlite> SELECT title, rating/2 AS rating FROM Movie;
title rating
--------------- ------
Fargo 4.1
Raising Arizona 3.8
Spiderman 3.7
Wonder Boys 3.8
We can rename both columns, if we like:
sqlite> SELECT title AS name, rating/2 AS rating FROM Movie;
name rating
--------------- ------
Fargo 4.1
Raising Arizona 3.8
Spiderman 3.7
Wonder Boys 3.8
The AS clause enables us to give an alias to an attribute or even a relation. In these examples, the alias is useful for presentation purposes. But sometimes we may need an alias to even write the query we desire.
Consider this request: We would like to find all pairs of actors who have starred in a film together. Our StarsIn relation has all information we need to solve this problem:
sqlite> SELECT * FROM StarsIn;
actor movie
--------- ---------------
Cage Raising Arizona
Maguire Spiderman
Maguire Wonder Boys
McDormand Fargo
McDormand Raising Arizona
McDormand Wonder Boys
All we need to do is to look at all combinations of tuples and find combinations with the same value for movie. But we have a problem when we try to find the product of StarsIn with: itself:
sqlite> SELECT * FROM StarsIn, StarsIn;
Error: ambiguous column name: main.StarsIn.actor
The error message here isn't as helpful as it might be. We can
figure out the problem if we think about what we know when we
join two tuples with a commonly-named attributes... We tell the
two attributes apart by prefixing the attribute name with the
relation name. But here the relations have the same name, so
the names are still ambiguous!
In relational algebra, we could solve this problem by using the renaming operator, ρ, to create new relations with different name:
ρStarsIn1(StarsIn) ⨯ ρStarsIn2(StarsIn)
SQL doesn't have a renaming operator, but we can use AS clauses to create local aliases for our relation, compute a product, and then look the same movie title in both columns:
sqlite> SELECT StarsIn1.actor, StarsIn2.actor
...> FROM StarsIn AS StarsIn1, StarsIn AS StarsIn2
...> WHERE StarsIn1.movie = StarsIn2.movie;
actor actor
--------- ---------
Cage Cage
Cage McDormand
Maguire Maguire
Maguire Maguire
Maguire McDormand
McDormand McDormand
McDormand Cage
McDormand McDormand
McDormand Maguire
McDormand McDormand
Aliases to the rescue!
We still have a couple of problems. First, our query thinks that actors star in movies with themselves. Now, I think Frances McDormand is an amazing actor, but she can't star in a movie with herself. Let's only keep tuples where the actors' names are different:
sqlite> SELECT StarsIn1.actor, StarsIn2.actor
...> FROM StarsIn AS StarsIn1, StarsIn AS StarsIn2
...> WHERE StarsIn1.movie = StarsIn2.movie
...> AND StarsIn1.actor != StarsIn2.actor;
actor actor
--------- ---------
Cage McDormand
Maguire McDormand
McDormand Cage
McDormand Maguire
Better, but one problem remains. Our cross product has some tuples in which Cage is chosen from Stars1 and other tuples in which Cage is chosen from Stars2. We can winnow our result further by looking for combinations that favor actors in alphabetical order:
sqlite> SELECT StarsIn1.actor, StarsIn2.actor
...> FROM StarsIn AS StarsIn1, StarsIn AS StarsIn2
...> WHERE StarsIn1.movie = StarsIn2.movie
...> AND StarsIn1.actor < StarsIn2.actor;
actor actor
--------- ---------
Cage McDormand
Maguire McDormand
And there we have it!
This example gives you a taste of what is possible when using a local alias to rename a relation. Next session, we will see more problems in which renaming an attribute or a relation within a query enables us to write more complex queries. Those queries will also use another idea that we saw briefly last week: subqueries.
There is no deliverable for this session. Just play! Try the queries you've seen in this session in SQLite. If you are feeling adventurous, write a few more SQL queries of your own design against the movie database or the stock database. Let me know if you learn anything interesting about the data as a result.