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.7Prestige 10 1888.7Clandestine 11 2126.41Ordinary 5 432.5Loyal 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. 11:54 O mO, 4G14 Í 66%++Document 20 - FoneForFood Database.docxFoneForFood Database Structure--------- .1. F_OWNER5. F_CUSTOMERCno1:1 MemberOf (FK)2. F_RE STAURANTRCode1:M NameRTypeFoneForFoodOwn erldIS uname1:1Database0: MSurnameFNa meInitialsCeINo1:1 Mg rSurm me0:M Mg rhitialsMg rC b ssStructureStrAddrEm ilSububCellNoSexСТуреPhoneNoJoinDa teLocationOwnerkd (FK)Recommended By (FK)1:11:M1:19. F_ORDERONo1:M6.F_ PAY TYPEPTCodeODatTime1:MCN6 (FK)TCode (FK)DMCellNo (FK)DeliveredCollect10. F_ORDITEMOno PK)1: MorfemRMe m# (FK)Quan4. F_RMITE MRMterp1:11:M temDescKemPriceRCOle (FK)CaCode EPT Desc1:M1:M1:M1:M1:11:18. F_DELIVERY MANDMC el INo3.F_ CATE GORYCatCode7. F_DRIVER LICENSEDLCodeDLDE1:11: MSurnameCatDe sFNa mesONAdNoDLCode (FK)5. F_CUSTOME RNUMBER (5)Me mberof (FK) NUMBER(5)VARCHAR2(13)VARCHAR 2(1 2)VARCHAR2(20)VARCHAR2(12)CHAR(10)9. F_ORDERNUMBER (3)1.F_OW NEROwnerldSumameNUMBER (2)VARCHAR 2(13)VARCHAR 2(4)CHAR(10)VARCHAR 2(20CNoONoOD ate TimeCNo (FK)PTCode (FK) CHAR(2)DMCE INo (FK) CHAR (10)De live redCo llectDATESumameFNameStrAddrIritialsNUMBER(5)CelINoEmailSuburbCHAR2. F_RESTAURANTCelINoCHARCHAR(4)RCodeNameRtypeMgrSunameMgrinitialsСТуреJoinD ateCHARVARCHAR2(15)CHAR(2)VARCHAR2(13)VARCHAR2(4)CHAR (2)10. F_OR DITEMNUMBE R(3)NUMBER(1)RMIE m# (FK) NUMBER(4)NUMBER (2)DATEOno (FK)6. F_PAYTYPECHAR (2)VARCHAR 2(20)OltemРTCodeMgrClassSexPTDE scQuartityCHARCHAR(10)VARCHAR 2(1 5)NUMBE R(1)RecommerdedBy (FK) VARCHAR 2(13)7. F_DRIVERLICENSEVARCHAR 2(2)VARCHAR2(16)PhoreNoLocationDLCodeOwnerld (FK)DLDesc8. F_DELVER YMANCHAR(10)DMCelINoSumame3. F_ CATE GORYCHAR (2)VARCHAR 2(9)CatCodeVARCHAR2(7)VARCHAR 2(14)VARCHAR 2(13)VARCHAR 2(2)CatDescFNamesdNo4. F_RMITEMDLCode (FK)RMItem#NUMBER (4)VARCHAR 2(30)NUMBER (5,2)CHAR (4)CHAR(2)ItemDescItemPriceRCode (FK)CatCode (FK)FoneForFood database listingGeneral Remarks regarding the FoneForFood database.This document lists the data as stored in the FoneForFood database of which thestructure of the database is listed on the previous page.listed belowThe headings ofTherefore, it is critical that you use the column names as they appear in the datastructure as listed on the previous page and not as listed further down bethedatacreated using column aliases.were117.SOL> SELECT * FROM f_owner;NotSeen this adAd coveredAlreadybought thisinterested inmultipletimescontentthis ad||||||||||||||||||||||||||||||

About VIEW in SQL : In SQL, a view is a virtual table based on the result-set of an SQL statement. A…

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

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE