Question

customer ssn VARCHAR(9) firstname VARCHAR(45) lastname VARCHAR(45) phone VARCHAR(45) vendor ? vid INT vendorname VARCHAR(45) vendorphone VARCHAR(45) vendoremail VARCHAR(45) order orderno INT date DATE ◇ ssn VARCHAR(9) Indexes ◇ email VARCHAR(45) ◇ vendorsize VARCHAR(45) Indexes Indexes キ コorder-item ▼ oid INT sku INT Indexes item sku INT itemname VARCHAR(45) itemprice DECIMAL(9,2) ◇ vid INT Indexes

Write a SQL query that shows the price of each order made by customers whose last name starts with the letter M. Display the order number, the last name on the order, and the price of the order (Order Price). Show the results with the highest order price first.

Write a SQL query that determines the most expensive item purchased in each order (Item Price). Display the order number, the date of the order, the last name of the customer that placed the order, and the price of the most expensive item on that order. Display in alphabetical order by last name.

Write a SQL query that determines and displays the total price of items sold (Total Price) before 2015 and after and including the year 2018.

Write a SQL query that determines the number of items purchased by each customer in the year 2013. Display the last name of each customer and the number of items purchased by each customer in that year in alphabetical order by last name.

AS ALWAYS, YOUR GENIUS ASSISTANCE IS MORE THAN GREATLY APPRECIATED!!!

Answer 1

1) select i.itemname ,i.itemprice ,oi.oid ,c.lastname ,
from item i,order_item oi, order o,customer c
where i.sku=oi.sku
and oi.oid=o.ordemo
and o.ssn=c.ssn
and c.lastname like 'M%'
order by i.itemprice desc

2) select c.lastname,o.date,itmprice,orderid from(select max(i.itemprice) itmprice ,oi.oid orderid
from item i,order_item oi, order o
where i.sku=oi.sku
and oi.oid=o.ordemo
group by oi.oid),order o,customer c
where o.ordemo=oid
order by c.lastname

3)(select sum(item.itemprice) as totalprice
from item,order_item oi, order o
where item.sku=oi.sku
and oi.oid=o.ordemo
and year(o.date)<'2015')
union(select sum(item.itemprice)
from item,order_item oi, order o
where item.sku=oi.sku
and oi.oid=o.ordemo
and year(o.date)>='2018')

4)select c.lastname,o.ssn,a.noofitems,a.orderid from(select oid orderid,count(itemname) noofitems
from item,order_item
where item.sku=order_item.sku
group by oid) a,order o,customer c
where a.orderid=o.ordemo
o.ssn=c.ssn
and year(date)='2013'