SQL (Structured Query Language) — pronounced “sequel” or “ess queue ell” — is a computer programming language tailored to interacting with data stored in relational databases. SQL provides all the necessary tools to create, read, update, and delete (CRUD) that data.
This SQL tutorial shows how you can execute “SQL queries” — tailored requests for information — from virtually any database (including MySQL, SQLite, Apache Presto, Firebird SQL, Google BigQuery, Oracle, Microsoft SQL Server, Sybase, SAP HANA, IBM DB2, and many others).
Tables, Rows, Columns, NULL
As this is a practical, rather than theoretical, blog post let’s dive right into nomenclature and then code samples. Following is a representation of a database table; it contains three columns (ID, NAME, and CODENAME) and three rows (each containing a numeric ID, a human name, and a Secret Service codename). All is reasonably unremarkable save the codename for Nancy Pelosi, which reads NULL.
NULL is a special value, neither a zero-length text string nor true or false (as databases implement a three-value logic to include NULL). In computer programming parlance NULL = NULL
does not return true with the equality operator =
but instead requires a special IS NULL
comparison operator. (This will become less opaque as we progress.) NULL means “no value here.”
Note also that the ID
column seems to contain unique, monotonically increasing integer values, but you’d have to check the SQL Create Table statement to see how the table was defined. Without further knowledge there’d be no reason to not allow an ID of 3.14159 in each and every row.
ID | NAME | CODENAME |
1 | Joseph Biden | Celtic |
2 | Kamala Harris | Pioneer |
3 | Nancy Pelosi | NULL |
This seems a good time to note that there’s a SQL standard (the core of the language) that’s evolved and also vendor-specific dialects, so you’ll have to check how the database you’ve chosen to use implements values for that ID column; it may be NOT NULL IDENTITY(1,1) PRIMARY
or perhaps AUTO_INCREMENT
.
SQL Data Types
Because vendors choose which ANSI standard SQL to implement, and the standard evolves, you’ll have to check your specific database for the data types and synonyms accepted. In general, all databases support the following data types:
Type | SQL |
character | CHAR, VARCHAR, CLOB |
binary | BINARY, VARBINARY, BLOB |
numeric (exact) | NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT |
numeric (approximate) | FLOAT, REAL, DOUBLE |
time | DATE, TIME, TIMESTAMP |
other | INTERVAL, BOOLEAN, XML, JSON |
Create
Here’s standard SQL code to create the above table without a unique, incrementing ID column being enforced by the database.
CREATE TABLE Codenames (
ID INT,
NAME VARCHAR(50),
CODENAME VARCHAR(15) ) ;
To create the table above you would say:
INSERT INTO Codenames (
ID,
NAME,
CODENAME )
VALUES
( 1, 'Joseph Biden', 'Celtic' ),
( 2, 'Kamala Harris', 'Pioneer' ),
( 3, 'Nancy Pelosi', NULL ) ;
As whitespace is optional in SQL the terse equivalent would be:
INSERT INTO Codenames (ID, NAME, CODENAME ) ( 1, 'Joseph Biden', 'Celtic' ), ( 2, 'Kamala Harris', 'Pioneer' ), ( 3, 'Nancy Pelosi', NULL ) ;
The usual tension between verbosity (for code readability and ease of maintenance) and terseness (for speed of writing text) must be considered when writing your SQL programs.
Read
To read values from the table you may use either a summary version which uses the wildcard character * to denote everything:
SELECT * FROM Codenames ;
ID | NAME | CODENAME |
1 | Joseph Biden | Celtic |
2 | Kamala Harris | Pioneer |
3 | Nancy Pelosi | NULL |
or you may explicitly request which columns (and the order in which those columns appear):
SELECT CODENAME, NAME FROM Codenames ;
CODENAME | NAME |
Celtic | Joseph Biden |
Pioneer | Kamala Harris |
NULL | Nancy Pelosi |
You may also order the results as you see fit.
SELECT NAME, CODENAME FROM Codenames ORDER BY CODENAME ;
NAME | CODENAME |
Nancy Pelosi | NULL |
Joseph Biden | Celtic |
Kamala Harris | Pioneer |
Note that NULL is alphabetically sorted before ‘A’
.
Update
SQL allows one to update values previously inserted into the database tables with the SQL Update statement.
UPDATE
table_name
SET
column_1 = value_1,
column_2 = value_2, ...
WHERE
condition ;
To make Celtic’s human name more formal:
UPDATE
Codenames
SET
name = 'Joseph R. Biden, Jr.'
WHERE
ID = 1 ;
Note the use of the WHERE clause to restrict updating values to the appropriate rows.
Read more about the SQL Update statement.
Delete
The WHERE clause is used in a parallel manner with DELETE to restrict the deletion of rows.
DELETE FROM
Codenames
WHERE
NAME LIKE '%den%' ;
Introduced here is an example of SQL wildcard string matching. The clause NAME LIKE '%den%'
restricts the rows deleted to those in which the NAME column contains the text string “den
“.
Truncate
Another way to delete all the rows from a table is the SQL Truncate command (which doesn’t use the WHERE clause).
TRUNCATE TABLE Codenames;
is the equivalent of
DELETE * FROM Codenames;
Drop
Deleting all the table data along with the table structure is done with the SQL Delete command.
DROP TABLE Codenames;
Note that different databases may have subtleties with regards to Delete, Truncate, and Drop.
Join
A properly decomposed set of database tables will isolate commonly-used large expensive-to-store values (such as text strings) into separate tables. SQL Join provides the capability to stitch together decomposed tables into one tailored to meet specific data-processing needs.
EMPLOYEES:
NAME | ROLE |
Alice | 1 |
Bob | 2 |
ROLES:
ID | DESCRIPTION |
1 | Flying Car and Autonomous Flight Engineer |
2 | Robotics Software Engineer |
This SQL statement puts together the above two tables, EMPLOYEES and ROLES, in such a way that the output displays
NAME | DESCRIPTION |
Alice | Flying Car and Autonomous Flight Engineer |
Bob | Robotics Software Engineer |
SELECT
EMPLOYEES.NAME, ROLES.DESCRIPTION
FROM
EMPLOYEES
INNER JOIN
ROLES ON EMPLOYEES.ROLE = ROLES.ID ;
Read more about the SQL Join statement.
Case
SQL Case provides an if-then-else control flow capability, allowing business logic to be applied.
CASE
WHEN condition_one THEN result_one
WHEN condition_two THEN result_two
ELSE catch_all_result
END;
For example, to give the mail room staff a heads-up on which shipping methods are needed for a variety of outgoing shipments, something like the following would work.
SELECT NAME, CITY, COUNTRY
CASE
WHEN 'USA' THEN 'USPS'
WHEN 'UK' THEN 'Royal Mail'
ELSE 'other international shipper'
END AS ShippingMethod
FROM People
ORDER BY ShippingMethod ;
Read more about the SQL Case statement.
Conclusion
SQL is a compact, flexible language which allows you the ability to create, read, update, and delete data in database tables. Rather than one ubiquitous SQL standard adhered to across all database implementations there’s both an ANSI standard that’s evolved over time, adopted at different releases by commercial vendors, as well as multiplicity of synonyms for standard SQL constituents and another multiplicity of proprietary SQL extensions which are not transferable between databases.
This blog post has merely touched upon the general shape of SQL, uncovering only a tiny part of its power and capabilities.
To learn more about SQL, enroll in our SQL Nanodegree program, described in detail here.