SQL Code

SELECT

specifies the attributes to be returned by the query

FROM

specifies the table(s) from which the data will be retrieved

WHERE

filters the rows of data based on provided criteria

• SELECT FROM WHERE

GROUP BY

groups the rows of data into collections based on sharing the same values in one or more attributes

• SELECT FROM WHERE GROUP BY

HAVING

filters the groups formed in the GROUP BY clause based on provided criteria

• SELECT FROM WHERE GROUP BY HAVING

ORDER BY

sorts final query result rows in ascending or descending order based on the values of one or more attributes

• SELECT, FROM, ORDER BY

JOIN USING

returns only the rows with matching values in the column indicated in the USING clause—and that column must exist in both tables

• SELECT FROM JOIN USING

JOIN ON

express a join when the tables have no common attribute names. query returns only the rows that meet the indicated join condition

• SELECT FROM JOIN ON

special operators

BETWEEN, IN, LIKE, IS NULL, NOT

aggregate functions

count, min, max, sum, avg

subquery

• query (SELECT statement inside another query)

• can return one or more values

types of subqueries

• WHERE: most common, uses an inner select on the right side of a WHERE comparison expression

• IN

• HAVING: used to restrict the output of a GROUPBY query by applying conditional criteria

• multirow (ALL and ANY)

  • ALL: compares a single value with a list of values returned by first query using comparison operator

  • ANY: compares single value to list of values and select only rows greater than or less than value in list

• FROM: specifies from which data will be drawn

Other functions

• data and time

• numeric

• string

• conversion

UNION

combines rows from two or more queries without including duplicate rows

UNION ALL

used to produce a relation that retains the duplicate rows and to unite more than just two queries

INTERSECT

can be used to combine rows from two queries, returning only the rows that appear in both sets