SQL Exercise 01 Solution

--Q1
SELECT Author_FirstName, Author_LastName FROM Author WHERE UPPER(Author_City) = 'AUCKLAND';

--Q2
SELECT 'In transaction ' || Transaction_No || ', ' || Quantity || ' books were ' || LOWER(Transaction_Type) AS TRANSACTIONS FROM Inventory i, TransactionType t WHERE i.Transaction_Type_ID = t.Transaction_Type_ID AND Transaction_No <= 10;

--Q3
SELECT Book_Title, IFNULL(Book_Type, 'UKN') AS Book_Type FROM Book;

--Q4
--Without Join
SELECT Staff_Code, Role_ID, Salary, CASE
WHEN Role_ID = 1 THEN Salary*0.9
WHEN Role_ID = 2 THEN Salary
WHEN Role_ID = 3 THEN Salary*1.15
END AS Revised_Salary
FROM StaffAssignment;

--With Join
SELECT Staff_Code, Role, Salary, CASE
WHEN LOWER(Role) = 'branch manager' THEN Salary*0.9
WHEN LOWER(Role) = 'sales person' THEN Salary
WHEN LOWER(Role) = 'office admin' THEN Salary*1.15
END AS Revised_Salary
FROM StaffAssignment s, Role r
WHERE s.Role_ID = r.Role_ID;

--Q5
SELECT Book_Title, a.Author_Number, p.Publisher_Code, Edition FROM Book, Author a, Publisher p, Writing;

--Q6
SELECT Book_Title, a.Author_Number, p.Publisher_Code, Edition
FROM Book b, Author a, Publisher p, Writing w
WHERE b.Book_Code = w.Book_Code
AND a.Author_Number = w.Author_Number
AND p.Publisher_Code = w.Publisher_Code;

--Q7
SELECT Book_Title, b.Book_Code, w.Book_Code, Author_Number, Publisher_Code, Edition
FROM Book b, Writing w
WHERE b.Book_Code = w.Book_Code;

--Q8
SELECT Book_Title, Publisher_Name, Edition FROM Book b, Publisher p, Writing w WHERE b.Book_Code = w.Book_Code AND p.Publisher_Code = w.Publisher_Code;

--Q9
SELECT Book_Code, Price, Book_Grade
FROM BookPrice, BookGrade
WHERE Price BETWEEN MIN_VALUE AND MAX_VALUE;

--Q10
SELECT p.Book_Code, Book_Title, Price, Book_Grade
FROM BookPrice p, Book b, BookGrade
WHERE Price BETWEEN MIN_VALUE AND MAX_VALUE
AND p.Book_Code = b.Book_Code;

--Q11
SELECT Book_Title, Transaction_No, Transaction_Date, Quantity
FROM Book b LEFT OUTER JOIN Inventory i
ON b.Book_Code = i.Book_Code;

--Q12
SELECT Book_Title, Transaction_No, Transaction_Date, Quantity
FROM Book b LEFT OUTER JOIN Inventory i
ON b.Book_Code = i.Book_Code
WHERE IFNULL(Quantity,0) < 100;

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.