CP363 : SQL SELECT

Function

To retrieve information from a database.

Syntax

SELECT [ ALL | DISTINCT ] expression [ [ AS ] alias-name ] | *
...[ FROM table-list ]
...[ WHERE search-condition ]
...[ GROUP BY column-name, ... ]
...[ HAVING search-condition ]
...[ ORDER BY { expression | integer } [ ASC | DESC ], ... ]

Description

The SELECT statement retrieves data from the database.

Examples


SELECT * FROM member

Select all values of all tuples from the table member.


SELECT last_name, first_name FROM member
  ORDER BY last_name, first_name

Selects the values of the last_name and first_name attributes for all tuples in member, and displays them in order by first name within last name.


SELECT last_name, first_name, address FROM member
  WHERE last_name = 'Copp'
  ORDER BY last_name, first_name

Selects the values of the last_name, first_name, and address attributes for all tuples where the member's last name is 'Copp'.


SELECT last_name, first_name, address FROM member
  WHERE last_name like 'C%'
  ORDER BY last_name, first_name

Selects the values of the last_name, first_name, and address attributes for all tuples where the member's last name begins with a 'C'.


SELECT Date_Borrowed, COUNT( Date_Borrowed ) FROM Borrowed
  GROUP BY Date_Borrowed
  WHERE Date_Borrowed >= '1999/01/01'
  HAVING COUNT( Date_Borrowed ) > 100
  ORDER BY Date_Borrowed

Lists the dates and number of books borrowed on those dates for dates after January 1st, 1999, where the number of books borrowed on that date was more than 100. The WHERE clause selects individual tuples, while the HAVING clause selects tuples according to their aggregate values of particular attributes; in this case based upon the COUNT clause.


SELECT DISTINCT Date_Borrowed FROM Borrowed

Generates a list of unique dates when books were borrowed. No matter how many times a particular date appears in the Borrowed table, it appears only once in the output list due to the DISTINCT keyword. (This can significantly slow a selection down).