SQL Cheatsheet

SELECT Syntax

SELECT col_name, col_date, col_category, col_zonenum
FROM table_name
WHERE col_name LIKE '*abc*' AND
col_zonenum = 2
ORDER BY col_date ASC|DESC;

SELECT DISTINCT Syntax

SELECT DISTINCT col_name, col_date FROM table_name;

BETWEEN Syntax
For dates

SELECT * FROM Orders
WHERE OrderDate BETWEEN '2011/02/25' and '2011/02/27 23:59:59.999';

For numbers

SELECT NumOfQty FROM Orders
WHERE NumOfQty BETWEEN 2 AND 8;

HAVING Syntax
The HAVING is used in the SQL statement in cases when the WHERE keyword could not be used with aggregate functions.

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;

INSERT Syntax
Data inserted in the order of the fields in the table

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

Data inserted to specific field.

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

UPDATE Syntax

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

DELETE Syntax

DELETE FROM table_name
WHERE some_column=some_value;

SQL Joins

CREATE Database Syntax

CREATE DATABASE dbname;

CREATE Table Syntax

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

Drop Database Syntax

DROP INDEX index_name ON table_name

SQL Functions Syntax
AVG
The AVG() function returns the average value of a numeric column.

SELECT AVG(Price) AS PriceAverage FROM Products;

COUNT
The COUNT() function returns the number of rows that matches a specified criteria.

SELECT COUNT(column_name) FROM table_name;

FIRST
The FIRST() function returns the first value of the selected column.

SELECT FIRST(column_name) FROM table_name;

LAST
The LAST() function returns the last value of the selected column.

SELECT LAST(column_name) FROM table_name;

MAX
The MAX() function returns the largest value of the selected column.

SELECT MAX(column_name) FROM table_name;

MIN
The MIN() function returns the smallest value of the selected column.

SELECT MIN(column_name) FROM table_name;

SUM
The SUM() function returns the total sum of a numeric column.

SELECT SUM(column_name) FROM table_name;

UCASE
The UCASE() function converts the value of a field to uppercase.

SELECT UCASE(column_name) FROM table_name;

LCASE
The LCASE() function converts the value of a field to lowercase.

SELECT LCASE(column_name) FROM table_name;

MID
The MID() function is used to extract characters from a text field.
Example outputs first 4 letters of the “City” field. For example, if the field “City” has a value of “Singapore”, the SQL query returns “Sing”.

SELECT MID(City,1,4) AS ShortCity FROM Customers;

LEN
The LEN() function returns the length of the value in a text field.

SELECT LEN(column_name) FROM table_name;

ROUND
The ROUND() function is used to round a numeric field to the number of decimals specified.

SELECT ROUND(column_name,decimals) FROM table_name;

NOW
The NOW() function returns the current system date and time.

SELECT NOW() FROM table_name;

FORMAT
The FORMAT() function is used to format how a field is to be displayed.
Date format

SELECT FORMAT(Now(),'yyyy/MM/dd hh:mm:ss tt') FROM Products;
SELECT FORMAT(Now(),'h:m:s')
  FROM Products;                              -- Returns "1:10:47".
SELECT FORMAT(Now(),'hh:mm:ss tt')
  FROM Products;                              -- Returns "01:10:47 AM"
SELECT FORMAT(Now(),'dddd, MMM d yyyy')
  FROM Products;                              -- Returns "Thursday, Feb 22 2016"

Date numbers

SELECT FORMAT(1222.4, '##,##0.00')            -- Returns "1,222.40".
SELECT FORMAT(345.9, '###0.00')               -- Returns "345.90".
SELECT FORMAT(15, '0.00%')                    -- Returns "1500.00%".

Datatypes