Notes

Part 1
--1
SELECT bc.No_Of_Copies
FROM BOOK_COPIES bc
WHERE bc.BranchId = (
SELECT lb.BranchId
FROM LIBRARY_BRANCH lb
WHERE lb.BranchName = 'Sharpstown'
)
AND bc.BookId = (
SELECT b.BookId
FROM BOOK b
WHERE Title = 'The Lost Tribe'
);

--2
SELECT lb.BranchName, bc.No_Of_Copies
FROM BOOK_COPIES bc
JOIN LIBRARY_BRANCH lb ON bc.BranchId = lb.BranchId
JOIN BOOK b ON bc.BookId = (
SELECT b.BookId
FROM BOOK b
WHERE b.Title = 'The Lost Tribe'
);

--3
SELECT b.Name
FROM BORROWER b
LEFT JOIN BOOK_LOANS bl ON b.CardNo = bl.CardNo
WHERE b.CardNo != bl.CardNo;

--4
SELECT b.Title, bo.Name, bo.Address
FROM BOOK_LOANS bl
JOIN BOOK b ON bl.BookId = b.BookId
JOIN BORROWER bo ON bl.CardNo = bo.CardNo
WHERE bl.BranchId = (
SELECT lb.BranchId
FROM LIBRARY_BRANCH lb
WHERE lb.BranchName = 'Sharpstown')
AND bl.DueDate = SYSDATE;

--5
SELECT lb.BranchName, COUNT(bl.BranchId)
FROM LIBRARY_BRANCH lb
JOIN BOOK_LOANS bl ON lb.BranchId = bl.BranchId
GROUP BY lb.BranchName;

--6
SELECT bo.Name, bo.Address, COUNT(bl.CardNo)
FROM BORROWER bo
JOIN BOOK_LOANS bl ON bo.CardNo = bl.CardNo
GROUP BY bo.Name, bo.Address
HAVING COUNT(bl.CardNo) > 5;

--7
SELECT b.Title, bc.No_Of_Copies
FROM BOOK b
JOIN BOOK_COPIES bc ON b.BookId = bc.BookId
JOIN BOOK_AUTHORS ba ON b.BookId = ba.BookId
JOIN LIBRARY_BRANCH lb ON bc.BranchId = lb.BranchId
WHERE ba.AuthorName = 'Stephen King'
AND lb.BranchName = 'Central';

Part 2
--8
SELECT e.FNAME, e.LNAME
FROM EMPLOYEE e
JOIN WORKS_ON wo ON e.SSN = wo.ESSN
JOIN PROJECT p ON wo.PNO = p.PNUMBER
WHERE e.DNO = 5
AND p.PNAME = 'ProductX'
AND wo.HOURS > 10;

--9
SELECT p.PNAME, SUM(wo.HOURS)
FROM PROJECT p
JOIN WORKS_ON wo ON p.PNUMBER = wo.PHO
GROUP BY p.NAME;

--10
SELECT e.FNAME, e.LNAME
FROM EMPLOYEE e
JOIN WORKS_ON wo ON e.SSN = wo.ESSN
JOIN PROJECT p ON wo.PNO = p.PNUMBER
WHERE COUNT(DISTINCT wo.ESSN) = COUNT(p.PNUMBER);

--11
SELECT e.FNAME, e.LNAME
FROM EMPLOYEE e
LEFT JOIN WORKS_ON wo ON e.SSN = wo.ESSN
WHERE e.SSN != wo.ESSN;

--12
SELECT e.FNAME, e.LNAME, e.ADDRESS
FROM EMPLOYEE e
JOIN DEPARTMENT d ON e.DNO = d.DNUMBER
JOIN DEPT_LOCATIONS dl ON d.DNUMBER = dl.DNUMBER
JOIN WORKS_ON wo ON e.SSN = wo.ESSN
JOIN PROJECT p ON wo.PNO = p.PNUMBER
WHERE p.PLOCATION = 'HOUSTON'
AND dl.DLOCATION != 'HOUSTON';

--13
SELECT e.LNAME
FROM EMPLOYEE e
JOIN DEPARTMENT d ON e.DNO = e.d.DNUMBER
JOIN DEPENDENT de ON e.SNN = de.ESSN
WHERE d.MGRSSN = e.SSN
AND e.SSN != de.ESSN;

--14
SELECT e.SALARY
FROM EMPLOYEE e
WHERE e.SALARY > AVG(e.SALARY)
ORDER BY SALARY DESC;

--15
SELECT e.SALARY
FROM EMPLOYEE e
JOIN DEPARTMENT d ON e.DNO = d.DNUMBER
WHERE e.SALARY > (
SELECT AVG(e.SALARY)
FROM EMPLOYEE e
WHERE e.DNO = d.DNUMBER
)
ORDER BY SALARY ASC;