Week 2 exercises

Let's get some exercise!

Okay, now let's actually get some exercise!

Use the editor on this page to answer some questions from the Chinook database. Copy your answers into an SQL file. This is what you'll submit on Blackboard.

This is also a chance for you to practice formatting your code properly. And remember - if you have any questions, just ask me!

Questions:

1. Get all the rows in the invoice table with an invoiceId of 400 or greater, and where the billingState is listed. Sort these rows so that the invoiceId goes from highest to lowest.
2. Get 6 rows from the employee table. Sort the employees from oldest to youngest. Exclude the two oldest.
3. Get a list of cities where the store has customers, without duplicates. Make all the city names lowercased.
4. Get a list of all customers who have the letter 't' (either uppercase or lowercase) in either their first or last names. Include a column that lists the third letter of their last name.
5. Get all of the rows from the artist table that contain an o with an ülaut ('ö'). In the results table, replace this character with a smiley face (':)')
6. Get all results from the customer table. Replace null values in the state and fax columns with an empty string.
7. Return all album titles longer than 20 characters. Only display the first twenty characters, and make all characters lowercase.
8. Get all invoices from Berlin where the total is more than $5, and the invoices from Toronto where the total is more than $5. Sort from newest to oldest. Remember: if there is more than one way to write your code, you should stop and think about which way is best.