Subqueries

Instructions

1. Task: Find the name of longest track in the store. For verification: it's length in milliseconds is 5286953.

   Task: Find the longest track in album titled 'War' from 'U2'.

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

   Note: Do not forget that the same length as the longest track from War album can also have some other track from other album.

2. Task: Get a list of tracks which where never purchased. For verification: there should be 1519 tracks.

   Note: Those that were not purchased are not in any invoice line.

   Task: List the names of all songs that do not belong to the Music Videos playlist. For verification: there should be 3502 tracks.

   Note: Write 2 select versions. One with using (NOT) IN operator and second one with (NOT) EXISTS operator.

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

   Note: If you seriously don't know, you can try displaying result hint, but I recommend that you first think it thoroughly.

   Result:

   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.

   Show resultHide result

   Task: Get a list of first 10 unique names of tracks from playlist 'TV Shows' ordered alphabetically.

   Note: This time you will need to use the ROWNUM pseudo-column (see lecture 6 - Select statement).

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: Write UPDATE for discounting by 20% all tracks, which have higher price than average. For verification: it should change 213 records.

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