Write a SQL statement to create a view for the database called CustomerFullNameS

Question

Write a SQL statement to create a view for the database called CustomerFullNameSaleHistoryView that (1) includes SALE.SaleID, SALE.SaleDate, customer full name (named as FullName) obtained by the FullNameFunction function using CUSTOMER.FirstName and CUSTOMER.LastName, and ITEM.ItemPrice (ItemPrice will not be included as is, but only as sum and average aggregates as described in (3)); (2) groups sales by SALE.SaleID, SALE.SaleDate, and then by FullName; and (3) sums and averages ITEM.ItemPrice for each order for each customer. As a result, this view should look like the following:

CustomerFullNameSaleHistoryView (SaleID, SaleDate, FullName, SumItemPrice, AveItemPrice)

Explanation / Answer

Please find the solution below

create table sales_table
(
SNO int,
saleid int,
saledate date,
customerid int
)

create table customer_details_table
(
customerid int primary key,
firstname varchar(30),
lastname varchar(30)
)

create table item_details_table
(
saleid int,
itemprice int
)

1)

select saleid,saledate,CONCAT ( 'firstname','lastname') from sales_table st,customer_details_table cdt
where
st.customerid=cdt.customerid

2)

select saleid,saledate,CONCAT ( 'firstname','lastname') fullname from sales_table st,customer_details_table cdt
where
st.customerid=cdt.customerid
group by saleid,saledate,fullname

3)
select saleid,saledate,CONCAT ( 'firstname','lastname') fullname,sum(itemprice),avg(itemprice) from sales_table st,customer_details_table cdt,
item_details_table idt
where
st.customerid=cdt.customerid
and
st.saleid=idt.saleid
group by saleid,saledate,fullname

create view CustomerFullNameSaleHistoryView as
select saleid,saledate,CONCAT ( 'firstname','lastname') fullname,sum(itemprice),avg(itemprice) from sales_table st,customer_details_table cdt,
item_details_table idt
where
st.customerid=cdt.customerid
and
st.saleid=idt.saleid
group by saleid,saledate,fullname

Related Questions

Navigate

• Browse (All)
• Browse W
• Subjects

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