[SOLVED] database information retrieval gui Page 1 of 3

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.