Need help with Henry Books Database questions: Henry’s is contemplating running
ID: 3713993 • Letter: N
Question
Need help with Henry Books Database questions:
Henry’s is contemplating running a 15% off sale on all of its books. To determine what the discounted prices would be, list the book code, title and discounted price of every book. NOTE: Label the discounted price column appropriately and list ONLY one row for each book code, title and discounted price.
What is the command to determine the name of the most expensive book? If your query results in more than one of the same row, make sure the query is modified to return only one of the same row.
Find out how many books are of book type MYS in all of the Henry branches. Be sure the column(s) are labeled appropriately.
Henry is considering increasing the price of his books in "Excellent" condition by 10%. Prepare a query that will show him the book code, book title, the current price and the price increased by 10%. Avoid displaying duplicate rows.
Write a query that will display the book title, author first name and author last name for all books in the “Henry’s on the Hill” branch that are in “Excellent” condition. Be sure to list only one entry per book title.
Write the SQL query you would run to delete entries with poor-quality books from the COPY table.
What SQL query would you run to remove all of the data from the BOOK table but keep the table structure?
Write the SQL statement that will create a new table that is a copy of the table BOOK (table structure with the same data) that will be called BOOK2?
Write the SQL statement that will add a new column to BOOK2 called ISBN for ISBN numbers.
Henry’s wants entries for this new column but will not require them.
Write the SQL script that will remove all of the tables from the Henry Books database in the correct order.
Explanation / Answer
SELECT BOOK_CODE, TITLE, (PRICE*0.15) AS DISCOUNTED_PRICE FROM BOOK;
SELECT DISTINCT TITLE, PRICE FROM BOOK WHERE (PRICE = (SELECT MAX(PRICE) FROM BOOK) );
SELECT BRANCHNUM AS BRANCH_NUMBER,COUNT(*) AS NUMBER_OF_BOOKS FROM INVENTORY WHERE BOOKCODE IN (SELECT BOOKCODE FROM BOOK WHERE TYPE='MYS') GROUP BY BRANCHNUM;
SELECT DISTINCT BOOKCODE, TITLE, PRICE, PRICE+(PRICE*0.10) AS INCREASED_PRICE FROM BOOK;