What is SQL - Part 4 - TheLazy.Dev

Completing the basics

This is part 4 of a four-part article which explains SQL

Part 1
Part 2
Part 3
Part 4

Content:

NULL values
CASE WHEN
Views
Aggregate functions at work
Final words
Glossary

NULL values

NULL values are special and treated differently. Look at these comparisons:

comparison	result
`NULL` = `''`	`NULL`
`NULL` = `0`	`NULL`
`NULL` <> `''`	`NULL`
`NULL` <> `0`	`NULL`
`NULL` = `NULL`	`NULL`
`NULL` <> `NULL`	`NULL`
`NULL` IS `NULL`	`true`
`NULL` IS NOT `NULL`	`false`

If a column admits NULL values, or the result of a LEFT JOIN or RIGHT JOIN query (like missing rows in a related table) produce NULL values, those then need to be treated and compared using the IS or IS NOT clauses; as all other comparisons return NULL as shown in the chart above

CASE WHEN

In standard SQL the CASE clause helps to create results using a simple syntax
Here's an example

SELECT 
    id,
    name,
    CASE WHEN name LIKE '%Sport%'
      THEN 'Sports'
      ELSE 'Formal'
    END AS type
FROM products
ORDER BY id;

This query will produce the following results, using the DB schema defined so far:

id	name	type
1	Sport shoes A	Sports
2	Sport watch B	Sports
3	Suit C	Formal

Several WHEN ... THEN ... parts can be concatenated using the same CASE clause
After those, the ELSE part goes; if there is no ELSE and none of the WHEN parts match, the result for the column is a NULL
The END part must be the last one of the clause

Views

A View could be created using the previous SELECT
Here is the view named products_type:

CREATE OR REPLACE VIEW products_type AS
SELECT 
    id,
    name,
    CASE WHEN name LIKE '%Sport%'
      THEN 'Sports'
      ELSE 'Formal'
    END AS type
FROM products
ORDER BY id;

The syntax CREATE OR REPLACE is not supported by all DBMS, so consider using instead CREATE and if in need to update it, delete it first with DROP VIEW
This View could be used almost like a table. Consider:
- It adds a calculated column, type
- It already provides an ORDER BY

SELECT *
FROM products_type
WHERE type = 'Sports';

id	name	type
1	Sport shoes A	Sports
2	Sport watch B	Sports

Also, a different ORDER BY could be used
Some DBMS allow views to INSERT, UPDATE or DELETE the underlying table(s), if at all possible, given the query

Aggregate functions at work

How to obtain the total quantity sold, per month, per product, for year 2020, in just one query?
Here is the result of the query

id	name	jan	feb	mar	apr	may	jun	nov	dec
1	Sport shoes A	5	0	1	0	0	2	0	0
2	Sport watch B	1	9	0	0	0	0	0	3
3	Suit C	0	1	0	1	2	0	2	0

The syntax vary a little among DBMS engines, due to differences in the treatment of DATE types

PostgreSQL

SELECT
    s.id_product AS id,
    p.name,
    SUM(CASE WHEN date_part('month', s.date) = '01' THEN s.quantity ELSE 0 END) AS jan,
    SUM(CASE WHEN date_part('month', s.date) = '02' THEN s.quantity ELSE 0 END) AS feb,
    SUM(CASE WHEN date_part('month', s.date) = '03' THEN s.quantity ELSE 0 END) AS mar,
    SUM(CASE WHEN date_part('month', s.date) = '04' THEN s.quantity ELSE 0 END) AS apr,
    SUM(CASE WHEN date_part('month', s.date) = '05' THEN s.quantity ELSE 0 END) AS may,
    SUM(CASE WHEN date_part('month', s.date) = '06' THEN s.quantity ELSE 0 END) AS jun,
    SUM(CASE WHEN date_part('month', s.date) = '07' THEN s.quantity ELSE 0 END) AS jul,
    SUM(CASE WHEN date_part('month', s.date) = '08' THEN s.quantity ELSE 0 END) AS aug,
    SUM(CASE WHEN date_part('month', s.date) = '09' THEN s.quantity ELSE 0 END) AS sep,
    SUM(CASE WHEN date_part('month', s.date) = '10' THEN s.quantity ELSE 0 END) AS oct,
    SUM(CASE WHEN date_part('month', s.date) = '11' THEN s.quantity ELSE 0 END) AS nov,
    SUM(CASE WHEN date_part('month', s.date) = '12' THEN s.quantity ELSE 0 END) AS dec
FROM sales s
    JOIN products p ON s.id_product = p.id
WHERE date_part('year', s.date) = '2020'
GROUP BY s.id_product, p.name
ORDER BY id_product;

MySQL / MariaDB

SELECT
    s.id_product AS id,
    p.name,
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '01' THEN s.quantity ELSE 0 END) AS "jan",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '02' THEN s.quantity ELSE 0 END) AS "feb",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '03' THEN s.quantity ELSE 0 END) AS "mar",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '04' THEN s.quantity ELSE 0 END) AS "apr",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '05' THEN s.quantity ELSE 0 END) AS "may",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '06' THEN s.quantity ELSE 0 END) AS "jun",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '07' THEN s.quantity ELSE 0 END) AS "jul",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '08' THEN s.quantity ELSE 0 END) AS "aug",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '09' THEN s.quantity ELSE 0 END) AS "sep",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '10' THEN s.quantity ELSE 0 END) AS "oct",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '11' THEN s.quantity ELSE 0 END) AS "nov",
    SUM(CASE WHEN EXTRACT(MONTH FROM s.date) = '12' THEN s.quantity ELSE 0 END) AS "dec"
FROM sales s
    JOIN products p ON s.id_product = p.id
WHERE EXTRACT(YEAR FROM s.date) = '2020'
GROUP BY s.id_product, p.name
ORDER BY s.id_product;

SQLite

SELECT
    s.id_product AS id,
    p.name,
    SUM(CASE WHEN strftime('%m', s.date) = '01' THEN s.quantity ELSE 0 END) AS jan,
    SUM(CASE WHEN strftime('%m', s.date) = '02' THEN s.quantity ELSE 0 END) AS feb,
    SUM(CASE WHEN strftime('%m', s.date) = '03' THEN s.quantity ELSE 0 END) AS mar,
    SUM(CASE WHEN strftime('%m', s.date) = '04' THEN s.quantity ELSE 0 END) AS apr,
    SUM(CASE WHEN strftime('%m', s.date) = '05' THEN s.quantity ELSE 0 END) AS may,
    SUM(CASE WHEN strftime('%m', s.date) = '06' THEN s.quantity ELSE 0 END) AS jun,
    SUM(CASE WHEN strftime('%m', s.date) = '07' THEN s.quantity ELSE 0 END) AS jul,
    SUM(CASE WHEN strftime('%m', s.date) = '08' THEN s.quantity ELSE 0 END) AS aug,
    SUM(CASE WHEN strftime('%m', s.date) = '09' THEN s.quantity ELSE 0 END) AS sep,
    SUM(CASE WHEN strftime('%m', s.date) = '10' THEN s.quantity ELSE 0 END) AS oct,
    SUM(CASE WHEN strftime('%m', s.date) = '11' THEN s.quantity ELSE 0 END) AS nov,
    SUM(CASE WHEN strftime('%m', s.date) = '12' THEN s.quantity ELSE 0 END) AS dec
FROM sales s
    JOIN products p ON s.id_product = p.id
WHERE strftime('%Y', date) = '2020'
GROUP BY id_product
ORDER BY id_product;

In this example, two features of DML are combined together:
- GROUP BY, to summarize row values
- CASE WHEN to obtain calculated values for each month of the year, replacing the quantity with a zero when the month in the date doesn't match the column it's calculated for

Final words

It's my hope that this brief introduction to SQL has piqued your interest.

It's a very powerful Domain Specific Language.

Having a basic notion of the SQL fundamentals, in my view, is essential for improving our use of it.

Glossary

Term	a.k.a.	What it is
Check Constraint	Check	A Constraint where the values of one or more columns are limited to a specific set
Constraint		Set of restrictions in a DB which make the DB consistent; there are several types of constraints, such as: Primary Key, Foreign Keys, Unique Keys, `NULL`, Check Constraints
Data Base	DB	A combination of tables, rules, constraints, triggers and stored procedures which is managed by a DBMS
Data Base Management System	DBMS	The software that administer Data Bases; in this context, an SQL DB
Data Definition Language	DDL	Defines the schema in a DB
Data Manipulation Language	DML	Operates on tables in a DB
Foreign Key	FK	A combination of one or more columns in a table, which point to the PK in a table
Primary Key	PK	It's a unique composition of values in a row, which make the row unique; serves as an identification, and no `NULL` columns are allowed
Relational Data Base Management	RDBMS	a DBMS which follows the Relational math principles
Schema		The complete definition of a set of tables, constraints and other objects in a DB
Table	Relation	A matrix formed of tuples, each of them consists of the same type of values (each positioned element); if the table has a PK, then it might be considered a Set, from Set Theory, in the sense that each element of the set is a tuple, and there is no more than one tuple contained in the set with the same values
Trigger		Code that the DBMS executes whenever changes occur on the DB; this code is configured within the DB, and it could be written in SQL or a procedural language within the DBMS; this usually implies difference between DBMS vendors or products
Unique Key	UK	Similar to PK, a unique combination of columns in each row, except that `NULL` values are accepted
View		A projection of one or more tables which produce a table-like result from a `SELECT` statement; there are different types of views: Read Only, Writable and Materialized