Introduction

The whole point of a database is to be able extract data from it. SQL's SELECT statement is the workhorse of the language. The following is the syntax and some example code.

Syntax

SELECT Syntax

The SELECT statement retrieves data from the database.

          
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 ], ... ]

member SELECT

              
SELECT * FROM member

Selects all values from member.

member SELECT with Projection

                  
SELECT memberSurname, memberForename FROM member
  ORDER BY memberSurname, memberForename

Selects the members last and first name attributes for all tuples in member, and displays them in order by first name within last name.

member Selection and Projection

                  
SELECT memberSurname, memberForename FROM member
  WHERE memberSurname = 'Copp'

Selects the members last and first name attributes for all tuples in member where the member's last name is 'Copp'.

member Selection and Projection with LIKE

                  
SELECT memberSurname, memberForename, memberAddress FROM member
  WHERE memberSurname LIKE 'C%'
  ORDER BY memberSurname, memberForename

Selects the members last and first name attributes and address attribute for all tuples in member where the member's last name begins with a 'C'.

dateBorrowed Selection and Projection with GROUP, COUNT, and HAVING

                  
SELECT dateBorrowed, COUNT(dateBorrowed)
  FROM borrowed
  WHERE dateBorrowed >= '2024-01-01'
  GROUP BY dateBorrowed
  HAVING COUNT(dateBorrowed) > 100
  ORDER BY dateBorrowed

Lists the dates and number of books borrowed on those dates for dates after January 1st, 2024, 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. The GROUP clause provides the aggregation. The COUNT clause determines how many tuples are in each group.

dateBorrowed Projection with DISTINCT

                  
SELECT DISTINCT dateBorrowed FROM borrowed

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

SQL SELECT

Introduction

Syntax