CS61A: SQL

%load_ext sql
%config SqlMagic.style = '_DEPRECATED_DEFAULT'
%sql sqlite:///cs61a.db

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

Databases

• Database Management Systems (DMS) record and retrieve data.
• DMS organize data in tables which are collections of records that are rows that have a value for each column in the table.
  • Very much like a spreadsheet.
  • Each column has an assigned value.
• SQL is a declarative programming language

Declarative Programming

• In declarative languages such as SQL & Prolog:
  • A “program” is a description of the desired result
  • The interpreter figures out how to generate the result.
  • The programmer just has to describe what kind of data that they want to retrieve, and the interpreter creates the process to gather that data.
• In Imperative languages such as Python & Scheme:
  • A “program” is a description of computational processes
  • The interpreter carries out the execution and evaluation of rules.
  • The programmer must specify the process of how to get to the result.
• Expressions in SQL are named queries, and queries often take an existing table and build a new table from the table.

Structured Query Language (SQL)

• Typically use Sqlite3 for programs designed for one machine.
• A SELECT statement always includes a comma-separated list of column descriptions.
  • A column description is an expression that is optionally followed by AS and a column name
  • AS provides a new name for the column
  • Always end SQL with semi-columns.
• Selecting a literal (string or number), would generate a one-row table
• The union of two select statements is a table that contains the rows of both of their results

Naming Tables

• The result of a SELECT statement is displayed to the user, but not stored.
• We may give the result of a SELECT statement a permanent name with CREATE TABLE
• Ex: Constructing a table to keep track of the parent-child relationships between dogs:

%%sql 
CREATE TABLE parents AS
SELECT "daisy" AS parent, "hank" AS child UNION
SELECT "ace", "bella" UNION
SELECT "ace", "charlie" UNION
SELECT "finn", "ace" UNION
SELECT "finn", "daisy" UNION
SELECT "finn", "ginger" UNION
SELECT "ellie", "finn";

 * sqlite:///cs61a.db
Done.





[]

%%sql
SELECT * FROM parents;

 * sqlite:///cs61a.db
Done.

parent	child
ace	bella
ace	charlie
daisy	hank
ellie	finn
finn	ace
finn	daisy
finn	ginger

• Normally, tables aren’t created to scratch with unions. There are existing tables.

Projecting Tables

• To build one table from another table, we project an existing table.
• A SELECT statement can specify an input table using a FROM clause.
• A subset of the rows of the input table can be selected using a WHERE clause
• An ordering over the remaining rows can be declared using an ORDER BY clause
• Column descriptions determine how each input row is projected to a result row

SELECT [column expressions] FROM [table] WHERE [condition] ORDER BY [order];

• Ex: Get all children of the dog ace
  • Both child and parent are columns of parents.

%%sql
SELECT child FROM parents WHERE parent = "ace";

 * sqlite:///cs61a.db
Done.

child
bella
charlie

• A select statement considers all rows of a table.

Arithmetic

• A select statement may contain arithmetic
  • Column names evaluate to row values in select statements, and arithmetic expressions may combine row values and constants.
• Ex: ski lifts at a ski resort

%%sql
CREATE TABLE lift AS
    SELECT 101 AS chair, 2 AS single, 2 AS couple UNION
    SELECT 102, 0, 3 UNION
    SELECT 103, 4 ,1;

 * sqlite:///cs61a.db
Done.





[]

• Ex: To get the total number of people on each chair:
  • Values are calculated within rows, values from different rows are not mixed.

%%sql
SELECT chair, single + 2 * couple AS seats FROM lift;

 * sqlite:///cs61a.db
Done.

chair	seats
101	6
102	6
103	6

• Discussion Question: Given the table ints that descibes how to sum powers of 2 to form various integers:

%%sql
CREATE TABLE ints as 
    SELECT "zero" AS word, 0 AS one, 0 AS two, 0 AS four, 0 AS eight UNION
    SELECT "one", 1, 0, 0, 0 UNION
    SELECT "two", 0, 2, 0, 0 UNION
    SELECT "three", 1, 2, 0, 0 UNION
    SELECT "four", 0, 0, 4, 0 UNION
    SELECT "five", 1, 0, 4, 0 UNION
    SELECT "six", 0, 2, 4, 0 UNION
    SELECT "seven", 1, 2, 4, 0 UNION
    SELECT "eight", 0, 0, 0, 8 UNION
    SELECT "nine", 1, 0, 0, 8;

 * sqlite:///cs61a.db
Done.





[]

• Part A: Wrtie a select statement for a two-column table of the word and value for each integer.

%%sql

SELECT word, one + two + four + eight AS value FROM ints ORDER BY value;

 * sqlite:///cs61a.db
Done.

word	value
zero	0
one	1
two	2
three	3
four	4
five	5
six	6
seven	7
eight	8
nine	9

• Part B: Write a select statement for the word names of the powers of two

%%sql
SELECT word FROM ints WHERE one + two/2 + four/4 + eight/8 = 1 ORDER BY one+two+four+eight;

 * sqlite:///cs61a.db
Done.

word
one
two
four
eight