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.


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:

west coastBerkeley

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


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: