Objectives
- Practice SELECT with subqueries
- Try the IN and EXISTS operators
- Try subqueries for modification statements
Motivation
We are working on Chinook database with the following schema.
Instructions
Step 1
Task 1.1
Find the name of longest track in the store. For verification: it's length in milliseconds is 5286953.
Task 1.2
Find the longest track in album titled 'War' from 'U2'.
Comment
Again, try to think in steps. Try to find the length of the longest track in War album from U2. Then write select to find the track name in album War if you know its length. And finally connect these two queries into one query with subquery.
Comment
Do not forget that the same length as the longest track from War album can also have some other track from other album.
Step 2
Task 2.1
Get a list of tracks which where never purchased. For verification: there should be 1519 tracks.
Comment
Those that were not purchased are not in any invoice line.
Task 2.2
List the names of all songs that do not belong to the Music Videos playlist. For verification: there should be 3502 tracks.
Comment
Write 2 select versions. One with using (NOT) IN operator and second one with (NOT) EXISTS operator.
Step 3
Task 3.1
Get information about customer who spent the most money in our store (the sum of his invoices is the highest). Get his id,first name, last name and total amount. For verification: total amount is $49,62.
Comment
If you seriously don't know, you can try displaying result hint, but I recommend that you first think it thoroughly.
First get a list of customers with total amount of their spent money, order them by the amount of money and than use write outer select to choose only the first row of result (this time the subquery will be in the FROM clause).
Of course there are several ways to solve this task, you can also use aggregation functions.
Task 3.2
Get a list of first 10 unique names of tracks from playlist 'TV Shows' ordered alphabetically.
Comment
This time you will need to use the ROWNUM pseudo-column (see lecture 6 - Select statement).
Step 4
You can use subquery even for modification statements (INSERT, UPDATE, DELETE). Using such subquery we can modify the database dynamically based on its current state.
Task 4.1
Write UPDATE for discounting by 20% all tracks, which have higher price than average. For verification: it should change 213 records.
Comment
Solve this task in a transaction which will not be commited but rollbacked. Next tasks will be using data without these UPDATE in mind, therefore some verification information could be impacted if you commit this transaction. Therefore use ROLLBACK statement afterward!
Resources
Additional Tasks
Task A.1
Chinook
Get a list of tracks, which have been bought at least 100 times. For verification: there is actually 0 tracks which have been bought 100 times, but there is 256 tracks which have been bought 2 times.
Task A.2
Chinook
Find out which of playlists is the longest (there can be more with the same length). For verification: there are 2, both are named 'Music', one with id 1 and second with id 8.
Task A.3
Chinook
Add the 'premium' column to the 'customer' table, which will fulfill the following:
- it will be just one character from the set {'T', 'F'}, so the customer can be either a premium or not;
- be default each customer is not a premium (value 'F'); and
- value in column is required.
Task A.4
Chinook
Write UPDATE with a subquery that sets the 'premium' flag to 'T' (makes customer premium, as it can be useful for example to providing discounts for loyal (premium) customers) to customers who have spend more than $40 in the store.
Comment
Again, if you do not know, you can display the hint (but I recommend that you first think about it seriously). Once again try to think incrementaly. How do you find out which customers have spend more than 40 dollars? You will need to join customers with their invoice line rows. There you need to calculate sum of all sold items by grouping it by customer id (price of one item is quantity*unitprice). Now you need just to filter everybody who has spend more than $40. That way you will get customer IDs and you can use them for UPDATE statement in order to set 'premium' to 'T'.