Structured English Query Language

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_name as 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_desc not like ‘%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;