|1. Which table(s) contains the information that you need?
The tables that contain the information that we will work with should
be mentioned after FROM. When you start to compose a query, it is normally
best to start with the FROM statement. The command FROM [Name of Table]
initially selects all the rows and all the columns in the table(s) that
you mention. The answer to the first question in this exercise is evidently:
BOOK. The titles of the books can be found in the table BOOK. So, at this
stage we can write down FROM BOOK
2. Are you interested in all the records in this/these table(s)?
The exercise also clearly mentions that "all the books" should
be displayed, so the answer to this question is "yes".
If the answer to question 2 is positive, no further action is needed. You
can then simply move on to the next question.
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?
Question 3 and 4 do not apply in the case of this query. We do not want
aggregate information about the records but we want the actual records themselves.
The titles of the books apparently need to be displayed.
4. Which columns do you want to see?
This step should help you to compile the select list.
The select list refers to those columns that should be stated after SELECT
and which will thus be included in the result of the query. The answer to
this question can normally be derived directly from the exercise. If you
are interested in all the columns in the table book, then you can write
down SELECT * FROM BOOK. The asterisk functions as a wildcard which
selects all the columns which are available at this stage. In this exercise,
we only want to see the titles. The information that is contained in the
other columns can be ignored. Our query can be expanded into SELECT TITLE
5. Do you want to see duplicate values or are you only interested in different
Under certain circumstances, there will be many similar
results in your query. If you simply want to know which different values
are present, you need to write the word DISTINCT directly after the SELECT
command in order to exclude the occurrence of double values. If two identical
rows are present, then DISTINCT will delete the duplicated values. In this
exercise, we want to see all the values, even if the same value appears
in different records. DISTINCT is not needed in this exercise.
6. Do the records in the result set need to be sorted in any particular
The exercise specifies that the books need to be
ordered in alphabetical order. To accomplish this, the keywords ORDER
BY can be used. ORDER BY should be followed by the name of the column
which must be used as the basis for the sorting operation. The column(s)
that you mention after ORDER BY does not necessarily have to be the same
as the column(s) that you mention after SELECT. It is also possible to
mention more than one column after ORDER BY. These names of columns should
then be separated by a comma. The rows will then initially be sorted by
the first columns that you mention. If certain columns contain the same
value, the records in these sections will be sorted again by the second
column that you mention, and so on. The order in which you mention these
attributes is thus important. In this exercise, we want to sort the records
only by their title. This means that we should append ORDER BY TITLE
to our query. You can also use ASC or DESC to indicaste whether it needs
to be an ascending or a Descending order. SQL can sort both numerically
As we have now answered all the questions, we are now able to write the full query:
SELECT NAME_FIRST, NAME_LAST, YEAR_OF_BIRTH
WHERE YEAR_OF_BIRTH > 1900.
To see the result of this query, click on "Execute query" below.