SQL Query Reference

SQL Queries have 3 basic parts:


1. The Statement

Essentially determines the purpose of the SQL query - to retrieve records, delete them, change them, etc.

2. The Clause(s)

Determines which records are affected by the query and how they are sorted -and can also perform a number of other functions, including grouping of identical records.

3. The Operation(s)

Actually perform operations on the records, such as combining or joining result sets.

Clauses


FROM Clause
Determines the tables in the database that the query is to affect/retrieve.


WHERE Clause
Determines the selection criteria to be used.


ORDER BY Clause

Determines the field(s) to be used to sort the results of a SELECT or SELECT...INTO statement, together with the order – either 'ASC' (ascending) or ‘DESC’ descending).

GROUP BY Clause

Determines the field(s) to be used for the grouping of the results of a SELECT or SELECT...INTO statement. 

HAVING Clause

Specifies which grouped records are displayed in a SELECT statement with a GROUP BY clause.

IN Clause
Identifies tables in any external database to which the database engine can connect.


Operations


UNION Operation

Used in combination with both Statements and Clauses for combining the results of two or more independent queries or tables.

INNER JOIN Operation:


LEFT JOIN Operation


RIGHT JOIN


SQL Sub Queries

A subquery is a SELECT statement nested inside a SELECT, SELECT...INTO, INSERT...INTO, DELETE, or UPDATE statement or inside another subquery. You can use three forms of syntax to create a subquery:

comparison [ANY | ALL | SOME] (sqlstatement )
expression [NOT] IN (sqlstatement )
[NOT] EXISTS (sqlstatement )

comparison An expression and a comparison operator that compares the expression with the results of the subquery.

expression An expression for which the result set of the subquery is searched. 

sqlstatement A SELECT statement, following the same format and rules as any other SELECT statement. It must be enclosed in parentheses.

You can use a subquery instead of an expression in the field list of a SELECT statement or in a WHERE or HAVING clause. In a subquery, you use a SELECT statement to provide a set of one or more specific values to evaluate in the WHERE or HAVING clause expression. 

Use the ANY or SOME predicate, which are synonymous, to retrieve records in the main query that satisfy the comparison with any records retrieved in the subquery. The following example returns all products whose unit price is greater than that of any product sold at a discount of 25 percent or more:

SELECT * FROM Products
WHERE UnitPrice > ANY
(SELECT UnitPrice FROM OrderDetails
WHERE [Discount] >= .25);

Use the ALL predicate to retrieve only those records in the main query that satisfy the comparison with all records retrieved in the subquery. If you changed ANY to ALL in the above example, the query would return only those products whose unit price is greater than that of all products sold at a discount of 25 percent or more.

This is much more restrictive.

Use the IN predicate to retrieve only those records in the main query for which some record in the subquery contains an equal value. The following example returns all products sold at a discount of 25 percent or more:

SELECT * FROM Products
WHERE ProductID IN
(SELECT ProductID FROM OrderDetails
WHERE [Discount] >= .25);

Conversely, you can use NOT IN to retrieve only those records in the main query for which no record in the subquery contains an equal value.

 Use the EXISTS predicate (with the optional NOT reserved word) in true/false comparisons to determine whether the subquery returns any records. 

You can also use table name aliases in a subquery to refer to tables listed in a FROM clause outside the subquery. The following example returns the names of employees whose salaries are equal to or greater than the average salary of all employees with the same job title. The Employees table is given the alias “T1":

SELECT LastName,
FirstName, Title, Salary
FROM Employees AS T1
WHERE Salary >=
(SELECT Avg(Salary)
FROM Employees
WHERE T1.Title = Employees.Title) Order by Title;


The AS reserved word is optional.


Examples
SELECT LastName, FirstName, Title, Salary
FROM Employees
WHERE Title LIKE "*Sales Rep*" AND Salary > ALL
(SELECT Salary FROM Employees
WHERE (Title LIKE "*Manager*") OR (Title LIKE "*Director*"));

Lists the name, title, and salary of every sales representative whose salary is higher than that of all managers and directors.

SELECT DISTINCTROW ProductName, UnitPrice
FROM Products WHERE UnitPrice =
(SELECT UnitPrice FROM [Products]
WHERE ProductName = "Aniseed Syrup");


Lists the name and unit price of every product whose unit price is the same as that of Aniseed Syrup.


SELECT DISTINCTROW [Contact Name], CompanyName, [Contact Title], Phone
FROM Customers WHERE CustomerID IN
(SELECT DISTINCTROW CustomerID
FROM Orders WHERE OrderDate BETWEEN #04/1/94# AND #07/1/94#);


Lists the company and contact of every customer who placed an order in the second quarter of 1994.


SELECT LastName, FirstName, Title, Salary
FROM Employees T1
WHERE Salary >=
(SELECT AVG(Salary)
FROM Employees
WHERE Employees.Title = T1.Title)
ORDER BY Title;


Lists employees whose salary is higher than the average salary for all employees.


SELECT FirstName, LastName
FROM Employees AS E
WHERE EXISTS
(SELECT *
FROM Orders AS O
WHERE O.EmployeeID = E.EmployeeID);

Selects the name of every employee who has booked at least one order. This could also be done with an INNER JOIN.