In this class, we'll be using relational databases, which are the most common type of databases.
A table is a collection of records, which are rows that have a value for each column. Each column has a name and a data type.
The Structured Query Language (SQL) is perhaps the most widely used programming language on Earth.
SQL is a declarative programming language.
In declarative languages such as SQL (and Prolog):
In imperative languages such as Python (and Scheme):
create table cities as select 38 as latitude, 122 as longitude, "Berkeley" as name union select 42, 71, "Cambridge" union select 45, 93, "Minneapolis";
This creates the table from above.
select "west coast" as region, name from cities where longitude >= 115 union select "other", name from cities where longitude < 115;
This creates the table below:
The SQL language is an ANSI and ISO standard, but DMBS's implement custom variants.
selectstatement creates a new table, either from scratch of by projecting a table
create tablestatement gives a global name to a table
In this class, we will use sqlite.
select statement always includes a comma-separated list of column descriptions. A column description is an expression, optionally followed by
as and a column name.
Selecting literals creates a one-row table. The
union of two
select statements is a table containing the rows of both of their results.
select [expression] as [name] [union...];
SQL is often used as an interactive language. The result of a
select statement is displayed to a user but not stored, unless you use a
create table statement to name said table.
create table [name] as [select statement];
select statement can specify an input table using a
A subset of the rows can be selected using a
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 [columns] from [table] where [condition] order by [order];
select expression, column names evaluate to row values which can be combined with each other and with constants using arithmetic.
create table lift as select 101 as chair, 2 as single, 2 as couple union select 102 , 0 , 3 union select 103 , 4 , 1; select chair, single + 2 * couple as total from lift;
The last select statement generates the following table: