SEQUEL for short — SQL for really short.

Remember we had Relational Algebra:

$\Pi_{\textrm{salpername}} (\sigma_{(\textrm{manager}=44)} (\textrm{Salesperson}))$

And we had relational calculus:

$\{t | \exists x(s\in\textrm{Salesperson}\wedge s.\textrm{manager}=44\wedge t.\textrm{name}=s.\textrm{salpername}\}$

These are the foundational constructs for query expression.

In terms of computational power: Codd proved that Relational Algebra is as powerful as Relational Calculus is as powerful as SQL.

There are many flavors of SQL (each database implements various parts of the various standards)

- MySQL (ANSI-SQL/PSM (persistent storage model))
- DB2 (SQL-PL (procedural language))
- Microsoft (T-SQL (transact))
- Oracle (PL/SQL)
- PostgresQL (PL/pgSQL (based on PL/SQL))
- Teradata (SPL (stored procedural language))

etc

It is a very high level language

Syntax:

SELECT attributes $\Pi$

FROM relations $R$

WHERE condition; $\sigma$

What salespersons have manager with ID 44?

SELECT salpersname

FROM Salesperson

WHERE Salesperson.manager_id = 44 ;

This returns a relation, just like Relational Calculus and Relational Algebra.

Find Names of Customers who live in Tokyo.

SELECT cust_name

FROM Customers

WHERE Customers.city = ‘Tokyo’ ;

What’s the result?

CUST_NAME

-----------

Watabe Bros

It’s a declarative language close to Relational Calculus. We declare what we want from the database

We can relax the language a little bit:

SELECT *

FROM Customers

WHERE city = ‘Tokyo’ ;

The * returns all attributes, and if there is no ambiguity then we can remove Customer. from the where clause.

**SELECT does not eliminate duplicates like Projection in relational algebra.**

#### Renaming

Just as in relational calculus you can name your result attributes whatever you like, you can do the same in SQL:

SELECT cust_nameas name

FROM Customers

WHERE Customers.city = ‘Tokyo’ ;

You can also rename relations

SELECT cust_name

FROM Customers C

WHERE C.city = ‘Tokyo’ ;

No ‘as’ here.

## Expressions

SQL allows simple math expressions.

SELECT prod_id, prod_desc, price*1.07 as aftertax

FROM Product;

#### Where Clause

This is where the magic happens in SQL.

Attribute names come from the relations in the FROM clause

- Can do comparison operations
- Arithmetic
- String operations
- Pattern matching
- Lots of functions
- Atoms are separated by logic operators (just like relational calculus)
- Case insensitive (except certain pattern matching operations)
- String literals go in single quotes
- Single quote is the delimiter inside a string.

#### Patterns

where prod_desc like ‘%lamp‘

% is wildcard character that matches multiple characters

– is wildcard character that matches only one character.

where prod_descnotlike ‘%lamp‘

not is a negation

In total:

Give me after tax price and description of non-lamps.

SELECT prod_id, prod_desc, price*1.07 as aftertax

FROM Product

WHERE prod_desc not like ‘%lamp’

Find products with a margin of more than 60%

SELECT P.prod_desc FROM product P WHERE (P.price) > (P.cost*1.6);

margin is “sales price less the [costs], divided by the sales price”.

entrepreneur.com

#### Null Values

What happens if:

SELECT salpers_name

FROM Salesperson

WHERE manager_id > 20 or manager_id <= 20

Does Terry Cardon appear in the result set? Yes or no?

Answer: **No**

Truth logic in SQL is three-valued:

True, False, Unknown

We only return tuples when True.

Testing for null:

or manager_id is null or manager_id is not null

#### Multi-relation Queries

“Joins”

SELECT Product.prod_desc, Manufacturer.manufactr_name

FROM Product, Manufacturer

WHERE Product.manufactr_id = Manufacturer.manufactr_id;

This is a theta join, and an equi-join. (MySQL doesn’t really have natural join, but some DBMSs do)

Find customer-names who bought lamps:

SELECT Customer.cust_name

FROM Customer, Sale, Product

WHERE Customer.cust_id = Sale.cust_id and Sale.prod_id = Product.prod_id and product.prod_desc like '%lamp';

#### Semantics of SQL queries

SELECT $a_1,a_2,\ldots,a_k$

FROM $R_1,R_2,\ldots,R_3$

WHERE $\sigma_*$

**Nested Loop:**

Answer = {} for x∈R_1 do for y∈R_2 do for z∈R_n do if σ_* then answer = answer ∪{a_1,a_2,…,a_k}

Compute the gross revenue for each sale.

SELECT P.prod_desc, P.price*S.qty AS revenue

FROM sale S, product P

WHERE S.prod_id = P.prod_id;