SQL Tutorial

Previous | Home  | Next  

SQL Keywords

Introduction
FROM, SELECT, WHERE, DISTINCT
ORDER BY
AGGREGATE FUNCTIONS
GROUP BY
HAVING
INNER JOIN
LEFT JOIN

Examples

A structured approach to writing queries
Example 1
Example 2
Example 3
Example 4
Example 5
Example 6
DBWP - SQL Tutorial

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 PERSON.

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 characteristic.

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 values?

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 way?
 
No particular order is demanded in this question, so no new keywords need to be added to the query.

 




[ up  | next ]