SQL Cheat Sheet

Intro and Syntax

SQL is a standard language for accessing and manipulating databases.


Data Definition Language or DDL deals with defining databases and the tables.


Data Manipulation Language or DML deals with the CRUD operations.


CRUD means Create, Read, Update, and Delete with each having SQL commands.


Database Tables

  • each table identified by name
  • tables contain records(rows) with data

SQL Statements

  • are not case sensitive but convention is to write all keywords in uppercase
  • each statement needs to end with a semicolon

Create

Create or add data to the database.

CREATE DATABASE

CREATE DATABASE dbname;

Used to create a database.

CREATE TABLE

CREATE TABLE table_name(column_name1 data_type(size),...);

Used to create a table in a database.

CREATE INDEX

CREATE INDEX index_name ON table_name (column_name);

Creates an index on a table and duplicate values are allowed.

CREATE UNIQUE INDEX

CREATE UNIQUE INDEX index_name ON table_name (column_name);

Creates a unique index on a table.

Constraints

Constraints are the rules enforced on data columns on tables and are used to limit the type of data that can go into a table.

  • NOT NULL: ensures that a column cannot have NULL values
  • PRIMARY KEY: uniquely identifies each rows/records in a database table
  • FOREIGN KEY: uniquely identifies rows/records in any another database table

INSERT INTO

INSERT INTO table_name VALUES (value1,...);

Used to insert new records in a table.


Read

Searching, filtering, and limiting the result set.

SELECT

SELECT column_name,column_name FROM table_name;

and

SELECT * FROM table_name;

Used to select data from a database.

WHERE

SELECT column_name,column_name FROM table_name WHERE column_name operator value;

Used to filter records.

AND

SELECT column_name,column_name FROM table_name WHERE column_name operator value AND column_name operator value;

Operator displays a record if both the first condition and the second condition are true.

OR

SELECT column_name,column_name FROM table_name WHERE column_name operator value OR column_name operator value;

Operator displays a record if either the first condition or the second condition is true.

ORDER BY

SELECT column_name, column_name FROM table_name ORDER BY column_name ASC|DESC, column_name ASC|DESC;

Used to sort the result-set.

IN

SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...);

Operator allows you to specify multiple values in a WHERE clause.

BETWEEN

SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;

Operator selects values(numbers, text, or dates) within a range.

LIKE

SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern;

Operator is used in a WHERE clause to search for a specified pattern in a column.

Wildcard Characters

Used with the LIKE operator to search for data within a table.

  • %: subsitute for zero or more characters
  • _: substitute for a single character
  • [charlist]: sets and ranges of characters to match
  • [^charlist] or [!charlist]: matches only a character not specified within the brackets

Data Types

String

  • CHAR: fixed length n(0-255); must have exactly n characters
  • VARCHAR: variable length n(0-255); can have up to x characters
  • TEXT: variable length; for data greater than 255 characters

Numeric

  • INTEGER
  • FLOAT
  • DECIMAL

Date & Time

  • DATE: YYYY-MM-DD
  • TIME: HH:MM:SS
  • DATETIME: YYYY-MM-DD HH:MM:SS
  • TIMESTAMP: YYYYMMDDHHMMSS

Read continued...

Joins

Used to combine rows from two or more tables based on a common field between them.

INNER JOIN

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

Selects all rows from both tables as long as there is a match between the columns in both tables.

LEFT JOIN

SELECT column_name(s) FROM table1 INNER JOIN table2 ON table1.column_name=table2.column_name;

or

SELECT column_name(s) FROM table1 LEFT OUTER JOIN table2 ON table1.column_name=table2.column_name;

Returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match.

RIGHT JOIN

SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON table1.column_name=table2.column_name;

or

SELECT column_name(s) FROM table1 RIGHT OUTER JOIN table2 ON table1.column_name=table2.column_name;

Returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

FULL OUTER JOIN

SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON table1.column_name=table2.column_name;

Returns all rows from the left table (table1) and from the right table (table2) by combining the result of both LEFT and RIGHT joins.


Update

Update or edit entries in a database.

UPDATE

UPDATE table_name SET column1=value1, column2=value2,... WHERE some_column=some_value;

Used to update existing records in a table.


Delete

Remove or delete data from a database.

DELETE

DELETE FROM table_name WHERE some_column=some_value;

Used to delete records(rows) in a table.


SQL Functions

Built-in functions for performing calculations on data.

SQL Aggregate Functions

SQL aggregate functions return a single value, calculated from values in a column.

  • AVG(): returns the average value
  • COUNT(): returns the number of rows
  • FIRST(): returns the first value
  • LAST(): returns the last value
  • MAX(): returns the largest value
  • MIN(): returns the smallest value
  • SUM(): returns the sum

GROUP BY

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name;

Used in conjunction with the aggregate functions to group the result-set by one or more columns.

HAVING

SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value;

Clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

SQL Scalar Functions

SQL scalar functions return a single value, based on the input value.

  • UCASE(): converts a field to uppper case
  • LCASE(): converts a field to lower case
  • MID(): extract characters from a text field
  • LEN(): returns the length of a text field
  • ROUND(): rounds a numeric field to the number of decimals specified
  • NOW(): returns the current system date and time
  • FORMAT(): formats how a field is to be display