Sort the results book number and then author figure

56 CODE
Write a query that displays the book title, cost and year of publication for every book in the system. Sort the results by book title.

SELECT BOOK_TITLE, BOOK_COST, BOOK_YEAR FROM BOOK
ORDER BY BOOK_TITLE;

59 CODE
Write a query to display the book number, book title, and subject for every book sorted by book number (Figure P7.59). (20 rows)

SELECT BOOK_NUM, BOOK_TITLE AS TITLE, BOOK_SUBJECT AS "Subject of Book" FROM BOOK
ORDER BY BOOK_NUM;

62 CODE
Write a query to display the book number, title, and cost of each book sorted by book number (Partial results are shown in Figure P7.62).

SELECT BOOK_NUM, BOOK_TITLE, BOOK_COST FROM BOOK
ORDER BY BOOK_NUM;

CODE 65
Write a query to display the book number, title, and cost for all books that cost $59.95 sorted by book number (Figure P7.65).

SELECT BOOK_NUM, BOOK_TITLE, BOOK_COST FROM BOOK
WHERE BOOK_COST=59.95
ORDER BY BOOK_NUM;

CODE 68
Write a query to display the book number, title, and year of all books published after 2015 and on the “Programming” subject sorted by book number (Figure P7.68).

SELECT BOOK_NUM, BOOK_TITLE, BOOK_YEAR
FROM BOOK
WHERE BOOK_YEAR > '2015' and BOOK_SUBJECT = 'Programming' ORDER BY BOOK_NUM;

SELECT BOOK_NUM, BOOK_TITLE, BOOK_SUBJECT FROM BOOK
WHERE BOOK_TITLE LIKE '%Database%'
ORDER BY BOOK_NUM;

CODE 72
Write a query to display the patron ID, first and last name of all patrons who are students, sorted by patron ID (Figure P7.72). (44 rows)

SELECT AU_ID, AU_FNAME, AU_LNAME
FROM AUTHOR
WHERE AU_BIRTHYEAR IS NULL
ORDER BY AU_ID;

CODE 75
Write a query to display the author ID, first and last name of all authors whose year of birth is known. Ensure the results are sorted by author ID (Figure P7.75).

SELECT AU_ID, AU_FNAME, AU_LNAME, AU_BIRTHYEAR FROM AUTHOR
ORDER BY AU_BIRTHYEAR DESC, AU_LNAME

CODE 78
Write a query to display the number of books in the FACT system (Figure P7.78).

SELECT Count(BOOK_NUM) AS "Available Books" FROM BOOK
WHERE PAT_ID IS NULL

CODE 81
Write a query to display the highest book cost in the system (Figure P7.81).

SELECT Count(DISTINCT PAT_ID) AS "DIFFERENT PATRONS" FROM CHECKOUT;

CODE 84
Write a query to display the subject and the number of books in each subject. Sort the results by the number of books in descending order and then by subject name in ascending order (Figure P7.84).

SELECT SUM(BOOK_COST) AS "Library Value" FROM BOOK;

Question 87:
Write a query to display the patron ID, book number, and days kept for each checkout. “Days Kept” is the difference from the date on which the book is returned to the date it was checked out. Sort the results by days kept in descending order, then by patron ID, and then by book number (Figure P7.87). (68 rows)
SELECT PAT_ID AS "PATRON", BOOK_NUM AS "BOOK", CHECK_IN_DATE ­ CHECK_OUT_DATE AS "Days Kept"
FROM CHECKOUT
ORDER BY CHECK_IN_DATE ­ CHECK_OUT_DATE DESC, PAT_ID, BOOK_NUM;

CODE 90
Write a query to display the author last name, author first name, and book number for each book written by that author. Sort the results by author last name, first name, and then book number (Figure P7.90). (25 rows)
SELECT AU_LNAME, AU_FNAME, BOOK_NUM
FROM AUTHOR
JOIN WRITES ON AUTHOR.AU_ID = WRITES.AU_ID
ORDER BY AU_LNAME, AU_FNAME, BOOK_NUM

CODE 91
Write a query to display the author ID, book number, title, and subject for each book. Sort the results by book number and then author ID (Figure P7.91). (25 rows)
SELECT AU_ID, BOOK.BOOK_NUM, BOOK_TITLE, BOOK_SUBJECT
FROM BOOK
JOIN WRITES ON BOOK.BOOK_NUM = WRITES.BOOK_NUM
ORDER BY BOOK_NUM, AU_ID;

CODE 96
Write a query to display the author ID, first and last name, book number, and book title of all books in the subject “Cloud”. Sort the results by book title and then by author last name (Figure P7.96).

SELECT AUTHOR.AU_ID, AU_FNAME, AU_LNAME, BOOK.BOOK_NUM, BOOK_TITLE
FROM AUTHOR
JOIN WRITES ON AUTHOR.AU_ID = WRITES.AU_ID
JOIN BOOK ON WRITES.BOOK_NUM = BOOK.BOOK_NUM
WHERE BOOK_SUBJECT = "Cloud"
ORDER BY BOOK_TITLE, AU_LNAME;

CODE 99
Write a query to display the book number, title, and number of times each book has been checked out. Limit the results to books that have been checked out more than five times. Sort the results in descending order by the number of times checked out and then by title (Figure P7.99).

SELECT BOOK.BOOK_NUM, BOOK_TITLE, Count(CHECK_NUM) AS "Times Checked Out"
FROM BOOK
JOIN CHECKOUT ON BOOK.BOOK_NUM = CHECKOUT.BOOK_NUM
GROUP BY BOOK_NUM, BOOK_TITLE
HAVING Count(CHECK_NUM) > 5
ORDER BY Count(CHECK_NUM) DESC, BOOK_TITLE;

CODE 104
Write a query to display the patron ID and the average number of days that patron keeps books during a checkout. Limit the results to only patrons who have at least three checkouts. Sort the results in descending order by the average days the book is kept, and then in ascending order by patron ID (Figure P7.104).

SELECT PATRON.PAT_ID, ROUND(AVG(CHECK_IN_DATE-CHECK_OUT_DATE),2) AS "Average Days Kept"
FROM CHECKOUT
JOIN PATRON ON CHECKOUT.PAT_ID = PATRON.PAT_ID
GROUP BY PAT_ID
HAVING COUNT(CHECK_NUM)>=3
ORDER BY ROUND(AVG(CHECK_IN_DATE-CHECK_OUT_DATE),2) DESC,
PATRON.PAT_ID;