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;