Part A - Single and multiple table queries1. 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
HIT 234 Assignment 1
Part A - Single and multiple table queries
List in ascending order the Last Names of all borrowers with Card Number less than 150.
SELECT BORROWER.LNAME FROM BORROWER
WHERE BORROWER.CARDNO < 150
ORDER BY BORROWER.LNAME ASC
RESULT: 1DAGG
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
SELECT BORROWER.LNAME, BORROWER.FNAME, BRANCH.BRANCHNAME, BOOK.TITLE FROM BORROWER, BRANCH, BOOK, LOAN
WHERE BORROWER.CARDNO = LOAN.CARDNO AND LOAN.ISBN = BOOK.ISBN
AND LOAN.DATEOUT BETWEEN '03/JUN/14' AND '08/MAR/18' ORDER BY BORROWER.LNAME, BORROWER.FNAME ASC
RESULT: 16
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.
SELECT COUNT (*) FROM BORROWER WHERE LNAME LIKE 'D%' OR LNAME LIKE 'E%'
OR LNAME LIKE 'Q%' OR LNAME LIKE 'Z%'
Result: 25
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.
SELECT BORROWER.FNAME, BORROWER.LNAME FROM BORROWER, LOAN WHERE BORROWER.CARDNO = LOAN.CARDNO
AND LOAN.DATEIN <= '15/MAR/15' AND BORROWER.SUBURB = 'KARAMA' AND LOAN.CARDNO < 200
Result: 0
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
SELECT
BRANCH.BRANCHNAME AS BRANCHNAME, (
SELECT COUNT (LOAN.BRANCHID) FROM LOAN
WHERE BRANCH.BRANCHID = LOAN.BRANCHID
) AS LOANCOUNT FROM BRANCH
Result: 8
6. Create your own question based on the library database, and also provide a SQL Statement to answer your question.
Question: - What are the records of those loans between 3 June 2014 and 6 March 2018?
•The output should include the name of the card holder, and title of the book.
•List in ascending order by last name and first name
ANSWER:-
SELECT BORROWER.LNAME, BORROWER.FNAME, BRANCH.BRANCHNAME, BOOK.TITLE FROM BORROWER, BRANCH, BOOK, LOAN
WHERE BORROWER.CARDNO = LOAN.CARDNO AND LOAN.ISBN = BOOK.ISBN
AND LOAN.DATEOUT BETWEEN '03/JUN/14' AND '06/MAR/18' ORDER BY BORROWER.LNAME, BORROWER.FNAME ASC
Result: 16
Part C – All Topics
1.What are the 5 most popular books?
◦ Show ASBN, Title.
◦ HINT: To limit results use ROWNUM.
SELECT LOAN.ISBN, BOOK.TITLE
FROM LOAN, BOOK
WHERE LOAN.ISBN=BOOK.ISBN
AND ROWNUM<=5;
2. Which branches currently have no loans out? ◦ Show all branch names. ◦ List in ascending order by branch
SELECT branchname FROM branch where branchid not in (select distinct branchid from loan l)
order by branchname asc;
3. Find the longest time a book has been loaned out for each branch
◦ Show only branch name and number of days.
◦ List in ascending order by branch name.
◦ Show all branches, even if there are no loaned out books. Therefore, a branch may have a null value for the longest loaded out book.
◦ Format the number of days to one decimal place.
SELECT BRANCH.BRANCHNAME, (SELECT MAX(LOAN.DATEIN - LOAN.DATEOUT) FROM LOAN WHERE LOAN.BRANCHID = BRANCH.BRANCHID) AS "Number of Days" FROM BRANCH ORDER BY BRANCH.BRANCHNAME ASC;
4. Find the borrower that has returned a book on the dated 16 December 2010 and 8 May 2012 ◦ Show two different methods.
SELECT DISTINCT B.LNAME, B.FNAME, L.DATEIN FROM BORROWER B, LOAN L WHERE B.CARDNO=L.CARDNO AND L.DATEIN= DATE '2010-12-16' OR L.DATEIN= DATE '2012-05-08' ;
5. Devise a delete query, which will eliminate duplicates for customer columns `ID`, `CustomerLastName`, `CustomerFirstName` and invoice columns `Invoice_Date` and `Invoice_No`. ◦ Ensure the query removes all duplicate data.
DELETE FROM CDUINVOICE WHERE rowid in (SELECT rowid
FROM(SELECT ROWID,ROW_NUMBER() OVER (PARTITION BY id ORDER BY ROWID ) AS DUPLICATE FROM CDUINVOICE)
WHERE DUPLICATE>1);
SELECT *
FROM CDUCUSTOMER
