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.
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
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:
|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|
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.
To read values from the table you may use either a summary version which uses the wildcard character * to denote everything:
SELECT * FROM Codenames ;
or you may explicitly request which columns (and the order in which those columns appear):
SELECT CODENAME, NAME FROM Codenames ;
You may also order the results as you see fit.
SELECT NAME, CODENAME FROM Codenames ORDER BY CODENAME ;
Note that NULL is alphabetically sorted before
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.
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 “
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;
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.
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.
|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
|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.
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.
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.