Day 12

Thursday, Mar 9, 2017

Contents

Upcoming SQL quiz

SQL quiz next Thursday, March 16. Will be using computer to run queries. Make sure all the databases we discuss today are on your computer!

Quiz today

At some point during class today, ask a question about SQL.

sqlite3 and some basic SQL commands

sqlite3 metacommands start with a period. Google to find a list of all of them.

SQL commands end with a semicolon (;).

463$ sqlite3 test.db
SQLite version 3.8.11.1 2015-07-29 20:00:57
Enter ".help" for usage hints.
sqlite>

Some SQLITE metacommands:

sqlite> .tables
sqlite> .mode column
sqlite> .headers on
sqlite> .read
sqlite> .mode csv
sqlite> .separator ,
sqlite> .separator t
sqlite> .open
sqlite> .import

Some SQL commands (they all end with a semicolon):

sqlite> create table ... ;
sqlite> insert into ... ;
sqlite> select * from ... ;

Ctrl-d or .quit to exit from sqlite3.

Let's review some movies

Go to here: https://docs.google.com/document/d/1C8ZcUbhZgO7Mx7NJBisUAD80qJhAuc99OLfkyqAXn8Y/edit?usp=sharing except change the first Z to an X.

Importing a csv file as a table

sqlite> .import foo.csv mytable

will use first row as the column names unless the table mytable has already been created

Some more databases to learn SQL on

Tiny

Let's make some of our own with a spreadsheet.

Small

Our movie ratings

Simulated UB international student database (Disclaimer: I created the fake student names in this database by building each syllable as random consonant + random vowel + random consonant. Anything comical is accidental.)

Next: invent some questions you'd like to find out from the movie database.

Bigger

2016 presidential election results

Download these csv files: 2012 presidential election results, county codes and names.

sqlite> .import foo.csv mytable

May need metacommand ".mode csv" and/or ".separator ,"

Let's learn the function of basic SQL commands

Then try them out on movies and election databases.

Figure out how to use all of the following keywords and symbols:

select
from
limit
where
= < >
order by
desc
like '%foo%'
as
+-/*
count
max
min
in

Counting

2016_03_08/count.png

Joins

"Joins" are more-or-less subsets of the cartesian product of tables.

inner join vs outer join

Try it out on fake UB student database.

An alternate syntax for joins is to just specify the tables you want to join in a comma-separated list, like this:

select name, title from reviewer, movie, rating where reviewer.rid=rating.rid and rating.mid=movie.mid;

You can also give tables aliases to save typing, like this:

select name, title from reviewer re, movie m, rating ra where re.rid=ra.rid and ra.mid=m.mid;

Aggregation by groups

To aggregate (e.g. sum, average, count, min, max) by groups, use "GROUP BY"

SELECT a,b,c AGG(d) FROM table GROUP BY a,b;

where AGG stands for one of SUM, COUNT, MAX, etc.

Returns a table with (here) 4 columns. Groups the rows by distinct (a,b), and returns 1 row per group.

If a requested column is neither aggregated nor grouped by, you get a seemingly random choice of the values occurring in the group.

Creating VIEWs

CREATE VIEW foo AS SELECT ...

I (over?)use this construct essentially in the way we set variables in any reasonable programming language.

Unfortunately to re-run code, you'll have to first drop an existing VIEW:

DROP VIEW IF EXISTS foo;

My .sqlite_history from today

Here it is