SELECT Statement

Describes the SELECT statement and basic query options

The SELECT statement finds and retrieves rows, columns, and derived values from one or more tables of a database. The SELECT statement is flexible, with many options, and accepts column specifications, search conditions, ordering instructions, and other parameters. The powerful and complex SELECT statement is a core feature of SQL and a thorough description of it well exceeds the scope of this document. This section outlines basic SELECT syntax and clauses which suffice for most simple queries.

Syntax

A SELECT operation may retrieve one or many records, so in PBL it is commonly placed within a for each loop, as follows:

for each <variable> in
    SELECT [DISTINCT | ALL] <column1>, <column2>,...
    FROM <table1>, <table2>, ...
    [WHERE <condition>]
    [GROUP BY <grouping-column1>, <grouping-column2>, ...]
    [HAVING <group-selection-condition1>]
    [ORDER BY <ordering-col1> [ASC | DESC], 
              <ordering-col2> [ASC | DESC], ...]
do
	   // ...
end

The columns to be retrieved are delimited by commas or, alternatively, an asterisk (*) may be used to retrieve all columns from every table queried. It is recommended that you specify each column you need rather than retrieving all of them, as this will improve performance, substantially if the table is large and contains many columns you do not need.

You can request that a column be returned under an alias by using the AS clause:

SELECT clientId, fn AS firstName FROM clients

This selects clientId and fn from the database, but it will return the columns as clientId and firstName. In this way, you will be able to access the column using firstName in your code rather than fn, so it will be easier to read.

A column can also be an expression or an aggregate function. Aggregate functions combine values from every row into a single value, such as a sum or an average, and are discussed below.

You may use the ORDER BY clause to sort the results of the query according to a given value. Ordering may be ascending (ASC), or descending (DESC). Ascending order is the default and need not be specified. You can order by one or more columns, delimited by commas. Sorting is first done on the first ORDER BY column, and subsequent ORDER BY columns are used when the previous column contains equal values.

The following example displays the name of every employee with a salary higher than 25,000:

for each e in
    SELECT *
    FROM employees
    WHERE salary > 25000
    ORDER BY lname
do
    display "employee name: " + e.lname + ", " + e.fname
end

Using Aggregate Functions

The following example selects the maximum salary in the employee table using the MAX function. The row.1 term is used to specify the first column. The variable salary is used to store the result of the maximum salary in the table:

for each row in
    SELECT MAX(salary)
    FROM employees
do
    salary = row.1
end

The following example returns the average salary of employees grouped by depnumber, but does not return employees where depnumber is equal to 3 or 4, or cases where 5 or fewer employees have the same depnumber value:

for each e in
    SELECT depnumber, COUNT(*), AVG(salary)
    FROM employees
    WHERE depnumber != 3 and depnumber !=4
    GROUP BY depnumber
    HAVING COUNT(*) > 5
    ORDER BY depnumber
do
    // ...
end