SQL

External

• Learning SQL, 3rd Edition by Alan Beaulieu
• http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt

Internal

• Relational Databases
• PrestoDB
• PostgreSQL

Overview

This article documents standard SQL. For database-specific extensions, we'll link to the databases-specific DML operations page.

SQL is a standard non-procedural language designed to work with relational databases, allowing the users to interact with the database instance by issuing schema statements, data statements and transaction statements.

SQL statements define the necessary inputs and outputs for the database interaction, but it does not define how the statements should be executed. The details of the execution are left to the database engine component known as the optimizer. The optimizer parses the SQL statements and depending on how the tables are configured and what indexes exist, it decides the most efficient execution path. Most database engines will allow you to influence the optimizer's decisions by accepting optimizer hints.

The first standard for the SQL language was published by the American National Standards Institute (ANSI) in 1986. Subsequent refinements led to new releases of the SQL standard in 1989, 1992, 1999, 2003, 2006, 2008, 2011, and 2016.

Relational Database Terminology

Table

In a relational model, data is stored in a set of tables, also known as relations. Within a relation, data is organized in rows (records) and columns. Each row describes an entity. Each is uniquely identified by a primary key. The rows may contain columns that are copies of other tables' primary keys. These are knowns as foreign keys and are used in joining related data together.

Also see:

Relational Databases

From an SQL perspective, there are four types of tables. All of them can be used in the FROM clause of a SELECT query:

Permanent Table

A table created with a CREATE TABLE statement.

Temporary Table

A temporary (or volatile) table can be created with CREATE TEMPORARY TABLE. It looks just like a permanent table, but any data inserted into a temporary table will disappear at some point, generally at the end of a transaction or when the database session is closed.

Derived Table

Derived tables are dynamically built in memory when a containing query executes a subquery. The derived table data is held in memory for the duration of the query execution and then discarded. See:

Querying a Derived Table

Virtual Table (View)

A view, or a virtual table, is a query that is stored in the database's data dictionary. It looks and acts like a table, but there is no data associated with the view. A view is created with a CREATE VIEW statement.

Result Set

The result of an SQL query, known as a result set, is also a table maintained in memory.

SQL Schema Statements (DDL)

SQL schema statements are used to define the data structures stored in the database. This SQL subset is also known as Data Definition Language (DDL). All database elements (tables, constraints, etc.) create with the SQL schema statements are stored in a special set of table called the data dictionary, or metadata. Also see:

Database Metadata

CREATE | ALTER | DROP TABLE

CREATE | ALTER | DROP TABLE

CREATE | ALTER | DROP VIEW

CREATE | ALTER | DROP VIEW

Constraints

SQL Constraints

SQL Data Statements (DML)

SQL data statements are used to manipulate the data structures perviously defined using the SQL schema statements. This SQL subset is also known as Data Manipulation Language (DML).

Queries with SELECT

Queries with SELECT

INSERT

INSERT

UPDATE

UPDATE

DELETE

DELETE

SQL Transaction Statements

The SQL transaction statements are used to begin, end, and rollback transactions.

Transaction Statements

Standard SQL Data Types

Standard SQL Data Types

SQL NULL

There are cases when values for certain columns cannot be determined. In these cases, the column is said to be NULL (it is not said that it equals to NULL). NULL indicates an absence of a value. NULL is used in the following cases:

• not applicable
• unknown
• empty set

When creating a table, the columns are allowed to be NULL by default. If specific columns must not accept NULL values, they must be declared with a NOT NULL constraint.

Also see:

NULL in WHERE Conditions

Indexes

SQL Indexes

Set Operations

SQL Set Operations

Analytic Functions

TO PROCESS: https://learning.oreilly.com/library/view/learning-sql-3rd/9781492057604/ch16.html

Parameterized SQL Statements

Parameterized SQL Statements in PostgreSQL

Parameterized SQL Statements in MySQL