SQL - Part 1: Basics

In this lecture, we will learn how to write queries in SQL.
Example database to be used in this lecture is given in SQL here:

See example database to be used.
First a few early remarks about SQL.

Overview

SQL is an industry standard language for relational databases.
Almost all database management systems implement SQL the same, except:
- Core of the SQL standard is the same across all databases
- Advanced features may vary from database to database
- It is highly advisable to write queries that are portable from system to system: no bells or whistles unless it really gets you some strong performance gains.
We will try to distinguish between core and special features as much as possible.

SQL as a database language

SQL is a full language that has many components:
- Query language:
```
SELECT ... FROM ... WHERE ...
```
  allows you to write queries to find what is stored in databases.
- DML: data manipulation language
```
INSERT
UPDATE
DELETE
```
  allows you to change the contents of the existing tables
- DDL: data definition language
```
CREATE DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
```
  allows you to define database objects: schema, tables, indices, etc.
There are many other components to SQL, we will learn each in time.

First, query languages.

General Comments

A logical/declarative query language
Express what you want, not how to get it
Each SQL expression can be translated to multiple equivalent relational algebra expressions
SQL is tuple based, each statement refers to individual tuples in relations
SQL has bag semantics
Recall RDMS implementations of relations as tables do not require tables to always have a key, hence allowing the possibility of duplicate tuples

Same is true for SQL, an SQL expression may return duplicate tuples, unless they are removed explicitly.
SQL is case insensitive (though strings are case sensitive of course)
Syntax:
- All statements must end with a semi-colon!
- Strings are single quoted.

Control Flow

It is best to imagine the control flow of SQL as
1. From: read relations involved in the from
2. Where: check for each tuple if it passes the where clause
3. Select: for tuples that pass the where clause, construct the output by the projection attributes in select
This will become very important for understanding which statements are valid. We will add many more components to this main structure as we learn more about SQL.

Main Syntax: Bag Semantics and Duplicate Removal

Given:

SELECT
   baker
FROM
   bakers
WHERE
   hometown = 'London'
   and age < 30;

This is equivalent to a bag relational algebra query as follows:

$\Pi_{baker}\, (\sigma_{\mbox{hometown='London' and age < 30}}\, bakers)$
Note that this query may return duplicates because there may be multiple bakers from London younger than 30.
- SQL programmers need to be aware of the schema to know whether results can have duplicates or not.

If duplicates are not needed in results, then they can be explicitly removed:

SELECT DISTINCT
   episodeid
FROM
   signatures
WHERE
   baker = 'Dan' or baker = 'Jon';

SQL - SELECT statement

It is a bit confusing at first, but remember: SELECT part of SQL is actually projection in relational algebra.
- SELECT is constructing a single output tuple for each tuple that passes the conditions in the WHERE clause
SELECT is extended projection:
- You can rename attributes returned
- You can use expressions over the attributes
- You can return constants
- Optionally, you can remove duplicates using distinct (only one DISTINCT clause in a single query)
```
SELECT
    left(fullname, strpos(fullname, ' ')) as firstname
    , UPPER(substring(fullname from strpos(fullname, ' ')+1)) as lastname
    , 'baker' as position
    , occupation || ' from: ' || hometown as label
FROM
   bakers ;
```
- position is a new column with a fixed value, constant ‘baker’
- firstname is a substring of a column
- label is a concatenation of two strings
- functions can be combined in complex expressions
Given SQL is a programming language, there are many utility functions that help simplify your type. You can find them here:

http://www.postgresql.org/docs/9.3/interactive/functions.html
Functions used in the SELECT statement operate on single values, not a set/bag of values: A+B, not sum(A).
AS for renaming attributes is not needed in some databases, but it is good to have to be compliant for standards.

SQL - WHERE statement

WHERE statement is equivalent to the selection in relational algebra.
It contains a Boolean expression over individual tuples
For each tuple produced by the FROM statement, we check whether the WHERE statement is true.

If it is true, then we produce a tuple that will be passed to the SELECT statement.
```
SELECT
   *     --produce all attributes
FROM
   episodes
WHERE
   firstaired > date '2018-10-01'
   and viewers7day > 9.0 ;
```

Regular Expressions using LIKE

You can compare a string using regular expressions, but you must use the keyword LIKE
- % stands for 0 or more characters
- _ stands for exactly 1 character

What is the difference in output?

days LIKE '%R%'
days LIKE '_R'
days = 'R'
days = '%R%'

You can tell SQL not to treat a character as part of the regular expression by escaping it.
```
val like '%bc'
```
will match ‘abc’ and ‘a%bc’
```
val like '%\%bc'
```
will only match ‘a%bc’
You can change the escape character with the keyword ESCAPE.
```
like '%x%bc' ESCAPE 'x'
```
This will also only match ‘a%bc’.
Postgresql supports SIMILAR TO as well using more complex and SQL standard regular expressions, though it considers these regular expressions potential security hazards.

Special characters in strings

Strings are delimited by single quote
- Escape single quote by repeating it:
```
SELECT
    'professor''s cat' ;
```
Any special character needs to be escaped. The general escape character is ``.
```
select name || E'\n' || email from students ;
```
Returns values that has a newline in them.

NULL values

WHERE statement implements Boolean logic. However, sometimes attributes may have null values. How should they be interpreted?
NULL is a special value in SQL.
- NULL is not the same as empty string. Any data type can have NULL value.
NULL values are used to represent different things:
- A value for the attribute does not exist (yet):
  
  The grade for a course in progress does not exist.
- The value exists but it is not known.
  
  We may know that a person has a phone, but we do not know the phone number.
- It is not known whether a value exists or not.
  
  A faculty may or may not have an office yet.
Note that storing empty string for a value is asserting that its value is nothing, which is different than saying it has no value! Do not confuse the two.

Boolean Statements with NULL values

Given the special meaning of NULL, any comparison involving a NULL value returns UNKNOWN:
```
NULL = 5   evaluates to UKNOWN
NULL > 5   evaluates to UKNOWN
NULL LIKE '%' evaluates to UKNOWN
```
in this last case, any string would satisfy this condition. But, still when the value is NULL, we will return UNKNOWN.
WHERE statement will only return tuples that evaluate to True. Any tuples with UNKNOWN values are eliminated.

Boolean conditions with UNKNOWN statements need to be evaluated first:

NULL = 5 OR   4>5    EVALUATES TO UNKNOWN
NULL = 5 AND  4>5    EVALUATES TO FALSE

Boolean logic with UNKNOWN VALUES:

C1

C2

C1 OR C2

C1 AND C2

NOT C2

TRUE

UNKNOWN

TRUE

UNKNOWN

UNKNOWN

FALSE

UNKNOWN

UNKNOWN

FALSE

UNKNOWN

UNKNOWN

UNKNOWN

UNKNOWN

UNKNOWN

UNKNOWN

Comparing NULL values

To check a value is NULL or not, no selection criteria will work.

create table abc (val varchar(10)) ;
insert into abc values('cat');
insert into abc values('dog');
insert into abc values(null);

select * from abc ;  -- returns 3 tuples
select * from abc where val like '%'; -- returns 2 tuples
select * from abc where length(val)>=0; -- returns 2 tuples

You need to explicitly search for NULL using the keyword IS NULL or IS NOT NULL.

select * from abc where val is NULL ; -- returns 1 tuple
select * from abc where val is NULL or val like '%'; -- returns all tuples

Complex expressions

SQL has many functions for different data types. Any expression involving these functions are allowed.
Some example functions:
- String operations: ||, upper, lower, position, substring, trim
- Numerical operations: +,-,*,/,%,^,!
- Mathematical operations: abs, ceil, floor, log, mod, round, sqrt
- Utilities: random, now

Date based data types

Data types:
- Date (year, month, day)
- Time of day
- Timestamp (date and time combined)
- Interval (a time duration)

Full support for complex operations on date/time data types

date '2016-01-28' + 2 = date '2016-01-30'   --default assumption of day
date '2016-01-28' + interval '2 day' = timestap '2016-01-30 00:00:00'
date '2016-01-28' + interval '3 hours' = timestamp '2016-01-28 03:00:00'
timestamp '2016-01-28 03:00:00' + interval '10 hours' = timestamp '2016-01-28 13:00:00'
time '12:00:00' + interval '8 hours' = time '20:00:00'
date '2016-05-19' - date '2016-01-28' = 112   -- integer number of days

Postgresql functions allow complex operations over date/time. Be careful, these functions apply to specific data types only but not necessarily do implicit type conversion:

extract(field from timestamp)  --day, month, year, hour,
                               --minute, seconds, dow

select extract(year from now());

date_part
-----------
2016
(1 row)

Convert between data types:

to_char(timestamp, text)
to_date(text, text)

to_date('02 29 2016', 'MM DD YYYY')

You can also check whether two time intervals overlap with each other:

select (date '2016-03-01', date '2016-03-31') overlaps
       (date '2016-02-25', date '2016-03-04');

True

select (date '2016-03-01', date '2016-03-31') overlaps
(date '2016-02-25', date '2016-02-29');

False

Example: Find requirements that have been enforced for at least 1 year:

select * from requires where cast(now() as date) - enforcedsince > 365;

course_id | prereq_id | isenforced | enforcedsince
-----------+-----------+------------+---------------
         5 |         1 | t          | 2011-01-01

FROM Clause

So far we have seen a single table in the FROM clause. What happens with multiple tables?
```
SELECT * FROM bakers, technicals ;
```
This is actually a Cartesian product of two tables. To make this a join, we must include a join condition:
```
SELECT *
FROM
   bakers b
   , technicals t
WHERE
   b.baker = t.baker;
```
The variables b and t are aliases for the table names, especially needed if the two tables have attributes with the same name.
In short, a query of the form:
```
SELECT attributes FROM R1,R2,.., Rn WHERE Conditions
```
is equivalent to the relational algebra operation:

$\Pi_{attributes}\, (\sigma_{Conditions}\, (R1\times R2 \times \ldots \times Rn))$

Get used to reading the above query as follows:

For each tuple in the Cartesian product R1xR2x...xRn
   If it satisfies the conditions in the WHERE clause
      Construct a tuple in the output for attributes in the SELECT clause

WHERE statement contains both join conditions and selection conditions

Example Queries

Return the name and hometown of bakers who came in first in at least two different technical challenges.

SELECT DISTINCT
   b.fullname
   , b.hometown
FROM
   technicals t1
   , technicals t2
   , bakers b
WHERE
   t1.episodeid <> t2.episodeid
   and t1.baker = t2.baker
   and t1.rank = 1
   and t2.rank = 1
   and t1.baker = b.baker;

Return name and hometown of all bakers who used chocolate in their showstopper challenge of an episode and came first in that episode.

SELECT DISTINCT
   b.fullname
   , b.hometown
FROM
   showstoppers ss
   , results r
   , bakers b
WHERE
   ss.baker = r.baker
   and b.baker = r.baker
   and r.result = 'star baker'
   and r.episodeid = ss.episodeid
   and lower(ss.make) like '%chocolate%';

Return the fullname of bakers who used ginger in both a showstopper and a signature challenge.

SELECT DISTINCT
    b.fullname
FROM
    showstoppers ss
    , signatures s
    , bakers b
WHERE
    lower(ss.make) like '%ginger%'
    and lower(s.make) like '%ginger%'
    and s.baker = ss.baker
    and s.baker = b.baker;

Set and Bag Operations

SQL allows for SET and BAG operations:
- SET operations: UNION, INTERSECT, EXCEPT
- BAG operations: UNION ALL, INTERSECT ALL, EXCEPT ALL

The operations are over results of SQL queries:

(SELECT ... FROM ... WHERE ...)
UNION
(SELECT ... FROM ... WHERE ...)

Same as in relational algebra, the queries should be union compatible:
- Same attributes and same names (though most databases will allow same number of attributes with different names as long as the domain of attributes at each location match)

Suppose we have:

Table a1 with id values: 1,2,2,2,3,3 Table a2 with id values: 2,3,3

select * from a1 union select * from a2 ;

returns 1,2,3 -- set operation

select * from a1 intersect select * from a2 ;

returns 2,3

select * from a1 except select * from a2 ;

returns 1

select * from a1 union all select * from a2 ;

returns 1,2,2,2,2,3,3,3,3  -bag union

select * from a1 intersect all select * from a2 ;

returns 2,3,3  -bag intersection

select * from a1 except all select * from a2 ;

returns 1,2,2  -bag difference

Example: Return full name of all bakers who either won star baker or won a technical challenge.

SELECT
    b.fullname
FROM
    bakers b
    , results r
WHERE
    b.baker = r.baker
    and r.result = 'star baker'
UNION
SELECT
   b.fullname
FROM
   bakers b
   , technicals t
WHERE
   b.baker = t.baker
   and t.rank = 1;

Example: Return full name of all bakers who star baker but never won a technical challenge.

SELECT
    b.fullname
FROM
    bakers b
    , results r
WHERE
    b.baker = r.baker
    and r.result = 'star baker'
EXCEPT
SELECT
   b.fullname
FROM
   bakers b
   , technicals t
WHERE
   b.baker = t.baker
   and t.rank = 1;

Find full name of bakers who were never eliminated (and hence were in the top three).

SELECT fullname FROM bakers
EXCEPT
SELECT
   b.fullname
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'eliminated';

Set compatibility is important in SQL as well. We could not do this:

SELECT baker, fullname FROM bakers
EXCEPT
SELECT baker FROM results r WHERE result = 'eliminated';

we get the error:

ERROR:  each EXCEPT query must have the same number of columns
LINE 3:      SELECT baker FROM results r WHERE result = 'eliminated'...

However, we can do this:
```
SELECT baker FROM bakers
EXCEPT
SELECT baker as b1 FROM results r WHERE result = 'eliminated';
```
Why? Even though the attributes are not named the same, they are of the same type and the same number of columns.
Find all bakers who won no technicals or have not won star baker. Return their full name.
- Construct slowly, write the following in SQL:
  - R1: all bakers
  - R2: bakers who won technicals
  - R3: bakers who won star baker
- Now we can compute (R1 EXCEPT R2) UNION (R1 EXCEPT R3)

AGGREGATES

Similar to the aggregates in bag relational algebra, you can find the aggregate for a specific column or combination of columns.
Commonly used aggregates are: min, max, avg, sum, count, stddev.
An aggregate returns a single tuple (unless accompanied by other clauses like GROUP BY or FILTER).

Find total number of times ‘Kim-Joy’ won star baker.
```
SELECT
   count(*) as num_wins
FROM
   results
WHERE
   baker = 'Kim-Joy';
```
Note:
- count(*) counts the total number of tuples.
- count(attribute) counts the total number of values for a given attribute, disregarding the NULL values.
- count(DISTINCT attribute) counts the total number of distinct values for a given attribute, disregarding the NULL values.

GROUP BY

Instead of computing the aggregates for the whole query, it is possible to compute it for a group.
- Group by multiple attributes by finding tuples that have the same values for the grouping attributes
- For each group, produce a single tuple containing grouping attributes and any agregates over the group.
- To return an attribute from a relation, you must include it in the grouping attributes.

Example: Find the total number of star baker wins for each baker. Return the full name and hometown of each baker.

SELECT
   b.baker
   , b.fullname
   , count(*) as numwins
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'star baker'
GROUP BY
   b.baker
   , b.fullname;

Note: we group by name to be able to return it, even though it is unique due to the primary key. This is the safest way.

If your DBMS checks for constraints at compile time, you do not have to include name. Later versions of Postgresql allows this:
SELECT b.baker , b.fullname , count(*) as numwins FROM bakers b , results r WHERE b.baker = r.baker and r.result = 'star baker' GROUP BY b.baker;

GROUP BY - HAVING

Group by statement can be followed by an optional HAVING clause.
You can write conditions to eliminate gruops in the HAVING clause.
What makes sense in the HAVING clause?

Aggregates over the groups.

All other conditions should be put in the WHERE clause to reduce the size of the relation to be grouped.

Find all bakers who have used ‘chocolate’ or ‘ginger’ in the showstopper challenge at least two different episodes and won star baker at least twice. Return their fullname.

SELECT
   b.baker
   , b.fullname
FROM
   bakers b
   , showstoppers ss
   , results r
WHERE
   b.baker = ss.baker
   and b.baker = r.baker
   and r.result = 'star baker'
   and (lower(ss.make) like '%ginger%' or lower(ss.make) like '%chocolate%')
GROUP BY
   b.baker
HAVING
   count(DISTINCT ss.episodeid) >= 2
   and count(DISTINCT r.episodeid) >= 2;

ORDER BY

You can order the tuples returned by the query with respect to one or more attributes.

Return the students, order with respect to year (descending) and name (ascending).

SELECT
    *
FROM
    episodes
ORDER BY
    viewers7day desc
    , id asc;

Return bakers ordered by the number of wins they had.

SELECT
   b.baker
   , count(*) as numwins
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'star baker'
GROUP BY
   b.baker
ORDER BY
   numwins desc;

LIMIT

You can limit the number of tuples returned, by the LIMIT statement, the last possible statement to add.
LIMIT makes the most sense when combined with an order by.

Find the top 3 bakers in terms of number of wins. Return their name.

SELECT
   b.baker
   , b.fullname
   , count(*) as numwins
FROM
   bakers b
   , results r
WHERE
   b.baker = r.baker
   and r.result = 'star baker'
GROUP BY
   b.baker
ORDER BY
   numwins desc;
LIMIT
   3;

FULL SQL SYNTAX

Now that we have seen the full SQL syntax, let’s revisit how a complex statement such as the following is executed.

SELECT A1 AS X FROM B1 WHERE C1 GROUP BY D1 HAVING E1
UNION
SELECT A2 AS X FROM B2 WHERE C2 GROUP BY D2 HAVING E2
UNION
SELECT A3 AS X FROM B3 WHERE C3 GROUP BY D3 HAVING E3
ORDER BY X
LIMIT 10;

1. FROM B1 WHERE C1 GROUP BY D1 HAVING E1 => construct A1
2. FROM B2 WHERE C2 GROUP BY D2 HAVING E2 => construct A2
3. FROM B3 WHERE C3 GROUP BY D3 HAVING E3 => construct A3
4. TAKE UNION/APPLY SET OPERATIONS
   (use parantheses as needed for appropriate ordering)
5. ORDER BY (a single order per query)
6. LIMIT (a single LIMIT query)

The ordering is important. In the above query for top 3 students, we can order by a column named numstudents because ORDER BY comes after SELECT. However, we CANNOT refer to this attribute anywhere before ORDER BY (such as in HAVING).

Common Errors When Writing SQL Queries

Do not forget join conditions. Even if a foreign key constraint exists, you must explicitly write the join condition.

Remember the ordering of execution. The following query is is not correct, why?

  SELECT  baker, count(*) as numwins
  FROM results r WHERE result = 'star baker'
  GROUP BY b.baker HAVING numwins >1 ;

  ERROR:  column "numwins" does not exist
  LINE 3:      GROUP BY b.baker HAVING numwins >1 ;

Hint: remember the order of execution.

Remember that aggregates only make sense after a group by statement. So, only in HAVING and SELECT.

SELECT baker FROM results WHERE result = 'star baker' and count(*)>1
GROUP BY baker;

ERROR:  aggregate functions are not allowed in WHERE
LINE 1: ...aker FROM results WHERE result = 'star baker' and count(*)>1

You cannot return an attribute that is not part of group by.

SELECT make FROM showstoppers GROUP BY baker ;

ERROR:  column "showstoppers.make" must appear in the
GROUP BY clause or be used in an aggregate function
LINE 1: SELECT make FROM showstoppers GROUP BY baker ;

Also think for a second to see that this query makes no sense.

You can do a selection or return an attribute that is part of group by, but be careful:
```
SELECT result, count(*) FROM results
GROUP BY result HAVING result = 'star baker' ;
```
This would not work is semester was not part of the grouping attributes.

While not technically wrong, this is an inefficient query. If you are going to do a selection on semester, you should do it in the WHERE clause. You will reduce the size of the query that needs to be processed with the remaining statements.

Here is the better version of the same query:
```
SELECT result, count(*) FROM results
WHERE result = 'star baker'
GROUP BY result;
```