SQL Query

The SELECT statement is an SQL statement that constructs a query which returns a modified subset of the table it is used in. It belongs to the data manipulation subset of SQL.

A table that consists of no references is a base table, and queries are tables constructed from such base tables.

SELECT [* | (ColumnA, ColumnB)]
	FROM [table_name | table_list]
	WHERE [row_condition(s)]
	LIKE [regex_expression]
	GROUP BY {column} [ASC | DESC]
	HAVING [group_condition]
	ORDER BY {column | expression | position} [ASC | DESC]
	LIMIT [n] OFFSET [a]

• SELECT: Queries all or a subset of columns from table_name or table_list. It operates in a similar fashion to projection (and annoyingly enough, not selection!) in relational algebra - ${\pi }_{\text{(columnA, columnB)}}(\text{table})$.

• FROM: Sets the table(s) to be queried from. If multiple tables are given, performs a cross product first.

• WHERE: Uses row condition(s) to determine which rows to be added to the final table. Operates like relational algebra’s selection - ${\sigma }_{(\text{row condition})}(\text{table})$

  • LIKE: Uses regex to match columns:
    • % is match any number of characters
    • _ is match one character

• GROUP BY: Defines column(s) which can be used to group the final rows. Useful when applying aggregate functions because the aggregate function is then applied onto the group, instead of the entire function

  • HAVING: Same principle as WHERE, but applies to groups instead. Aggregate functions can be used here.

• ORDER BY: Defines sorting columns which the rows are sorted by, either in ASC (ascending) or DESC (descending). If not provided, there is no guarantee on the ordering of the resultant table.

• LIMIT: Only shows the first n rows. OFFSET can be used to ‘skip’ the first a rows, i.e. LIMIT 5 OFFSET 3 shows rows 4,5,6,7,8

• AS : Similar to the renaming operator in relational algebra, renames columns with the syntax: oldName AS newName