Objectives
- SELECT statement.
- INNER JOIN.
- NATURAL JOIN.
- OUTER JOIN.
- Recursive joining.
Motivation
Joining of tables allows us to use foreign and primary keys in order to obtain information about relationship between entities stored in DB. For example: In the Chinook database we can see the names of the albums to which tracks belong just by joining tables Track and Album on foreign key Track.albumid, which is used to reference album row for each track:
SELECT track.name, album.title
FROM album JOIN track ON album.albumId = track.albumId;
We are working on the Chinook database with the following schema.
Instructions
Step 1
Task 1.1
Get a list of all video files (the media type name contains 'video'). For verification: there should be 214 rows.
Comment
In order to get media type of track you need to join tables Track and MediaType.
Task 1.2
Get a list of all customers, which are supported by Margaret Park. In the result show customers' lastname, firstname and phone. Order list by lastname and firstname. For verification: there should by 20 rows and one name is Bjørn Hansen.
Comment
Once again for complicated task try to work in an iterative way. First try only to get combinations of employee and his supported customers. Then add the filtering clause for customers supported by Margaret Park. Then show only firstname, lastname and phone. Finally, order the results.
Step 2
As a part of this laboratory class we will practice natural join, even it is not used much in practice. Perhaps it will help you to better understand why it is not used commonly.
During last laboratory class, one task was to practice joining of tables. You were supposed to list titles of all albums from U2 artist. You needed to join tables Album and Artist on condition 'Album.artistid=Artist.artistid'. Because we can see that the columns for the foreign key and the primary key have same name, we know we can use NATURAL JOIN.
Task 2.1
Find a list of titles of all U2 albums, but use NATURAL JOIN in order to get it.
Task 2.2
Try to rewrite the solution of today's first task using NATURAL JOIN. Is it even possible? Think! If after long time thinking it is not easy for you, try to display solution:
Solution
We will not be able to solve this task using NATURAL JOIN. In this case there are not same names of columns used for foreign and primary keys but also for names of track (Track.name) and name of media type (MediaType.name).
In this case, NATURAL JOIN is trying to join tables based on same ids (Track.mediatypeid and MediaType.mediatypeid) but also based on same names of track and media type (Track.name and MediaType.name). In order for NATURAL JOIN to be able to really join this tables there would need to be track and media type with the same mediatypid and same name, which is not a case in your example. We don't have track with same name as media type name.
Task 2.3
Rewrite select for getting list of video tracks using JOIN USING. JOIN USING works similar like NATURAL JOIN, but allows use to explicitly specify names of columns used for joining of tables. Therefore it allows us to solve problem with not using columns with same names but don't represent relationship between tables (in this case it was column 'name').
Comment
Reason why we don't use NATURAL JOIN in practice is that it uses all columns with same name in order to join tables. If a person changes one table by adding a column that will match the name of column from other table, SELECT with NATURAL JOIN will start working differently than before.
Step 3
Task 3.1
Get a list of all artists who do not have an album.
Comment
Use LEFT OUTER JOIN to get all artists even ones without any album. Then you just need to filter the results and exclude all others. For verification: there should be 71 rows.
Task 3.2
Get a list of all employees showing their name (firstname and lastname in one column) and their direct superior employee. Be sure to include employee without any direct superior.
Task 3.3
Get a firstname, lastname and birthdate of the oldest employee.
Comment
To solve it you can use same principle as we used for solving problem of artists without album - combination of LEFT JOIN and filter for null value.
Comment
In addition, however, you need to realize one more thing, and that in the condition for joining does not need to be equality, but you can test even for birthdate to be larger than other birthdate.
Resources
Additional Tasks
Task A.1
Chinook
Get a list of all customers who did not order anything in 2012. Resulting list order by lastname and firstname ascending. For verification: there should be 12 rows.
Task A.2
Company
Get a list of employees and theirs spend vacation in 2016 and text informing if employee was spending or not spending his/her vacation. For example.:
| surname | name | vac_spent | Spending? |
|---|---|---|---|
| Bajny | Ivan | 23 | yes |
| Maly | Jan | 10 | yes |
| Vodny | Marian | 0 | no |
Comment
Use the DECODE function to display "yes" or "no" text for "Spending?" column.
Task A.3
Company
Get name, surname and remaining vacation of employees, when we define remaining vacation as not spent vacation from 2015 plus remaining vacation from 2016. For verification: Ivan Bajny has remaining vacation 22 days.
Task A.4
Chinook
Which playlists does not have any track with Latin genre? For verification: there should be 14 playlists.