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
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
Chapter10: Application Development
Section: Chapter Questions
Problem 9VE
Related questions
Question
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.
Transcribed Image Text:11:54 O m
O, 4G
14 Í 66%
++
Document 20 - FoneForFood Database.docx
FoneForFood Database Structure
---
------ .
1. F_OWNER
5. F_CUSTOMER
Cno
1:1 MemberOf (FK)
2. F_RE STAURANT
RCode
1:M Name
RType
FoneForFood
Own erld
IS uname
1:1
Database
0: MSurname
FNa me
Initials
CeINo
1:1 Mg rSurm me
0:M Mg rhitials
Mg rC b ss
Structure
StrAddr
Em il
Subub
CellNo
Sex
СТуре
PhoneNo
JoinDa te
Location
Ownerkd (FK)
Recommended By (FK)
1:1
1:M
1:1
9. F_ORDER
ONo
1:M
6.F_ PAY TYPE
PTCode
ODatTime
1:MCN6 (FK)
TCode (FK)
DMCellNo (FK)
Delivered
Collect
10. F_ORDITEM
Ono PK)
1: Morfem
RMe m# (FK)
Quan
4. F_RMITE M
RMterp
1:1
1:M temDesc
KemPrice
RCOle (FK)
CaCode E
PT Desc
1:M
1:M
1:M
1:M
1:1
1:1
8. F_DELIVERY MAN
DMC el INo
3.F_ CATE GORY
CatCode
7. F_DRIVER LICENSE
DLCode
DLDE
1:1
1: MSurname
CatDe s
FNa mes
ONA
dNo
DLCode (FK)
5. F_CUSTOME R
NUMBER (5)
Me mberof (FK) NUMBER(5)
VARCHAR2(13)
VARCHAR 2(1 2)
VARCHAR2(20)
VARCHAR2(12)
CHAR(10)
9. F_ORDER
NUMBER (3)
1.F_OW NER
Ownerld
Sumame
NUMBER (2)
VARCHAR 2(13)
VARCHAR 2(4)
CHAR(10)
VARCHAR 2(20
CNo
ONo
OD ate Time
CNo (FK)
PTCode (FK) CHAR(2)
DMCE INo (FK) CHAR (10)
De live red
Co llect
DATE
Sumame
FName
StrAddr
Iritials
NUMBER(5)
CelINo
Email
Suburb
CHAR
2. F_RESTAURANT
CelINo
CHAR
CHAR(4)
RCode
Name
Rtype
MgrSuname
Mgrinitials
СТуре
JoinD ate
CHAR
VARCHAR2(15)
CHAR(2)
VARCHAR2(13)
VARCHAR2(4)
CHAR (2)
10. F_OR DITEM
NUMBE R(3)
NUMBER(1)
RMIE m# (FK) NUMBER(4)
NUMBER (2)
DATE
Ono (FK)
6. F_PAYTYPE
CHAR (2)
VARCHAR 2(20)
Oltem
РTCode
MgrClass
Sex
PTDE sc
Quartity
CHAR
CHAR(10)
VARCHAR 2(1 5)
NUMBE R(1)
RecommerdedBy (FK) VARCHAR 2(13)
7. F_DRIVERLICENSE
VARCHAR 2(2)
VARCHAR2(16)
PhoreNo
Location
DLCode
Ownerld (FK)
DLDesc
8. F_DELVER YMAN
CHAR(10)
DMCelINo
Sumame
3. F_ CATE GORY
CHAR (2)
VARCHAR 2(9)
CatCode
VARCHAR2(7)
VARCHAR 2(14)
VARCHAR 2(13)
VARCHAR 2(2)
CatDesc
FNames
dNo
4. F_RMITEM
DLCode (FK)
RMItem#
NUMBER (4)
VARCHAR 2(30)
NUMBER (5,2)
CHAR (4)
CHAR(2)
ItemDesc
ItemPrice
RCode (FK)
CatCode (FK)
FoneForFood database listing
General Remarks regarding the FoneForFood database.
This document lists the data as stored in the FoneForFood database of which the
structure of the database is listed on the previous page.
listed below
The headings of
Therefore, it is critical that you use the column names as they appear in the data
structure as listed on the previous page and not as listed further down be
the
data
created using column aliases.
were
117.
SOL> SELECT * FROM f_owner;
Not
Seen this ad
Ad covered
Already
bought this
interested in
multiple
times
content
this ad
||||||||||||||||||||||||||||||
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
Step by step
Solved in 2 steps
Knowledge Booster
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.Recommended textbooks for you
Systems Architecture
Computer Science
ISBN:
9781305080195
Author:
Stephen D. Burd
Publisher:
Cengage Learning
Systems Architecture
Computer Science
ISBN:
9781305080195
Author:
Stephen D. Burd
Publisher:
Cengage Learning