DEV Community

Cover image for What is SQL - Part 4
Marcelo Garbarino
Marcelo Garbarino

Posted on

What is SQL - Part 4

#sql #beginners

Completing the basics

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

Content:

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;
Enter fullscreen mode Exit fullscreen mode
  • 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;
Enter fullscreen mode Exit fullscreen mode
  • 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';
Enter fullscreen mode Exit fullscreen mode
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 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 

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode

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;
Enter fullscreen mode Exit fullscreen mode
  • 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

Top comments (0)