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.