EXAMPLE 2 : Write a query which returns the full names (last and first name)
and the year of birth of all persons born after 1900.
1. Which table(s) contains the information that you need?
In this exercise, we need information about persons and the years in which
they were born. Both of these items of information can be found in the the
table PERSON. The required information can be found in the columns NAME_FIRST,
NAME_LAST and YEAR_OF_BIRTH. For this reason, we can simply write down FROM
2. Ar you interested in all the records in this/these table(s)?
It is clear from the question that the query should
not return all the persons. The query should only return those persons
who were born after 1900. In other words, a person can only be included
in the result set on the condition that he or she was born after 1900.
To state such a condition, you can use the WHERE command. This command
will be used to evaluate certain records. WHERE should be followed by
a certain expression. In this exercise, we obviously need to include WHERE
YEAR_OF_BIRTH > 1900. For each row in the table, the value that
has been entered in the column YEAR_OF_BIRTH will be substituted in the
condition. If the resulting expression can be evaluated as true (obviously
in the case of years after 1900, e.g. 1910 > 1900), the row will be
selected. Otherwise, if the expression is evaluated as FALSE (e.g. 1890
> 1900) the row will be ignored. The WHERE command thus functions as
a filter. It only leaves those only those records which share a certain
The WHERE section may also contain some more complicated conditions.
You can combine any number of criteria using boolean operators such as
AND or OR. If you combine AND and OR, you need to use brackets in order
to indicate the order in which the conditions should be considered. This
is because there no fixed priority rules in SQL. Empty fields (fields
which do not contain any value) are referred to as NULL. If you want to
exclude records with empty field, you can a statement such as WHERE
[name of column] IS NOT NULL. Not that a comparison with NULL should
always take place with the IS or the IS NOT operator.
In this exercise, the query that we have arrived at at this stage is
FROM PERSON WHERE YEAR_OF_BIRTH > 1900
3. Are you interested in the actual contents of the records or in statistical
information about the records in the table? If so, do you want to receive
one answer for the entire table, or would you like to receive different
answers for different types of records?
We are interested in the actual data. The full names of the persons in the
table PERSON should appear in the result set.
4. Which columns do you want to see?
The WHERE command can be used to get rid of certain rows in a table, and
SELECT can be used to filter the columns. Those columns that you do NOT
mention will be deleted from the resulting table. The full names of the
persons should appear, so we should select both NAME_FIRST and NAME_LAST.
An updated version of the query is SELECT NAME_FIRST, NAME_LAST, YEAR_OF_BIRTH
FROM PERSON WHERE YEAR_OF_BIRTH > 1900.
5. Do you want to see duplicate values or are you only interested in different
All the records which remain at this stage should be displayed, even if
the result set contains duplicate values. It might occasionally be the case
that different persons have the same name, but, as they have been given
different primary keys, the assumption will be that are different persons.
6. Do the records in the result set need to be sorted in any particular
No particular order is demanded in this question, so no new keywords need
to be added to the query.