SQLEX15 - The BLOCKBUSTER Database
The Blockbuster database contains managment information from a Video Hire outlet (you may remember the notion of hiring a video to watch - 'twas the way of days past) for your delight and edification.
You can CREATE this database for yourself by getting the MySQL Script and running it in your copy of PHPMyAdmin
You can use My Query Engine Page (or more interestingly create your own)
This activity contains additional SQL exercises for practice. The questions are based on the following database for a classic movie hire franchise.
Movie (movieId, movname, minutes, yr, dirId)
Director (dirId, dirname, country)
Member (memberId, memname, address, owes)
Onhire (movieId, memberId, duedate)
In Movie: minutes is the length of the movie in minutes, yr is the year the picture was made.
In Director: country is the country of origin of the director.
In Member: owes is the amount not yet paid for movie hire.
All fields are text except length and year (number), owes (currency) and duedate (date).
Write queries for each of the following based on the Blockbuster database.
- List all details about club members.
- Which movies (id) are currently on hire?
- What are the names of the movies and in which year were they made?
- List the members (ids) who currently have a movie out (no repeats).
- What country does the director David Lean come from?
- In what year was Star Wars made?
- What is the name and address of member number 5045?
- Which members owe more than $50?
- List the non American directors.
- What is the movieid and title of the movies less than 180 minutes in length?
- Which members currently do not owe anything?
- Which movies were made in the 1970s?
- List the movie (id) hired by member 5034 and due back on 14th of September.
- List members who owe between $50 and $100.
- Which movie by director 107 does not run for 137 minutes?
- Which movie(s) over 200 minutes were made before 1960?
- List the north American (US and Canada) directors.
- Use in in a query to list the English, German and French directors.
- List the members whose name begins with an F.
- Which members live at addresses that are streets (end in St)?
- List the directors called John.
- Which movies made after 1990 begin with M?
Use subqueries to complete the following:
- What country is the director of Casablanca from?
- What is the name and address of the member hiring movie 1015?
- How much is owed by the member with videos due on 6th of September?
- Who directed Glory?
- Which movies (numbers) are on hire to members who do not owe anything?
- What movies (names) are being hired by R Miller?
- What is the address of the member who has Full Metal Jacket on hire?
- List the directors of movies due back on the 12th of September.
In the questions that follow you may or may not need subqueries:
- Add 10% GST to amounts owing.
- How many years ago was each movie made (current year – year made)?
- With 12 minutes of trailers (adverts) before each movie, how long would the tape for each run?
- There has been a clerical error. In a query reduce each due date by two days.
- List all movies and length of time in hours.
- List movies in alphabetical order.
- List movies in order, longest first.
- List directors by alphabetical order of country and then within each country list the directors in alphabetical order.
- List movie names in order of being made. Within each year list the longest movie first.
- List members and amounts owing. (Do not list members who do not owe anything.)
- List in order of movie number, the number and name of movies on hire.
- List in order of age, the names of movies due back on the 12th of September.
- What is the total amount currently owing?
- What is the average length of movies?
- How many movies were made before 1980?
- How many members owe money?
- What is the name of the longest movie?
- What is the average amount owing by members who owe money?
- What is the shortest movie made by Steven Spielberg?
- What movies (names) are on hire to the member who currently owes the most?
- List the members who owe more than the average.
- How many movies are due on each date?
- How many directors are from each country?
- Which year had most movies made?
Use joins to write the following queries:
- How much is owed by members who currently have movies on hire? List in order of amount owing.
- List directors of movies made in 1986 or 1987.
- What is the name and address of members who have movies due on or before the 8th of September?
- Who currently has Zulu on hire?
- What are the names of the movies on hire to the member who currently owes the most?
- Who directed the movie L. Austin has on hire?
- List any movies by a director that are the same length in time.
- What are the names of movies that are rented by members who have more than one movie due on the same day?
|
