Notes

SQL Sub Languages SQL consists of many types of statements, that can be grouped into 4 sublanguages:

• Data Definition Language (DDL), used to create tables and other objects (views, procedures, etc.)
• Data Manipulation Language (DML), used to insert, update and delete data.
• Data Query Language (DQL), used to retrieve data from the database.
• Data Control Language (DCL), used to grant access to the database objects. It's used to manage users' permissions on the db objects.

Data Definition Language (DDL)

• CREATE TABLE, creates a table.
• DROP TABLE, deletes a table from the database. It deletes both the data and the table definition.

Data Manipulation Language (DML)

• INSERT, insert a record into a table.
• INSERT (Select), insert more records into a table.
• DELETE, deletes one or more records from a table. DELETE supports the WHERE clause (like the SELECT statement).
• UPDATE, changes one or more values into one or more records. UPDATE supports the WHERE clause (like the SELECT statement).

Data Query Language (DQL) - The Basics

• SELECT, the select statement retrieves data from one or more tables.
• Use SELECT with column list to show columns.
• Use FROM to specify the source table or view
  • Specify both schema and object names.
• End all statements with a semicolon.
• Displaying only specified columns.
• Displaying all columns.

SELECT: Calculations & Aliases Calculations are scalar, returning one value per row.

Using scalar expressions in the SELECT clause:

SELECT: Functions In the select list (but also in the WHERE clause) we can use built-in functions:

• Date Functions (not supported in SQL LITE):
  • YEAR
  • MONTH
  • DAY
  • They return the year, the month or the day of a date.
• String Functions
  • TRIM, removes spaces at the beginning and the end of a string.
  • UPPER, LOWER, put a string in upper case or lower case.
• Math Functions
  • ABS, absolute value
  • ROUND, rounds a value to a specified precision.

SELECT DISTINCT & ORDER BY

SQL query results are not truly relational:

• Rows are not guaranteed to be unique
• DISTINCT returns unique values.
• No guaranteed order.
• ORDER BY forces and ORDER (use ASC for ascending or DESC for descending).

SELECT & WHERE The WHERE clause limits the rows returned by a SELECT query. The WHERE keyword is followed by a logical predicate. In the predicate we can use:

• Comparison operators like: =, >, <, >=, <=, LIKE.
• Logical operators like: AND, OR, NOT.
• Parenthesis ().

SELECT and CASE The CASE statement has the functionality of an IF-THEN-ELSE statement.

UNION and UNION ALL The SQL UNION operator is used to combine the result sets of 2 or more SELECT statements. It removes duplicate rows between the various SELECT statements. The UNION ALL operator behaves like UNION but it doesn't remove the duplicates.

Data Query Language (DQL) - Understanding Joins Definition: Cartesian product Characteristics of a Cartesian product

• Output or intermediate result of FROM clause.
• Combine all possible combinations of two sets.
• In SQL queries, usually not desired.

JOINS A JOIN operation puts together the columns of two tables, by matching the rows based on a predicate.

CROSS JOIN

INNER JOIN

LEFT OUTER JOIN

OUTER JOINS

• Return all rows from first table, only matches from second:

• Return all rows from second table, only matches from first:

• Return all matching rows + rows from first table with no match in second + rows from second table with no match in the first:

Handling NULLs

• SQL uses NULLs to mark missing values. With no missing values, predicate outputs are TRUE or FALSE only (5 > 2, 1 = 1). With missing values, outputs can be TRUE, FALSE or UNKNOWN (NULL > 99, NULL = NULL).
• Predicates return UNKNOWN when comparing missing value to another value, including another missing value.
• Query filters (ON, WHERE, HAVING) filter out UNKONWNs
• Testing for NULL, use IS NULL or IS NOT NULL rather than = NULL or <> NULL.
• Replacing NULLS, use the COALESCE function.

Grouping and Summarizing Data Aggregate Functions

• Return a scalar value (with no column name)
• Ignore NULLs except in COUNT(*)
• Can be used in: SELECT, HAVING, and ORDER BY clauses
• Frequently used with GROUP BY clause.

Aggregate Functions

• SUM
• MIN
• MAX
• AVG
• COUNT
• STDEV
• VAR
• VARP

Group By GROUP BY creates groups for output rows, according to a unique combination of values specified in the GROUP BY clause. The WHERE clause filters the rows before the aggregation is applied.\ We use GROUP BY + an aggregate function every time we want to compute an aggregation for each of the groups. Detail rows are "lost" after the GROUP BY clause is processed.

How GROUP BY works

Filtering Aggregates HAVING clause provides a search condition that each group must satisfy. HAVING clause is processed after GROUP BY.

COUNT DISTINCT How many different customers bought placed an order?

SubQueries Subqueries are nested queries: queries within queries. Results of inner query passed to outer query.

• Inner query acts like an expression from perspective of outer query. Subqueries can be self-contained or correlated.
• Self-contained subqueries have no dependency on outer query.
• Correlated subqueries depend on values from outer query. Subqueries can be scalar, multi-valued, or table-valued.

EXISTS and NOT EXISTS When a subquery is used with the keyword EXISTS, it works only as an existence test.

• No rows passed back to outer query.

EXISTS evaluates to TRUE or FALSE

• If any rows are returned by the subquery, EXISTS returns TRUE.
• If no rows are returned, EXISTS returns FALSE.

Syntax:

Customer with at least 1 order:

Customer with No orders:

Analytical Functions Set of SQL functions for analytical tasks. Analytical functions make many SQL tasks easy. Some functions:

• ROW_NUMBER()
• LAG/LEAD
• COUNT/MIN/MAX/SUM/AVG with the OVER clause. There's no loss of details like we have with the GROUP BY operation.

ROW_NUMBER ROW_NUMBER provides a simple way to get row numbering in a result sets. The row numbers can then be used for many purposes. Syntax: ROW_NUMBER() OVER(PARTITION BY col1, col2, ... ORDER BY col1, col2, ...)

Example: find the largest order for each customer.

The OVER clause The OVER clause consists of 2 parts:

• PARTITION BY, which defines the partitioning criterion.
• ORDER BY which specifies the order we want to apply to the records inside each partition.

LAG & LEAD LAG returns a value taken from n rows before the current row. (n is a parameter). If no value is found, the function returns the specified default value. Syntax: LAG(column_name, n, default_value) OVER(PARTITION BY partition_by_cols ORDER BY order_by_cols). LEAD returns a value taken from n rows past the current row. (n is a parameter). If no value is found, the function returns the specified default value. Syntax: LEAD(column_name, n, default_value) OVER(PARTITION BY partition_by_cols ORDER BY order_by_cols).

LAG Example

Aggregation Function with the OVER Clause The OVER clause allows computing the aggregations for a partition (or for all the rows in the result set). With the OVER clause the aggregations are returned along with each single row.

Windowing In addition to PARTITION BY and ORDER BY the OVER clause allows us to specify a window of rows to which the aggregation function is applied. Keywords:

• UNBOUNDED PRECEDING, it means that the window starts from the first row (inside a partition and using the order by criteria).
• N PRECEDING (where N is a number), it means that the window starts from n rows before the current row.
• CURRENT ROW, it specifies the current row as start or end of a window.
• UMBONDED FOLLOWING / N FOLLOWING, same as above, but it refers to the following rows.

Windowing Examples