Introduction

Last week, we began talking about algebraic and logical query languages.
We showed how to extend the relational algebra operations from earlier in the semester to work on bags (multisets or sets with repeats) rather than sets.
We said the relational algebra is often used as an intermediate language between SQL and the final computation of the results of a query.
It was important to extend this algebra to bags, as bags are often more natural in terms of computing results of a query.
We extended the relation algebra, with new and modified operators: `delta` (distinct), `gamma` (grouping and aggregation), `tau` (sorting), extended projection (`pi` with renaming and arithmetic and string expressions), and various outer joins.
We then started looking at a second query language, datalog, this time based on logic, which motivated some of the design choices in SQL.
We said the building blocks of datalog were atoms (either relational like `P(x_1,...,x_k)` or arithmetical like `X>10`), inputs to atoms (either variable `x,y,z...`, or constants such as 7, 'sally', etc), and rules of the form head atom `\leftarrow`, list of goal atoms or negations of atoms, for example,
```
LongMovie(t,y) <- Movies(t,y,l,g,s,p), l ≥ 100 .
```
We interpret this as if all the goals on the right are satisfied then the left hand goal will be too.
We said that rules without right hand sides are interpreted as facts: `R(1,2) \leftarrow .`
`R(3,4) \leftarrow .`
could be used to represent the table:
```
A|B
---
1|2
3|4
```
as it asserts that (1,2) is in R and and (3,4) is in R. In datalog terminology it asserts that R(1,2) is true.
Datalog uses the close world assumption, if something cannot be derived from the rules in our datalog DB then it is assumed false.
What distinguishes Datalog from the programming language Prolog or logic programming, is that: (1) the predicate in the head atom is not allowed to appear on the right hand side of the same rule, and (2) each variable on right hand side of a rule must appear in at least one non-negated relation atom (safely).
Today, we start by finishing up our discussion of datalog before looking at SQL as a query language

Extensional and Intensional Predicates

We often make a distinction between kinds of predicates.
Extensional predicates are predicates whose relation is stored in a database (can be thought of a collection of fact rules).
Intensional predicates are predicates whose relations are computed by applying one or more Datalog rules which are not facts.
The collection of all intensional predicates in a database we call the intensional database (IDB).
The collection of all extensional predicates in a database we call the extensional database (EDB).

Datalog Applied to Bags

So far we have been imagining our Datalog rules as applying to sets.
Just as with the relational algebra, we can imagine them applying to bags.
For example, consider the rule:
`H(x,z) <- R(x,y), S(y,z)` where relation `R(A,B)` is given by:
```
A|B
---
1|2
1|2
```
and `S(B,C)` is given by
```
B|C
---
2|3
4|5
4|5
```
then we can compute `H` as
```
1|3
1|3
```
by treating the two rows of `R` as distinct when trying to satisfy the clause.

Relational Algebra and Datalog

Each of the relation algebra operations can be implemented using Datalog.
The book shows how to do this for each operation, we only show a few.
The set operation `T := R(vec{a}) cup S(vec(a))` could be implemented via two rules: `T(vec{a}) <- R(vec{a})`. `T(vec{a}) <- S(vec{a})`.
The set operation `T := R(vec{a}) cap S(vec(a))` could be implemented via the rule: `T(vec{a}) <- R(vec{a}), S(vec{a})`.
The set operation `T := R(vec{a}) - S(vec(a))` could be implemented via the rule: `T(vec{a}) <- R(vec{a}), NOT \ S(vec{a})`.
Projection can be implement via leaving out variable `pi_{vec{a}}(R(vec{a}, vec{b}))` then could be expressed as:
`T(vec{a}) <- R(vec{a}, vec{b})`
Selection can be implemented using arithmetic atoms, join and products via conjunctions. For example, `sigma_{x>10}(R(x))` could be implemented as:
`T(x) <- R(x), x> 10`
`R(a) times S(b)` could be implemented as:
`T(a,b) <- R(a), S(b)`.

Quiz

Which of the following is true?

Entity sets in the E/R model most closely map to structs in the ODL model.
A projection applied to a set of unique rows in the bag model is the same as that projection applied to the same rows in the set model.
Our extended relational algebra supported sorting tuples of a relation by some attribute.

SQL as a Query Language

Most commonly used relational DBMS query and modify the database through a language called SQL ("Structured Query Language").
We have already seen some DDL (data definition language - create, insert, update, delete) aspects of SQL.
The portion of SQL that supports queries, the DML (data manipulation language), has capabilities very close to the relational algebra we have been studying.
It first became an ANSI (American National Standards Institute) standard in 1986.
The most recent version of the standard is SQL2023.
For the rest of today, we are going to learn about the DML portion of SQL in detail.

Simple Queries in SQL

Recall in SQL terminology a relation is called a table, and an attribute is called a column.
Almost all SQL queries make use of the three keywords SELECT, FROM, and WHERE:
```
SELECT list_of_columns
FROM list_of_tables
WHERE some_condition;
```

For example, if we have the relation:

Movies(title, year, length, genre, studioName, producerC#)

then to return all movies produced by Disney Studios in 1990, we could write:

SELECT *
FROM MOVIES
WHERE studioName = 'Disney' AND year = 1990;

In the above, * says return all columns that appear in tables/relations of the FROM clause, in this case, MOVIES.
The WHERE clause has the effect of a relational algebra select. I.e., in the relational algebra we could have written the above SQL as `sigma_{mbox(studioName = 'Disney' AND year = 1990)}(MOVIES)`.
SQL is case insensitive, so fRoM could have been used rather than the keyword FROM. Similarly, tables names etc are case insensitive.
Typically, SQL keywords are written in ALL-CAPS.
Content of quoted strings (usually single quotes), however, is case sensitive. So 'MOVIES' is different than 'Movies'.

Projections and More on Selection

SQL supports both standard and extended relational algebra projections.
For example, rather than use the * of the last slide, if we wanted just the title and length of movies produced by Disney Studios in 1990, we could have written:
```
SELECT title, length
FROM MOVIES
WHERE studioName = 'Disney' AND year = 1990;
```
To do extended projection operation, we can use the AS keyword to rename the output columns or have an output column which is computed as some expression. For example,
```
SELECT title AS name, length*0.016667 AS lengthInHours
FROM MOVIES
WHERE studioName = 'Disney' AND year = 1990;
```
Besides using "="'s to compare items in the WHERE clauses, we can compare things using `<>` (meaning not equal), `<`, `>`, <=, >=.
We can also make use of expressions such as:
```
(year - 1930) * (year - 1930) < 100
```
WHERE clauses can also be built up using the AND, OR, NOT boolean connectives, as well as the boolean values TRUE and FALSE. For example,
```
SELECT title
FROM Movies
WHERE (year > 1970 OR length < 90) AND studioName = 'MGM';
```

Strings and Pattern Matching in SQL

SQL strings may arise from attributes in tables defined using either the CHAR or VARCHAR.
Or they may arise from string literals: For example, 'hi there'.
Or from expressions built out of these using the concatenation operator ||. For example, 'Mr.'||fname||lname . Note this works in Sqlite, for Mysql there is a CONCAT function.
When comparing strings s1 and s2, SQL views them as a sequence of characters ignoring any padding characters that might arise from using a CHAR of some fixed length.
So s1 = s2 if the strings s1 and s2 are the same sequence of characters up to padding.
Comparisons such as `<`, `>`, <= and >= are done according to lex order (dictionary order). So 'fodder' < 'foo' and 'bar' < 'bargain'.
SQL also supports pattern matching using the LIKE and NOT LIKE operators and the two special characters % and _.
Here % matches any strings of 0 or more characters, and _ matches a single character.

For example,

SELECT title
FROM Movies
WHERE title LIKE 'Star ____';

whould also title to match 'Star Wars'.

If we wanted to match titles that contained an apostrophe s, we could do (notice we escape ' by repeating twice):
```
SELECT title
FROM Movies
WHERE title LIKE '%''s%';
```

Dates, Times

Recall we could define a date or time type in a table column using the keywords DATE and TIME.
Constants of these types can be given using the using quoted strings preceded by either the DATE or TIME keyword.
For example, DATE '1948-05-14', TIME '15:00:02', TIME '15:00:02.5', etc.
SQL also supports a TIMESTAMP type which combines DATE's and times. An example, timestamp literal might be: TIMESTAMP '1948-05-14 15:00:02'.

NULL, UNKNOWN in SQL

SQL allows attributes to have a special value NULL.
This value can be interpreted in different ways depending on the circumstance:
1. The value exists but is currently unknown.
2. The value is not applicable. For example, an unmarried person, might have a NULL value for spouseName.
3. The value is witheld. You don't have access to the actual value. For example, Boss' phone number.
If x happens to have the value NULL, then x+3 or other expressions built from x will also be NULL. Note the expression NULL + 3 and the comparison NULL = 3 are not legal SQL.
You can use the boolean expression, x IS NULL, to check if x is currently NULL.
When NULL's are compared rather than getting either TRUE or FALSE, in SQL, one can end up with the value UNKNOWN. Boolean combinations of AND, OR, NOT of UNKNOWN are given a standard semantics. For example, x AND y where y is UNKNOWN is UNKNOWN. Similarly, x OR y where x is TRUE and y might be UNKNOWN is TRUE.

Ordering Output in SQL

We can order the output of our queries by adding an ORDER BY clause:

SELECT *
FROM MOVIES
WHERE studioName = 'Disney' AND year = 1990
ORDER BY length, title;

The output above would be sorted by movie length where ties are broken by movie title in increasing alphabetical order.
The list of sort attributes in the ORDER BY clause can consist of either table attributes or expressions built out of table attributes.
We can also add the keyword ASC and DESC to say whether the sort direction is either ascending (default) or descending.

Finish Datalog - SQL as a Query Language

Outline