Stetson University BSAN 398 Chapter 7
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;
57 CODE
Write a query that displays the first and last name of every patron, sorted by last name and
then first name. Ensure the sort is case insensitive (Figure P7.57). (50 rows)
SELECT PAT_FNAME, PAT_LNAME
FROM PATRON
ORDER BY PAT_LNAME, PAT_FNAME;
58 CODE
Write a query to display the checkout number, checkout date, and due date for every
book that has been checked out sorted by checkout number (Figure P7.58). (68
rows)
SELECT CHECK_NUM, CHECK_OUT_DATE, CHECK_DUE_DATE
FROM CHECKOUT
ORDER BY CHECK_NUM;
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;
60 CODE
Write a query to display the different years in which books have been published. Include
each year only once and sort the results by year (Figure P7.60).
SELECT DISTINCT BOOK_YEAR
FROM BOOK
ORDER BY BOOK_YEAR;
61 CODE
Write a query to display the checkout number, book number, patron ID, checkout date, and
due date for every checkout that has ever occurred in the system. Sort the results by
checkout date in descending order and then by checkout number in ascending order (Partial
results are shown in Figure P7.63).
SELECT CHECK_NUM, BOOK_NUM, PAT_ID, CHECK_OUT_DATE,
CHECK_DUE_DATE
FROM CHECKOUT
ORDER BY CHECK_OUT_DATE DESC, CHECK_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 63
Write a query to display the checkout number, book number, patron ID, checkout
date, and due date for every checkout that has ever occurred in the system. Sort the
results by checkout date in descending order and then by checkout number in
ascending order (Partial results are shown in Figure P7.63)
SELECT CHECK_NUM, BOOK_NUM, PAT_ID, CHECK_OUT_DATE,
CHECK_DUE_DATE
FROM CHECKOUT
ORDER BY CHECK_OUT_DATE DESC, CHECK_NUM;
CODE 64
Write a query to display the book title, year, and subject for every book. Sort the results by
book subject in ascending order, year in descending order, and then title in ascending order
(Partial results are shown in Figure P7.64).
SELECT BOOK_TITLE, BOOK_YEAR, BOOK_SUBJECT
FROM BOOK
ORDER BY BOOK_SUBJECT, BOOK_YEAR DESC, BOOK_TITLE;
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 66
Write a query to display the book number, title, and cost for all books in the “Database”
subject sorted by book number (Figure P7.66).
SELECT BOOK_NUM, BOOK_TITLE, BOOK_COST
FROM BOOK
WHERE BOOK_SUBJECT="Database"
ORDER BY BOOK_NUM;
CODE 67
Write a query to display the checkout number, book number, and checkout date of all books
checked out before April 5, 2017 sorted by checkout number (Figure P7.67).
SELECT CHECK_NUM, BOOK_NUM, CHECK_OUT_DATE
FROM CHECKOUT
WHERE CHECK_OUT_DATE < "2017-04-05"
ORDER BY CHECK_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;
Code 69
Write a query to display the book number, title, subject, and cost for all books that are on
the subjects of “Middleware” or “Cloud”, and that cost more than $70 sorted by book
number (Figure P7.69).
SELECT BOOK_NUM, BOOK_TITLE, BOOK_SUBJECT, BOOK_COST
FROM BOOK
WHERE (BOOK_SUBJECT = 'Middleware' OR BOOK_SUBJECT = 'Cloud') AND
BOOK_COST > 70
ORDER BY BOOK_NUM;
CODE 70
Write a query to display the author ID, first name, last name, and year of birth for all authors
born in the decade of the 1980s sorted by author
SELECT AU_ID, AU_FNAME, AU_LNAME, AU_BIRTHYEAR
FROM AUTHOR
WHERE AU_BIRTHYEAR BETWEEN 1980 AND 1989
ORDER BY AU_ID
CODE 71
Write a query to display the book number, title, and subject for all books that contain
the word “Database” in the title, regardless of how it is capitalized. Sort the results by
book number (Figure P7.71).
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 PAT_ID, PAT_FNAME, PAT_LNAME
FROM PATRON
WHERE PAT_TYPE = 'Student'
ORDER BY PAT_ID
CODE 73
Write a query to display the patron ID, first and last name, and patron type for all patrons
whose last name begins with the letter “C”, sorted by patron ID (Figure P7.73).
SELECT PAT_ID, PAT_FNAME, PAT_LNAME, PAT_TYPE
FROM PATRON
WHERE PAT_LNAME LIKE 'C%'
ORDER BY PAT_ID
CODE 74
Write a query to display the author ID, first and last name of all authors whose year
of birth is unknown. Sort the results by author ID (Figure P7.74)