SQL Commands & Syntax

SQL Sub-languages

SQL is categorized into several sub-languages based on functionality:

• DDL (Data Definition Language): Deals with database schemas and descriptions. Commands: CREATE, ALTER, DROP, TRUNCATE, RENAME.
• DML (Data Manipulation Language): Deals with data manipulation. Commands: SELECT, INSERT, UPDATE, DELETE, MERGE.
• DCL (Data Control Language): Concerned with rights, permissions and other controls. Commands: GRANT, REVOKE.
• TCL (Transaction Control Language): Deals with transactions within a database. Commands: COMMIT, ROLLBACK, SAVEPOINT.

SELECT, DISTINCT & WHERE Clause

The SELECT statement is used to select data from a database. SELECT DISTINCT returns only distinct (different) values. WHERE is used to filter records.

SELECT DISTINCT column1, column2 FROM table_name WHERE condition;

AND, OR and NOT

The WHERE clause can be combined with AND, OR, and NOT operators.

• The AND operator displays a record if all the conditions separated by AND are TRUE.
• The OR operator displays a record if any of the conditions separated by OR is TRUE.
• The NOT operator displays a record if the condition(s) is NOT TRUE.

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

ORDER BY

The ORDER BY keyword is used to sort the result-set in ascending or descending order. It sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword.

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

INSERT INTO

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

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

NULL 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;

UPDATE & DELETE

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

UPDATE table_name SET column1 = value1 WHERE condition;
DELETE FROM table_name WHERE condition;

SELECT TOP

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

SELECT TOP number|percent column_name(s) FROM table_name WHERE condition;
-- Or using LIMIT:
SELECT column_name(s) FROM table_name LIMIT number;

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value.

• MIN(): Returns the smallest value of the selected column.
• MAX(): Returns the largest value of the selected column.
• COUNT(): Returns the number of rows that matches a specified criterion.
• AVG(): Returns the average value of a numeric column.
• SUM(): Returns the total sum of a numeric column.

LIKE Operator

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 FROM table_name WHERE columnN LIKE pattern;

IN & BETWEEN

The IN operator allows you to specify multiple values in a WHERE clause (shorthand for multiple OR conditions). The BETWEEN operator selects values within a given range (inclusive).

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

Joins in SQL

A JOIN clause is used to combine rows from two or more tables, based on a related column between them.

• INNER JOIN: Selects records that have matching values in both tables.
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matching records from the right table.
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matching records from the left table.
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table records.

UNION

The UNION operator is used to combine the result-set of two or more SELECT statements. Every SELECT statement within UNION must have the same number of columns, similar data types, and in the same order. UNION selects only distinct values by default. To allow duplicate values, use UNION ALL.

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

GROUP BY & HAVING

GROUP BY groups rows that have the same values into summary rows. HAVING is used because the WHERE keyword cannot be used with aggregate functions.

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

DDL Commands (CREATE, DROP, ALTER, TRUNCATE)

Data Definition Language commands are used to define the database structure.

• CREATE DATABASE databasename; - creates a new database.
• DROP DATABASE databasename; - drops an existing database.
• CREATE TABLE table_name (column1 datatype, …); - creates a new table.
• DROP TABLE table_name; - drops an existing table.
• TRUNCATE TABLE table_name; - deletes the data inside a table, but not the table itself.
• ALTER TABLE table_name ADD column_name datatype; - adds, deletes, or modifies columns in an existing table.