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 1 : Write a query which returns the titles of all the books in alphabetical order.

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 FROM BOOK.

5. Do you want to see duplicate values or are you only interested in different values?

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


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 and alphabetically.

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 FROM PERSON WHERE YEAR_OF_BIRTH > 1900.

To see the result of this query, click on "Execute query" below.




[ up  | next ]