Joining tables

Instructions

1. Task: Get a list of all video files (the media type name contains 'video'). For verification: there should be 214 rows.

   Note: In order to get media type of track you need to join tables Track and MediaType.

   Task: 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.

   Note: 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.

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: Find a list of titles of all U2 albums, but use NATURAL JOIN in order to get it.

   Task: 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:

   Result:

   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.

   Show resultHide result

   Task: 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').

   Note: 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.

3. Task: Get a list of all artists who do not have an album.

   Note: 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: 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: Get a firstname, lastname and birthdate of the oldest employee.

   Note: 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.

   Note: 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.