Part A - Single and multiple table queries Value: 10% For the first five questions you have to provide your answer in the form of a SQL statement, with description before as necessary. For example: 1. Example Question: Select all columns from the `Customer` table Example Answer: -- This answer will select out all the columns from the Customer table SELECT * from Customer; Questions 1 – 5: 1. List in ascending order the Last Names of all borrowers with Card Number less than 150. 2. What are the records of those loans between 3 June 2014 and 8 Mar 2018? ◦ The output should include the name of the cardholder, and title of the book ◦ List in ascending order by last name and first name 3. Find the total numbers of loans with last name beginning with D through E (including E); include last name beginning with Q as well as Z also. ◦ The output of the above question should be a column containing the sum of all the loan based on the condition in the query, alternatively the output could be a table with columns that has the last name as per the condition in the query condition, also the table contains the list of loans. 4. Find all borrowers for a loan that have the Date-in before 15-March-15 and the Card number between 100 and 300 in Karama or Darwin. ◦ The output for the above question is a list of all borrowers (first name and last name) based on the condition in the query. 5. Find the number of loans, which have been made from each branch? ◦ Note all branches need to be included even those with no loans ◦ The output of the above question is a table containing the list of loans only as per the condition in the query

-- This query will select and list in ascending order the Last Names of all borrowers with Card Number less than 150
SELECT Last_Name FROM Borrowers
WHERE Card_Number < 150
ORDER BY Last_Name ASC;

-- This query will select the records of those loans between 3 June 2014 and 8 Mar 2018, including the name of the cardholder, and title of the book, listed in ascending order by last name and first name
SELECT Cardholder_Name, Book_Title FROM Loans
WHERE Loan_Date BETWEEN '2014-06-03' AND '2018-03-08'
ORDER BY Cardholder_Last_Name ASC, Cardholder_First_Name ASC;

-- This query will find the total numbers of loans with last name beginning with D through E (including E); include last name beginning with Q as well as Z also.
SELECT COUNT(*) AS Total_Loans, Last_Name FROM Loans
WHERE Last_Name LIKE 'D%' OR Last_Name LIKE 'E%' OR Last_Name LIKE 'Q%' OR Last_Name LIKE 'Z%'
GROUP BY Last_Name;

-- This query will find all borrowers for a loan that have the Date-in before 15-March-15 and the Card number between 100 and 300 in Karama or Darwin.
SELECT First_Name, Last_Name FROM Borrowers
WHERE Date_in < '2015-03-15' AND Card_Number BETWEEN 100 AND 300 AND (Branch = 'Karama' OR Branch = 'Darwin');

-- This query will find the number of loans, which have been made from each branch, including those with no loans
SELECT COUNT(*) AS Number_of_Loans, Branch FROM Loans
GROUP BY Branch;