Commonly Used SQL Clauses
The following list describes the SQL clauses that you will use most frequently. In relation to the previous SQL statements, the WHERE clause is used with the DELETE, UPDATE, and SELECT statements; the remaining clauses are only used with the SELECT statement.
FROM
FROM table_name [,...]
Description:
The FROM clause specifies the tables from which a statement should retrieve data. You can combine the FROM clause with AS to assign aliases to table names. For example, to assign an alias of products to a table called us_products, you use the following syntax:
FROM us_products AS products.
WHERE
WHERE Boolean_expression
Description:
The WHERE clause restricts a statement so that it only returns those rows or records for which a Boolean expression that you define evaluates as true. For example, to delete only those rows from a table where a record has an employee name of John Smith, you execute the following:
DELETE FROM employee_table WHERE employee_name = 'John Smith'
GROUP BY
GROUP BY column_name [,...]
Description:
The GROUP BY clause divides the result of a SELECT statement into logical groups. The clause allocates data to these groups by grouping data that has identical values for the column names you specify. Typically, you do not use this clause to group the data that is displayed (use the ORDER BY clause to achieve this), but instead you use it together with an aggregate function, such as MAX, AVG, or COUNT. For example, to get a breakdown of how many employees live in each state, you issue the following command:
SELECT COUNT(*) FROM employee_table GROUP BY home_state
HAVING
HAVING search_condition
Description:
The HAVING clause works with the GROUP BY clause in much the same way as the WHERE clause works with the SELECT statement. It allows a statement to return only items within groups that conform to a condition the HAVING clause defines. For example, the following command lists the average employee salary for all departments where the average salary is greater than 30,000 dollars:
SELECT department, AVG(salary) FROM employee_table GROUP BY department HAVING AVG(salary)>30000
ORDER BY
ORDER BY { (column_name | column_position) [ASC | DESC]] }[,...]
Description:
The ORDER BY clause defines the order in which a statement returns a result set. The ASC and DESC options dictate whether the statement should return the results in ascending or descending order, respectively; ascending order is the default. The column_name is the name of the column by which to sort. The column position allows you to stipulate an ordinal position that relates to the column names or aliases you specify when creating the statement. For example, the following statement returns all the employee forenames and surnames from an employee table and orders the results by surname in descending order: