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

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:

  1. Which table(s) contains the information that you need?
  2. Are you interested in all the records in this/these table(s)?
  3. 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?
  4. Which columns do you want to see?
  5. Do you want to see duplicate values or are you only interested in different values?
  6. 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 ]