Write one SQL statement to create a view named ctype_view (that must not be dropped if it already exists) to display the information exactly as listed below. The column “Customer TYPES” is derived from the ctype values as follows” P-Prestige, C-Clandestine, O-Ordinary, L-Loyal, else Undefined (Use DECODE to derive these values). Column ctotal represents the number of customer types of the specific type and column ototal represents the total currency value of all the orders placed by customers of a specific customer type. (11)
Write one SQL statement to create a view named ctype_view (that must not be dropped if it already exists) to display the information exactly as listed below. The column “Customer TYPES” is derived from the ctype values as follows” P-Prestige, C-Clandestine, O-Ordinary, L-Loyal, else Undefined (Use DECODE to derive these values). Column ctotal represents the number of customer types of the specific type and column ototal represents the total currency value of all the orders placed by customers of a specific customer type. (11)
SQL> SELECT *
2 FROM ctype_view;
Customer TYPES CTOTAL OTOTAL
-------------- ---------- ----------
Undefined 0 413.7
Prestige 10 1888.7
Clandestine 11 2126.41
Ordinary 5 432.5
Loyal 30 2320.99
5 rows selected.
2.2 Use the view you created in your answer to question 2.1 and write one SQL statement to display the three client types that have the most customers exactly as listed below. You are not allowed to restrict or limit the column named ctotal or limit or restrict the customer types. (5)
Best THREE Customer TYPES CTOTAL
---------- -------------- ----------
1 Loyal 30
2 Clandestine 11
3 Prestige 10
3 rows selected.
Step by step
Solved in 2 steps
