998 SQL Syntaxes

997 SQL Quick Reference - # SQL Quick Reference from W3Schools (SQL Statement & Syntax)

Return all the columns from the table_name table:

SELECT * FROM _table_name_;

Here, column1, column2, ... are the field names of the table you want to select data from.
The table_name represents the name of the table you want to select data from.

SELECT column1, column2, ...
FROM table_name;

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.

SELECT DISTINCT column1, column2, ...
FROM table_name;

It is used to extract only those records that fulfill a specified condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

The ORDER BY keyword is used to sort the result-set in ascending or descending order.

SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;

The WHERE clause can contain one or many AND operators. The AND operator is used to filter records based on more than one condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;

The WHERE clause can contain one or more OR operators. The OR operator is used to filter records based on more than one condition.

SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;

The NOT operator is used in combination with other operators to give the opposite result, also called the negative result.

SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;

The INSERT INTO statement is used to insert new records in a table.

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

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

A field with a NULL value is a field with no value. It is not possible to test for NULL values with comparison operators, such as =, <, or <>. We will have to use the IS NULL and IS NOT NULL operators instead.

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;

The UPDATE statement is used to modify the existing records in a table.

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

The DELETE statement is used to delete existing records in a table.

DELETE FROM table_name WHERE condition;
DELETE FROM table_name;

The SELECT TOP clause is used to specify the number of records to return.

SQL Server / MS Access Syntax:

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

MySQL Syntax:

SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Oracle 12 Syntax:

SELECT column_name(s)
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS ONLY;

Older Oracle Syntax:

SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;

Older Oracle Syntax (with ORDER BY):

SELECT *
FROM (SELECT column_name(s) FROM table_name ORDER BY column_name(s))
WHERE ROWNUM <= number;

Aggregate functions are often used with the GROUP BY clause of the SELECT statement. The GROUP BY clause splits the result-set into groups of values and the aggregate function can be used to return a single value for each group.

MIN() - returns the smallest value within the selected column
MAX() - returns the largest value within the selected column
COUNT() - returns the number of rows in a set
SUM() - returns the total sum of a numerical column
AVG() - returns the average value of a numerical column

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

SELECT MIN(column_name)
FROM table_name
WHERE condition;

SELECT MAX(column_name)
FROM table_name
WHERE condition;

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

SELECT COUNT(column_name)
FROM table_name
WHERE condition;

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

SELECT SUM(column_name)
FROM table_name
WHERE condition;

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

SELECT AVG(column_name)
FROM table_name
WHERE condition;

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.

• The percent sign % represents zero, one, or multiple characters
• The underscore sign _ represents one, single character

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

SQL Wildcard Characters ****

The IN operator allows you to specify multiple values in a WHERE clause.

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

The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates. The BETWEEN operator is inclusive: begin and end values are included.

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

SQL aliases are used to give a table, or a column in a table, a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of that query. An alias is created with the AS keyword.

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;

The INNER JOIN keyword selects records that have matching values in both tables.

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

The LEFT JOIN keyword returns all records from the left table (table1), and the matching records from the right table (table2). The result is 0 records from the right side, if there is no match.

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

The RIGHT JOIN keyword returns all records from the right table (table2), and the matching records from the left table (table1). The result is 0 records from the left side, if there is no match.

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

The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records.

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

A self join is a regular join, but the table is joined with itself.

SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;

The UNION operator is used to combine the result-set of two or more SELECT statements. The UNION operator automatically removes duplicate rows from the result set.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

The UNION ALL operator is used to combine the result-set of two or more SELECT statements. The UNION ALL operator includes all rows from each statement, including any duplicates.

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);

The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result-set by one or more columns.

SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);

The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

The EXISTS operator is used to test for the existence of any record in a subquery. The EXISTS operator returns TRUE if the subquery returns one or more records.

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
  FROM table_name
  WHERE condition);

The ANY and ALL operators allow you to perform a comparison between a single column value and a range of other values.

• returns a boolean value as a result
• returns TRUE if ANY of the subquery values meet the condition

SELECT column_name(s)
FROM table_name
WHERE column_name operator ANY
  (SELECT column_name
  FROM table_name
  WHERE condition);

ALL means that the condition will be true only if the operation is true for all values in the range.

• returns a boolean value as a result
• returns TRUE if ALL of the subquery values meet the condition
• is used with SELECT, WHERE and HAVING statements

SELECT ALL column_name(s)
FROM table_name
WHERE condition;

SELECT column_name(s)
FROM table_name
WHERE column_name operator ALL
  (SELECT column_name
  FROM table_name
  WHERE condition);

The SELECT INTO statement copies data from one table into a new table.

SELECT *
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable
WHERE condition;

The INSERT INTO SELECT statement copies data from one table and inserts it into another table. The INSERT INTO SELECT statement requires that the data types in source and target tables match.

INSERT INTO table2
SELECT * FROM table1
WHERE condition;

INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;

The CASE expression goes through conditions and returns a value when the first condition is met (like an if-then-else statement). So, once a condition is true, it will stop reading and return the result. If no conditions are true, it returns the value in the ELSE clause. If there is no ELSE part and no conditions are true, it returns NULL.

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    WHEN conditionN THEN resultN
    ELSE result
END;

SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions

A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL query that you write over and over again, save it as a stored procedure, and then just call it to execute it. You can also pass parameters to a stored procedure, so that the stored procedure can act based on the parameter value(s) that is passed.

CREATE PROCEDURE procedure_name
AS
sql_statement
GO;

EXEC procedure_name;

Comments are used to explain sections of SQL statements, or to prevent execution of SQL statements.

-- Select all:

CREATE DATABASE databasename;

SHOW DATABASES

DROP DATABASE databasename;

BACKUP DATABASE databasename
TO DISK = 'filepath';

BACKUP DATABASE databasename
TO DISK = 'filepath'
WITH DIFFERENTIAL;

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

DROP TABLE table_name;

TRUNCATE TABLE table_name;

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

EXEC sp_rename 'table_name.old_name',  'new_name', 'COLUMN';

ALTER TABLE table_name
ALTER COLUMN column_name datatype;

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

ALTER TABLE table_name
MODIFY column_name datatype;

CREATE TABLE table_name (
    column1 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ....
);

NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Used to create and retrieve data from the database very quickly

CREATE INDEX index_name
ON table_name (column1, column2, ...);

CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);

DROP INDEX index_name ON table_name;

DROP INDEX table_name.index_name;

DROP INDEX index_name;

ALTER TABLE table_name
DROP INDEX index_name;

CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;

DROP VIEW view_name;