School of Science/ Computer Science and Information Technology
ISYS3412 Practical Database Concepts Week 7: Tute/Lab – SQL Programming
Semester 1 2020
1 Objective
The objectives of this tute/lab session are:
• Develop advanced SQL programming skills:
Ø Joining two or more tables; Ø More aggregate functions; Ø Nested queries.
1.1 PreparationTasks
You must already have completed Week 6 Tute/Lab session. If not, before attempting any of the activities in this sheet, make sure you are thoroughly competent with the basic SQL programming tasks (not the advanced activity) included in the Week 6 Tute/Lab sheet.
2 SQL Exercises
The movies database is built to store information about movies, directors, and stars (i.e. actors and actresses) in a video store. In addition, it stores information on members (who got active memberships with the video store) and their borrowing transactions.
A sketchy ER diagram for the movies database is given below.
Director
1..1 Directs
0..N 0..N
MvNumb {PK} MvTitle YrMade MvType
Crit MPA A Noms Awrd
Movie
0..N MovStar
1..1
By 0..N
1..1
Includes 0..N
Member
MmbNumb {PK} MmbName MmbAddr MmbCity MmbSt NumRent Bonus
JoinDate
DirNumb {PK} DirName DirBorn DirDied
Star
StarNumb {PK} StarName BrthPlce StarBorn StarDied
Borrow
TxnNumb {PK} BorDte
The following is the script of an imaginary scene associated with the video shop. You are required to provide the database assistance to Ben, the checkout clerk, by giving him the SQL statement for each of the query required in the discussion. Note that most of the SQL statements for answering these queries will require joining of two or more tables. Also, you will use nested SQL queries for some of the tasks.
Document: Week 7 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 15/04/202020 School/Department/Area Page 1 of 6
Jerry, a client (a member of the video rental shop) comes to the shop and is greeted by Ben.
Ben:
Good afternoon, I am Ben. May I help you, Sir?
Jerry:
Yes, please. The other day you showed me the full list of movie titles, but didn’t show me the directors who directed them. Can you show me?
Ben:
Just a second, I’ll check it in the database.
This question requires data from two tables: Movie and Director. Therefore, we must join them using the common attribute. DirNumb exists in Movie table as a foreign key, referencing Dirnumb in Director table. So, we can use that pair to join two tables. After the join, we filter movies on Director name (DirName). Use the simple JOIN .. ON within FROM clause to achieve this result.
Write a SQL statement to assist Ben to obtain required data from the database.
Furthermore, since both joining attributes have the exact same name (DirNumb) we can use NATURAL JOIN in this particular exercise.
Try the above query again, using NATURAL JOIN.
Jerry:
What an impressive collection! By the way, what are the movies directed by Woody Allen in your collection?
Ben:
Just a second, I’ll check it in the database.
This is a simple extension to the previous query. After doing the join (as in previous one), we filter on the DirName.
Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Just for curiosity. What are the movies you have in the collection, in which the director is still alive? And who are the directors of those movies?
Ben:
That’s easy, we have bio data of directors in our database.
That’s an easy query. Just extend the previous query with extra filtering condition. If DirDied attribute is blank, that means they are still alive.
Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Thanks, Ben. Great. I’d like to know more about Manhattan movie. Who are the lead actors in that movie?
Ben:
Of course. That is easy.
This requires data from Movie table and Star table. But, the difficulty is that we cannot directly join these two tables (no common attributes between them). The reason was that they came from a many-to-many relation in the underlying ER model, where were required to add extra relation in
Document: Week 7 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 15/04/202020 School/Department/Area Page 2 of 6
between. The MovStar table in our database represents this relation, which comprises of primary keys from two relations at each end of the relationship.
As a result, we are required to use three tables to retrieve required information. In this case, we join movie table with MovStar table using MvNumb attribute, and then join this result with Star table with StarNumb attribute.
Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Thank you. I am a bit curious, what are the movies that directors themselves played a role? Ben:
Interesting question! I’ll try to find out.
Hint: Even if it is the same person, dirnumb in Director relation and starnumb in Star relation may be different. So, comparing DirNumb vs StarNumb is meaningless and could result in incorrect results. Best approach is to compare the names in Director against names in Star, for each movie.
This require joining of Movie table with Director on one side and Movie table with Star on the other side. Then, match DirName and StarName attributes.
Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Thank you. Since you have information about every aspect of movies, you must have a database of directors, too. Can you show me the full list of directors, with all the movies they have directed. Ben:
Yes, I have information about directors.
This is a situation where a simple join does not produce the required output. A simple join will show the list of directors with their movies, but, it will miss the directors who haven’t directed a movie. So, you will not get the “full list” of directors. What you must use here is LEFT OUTER JOIN which displays all rows in the table in the left of the join. So, use Director in the left of the join, and Movie on the right side. Then, the query will show the desired result with NULL values to pad up Director rows with no associated movies.
Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
That’s interesting. Can you do the same for actors, too? I mean full list of actors with the movie list for each of them.
Ben:
I am here to help. Let me find out.
School/Department/Area
Document: Week 7 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 15/04/202020 Page 3 of 6
This is somewhat similar to the previous one. The difference is that we need to join three tables. Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Thanks. I know that Woody Allen nwas a famous actor in his days. Do you know his co-actors? Ben:
That’s easy to find.
That’s not as easy as Ben thinks. There are a few ways we can do it, though. One approach is to do a self-join. This is somewhat similar to what we did in the lectures to find the other languages spoken by the same people who spoke “Swahili”. First we join Star and MovieStar to obtain MvNumb’s where Woody Allen played. Then, make another pair of these two tables to of find the other actors who played in the movies that we have obtained in the first part of the query. Please note that we do not require Movie table in this join, because we are not asked to show the MvTitles in the result.
Write a SQL statement to assist Ben to obtain required data from the database.
As mentioned above, self-join is not the only way to do this query. Another approach is to use sub-queries using IN operator within WHERE clause.
The logic is as follows:
Build a query to produce a list of MvNumb’s in which Woody Allen starred.
Next, use the result from this (inner) query in IN clause in the outer query which generates the list of actors in those movies.
Re-write the above SQL statement using IN.
Another way to build this query is to use EXISTS. The logic is as follows.
Open Star and MovStar tables.
For each row in this join, pick the corresponding MvNumb
See if Woody Allen starred in that movie, if so the original related to an actor in a movie co-starred with Woody. Return that row.
You may require to do some post-processing to eliminate Woody Allen from the result.
Re-write the above SQL statement using EXISTS.
Jerry:
What is the most recent movie you have got in your collection? To extend my question, what is the oldest movie you have got?
Ben:
Why not try to find? Let’s try that.
This is a tricky one. You might think this is a simple matter of using the aggregate function MAX() on YrMade column will produce the result.
SELECT mvtitle, MAX(yrmde)
FROM movie;
But, you will get an error message – you cannot mix attribute values and aggregate functions in one SELECT clause, as shown above.
Hint: You will require a nested query where the inner query produces one value (as a result of the aggregate function). Use it in the WHERE clause in the outer query.
Jerry: My favourite director is Alfred Hitchcock. What are the movies directed by Hitchcock, but I haven’t borrowed yet?
Document: Week 7 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 15/04/202020 School/Department/Area Page 4 of 6
Ben: To answer this, I need to know your member number.
Jerry: Even if I go by the name Jerry, my real name is Thanh Tran. But I cannot recall my member number.
This is a classic example for the use of IN clause. We can break this problem in to two parts. Part 1: Generate a list of MvNumb’s for Hitchcock movies Jerry had borrowed.
Part 2: Identify the Hitchcock movies NOT IN the result set from part 1.
Write a SQL statement to assist Ben to obtain required data from the database.
This query can be written using NOT EXISTS. The logic is as follows: For each of Hitchcock movies,
Check if that movie is borrowed by Jerry.
If not, display the movie title.
Re-write the above query using NOT EXISTS.
Jerry: Among the Hitchcock movies, which one received the most academy awards?
Ben: Had Hitchcock ever received Academy awards? We can find it out from our database.
This query can be written using an aggregate function MAX. However, we still need to use a nested query to retrieve the title of the corresponding movie.
Write a SQL statement to assist Ben to obtain required data from the database.
Jerry:
Thanks Ben. You are very helpful. I’ll take these videos today. See you again next week. Ben: You are mostly welcome.
Document: Week 7 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 15/04/202020 School/Department/Area Page 5 of 6
3 Extension Work 3.1 GroupActivity
If you complete all the activities in Section 2, form yourself into groups of 4 – 5 around the table. Conferring with your colleagues, write up a new set of questions semantically similar to the above questions Ben was assisting with.
Then, swap your questionnaire with the adjoining group and write down SQL queries to answer the questions you received from them.
3.2 CreativeTask
Be a creative script writer. Extend your imagination and construct similar dialogues associated with the video shop and the movies database. Such dialogue should lead to querying the movies database, similar to above dialogues, and attempt to construct SQL commands for each query.
You are most welcome to post your creative dialogues in ‘General Discussions’ forum (without SQL queries) and invite other students to be your Database Assistant!
Document: Week 7 Tute-Lab V1.0.docx Author: Santha Sumanasekara Save Date: 15/04/202020 School/Department/Area Page 6 of 6