What is SQL - Part 4
Tags: sql, beginners
Published: 2021-04-20 23:41:12

---
title: What is SQL - Part 4
published: true
description: Completing the basics
tags: sql, beginners
cover_image: https://dev-to-uploads.s3.amazonaws.com/uploads/articles/j9rnxam9e350k45gvey3.png
---

## Completing the basics

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

- [Part 1](https://dev.to/marcegarba/what-is-sql-part-1-3dnb)
- [Part 2](https://dev.to/marcegarba/what-is-sql-part-2-1oa1)
- [Part 3](https://dev.to/marcegarba/what-is-sql-part-3-4ica)
- **Part 4**

**Content:**
* [NULL values](#null-values)
* [CASE WHEN](#case-when)
* [Views](#views)
* [Aggregate functions at work](#aggregate-functions-at-work)
* [Final words](#final-words)
* [Glossary](#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
```sql
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](#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](#view) could be created using the previous SELECT
* Here is the view named `products_type`:
```sql
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`
```sql
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](#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 | jul | aug | sep | oct | nov | dec |
|------------|---------------|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|-----|
|  1 | Sport shoes A |  5  |   0 |   1 |   0 |   0 |   2 |   0 |   0 |   0 |   0 |   0 |   0 |
|  2 | Sport watch B |   1 |   9 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   0 |   3 |
|  3 | Suit C        |   0 |   1 |   0 |   1 |   2 |   0 |   0 |   0 |   0 |   0 |   2 |   0 |


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

#### PostgreSQL
```sql
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
```sql
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
```sql
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](#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 id="check"/>A [Constraint](#constraint) where the values of one or more columns are limited to a specific set |
| Constraint       |          | <a id="constraint"/>Set of restrictions in a DB which make the [DB](#db) consistent; there are several types of constraints, such as: [Primary Key](#pk), [Foreign Keys](#fk), [Unique Keys](#uk), `NULL`, [Check Constraints](#check) |
| Data Base        |   DB      | <a id="db"/>A combination of tables, rules, constraints, [triggers](#trigger) and stored procedures which is managed by a [DBMS](#dbms) |
| Data Base Management System | DBMS | <a id="dbms"/>The software that administer [Data Bases](#db); in this context, an SQL DB |
| <a id="ddl"/>Data Definition Language | DDL | Defines the [schema](#schema) in a DB |
| <a id="dml"/>Data Manipulation Language | DML | Operates on tables in a DB |
| Foreign Key     |   FK     | <a id="fk"/>A combination of one or more columns in a table, which point to the [PK](#pk) in a table |
| Primary Key     |   PK     | <a id="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 |
| <a id="rdbms"/>Relational Data Base Management | RDBMS | a [DBMS](#dbms) which follows the Relational math principles |
| Schema          |          | <a id="schema"/>The complete definition of a set of [tables](#table), [constraints](#constraint) and other _objects_ in a [DB](#db) |
| Table          | Relation | <a id="table"/>A matrix formed of tuples, each of them consists of the same type of values (each positioned element); if the table has a [PK](#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         |           | <a id="trigger"/>Code that the [DBMS](#dbms) executes whenever changes occur on the [DB](#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     | <a id="uk"/>Similar to [PK](#pk), a unique combination of columns in each row, _except_ that `NULL` values are accepted |
| View            |          | <a id="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 |