ݺߣ

ݺߣShare a Scribd company logo

Movies questions

•
•
hccit
hccit

The document describes a 'Movies' database with four tables - Movie, Director, Member, and Onhire. It contains 16 questions to be answered using SQL queries with JOINS and SUBQUERIES on the database tables. The questions ask for information like the amount owed by members with movies on hire, directors of movies from certain years, member names and addresses with movies due on certain dates, movie names on hire for certain members, and more. It also provides an example of a self-join query and explains the need to use table aliases.

1 of 2
Download to read offline
‘Movies’ Questions The ‘Movies’ database contains four tables: Movie (movieID, movname, length, year, dirID) Director (dirID, dirname, country) Member (memberID, memname, address, owes) Onhire (movieID, memberID, duedate) All fields are text except length, year and owes (number), and duedate (date). Create and save SQL queries for the following: Use JOINS to answer these questions: 1. How much is owed by members who currently have movies on hire? List in order of amount owing. 2. List directors of movies made in 1986 or 1987. 3. What is the name and address of members who have movies due on or before the 8th of September? 4. Who currently has Zulu on hire? 5. What are the names of the movies on hire to the member who currently owes the most? 6. Who directed the movie L. Austin has on hire?
It is possible to join a table to itself. This can only be done by using different aliases for the same table. E.g.: Show any members who have the same address. Enter the following SQL query to verify it produces the desired result: select M1.memname, M2.memname, M1.address from Member M1, Member M2 where M1.address = M2.address and M1.memberID < M2.memberID Why do you think the last line is needed? Delete it and see what difference it makes. 7. List any movies by a director that are the same length in time. 8. What are the names of movies that are rented by members who have more than one movie due on the same day? (Good luck!) Use SUBQUERIES to answer these questions: 9. What country is the director of Casablanca from? 10. What is the name and address of the member hiring movie 1015? 11. How much is owed by the member with videos due on 6th September? 12. Who directed Glory? 13. Which movies (numbers) are on hire to members who do not owe anything? (HINT: use ‘is null’ in the WHERE clause) 14. What movies (names) are being hired by R Miller? 15. What is the address of the member who has Full Metal Jacket on hire? 16. List the directors of movies due back on the 12th of September.

More Related Content

Movies questions

  • 1. ‘Movies’ Questions The ‘Movies’ database contains four tables: Movie (movieID, movname, length, year, dirID) Director (dirID, dirname, country) Member (memberID, memname, address, owes) Onhire (movieID, memberID, duedate) All fields are text except length, year and owes (number), and duedate (date). Create and save SQL queries for the following: Use JOINS to answer these questions: 1. How much is owed by members who currently have movies on hire? List in order of amount owing. 2. List directors of movies made in 1986 or 1987. 3. What is the name and address of members who have movies due on or before the 8th of September? 4. Who currently has Zulu on hire? 5. What are the names of the movies on hire to the member who currently owes the most? 6. Who directed the movie L. Austin has on hire?
  • 2. It is possible to join a table to itself. This can only be done by using different aliases for the same table. E.g.: Show any members who have the same address. Enter the following SQL query to verify it produces the desired result: select M1.memname, M2.memname, M1.address from Member M1, Member M2 where M1.address = M2.address and M1.memberID < M2.memberID Why do you think the last line is needed? Delete it and see what difference it makes. 7. List any movies by a director that are the same length in time. 8. What are the names of movies that are rented by members who have more than one movie due on the same day? (Good luck!) Use SUBQUERIES to answer these questions: 9. What country is the director of Casablanca from? 10. What is the name and address of the member hiring movie 1015? 11. How much is owed by the member with videos due on 6th September? 12. Who directed Glory? 13. Which movies (numbers) are on hire to members who do not owe anything? (HINT: use ‘is null’ in the WHERE clause) 14. What movies (names) are being hired by R Miller? 15. What is the address of the member who has Full Metal Jacket on hire? 16. List the directors of movies due back on the 12th of September.