Page 1 of 3
© Claire Ellul
The Select Statement
The select statement is the third and final part of SQL, and can only be used
once the database and tables have been created using Data Definition
Language (DDL) and the data entered using Data Manipulation Language (DML).
It is key to extracting data from the database and using the database to answer
questions. It is the most commonly used command in SQL.
The select statement can be used both in command-line format and through
GUI based query and reporting tools. Select operations do not modify the
database content, but in some books can be found grouped together with DML
statements.
Two forms of select statements will be covered here:
Basic Information Retrieval
The basic select statement takes the form:
SELECT
FROM
WHERE
Suppose the EMPLOYEES table has the following attributes:
NI_Number
Name
Surname
Department
Grade
Salary
Page 2 of 3
© Claire Ellul
To find out information about all employees, you issue a select statement
against the table, such as
SELECT *
FROM ucfscde.EMPLOYEES
This statement will return a list of all employees in the database. The *
indicates ALL RECORDS, as follows:
NI_Number Name Surname Department Grade Salary
B 29296875 David Gower IT Consultant £25,000
A 32929302 James Smith IT Senior
Consultant
£35,000
X 29391832 James Jones HR Manager £35,550
U 38329203 Annette Smith IT Consultant £30,000
The basic select statement can also be qualified to filter out unwanted
records, such as
SELECT *
FROM ucfscde.EMPLOYEES
WHERE SURNAME = ‘Smith’
This will only return the details of all employees having surname Smith.
Another form of filtering can be done to return only some data about the
employees selected:
SELECT NI_NUMBER, NAME, SURNAME, DEPARTMENT
FROM ucfscde.EMPLOYEES
WHERE SURNAME = ‘Smith’
This will return:
NI_Number Name Surname Department
A 32929302 James Smith IT
U 38329203 Annette Smith IT
Page 3 of 3
© Claire Ellul
Aggregates
Aggregates allow you to perform sums in the database as you would in a
spreadsheet. For example:
SELECT SUM(SALARY)
FROM ucfscde.EMPLOYEES
WHERE DEPARTMENT = ‘IT’
would give a total of £90,000.
This could answer the HR question ‘How much am I paying the IT department in
salaries this year?’.
SELECT COUNT(*)
FROM ucfscde.EMPLOYEES
returns the answer 4, as there are 4 records in the table.