Declarative Programming - Fri, Nov 15

Database Management Systems

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.

LatitudeLongitudeName
38122Berkeley
4271Cambridge
4593Minneapolis

The Structured Query Language (SQL) is perhaps the most widely used programming language on Earth.

SQL is a declarative programming language.

Declarative Programming

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:

regionname
west coastBerkeley
otherMinneapolis
otherCambridge

Structured Query Language (SQL)

The SQL language is an ANSI and ISO standard, but DMBS's implement custom variants.

In this class, we will use sqlite.

Selecting Value Literals

A 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...];

Naming Tables

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];

Projecting Tables

A select statement can specify an input table using a from clause.

A subset of the rows 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 [columns] from [table] where [condition] order by [order];

Arithmetic

In a 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:

chairtotal
1016
1026
1036