Querying tables with SELECT

1.1 A simple request

The SELECT statement is used to query one or more rows of one or more tables.

Whereas the other instructions, seen so far, have only a very small number of clauses, the SELECT statement is the one that understands the most and is the very fact, the most complex SQL statement.

A simple SELECT query looks like this:

SELECT {list of columns | *} 
FROM table [WHERE conditional expression];

Columns can appear in any order. Using the (*) character makes appear all the columns of the table, in the order they exist in the table.

What are the outputs made with the ABC product?
SELECT *
FROM products
WHERE product_code = 'ABC';

1.2 The SELECT clause

To say that the SELECT clause presents a list of columns is a bit limiting. Anything which mathematical expression, function or value can be indicated instead of a column.

SELECT product_code, (quantity * cost * 1.07), 'dollars' 
FROM product;

For the sake of clarity, we can, for the benefit of a query, change the name of a column by adding an alias. It is placed immediately after the name of a column, or a expression of columns (e.g. a calculation), and is separated by a space.

SELECT quantity * cost * 1.07 price 
FROM product;

You can also use the optional keyword AS to express the definition of the alias.

SELECT quantity * cost * 1.07 AS price 
FROM product;

1.3 Eliminate Duplicates

Sometimes, a query may display repetitive lines (at least in appearance), especially when the primary key is not part of the displayed columns.

These repetitions are eliminated by using the DISTINCT keyword in the SELECT clause at the beginning from the list of columns.

SELECT
[DISTINCT] list of columns

Any row with the same values ​​as another row already presented will be ignored.

Remark

DISTINCT operates on the entire row and not on one column at a time.

1.4 Sorting results

To sort the results of a SELECT one must use the ORDER BY clause which must be placed at the very end of the instruction.

ORDER BY list
of columns

By default, the sort will be ascending. It is possible to modify this criterion, column by column.

Just add the DESC keyword after the column you want to sort.

List the names of employees in descending chronological order of birth dates.
SELECT name, birthdate
FROM employee
ORDER BY
birthdate DESC;

Remark

  • The ORDER BY clause always appears last.
  • The column (s) used must also be present in the SELECT clause (some DBMS allow to use columns for sorting that are not present in the SELECT).
  • Column names can be replaced by numbers that represent their order of presentation in the SELECT clause.
  • We can sort on a combination of several columns.
  • You can use an ascending or descending order, variable to each column.
  • By default, the ascending order is implicit.

Print Friendly, PDF & Email

Leave a Reply