SQL Exercise 01

All exercises would be based on the dataset Book:

1

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s