Query Description Example
INSERT INTO INSERT INTO table_name (column1, column2, column3, …) VALUES (value1, value2, value3, …);
Null values SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
UPDATE UPDATE table_name SET column1 = value1, column2 = value2, … WHERE condition;
DELETE DELETE FROM table_name WHERE condition;
SELECT TOP, LIMIT, ROWNUM MySQL SELECT ColumnName(s) FROM MyTable WHERE condition LIMIT 5;
SELECT TOP, LIMIT, ROWNUM Oracle SELECT column_name(s) FROM MyTable WHERE ROWNUM <= number;
SELECT TOP num PERCENT SELECT TOP 10 PERCENT * FROM Customers;
MIN, MAX, AVG, COUNT, SUM Numeric column for min, max, avg, sum. SELECT AVG(MyColumn) FROM MyTable;
Wildcards •% – The percent sign represents zero, one, or multiple characters WHERE MyColumn LIKE ‘a%o’
Wildcards •_ – The underscore represents a single character
IN SELECT column_name(s) FROM table_name WHERE column_name IN (value1, value2, …)/(SELECT STATEMENT);
BETWEEN Inclusive SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2;
Aliases It requires double quotation marks or square brackets if the alias name contains spaces. SELECT column_name AS alias_name FROM table_name;
CONCAT SELECT CustomerName, CONCAT(Address,’, ‘,PostalCode,’, ‘,City,’, ‘,Country) AS Address FROM Customers;
Joins LEFT – all rows from left hand table, RIGHT – all rows from rigfht hand table SELECT Customers.CustomerName, Orders.OrderID FROM Customers LEFT JOIN Orders ON Customers.CustomerID=Orders.CustomerID;
Self join Table is joined with itself. SELECT column_name(s) FROM table1 T1, table1 T2 WHERE condition;
UNION Combine the result-set of two or more SELECT statements. Each SELECT statement must have the same number and order of columns. Columns must have similar data types. SELECT
UNION ALL Faster, since doesn’t have to check for duplicates.
GROUP BY Often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG). SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
HAVING Used with aggregate functions (COUNT, MAX, MIN, SUM, AVG). SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;
EXISTS Test for the existence of any record in a subquery. SELECT column_name(s) FROM table_name WHERE EXISTS (SELECT column_name FROM table_name WHERE condition);
ANY, ALL True if subquery values meet the condition. Note: The operator must be a standard comparison operator (=, <>, !=, >, >=, <, or <=). SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);
SELECT INTO The new table will be created with the column-names and types as defined in the old table. Tip: SELECT INTO can also be used to create a new, empty table using the schema of another. Just add a WHERE clause that causes the query to return no data. SELECT * INTO MyNewTable [IN ExternalDB] FROM MyOldTable WHERE SomeCondition;
INSERT INTO SELECT Copies data from one table and inserts it into another table: data types in source and target tables must match, existing records in the target table are unaffected INSERT INTO MyTable1 (ColumnName1, ColumnName2, ColumnName3) SELECT ColumnName4, ColumnName2, ColumnName3 FROM MyTable2;
CASE Like an IF-THEN-ELSE statement. See screenshot 1
Null functions Function lets you return an alternative value if an expression is NULL (IFNULL, COALESCE, ISNULL, IsNull, NVL) SELECT MyColumn * (MyColumn2 + ISNULL(MyColumn3, 0)) FROM MyTable
Comments — comment, /* comment */