The aim of this second part of the tutorial is to show how to write queries in SQL in a structured
way. A method will be presented in this tutorial
which should enable you to arrive at the correct query through a step-by-step
process. When you are asked to write a query, it can be useful to follow a number of
steps. These steps involve answering the following 6 questions:
- Which table(s) contains the information that you need?
- Are you interested in all the records in this/these table(s)?
- Are you interested in the actual contents of the records or in statistical
information about the records in the table? If so, would you like to receive
one answer for the entire table, or would you like to receive different answers
for different types of records?
- Which columns do you want to see?
- Do you want to see duplicate values or are you only interested in different
- Do the records in the result set need to be sorted in any particular way?
These steps, to a large extent, mirror the way in which incoming queries are
processed by a DBMS. In general, it might be said that, in such situations,
there is always a progression from the general to the more specific. Database
programs normally inspect the FROM command first. This command will initially
select all the rows and all the columns in a certain table or in a certain combination
of tables. All the other SQL commands and keywords, such as SELECT, WHERE, GROUP
BY, HAVING, ORDER BY can then be used to get rid of certain records or of certain
column, or to sort or to organise the records in these rtables. A similar progression
underlies the method that is described here.
This tutorial will demonstrate that almost any query can be composed by always
following these six steps in the exact same order. Each question should give
you a clue as to if and how the various SQL commands should be used. Question
1 helps you to compile the FROM clause. If the answer to
question 2 if "no", then this should prompt you into using the WHERE
command. The answer to question 3 should give information on how to use GROUP
BY and HAVING. In step 4, you produce the SELECT
section of the query. Finally, questions 5 and 6 will help you to decide whether
or not DISTINCT or ORDER BY must
be used. Be aware that the order in which you answer these questions does not
correspond to the order in which the command should eventually appear in the
SQL query. The type of queries that will be covered in this tutorial (SQL consists
of course of many more commands and keywords, but these fall outside the scope
of this tutorial) should follow the following formula :
SELECT [DISTINCT] <names of columns>
FROM <names of tables>
[WHERE <condition> ]
[GROUP BY <names of columns> ]
[HAVING <condition> ]
[ORDER BY <names of columns> [ASC | DESC] ]
The square brackets indicate that the SQL command in question is optional.
Note that SELECT and FROM will appear in each SQL query. Operators such as '>' (greater than), '<' (less than), '>='
(greater than or equal to), '<=' (less than or equal to) can only be used
in a WHERE or a HAVING expression.
The validity of the method that is described above will be illustrated on the
basis of six examples. The examples in this part of the course make use of another
database than the first part. It is a database which can store information about
a booktrade. It contains the following tables: BOOK, PERSON, FILM, COMPANY,
EMPLOYMENT, SERIES, SERIALISATION, CITY, COUNTRY and LANGUAGE.
[ up | next ]