SQL Reference

by Chee Yee Lim


Posted on 2021-04-07



Collection of notes on key points for SQL


Overview

  • SQL
    • SQL stands for Structured Query Language.
    • Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.
      • Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard.
    • The convention is to write all SQL keywords in upper-case, although SQL keywords are not case sensitive.
    • The convention is to write semicolon at the end of each SQL statement, although only some database systems require it.
      • Semicolon allows the separation of each SQL statement hence allowing more than one SQL statement to be executed in the same call to the server.
    • It is required to use single quotes (double quotes allowed in some systems) around text values, but not numeric values.
  • RDBMS
    • RDBMS stands for Relational Database Management System and is the basis for SQL.
    • The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
    • Every table is broken up into smaller entities called fields. A field is a column in a table that is designed to maintain specific information about every record in the table.
    • A record, also called a row, is each individual entry that exists in a table. A record is a horizontal entity in a table.

List of important SQL statements for data operations

  • SELECT
    • Retrieve data from columns.
    • E.g. SELECT [columns] FROM [table]
  • SELECT DISTINCT
    • Retrieve distinct values from columns.
    • E.g. SELECT DISTINCT [columns] FROM [table]
  • WHERE
    • Filter records that satisfy a specific condition.
    • E.g. SELECT [columns] FROM [table] WHERE [condition]
    • Supported operators in conditions:
      • = - equal
      • > - greater than
      • < - less than
      • >= - greater than or equal
      • <= - less than or equal
      • <> - not equal. In some SQL versions, this is written as !=.
      • BETWEEN - between a range
      • IN - to specify multiple possible values for a column
      • AND - logical and
      • OR - logical or
      • NOT - logical not
      • LIKE - search for a pattern in string
  • ORDER BY
    • Sort the result in ascending or descending order. It sorts in ascending order by default.
    • E.g. SELECT [columns] FROM [table] ORDER BY [columns] ASC|DESC
  • IS NULL / IS NOT NULL
    • Check for null values in data. NULL values cannot be checked with comparison operators.
    • E.g. SELECT [columns] FROM [table] WHERE [columns] IS NULL|IS NOT NULL
  • TOP / LIMIT
    • Specify the number of records to return. A lot faster than returning all records.
    • E.g. (for MS SQL) SELECT TOP [number|percentage] [columns] FROM [table] WHERE [condition]
    • E.g. (for MySQL) SELECT [columns] FROM [table] WHERE [condition] LIMIT [number]
  • LIKE
    • It is used in a WHERE clause to search for a specific pattern.
    • Two wildcards are commonly used with the LIKE operator:
      • % - it represents zero, one or multiple characters
      • _ - it represents a single character
    • E.g. SELECT [columns] FROM [table] WHERE [column] LIKE [pattern]
      • E.g. pattern %app% - to find any string containing 'app'.
  • IN
    • It is used as a shorthand for multiple OR conditions.
    • E.g. SELECT [columns] FROM [table] WHERE [column] IN ([value1], [value2], ...)
  • BETWEEN
    • Selects values within a given range. The values can be numbers, text or dates.
    • BETWEEN operator is inclusive, including begin and end values.
    • E.g. SELECT [columns] FROM [table] WHERE [column] BETWEEN [value1] AND [value2]
  • AS
    • Aliases are used to give a column or a table a temporary name.
    • An alias only exists for the duration of the query.
    • E.g. SELECT [column] AS [column_alias] FROM [table]
    • E.g. SELECT [column] FROM [table] AS [table_alias]
  • Aggregation Functions
    • MIN, MAX, COUNT, AVG, SUM
    • E.g. SELECT MIN([column]) FROM [table] WHERE [condition]
  • JOIN
    • It is used to combine rows from two or more tables, based on a related column between them.
    • Inner join
      • Returns records that have matching values in both tables.
    • Left join
      • Returns all records from the left table, and the matched records from the right table.
    • Right join
      • Returns all records from the right table, and the matched records from the left table.
    • Full outer join
      • Returns all records when there is a match in either left or right tables.
    • Self join
      • Returns pairwise join between all records in one table.
    • E.g. SELECT [columns] FROM [table1] INNER JOIN [table2] ON [table1.column]=[table2.column]
    • E.g. SELECT [columns] FROM [table1] T1 LEFT JOIN [table1] T2 ON [T1.column]=[T2.column]
  • UNION/UNION ALL
    • It is used to combine two or more results of SELECT statements.
    • UNION selects distinct values by default. UNION ALL allows duplicated values.
    • E.g. SELECT [columns] FROM [table1] UNION SELECT [columns] FROM [table2]
  • GROUP BY
    • It groups rows that have the same values into summary rows.
    • It is often used with aggregate functions to group the results by one or more columns.
    • E.g. SELECT [columns] FROM [table] WHERE [condition] GROUP BY [columns] ORDER BY [columns]
  • HAVING
    • It is added to SQL because WHERE keyword cannot be used with aggregate functions.
    • E.g. SELECT [columns] FROM [table] WHERE [condition] GROUP BY [columns] HAVING [condition] ORDER BY [columns]
  • EXISTS
    • It is used to test for the existence of any record in a subquery. It returns true if the subquery returns one or more records.
    • E.g. SELECT [columns] FROM [table] WHERE EXISTS (SELECT [columns] FROM [table] WHERE [condition])
  • ANY / ALL
    • E.g. SELECT [columns] FROM [table] WHERE [column] [operator] ANY|ALL (SELECT [column] FROM [table] WHERE [condition])

List of important SQL statements for setting up database

  • UPDATE
    • Modifies data records in a database.
    • If WHERE conditions are not specified, all records in the database will be updated.
    • E.g. UPDATE [table] SET [column1]=[value1], [column2]=[value2], ..., WHERE [condition]
  • DELETE
    • Deletes data records from a database.
    • If WHERE conditions are not specified, all records in the database will be deleted.
    • E.g. DELETE FROM [table] WHERE [condition]
  • INSERT INTO
    • Inserts new data into a database.
    • E.g. INSERT INTO [table] ([column1], [column2], ...) VALUES ([value1], [value2], ...)
  • SELECT INTO
    • Copies data from one table to a new table.
    • E.g. SELECT [columns] INTO [new_table] FROM [old_table] WHERE [condition]
  • INSERT INTO SELECT
    • Copies data from one table and inserts it into another table.
    • E.g. INSERT INTO [table2] ([column1], [column2], ...) SELECT [column1], [column2], ... FROM [table1] WHERE [condition]
  • CREATE DATABASE
    • Creates a new database.
  • ALTER DATABASE
    • Modifies a database.
  • CREATE TABLE
    • Creates a new table.
  • ALTER TABLE
    • Modifies a table.
  • DROP TABLE
    • Deletes a table.
  • CREATE INDEX
    • Creates an index (search key).
  • DROP INDEX
    • Deletes an index.