SQL - SQL Introduction - SQL Total - SQL tutorial

SQL Tutorial — Database Programming

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.

IDNAMECODENAME
1Joseph BidenCeltic
2Kamala HarrisPioneer
3Nancy PelosiNULL

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:

TypeSQL
characterCHAR, VARCHAR, CLOB
binaryBINARY, VARBINARY, BLOB
numeric (exact)NUMERIC, DECIMAL, SMALLINT, INTEGER, BIGINT
numeric (approximate)FLOAT, REAL, DOUBLE
timeDATE, TIME, TIMESTAMP
otherINTERVAL, 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 ;
IDNAMECODENAME
1Joseph BidenCeltic
2Kamala HarrisPioneer
3Nancy PelosiNULL

or you may explicitly request which columns (and the order in which those columns appear):

SELECT CODENAME, NAME FROM Codenames ;
CODENAMENAME
CelticJoseph Biden
PioneerKamala Harris
NULLNancy Pelosi

You may also order the results as you see fit.

SELECT NAME, CODENAME FROM Codenames ORDER BY CODENAME ;
NAMECODENAME
Nancy PelosiNULL
Joseph BidenCeltic
Kamala HarrisPioneer

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:

NAMEROLE
Alice1
Bob2

ROLES:

IDDESCRIPTION
1Flying Car and Autonomous Flight Engineer
2Robotics Software Engineer

This SQL statement puts together the above two tables, EMPLOYEES and ROLES, in such a way that the output displays 

NAMEDESCRIPTION
AliceFlying Car and Autonomous Flight Engineer
BobRobotics 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.

Start Learning