Chapter 10 Selected Single-Row Functions ORACLE 12c: SQL by Joan Casteel

Create reports containing the following details from the database:

Q1 List all book titles in mixed case (uppercase first letters, the rest in lower case) along with the publishers contact name and the age of the book in months.

Q2 Create a query to show the ISBN, the title in mixed case, the authors name, current retail price, and the new price with 15% discount. Round the result to cents (two decimals).

Q3 Display total profit for order 1014, in results, include the title in mixed case and profit formatted with $ and no decimal places. Consider that the book may be purchased in multiple quantities.

Q4 Display the current date, day of the week, hours, minutes and seconds.

Q5 Display a list of each book title and ISBN, as well as 8th and 9th digits of ISBN and a position of the third occurrence of a 0 in the ISBN. Create a single query.

Q6 Find the length of the publisher's name field and show each value only once in results.

Q7 Find the date for next Thursday based on today's date.

Q8** Create a query that will let you create a new price list for books written by the same author. You do not want to have to key in the entire name, just the first three letters of the last name and the first letter of the first name. The report should show the ISBN, the title in mixed case, the authors name, current retail price, and the new price. Calculate the new discounted price based on the current retail price minus 12%. Round the result to cents (two decimals). For testing, discount the price of James Patterson's books.

CUSTOMERS Customer# Lastname Firstname Email Address City State Zip Referred Region ORDERS Order# Customer Orderdate Shipdate Shipstreet Shipcity Shipstate Shipzip Shipcost ORDERITEMS Order# Item# ISBN Quantity Paideach BOOKAUTHOR AUTHOR BOOKS ISBN AuthorID ISBN AuthorID Lname Title Fname Pubdate PublD Cost Retail Discount Category PUBLISHER PubID Name PROMOTION Contact Phone Gift Minretail Maxretail