Forem: Marcelo Garbarino

What is SQL - Part 4

Marcelo Garbarino — Tue, 20 Apr 2021 23:41:12 +0000

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

What is SQL - Part 3

Marcelo Garbarino — Thu, 15 Apr 2021 20:01:44 +0000

Viewing, updating and deleting data in tables

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

Part 1
Part 2
Part 3
Part 4

Retrieving data using the SELECT clause

As mentioned before, relations / tables / sets are an unordered collection of rows, or tuples.
Let's retrieve all the content in table products:

SELECT *
FROM products;

SELECT *
- This means brings all the columns; it could also be written as SELECT id, name
FROM products
- List of tables to operate on. In this case, we're talking about table products
- Here's a possible result:

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

Why possible? Because there is no specific ordering of rows; it all depends on how the DB system stores the rows, the order in which rows were INSERTed, and others

Ordering

In order to establish a specific order, we must use the ORDER clause:

SELECT id, name
FROM products
ORDER BY id;

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

In reverse order, by name:

SELECT id, name
FROM products
ORDER BY name DESC;

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

The ORDER clause may list more than one column, or expression; in this simple case, it doesn't make much sense

Filtering

Let's see how to limit the number of rows, based on filtering conditions:

SELECT *
FROM products
WHERE id = 2;

id	name
2	Sport watch B

SELECT *
FROM products
WHERE name LIKE 'Sport %'
ORDER BY id ASC

id	name
1	Sport shoes A
2	Sport watch B

Here, a combination of WHERE and ORDER BY clauses:
- WHERE name LIKE 'Sport %' means all rows where name starts with 'Sport':
- The % symbol maps from zero to any number of characters
- If the ? symbol were used, that would mean exactly one character, which would bring zero rows, as
ORDER BY id ASC: ASC for ascending, which is the default in the clause

Functions

SQL offers a number of standard functions, and in fact, there are two types of functions:
- Common functions, which apply to expressions in rows
- Aggregate and Window functions, which apply to more than one row at a time

Row functions

SELECT id, id * 2 AS double_id, upper(name) AS upper_name
FROM products
ORDER BY id;

id	double_id	upper_name
1	2	SPORT SHOES A
2	4	SPORT WATCH B
3	6	SUIT C

Several things to notice here:
- Expressions like id * 2 and functions like upper(name)
- the AS key word is used to give names to the expressions; if it were not used, the calculated columns would have default names, which vary depending on the DB system used

Aggregate and window functions

SELECT COUNT(*) AS count
FROM products;

count
3

SELECT id_product,
       COUNT(*) AS number_of_sales,
       SUM(amount) AS sum_amount
FROM sales
GROUP BY product
ORDER BY product;

id_product	number_of_sales	sum_amount
1	4	575.72
2	5	2697.32
3	4	1903.75

Here aggregate functions COUNT(), SUM() are used
Notice the resulting rows and content:
- Aggregate functions
- COUNT(*): counts the number of rows, _according to the GROUP BY criterion
- SUM(amount): calculates the sum of column amount according to the GROUP BY criterion
- AVG(x): calculates the average
- MAX(x), MIN(x): obtains the maximum or minimum value
- There are other aggregate functions in standard SQL
The list of columns in the SELECT when using grouping functions, should either:
- Be the result of an aggregate expression
- Be named in the GROUP BY clause
If this is not the case, the query should fail
- If it doesn't fail, for some DB engines, that's an issue, since it's not easy to spot a failed SELECT
- PostgreSQL is very strict, which is a great thing; not so with other DB products

Filtering grouped rules

Instead of using the WHERE clause, for filtering on aggregated expressions, HAVING has to be used:

SELECT id_product,
       COUNT(*) AS number_of_sales,
       SUM(amount) AS sum_amount
FROM sales
GROUP BY product
HAVING COUNT(*) < 5
ORDER BY product;

id_product	number_of_sales	sum_amount
1	4	575.72
3	4	1903.75

Combining results from more than one table

By using the JOIN clause, more than one table can be invoked in the SELECT statement.
Here is an example:

SELECT s.id_product,
       p.name,
       COUNT(*) AS number_of_sales,
       SUM(s.amount) AS sum_amount
FROM sales s
  JOIN products p
    ON s.id_product = p.id
GROUP BY s.id_product, p.name
HAVING COUNT(*) < 5
ORDER BY s.id_product;

A few changes to notice:
- FROM ... JOIN ... ON ...
- FROM used to name one table,
- JOIN names the second table
- ON establishes the way both tables are related
- Table name aliases:
- sales s and products p; this is not necessary, but instead of typing sales.id_product, product.name and sales.amount, aliases help us to shorten the sentences
- If the column names are unique among the invoked tables, there is no strict need to preface the column with the table or alias names, although it's a good practice. In fact, if in the future one of the column names are repeated, what worked so far will stop to do so, as the DB engine wouldn't know what table the column referrer to
the GROUP BY clause needs to include p.name; otherwise, an error should be triggered (again, not all DB engines do so)
Types of JOIN:
- INNER JOIN: Rows from both tables must be present (the default)
- LEFT JOIN: Rows from the left-side table must exist, for columns in the right-side table missing, NULL values are used
- RIGHT JOIN: If rows from the left-side table do not exist, NULL values are used instead
- CROSS JOIN: Cartesian product from both tables are retrieved, as long as the 'ON' clause is fulfilled

As an example of a CROSS JOIN, which is not much used, look at this simple SELECT:

SELECT p.id AS id_product,
       p.name AS name_product,
       s.id AS id_seller,
       s.name AS name_seller
FROM products p,
     sellers s
ORDER BY id_product, id_seller;

Simply naming the tables without any filtering condition, is equivalent to a CROSS JOIN:

id_product	name_product	id_seller	name_seller
1	Sport shoes A	234	John S.
1	Sport shoes A	281	Luisa G.
1	Sport shoes A	341	Mary T.
2	Sport watch B	234	John S.
2	Sport watch B	281	Luisa G.
2	Sport watch B	341	Mary T.
3	Suit C	234	John S.
3	Suit C	281	Luisa G.
3	Suit C	341	Mary T.

This SELECT is just an example of a CROSS JOIN result.

Updating table contents with UPDATE

The UPDATE sentence is used for updating rows
An example follows:

UPDATE sellers
SET name = 'María T.'
WHERE id = 341;

After this change, look at the table contents:

SELECT *
FROM sellers
ORDER BY id;

id	name
234	John S.
281	Luisa G.
341	María T.

Important points:
- Limit the UPDATE with a WHERE; otherwise, the update will impact all rows in a table, all of them, in one transaction
- Not all updates are guaranteed to success; for instance, in the schema with all constraints in place, this UPDATE will fail:

UPDATE sellers
SET id = 235
WHERE id = 234;

If you try this, you'll see that it fails, as there are relational integrity constraints which prevent tables sellers and products to update their PK if there is at least one row in the sales table that point to those values, as is the case in the example here
- See the error with PostgreSQL:

ERROR:  update or delete on table "sellers" violates foreign     
        key constraint "sales_seller_fkey" on table "sales"
DETAIL: Key (id)=(234) is still referenced from table "sales".

See it with MariaDB:

ERROR 1451 (23000): Cannot delete or update a parent row:
      a foreign key constraint fails
      (`course`.`sales`,
       CONSTRAINT `sales_ibfk_2`
       FOREIGN KEY (`id_seller`) REFERENCES `sellers` (`id`)
      )

Deleting table contents with DELETE

The DELETE DML sentence is used to remove rows from tables
Here's an example:

DELETE
FROM sales
WHERE quantity BETWEEN (4 AND 5);

After deleting these rows, the contents of the table are:

SELECT id_product,
       id_seller,
       date,
       quantity,
       amount
FROM sales
ORDER BY id_product, id_seller;

id_product	id_seller	date	quantity	amount
1	234	2020-06-10	2	148.34
1	234	2020-01-23	2	142.38
1	234	2020-03-01	1	75.00
1	341	2020-01-17	3	210.00
2	234	2020-12-25	1	220.00
2	341	2020-01-31	1	215.48
2	341	2020-12-01	2	448.50
3	281	2020-04-15	1	350.00
3	281	2020-05-13	2	605.25
3	341	2020-02-15	1	348.50
3	341	2020-11-18	2	600.00

As is the case with the failed UPDATE and constraints are in place, certain DELETE queries will fail
For instance:

DELETE
FROM sales
WHERE id = 234;

If the FOREIGN KEY constraints established in part 2 for table sales had the ON UPDATE CASCADE ON DELETE CASCCADE, neither the UPDATE nor the DELETE would fail:
- For the UPDATE, it would modify the values of the foreign keys in table sales
- For the DELETE, it would also remove rows from the sales table

What is SQL - Part 2

Marcelo Garbarino — Mon, 12 Apr 2021 15:35:21 +0000

Creating tables with SQL

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

Part 1
Part 2
Part 3
Part 4

In the example that follows, three very simple tables are created and content is added to them.

The three tables schema depicted in the image above is created and populated.

DDL for creating the tables

Data Definition Language is the subset of SQL which is used for creating, altering and dropping elements in a DB schema:

CREATE: creates databases, tables, views, indexes
ALTER: modifies existing object in a DB
DROP: destroys DB objects

Here are the statements for creating the tables:

Products

CREATE TABLE products (
    id    INTEGER NOT NULL,
    name  VARCHAR(50),
    PRIMARY KEY (id)
);

This table holds the products to be sold
Notice the names, types and constraints
- id is NOT NULL, that is, it doesn't admit NULL values
- name is of type VARCHAR(50), which means it admits up to 50 characters, and the length is not fixed, and it admits NULL values
- Besides, the PRIMARY KEY constraint identifies this column as the one that can't be repeated in the table
- In fact, the NOT NULL isn't strictly required, as PKs do not allow NULL VALUES

Sellers

CREATE TABLE sellers (
    id    INTEGER NOT NULL,
    name  VARCHAR(50),
    PRIMARY KEY (id)
);

The list of employees that sell products
Like products, id is the Primary Key, which assures there are no more than one row with the same values

Sales

CREATE TABLE sales (
    id          INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    id_product  INTEGER NOT NULL,
    id_seller   INTEGER NOT NULL,
    date        DATE NOT NULL,
    quantity    INTEGER NOT NULL,
    amount      REAL NOT NULL,
    FOREIGN KEY (id_product) REFERENCES products (id),
    FOREIGN KEY (id_seller)  REFERENCES sellers (id)
);

Here is the sales table, which is related with products and sellers
The PK is also named id, but notice that it in this case, there is the AUTOINCREMENT keyword; this is the SQLite syntax, AUTO_INCREMENT for MySQL and SERIAL for PostgreSQL, it means that when creating new rows in this table, the DB will automatically populate it with consecutive numbers
Importance to notice here are the FOREIGN KEY clauses:
- The first one links the possible values in sales with the values in the PK for products
- The other does the same with table sellers
What does it mean?
- Values for id_product and id_seller could not be any number; they have to match with existing rows in products and sellers
- Besides, this means that rows in the tables products and sellers cannot be deleted if at least one row in sales point to them ... unless the DDL were different:
- REFERENCES ... ON DELETE CASCADE ... ON UPDATE CASCADE
- ON DELETE CASCADE would mean that removing a row in the referenced table, would cascade to delete it in this table too
- ON UPDATE CASCADE would mean that if the PK would be changed in the reference table(s), this would cascade the update to this table
- As these are not the case, these clauses are not added to the CREATE TABLE sentence

With these SQL DDL clauses, the tables will be created and:

Proper names and data types will be established
Proper constraints, which will enforce the natural rules which should always be enforced in the DB, such as:
- NOT NULL: NULL values are not permitted in those attributes, or columns
- A NULL value does not mean zero, empty string, nor other type of 'natural', so to speak, empty values
- It means that for a combination of row and column (in Math, it would be a tuple and an attribute) there is no value present, and in SQL it's treated differently
- PRIMARY KEY: Does not allow duplicate rows
- Remember that Sets, in math, do not allow duplicate elements in it
- For a Table to be a perfect Set, there should not be any duplicate element, and each element in this Set is a tuple
- FOREIGN KEY: Prevents related table to hold invalid values

DML for inserting rows into the tables

Data Manipulation Language is the SQL subset used for modifying the contents of tables in a DB:

INSERT: inserts new rows into tables ¹
UPDATE: modifies values in existing rows
DELETE: removes rows
SELECT: retrieves content from the DB

Here are the statements for inserting rows into the tables created above in the DDL.

Products

INSERT INTO products (id, name) VALUES
    (1, 'Sport shoes A'),
    (2, 'Sport watch B'),
    (3, 'Suit C');

Sellers

INSERT INTO sellers (id, name) VALUES
    (234, 'John S.'),
    (281, 'Luisa G.'),
    (341, 'Mary T.');

Sales

INSERT INTO sales
    (id_product, id_seller, date, quantity, amount)
 VALUES
    (1, 234, '2020-01-23', 2, 142.38),
    (2, 234, '2020-02-01', 4, 813.34),
    (2, 341, '2020-01-31', 1, 215.48),
    (1, 234, '2020-03-01', 1, 75.00),
    (3, 341, '2020-02-15', 1, 348.50),
    (1, 341, '2020-01-17', 3, 210.00),
    (3, 281, '2020-04-15', 1, 350.00),
    (2, 281, '2020-02-28', 5, 1000.00),
    (3, 281, '2020-05-13', 2, 605.25),
    (1, 234, '2020-06-10', 2, 148.34),
    (2, 341, '2020-12-01', 2, 448.50),
    (2, 234, '2020-12-25', 1, 220.00),
    (3, 341, '2020-11-18', 2, 600.00);

Since the id in this table is auto incremental, there is no need to include that column in the INSERT above
There are other ways to generate unique default values for a column
- In particular, and in a syntax that varies according to the DBMS engine used, an auto-generated UUID may be used

1 Tables and updatable views in DB engines that support them.

What is SQL - Part 1

Marcelo Garbarino — Fri, 02 Apr 2021 02:05:18 +0000

Introduction to SQL

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

Part 1
Part 2
Part 3
Part 4

What is SQL?

In my experience, many developers, although expert in their coding fields, fail to grasp the important fundamentals of the SQL language and the math behind it.

For instance, strict SQL:

Is a Logical Programming language
- It's not Procedural!
Has strong math behind it
- It's based on Relational Algebra, which in turn uses Set Theory
Does not operate on files, records and fields, but rather on tables, rows and columns
- Quite a different approach and quite different concepts

Why briefly touch the theory rather than jump directly to SQL code samples

As part of my teaching younger devs, once I had to explain what Git is to coders that have used Subversion for years.

I understood that explaining the foundation features of git, which are absolutely different from those of SVN, right from the start, would give them the proper understanding and see what they can do with such a monster version control product. Concepts such as:

Content Addressable File system
Git objects, such as blobs, trees, commits, tags
Branching and merging in Git

In the same way, this first part starts with a little theory.

SQL, a Domain Specific Language

It's a very old 4th Generation Languages, widely in use, which expresses Data using Relational Algebra and (lately) other mathematical concepts, such as Recursion.

What is it?

Relational Algebra is closely related with Sets.

SQL was first formally defined in a 1974 paper.

The name SQL has two origins:

1 Abbreviation of Sequel, from the mentioned 1974 paper
2 Structured Quary Language

It has different parts, each of which deals with distinct aspects of manipulating data and metadata in a Database Management System, or DBMS, such as:

DDL: Data Definition Language
- Defines the objects in a DBMS, such as Tables, Views, Constraints
DML: Data Manipulation Language
- Retrieves, adds, deletes and updates data from Tables in a DBMS
DCL: Data Control Language
- Gives or revokes specific permissions for DB users

A Relation is a Table

A Table is a matrix that has:
- Columns (attributes), where each of them has a name and is of a specific data type, such as number, text, boolean and others
- Rows (tuples), which represent a unique combination of data in the columns
To consider a Table as a Set, in the mathematical sense, it should not allow duplicate rows, although this is not always the case
A few considerations of Sets, which will help to understand the way SQL works:
- Sets do not allow duplicate elements
- The order of elements in a set is not important
- All operations in sets are expressed at once
- There are operations in Sets, such as Unions, Intersections, Cartesian product

Constraints

To prevent tables to contain erroneous data, at the Database level the enforcement of rules may be established:
- Keys, such as Primary Keys ¹, Unique Keys, Foreign Keys ²
- Admission or prohibition of NULL values ³
- Check Constraints, where columns might have only selected values

1 Primary Keys assure that a table can't contain duplicate rows.
2 Foreign Keys restrict the values of one or more columns in a table to the Primary Keys of another table, or itself.
3 The NULL value represents both, unknown data and, when joining tables, missing data.