Oracle 11g SQL Joan Casteel Chapter 10 Hands-On Assignments

Oracle 11g SQL Joan Casteel Chapter 10 Hands-On Assignments

To perform the following assignments, refer to the tables in the JustLee Books database.
1. Produce a list of all customer names in which the first letter of the first and last names is in uppercase and the rest are in lowercase.

2. Create a list of all customer numbers along with text indicating whether the customer has been referred by another customer. Display the text “NOT REFERRED” if the customer wasn’t
referred to JustLee Books by another customer or “REFERRED” if the customer was referred.

3. Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can involve multiple copies.

4. Display a list of all book titles and the percentage of markup for each book. The percentage of markup should be displayed as a whole number (that is, multiplied by 100) with no decimal position, followed by a percent sign (for example, .2793 = 28%). (The percentage of markup should reflect the difference between the retail and cost amounts as a percent of the cost.)

5. Display the current day of the week, hour, minutes, and seconds of the current date setting on the computer you’re using.

6. Create a list of all book titles and costs. Precede each book’s cost with asterisks so that the width of the displayed Cost field is 12.

7. Determine the length of data stored in the ISBN field of the BOOKS table. Make sure each different length value is displayed only once (not once for each book).

8. Using today’s date, determine the age (in months) of each book that JustLee sells. Make sure only whole months are displayed; ignore any portions of months. Display the book title,
publication date, current date, and age.

9. Determine the calendar date of the next occurrence of Wednesday, based on today’s date.

10. Produce a list of each customer number and the third and fourth digits of his or her zip code. The query should also display the position of the first occurrence of a 3 in the customer number, if it exists.