SQL Tutorial

Previous | Home  | Next  

SQL Keywords



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

EXAMPLE 3 : Write a query which, for all the movies whose director is known, returns the titles of all the movies and the full names of their directors.

1. Which table(s) contains the information that you need?

The situation here is slightly different than in the previous two examples. To be able to show the names of movies, you obviously need to consult the table FILM. Here is a short fragment from that table:

The titles of the movies can clearly be extracted from this table. The directors are mentioned in the column DIRECTOR. In this exercise, you are asked to show the names of all the directors. This is were we run into difficulties. The full names of the directors are not in this table. The column DIRECOR only contains numbers. These numbers, as can be seen from the database definition, refer to the primary keys in the table PERSON. Here is a fragment from the table PERSON:

Each record in the table PERSON has been given a unique code in P_ID. This code can be used to uniquely address this record. The numbers which are given in the column DIRECTOR in the table FILM do indeed uniquely address a single row in the table PERSON. The column DIRECTOR should thus be interpreted as follows: Movie 1 (One Wonderful Night, 1914) is directed by person whose primary key P_ID is 7 (Elisha Calvert). Movie 2 (One Wonderful Night, 1944) is directed by the person whose primary key is 9 (Stuart Paton), and so on.

To be able to complete the assignment, you should be able to combine the information from these two tables. The full details of the person that is referred to by the number in the column DIRECTOR should be available in the table FILM. This can be achieved in SQL by using a so-called INNER JOIN. Creating such a join involves two steps :

  1. After FROM, you mention the two tables that need to be combined
  2. After WHERE, you explain how these two tables are linked.

In the vast majority of the cases, the relationship will be based on the fact that theprimary key in one table functions as a foreign key in the second table. In our current exercise, the primary key P_ID functions as a foreign key in the column DIRECTOR. Thus, to link FILM and PERSON we need to state: FROM PERSON, FILM WHERE P_ID = DIRECTOR.

The result of this JOIN is that, if a match is found between the value in the column DIRECTOR in FILM and the value in P_ID in PERSON, the record from person simply be "glued" to the record in the table FILM. This will produce a rather long result set whic looks as follows:

Note that, in this join, the value in DIRECTOR (originally from FILM) is exactly the same as the value in P_ID (originally from PERSON). If there is no match between FK and PK, the row will not be shown. Not all records will be shown in an INNER JOIN. This is also why the exercise has included the phrase "for all the movies whose directors are known".
Here, you can clearly see the efficiency of relational databases at work. The full details for each person will need to be recorded only once, and it is possible to refer to these details by making use of a simple and short code, the primary key. An SQL query can then combine information from different tables. If one person has directed many movies, the full details of this person can still be displayed next to each movie. Whereas the details are maintained centrally in the table PERSON, these details can be copied and displayed as many times as is necessary in the result of a query.

2. Are you interested in all the records in this/these table(s)?

All the records which have been joined in this way need to be displayed. Apart from the fact that there should be a match between FK and PK, no further criterium should be used to filter the number of records.

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?

This step can be skipped in this exercise.

5. Which columns do you want to see?

As can be derived directly from the exercise, the titles of the movies and the full names of the directors need to be displayed in the result of the query. All the columns which appear in the joined table (see above) are available for you to be select. In this exercise, we need to choose TITLE, NAME_FIRST and NAME_LAST.

6. Do you want to see duplicate values or are you only interested in different values? 7. Do the records in the result set need to be sorted in any particular way?
Duplicates may appear, and the records do not need to be sorted in the display.


[ up  | next ]