18 lines
725 B
SQL
18 lines
725 B
SQL
SELECT DISTINCT firstname, lastname, isbn, fine_paid
|
|
FROM library.dbo.member
|
|
JOIN library.dbo.loanhist ON member.member_no = loanhist.member_no
|
|
WHERE fine_paid IN (SELECT MAX(fine_paid) FROM library.dbo.loanhist)
|
|
ORDER BY isbn;
|
|
|
|
SELECT title, title.title_no, reservation.isbn, COUNT(reservation.isbn) AS 'Total reserved'
|
|
FROM library.dbo.title
|
|
JOIN library.dbo.loan ON title.title_no = loan.title_no
|
|
JOIN library.dbo.reservation ON loan.isbn = reservation.isbn
|
|
WHERE title.title_no IN
|
|
(
|
|
SELECT title_no FROM library.dbo.loan
|
|
JOIN library.dbo.reservation ON loan.isbn = reservation.isbn
|
|
GROUP BY title_no
|
|
HAVING COUNT(reservation.isbn) < 5 OR COUNT(reservation.isbn) > 50
|
|
)
|
|
GROUP BY title, title.title_no, reservation.isbn |