Hands-On Assignments To perform the following assignments, refer to the tables i

Question

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 width of the displayed Cost field is 12. different length value is displayed only once (not once for each book). 6. Create a list of all book titles and costs. Precede each book's cost with asterisks so that the 7. Determine the length of data stored in the ISBN field of the BOOKS table. Make sure each 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.

Explanation / Answer

1)
Ans: SELECT INITCAP (firstname), INITCAP(lastname)FROM customers;

2)
Ans: SELECT firstname, lastname, NVL2(referrred, 'REFERRED', 'NOT REFERRED')FROM customers;

3)
Ans:
SELECT title, TO_CHAR(quantity*(paideach-cost), '$999.99')FROM books JOIN orderitems USING(isbn)WHERE order# = 1002;

4)
Ans:
SELECT title, ROUND((retail-cost)/cost*100,0)||'%'FROM books;

5)
Ans:
SELECT TO_CHAR(CURRENT_DATE, 'DAY, HH:MI:SS')FROM dual;

6)
Ans: SELECT title, LPAD(cost, 12, '*')FROM books;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.