All exercises would be based on the dataset Book:
Write SQL statements for following queries.
- List the first and last names of authors from Auckland. You must match all possible cases of the word Auckland.
- Use a SELECT statement to display the following result using the inventory and transaction type tables
- Show a list of all book titles and their types. For books without a type show ‘UKN’.
- 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
- 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.
- Write a query to list the title from the book table, and the author number, publisher code, and edition from the writing table.
- 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.
- Write a query to select the book title, publisher name, and edition for all writings.
- For each bookprice, show the book code, price, and book grade. Book grades are defined in the bookgrade table.
- For each bookprice, show the book code, book title, price, and book grade.
- 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.
- 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.
