Structured Query Language

Introduction to SQL

The structured query language (SQL - pronounced sequel) is a domain specific query language used for manipulating data in a relational database management system (RDBMS) or for stream processing in a relational data stream managment system (RDSMS). It is particularly useful in handling structured data, i.e. data incoporating relations among entities and variables. SQL offers two main advantages over older read/write APIs such as ISAM or VSAM. Firstly it introduced the concept of accessing many records with one single command. Secondly, it eliminates the need to specify how to reach a record, e.g. with or without an index.

SQL consists of many types of statements, which may be informally classed as sublanguages, commonly: a data query language (DQL), a data definition language (DDL), a data control language (DCL), and a data manipulation language (DML). The scope of SQL includes data query, data manipulation (insert, update and delete), data definition (schema creation and modification), and data access control. It is essentially a declarative language (4GL), but also includes procedural elements. Four of the most common tasks that can be accomplished using SQL are create, retrieve, update, and delete or CRUD for short.

Popular RDBMS that support the SQL language are commercial products like Oracle Database and Microsofts SQL server and the open source MySQL. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and the International Organization for Statdarization (ISO) in 1987. Although the SQL language is standardized, its implementation varies slightly from system to system; meaning that code that needs to be ported from one system to another will most likely require some modification.

SQL Syntax

The SQL language is subdivided into several language elements, including:

  • Clauses, which are constituent components of statements and queries. (In some cases, these are optional.)

  • Expressions, which can produce either scalar values, or tables consisting of columns and rows of data

  • Predicates, which specify conditions that can be evaluated to SQL three-valued logic (3VL) (true/false/unknown) or Boolean truth values and are used to limit the effects of statements and queries, or to change program flow.

  • Queries, which retrieve the data based on specific criteria. This is an important element of SQL.

  • Statements, which may have a persistent effect on schemata and data, or may control transactions, program flow, connections, sessions, or diagnostics. SQL statements also include the semicolon (";") statement terminator. Though not required on every platform, it is defined as a standard part of the SQL grammar.

  • Insignificant whitespace is generally ignored in SQL statements and queries, making it easier to format SQL code for readability.

SQL language elements.

NOTE: The version of SQL supported by the Microsoft SQL server is known as Transact SQL or T-SQL for short. The keywords and functions shown below are for T-SQL. Rest your mouse over the top of a keyword or function to view its description.

T-SQL Keywords

  • ADD
  • ADD CONSTRAINT
  • ALL
  • ALTER
  • ALTER COLUMN
  • ALTER TABLE
  • AND
  • ANY
  • AS
  • ASC
  • BACKUP DATABASE
  • BETWEEN
  • CASE
  • CHECK
  • COLUMN
  • CONSTRAINT
  • CREATE
  • CREATE DATABASE
  • CREATE INDEX
  • CREATE OR REPLACE VIEW
  • CREATE PROCEDURE
  • CREATE TABLE
  • CREATE UNIQUE INDEX
  • CREATE VIEW
  • DATABASE
  • DEFAULT
  • DELETE
  • DESC
  • DISTINCT
  • DROP
  • DROP COLUMN
  • DROP CONSTRAINT
  • DROP DATABASE
  • DROP DEFAULT
  • DROP INDEX
  • DROP TABLE
  • DROP VIEW
  • EXEC
  • EXISTS
  • FOREIGN KEY
  • FROM
  • FULL OUTER JOIN
  • GROUP BY
  • HAVING
  • IN
  • INNER JOIN
  • INSERT INTO
  • INSERT INTO SELECT
  • IS NOT NULL
  • IS NULL
  • JOIN
  • LEFT JOIN
  • LIKE
  • LIMIT
  • NOT
  • NOT NULL
  • OR
  • ORDER BY
  • OUTER JOIN
  • PRIMARY KEY
  • PROCEDURE
  • RIGHT JOIN
  • ROWNUM
  • SELECT
  • SELECT DISTINCT
  • SELECT INTO
  • SELECT TOP
  • SET
  • TABLE
  • TOP
  • TRUNCATE TABLE
  • UNION
  • UNION ALL
  • UNIQUE
  • UPDATE
  • VALUES
  • VIEW
  • WHERE

T-SQL String Functions

  • ASCII
  • CHAR
  • CHARINDEX
  • CONCAT
  • Concat with +
  • CONCAT_WS
  • DATALENGTH
  • DIFFERENCE
  • FORMAT
  • LEFT
  • LEN
  • LOWER
  • LTRIM
  • NCHAR
  • PATINDEX
  • QUOTENAME
  • REPLACE
  • REPLICATE
  • REVERSE
  • RIGHT
  • RTRIM
  • SOUNDEX
  • SPACE
  • STR
  • STUFF
  • SUBSTRING
  • TRANSLATE
  • TRIM
  • UNICODE
  • UPPER

T-SQL Math/Numeric Functions

  • ABS
  • ACOS
  • ASIN
  • ATAN
  • ATN2
  • AVG
  • CEILING
  • COS
  • COT
  • COUNT
  • DEGREES
  • EXP
  • FLOOR
  • LOG
  • LOG10
  • MAX
  • MIN
  • PI
  • POWER
  • RADIANS
  • RAND
  • ROUND
  • SIGN
  • SIN
  • SQRT
  • SQUARE
  • SUM
  • TAN

T-SQL Date Functions

  • CURRENT_TIMESTAMP
  • DATEADD
  • DATEDIFF
  • DATEFROMPARTS
  • DATENAME
  • DATEPART
  • DAY
  • GETDATE
  • GETUTCDATE
  • ISDATE
  • MONTH
  • SYSDATETIME
  • YEAR

T-SQL Advanced Functions

  • CAST
  • COALESCE
  • CONVERT
  • CURRENT_USER
  • IIF
  • ISNULL
  • ISNUMERIC
  • NULLIF
  • SESSION_USER
  • SESSIONPROPERTY
  • SYSTEM_USER
  • USER_NAME