SQL Exercise 01

All exercises would be based on the dataset Book:

Write SQL statements for following queries.

1. List the first and last names of authors from Auckland. You must match all possible cases of the word Auckland.
2. Use a SELECT statement to display the following result using the inventory and transaction type tables
3. Show a list of all book titles and their types. For books without a type show ‘UKN’.
4. Complete the query below to show the revised salary of staff based on their role. Use the following as a guide:                                                                                                         – Branch Manager: 90% of original salary                                                                               – Sales Person: no change                                                                                                   – —     – Office admin: 115% of original salary                                                                                   – Hint: Check out how to use the CASE expression from SQLite documentation
5. Select the title from the book table, and the author number, publisher code, and edition from the writing table. Don’t use a join condition – i.e. no WHERE clause. This will display the result of a Cartesian product.
6. Write a query to list the title from the book table, and the author number, publisher code, and edition from the writing table.
7. Write a query to list the title and book code from the book table, and the book code, author number, publisher code, and edition from the writing table.
8. Write a query to select the book title, publisher name, and edition for all writings.
9. For each bookprice, show the book code, price, and book grade. Book grades are defined in the bookgrade table.
10.  For each bookprice, show the book code, book title, price, and book grade.
11. Create a query to list book inventory transactions. Show the book title, transaction number, date, and quantity. Show all book titles even if they don’t have any transactions.
12. Create a query to list book inventory transactions involving quantities less than 100. Show the book title, transaction number, date, and quantity. Treat all book titles without any transactions as having a quantity of 0, which means they should be included in the query as well.