Declarative Programming

Programming Paradigms

- Up until now, we’ve been focused (primarily) on imperative programming. 

- Imperative program contain explicit instructions to tell the computer how to accomplish something. The interpreter then executes those instructions

- Now, we’ll learn about declarative programming, where we can just tell the computer what we want, instead of how we want it done. The interpreter then figures out how to accomplish that.

- Declarative programs are often specialized to perform a specific task, because they allow for repetitive computation to be abstracted away and for the interpreter to optimize its execution

Imperative vs. Declarative

Suppose you’re going to a restaurant for dinner and need a table

Option 1:

“ First I need to find a table, so I’ll look through every available table and pick the one in the best location that has enough seats for my group, then I’ll need to find someone to wait on me and make sure I have enough menus, then… ”

Option 2:

“ Table for two! ”

SQL

SQL & Database Languages

-   SQL is an example of a (declarative) language with interacts with a database management system (DBMS) in order to make data processing easier and faster

-   It collects records into tables, or a collection of rows with a value for each column

Tables in SQL

SQL Basics

: The SQL language varies across implementations but we will look at some shared concepts.

- A SELECT statement creates a new table, either from scratch or by taking information from an existing table

- A CREATE TABLE statement gives a global name to a table.

- Lots of other statements exist: DELETE, INSERT, UPDATE etc …

- Most of the important action is in the SELECT statement.

Using SQL

https://sqlite.org/download.html

SQLite Download Page

http://sql.cs61a.org

61A SQL

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. SELECT [expression] AS [name], [expression] AS [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.

Naming Tables

- SQL is often used as an interactive language.

- The result of a SELECT statement is displayed to the user, but not stored.

- A CREATE TABLE statement gives the result a name. CREATE TABLE [name] AS [SELECT statements];

Selecting From Tables

SELECT Statements Project Existing Tables

- 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.

- Can declare the order of the remaining rows using an ORDER BY clause. Otherwise, no order

- Column descriptions determine how each input row is projected to a result row:

- SELECT [columns] FROM [table] WHERE [condition] ORDER BY [order] [ASC/DESC] LIMIT [number];

Arithmetic in SELECT Statements

: In a SELECT expression, column names evaluate to row values. Arithmetic expressions can combine row values and constants

Joining Tables

1. Two tables A & B are joined by a comma to yield all combinations of a row from A & a row from B.

2. Selects all combinations of rows from both tables. We only want the rows for curly haired dogs.

3. This filters the 56 rows to now only have rows where the fur is curly. But this has rows that have nothing to do with each other. We only care about rows where the two dogs match. 

4. The condition on which the tables are joined on is called the join condition.

Joining A Table With Itself

: Two tables may share a column name; dot expressions and aliases disambiguate column values.

SELECT [columns] FROM [table] WHERE [condition] ORDER BY [order];

: [table] is a comma-separated list of table names with optional aliases.

Aliasing

1. SELECT * FROM parents, parents;

: This doesn’t work because the tables share a column. Let’s fix that!

2. SELECT * FROM parents AS a, parents AS b;

: This works because now SQL can tell the columns in the two tables apart. Let’s now only keep rows where the children share a parent.

3. SELECT * FROM parents AS a, parents AS b WHERE a.parent = b.parent;

: We need to get rid of duplicates because pairs of siblings appear twice. We can do this by enforcing an arbitrary ordering, a.child < b.child alphabetically. Then we get the two columns we want.

4. SELECT a.child AS first, b.child AS second FROM parents AS a, parents AS b WHERE a.parent = b.parent AND a.child < b.child;

String Expressions

: String values can be combined to form longer strings.