Question

using SQL

Assume the relations shown below (underlined attributes are primary and foreign keys):

merchants(mid, name, city, state)

products(pid, name, category, description)

sell(mid, pid, price, quantity_available)

orders(oid, shipping_method, shipping_cost)

contain(oid,pid)

customers(cid, fullname, city, state)

place(cid, oid, order_date)

 

For each of the following questions write a SQL query:

List names and sellers of products that are no longer available (quantity=0)

List names and descriptions of products that are not sold.

How many customers bought SATA drives but not any routers between 2009 and 2019?

HP has a 20% sale on all its Networking products.

What did Uriel Whitney order from Acer on October 13, 2016? (make sure to at least retrieve product names and prices).

List the annual total sales for each company (sort the results along the company and the year attributes).

Which company had the highest annual revenue and in what year?

On average, what was the cheapest shipping method used in 2016?

What is the best sold ($) category for each company?

For each company find out which customers have spent the most and the least amounts.

 

 

 

General Guidelines

Your tables must have primary or foreign key constraints along with:

Products name constraint: Printer, Ethernet Adapter, Desktop, Hard Drive, Laptop, Router, Network Card, Super Drive, Monitor

Products category constraint: Peripheral, Networking, Computer

Sell price constraint: between 0 and 100,000

Sell quantity_available constraint: between 0 and 1,000

Orders shipping_method constraint: UPS, FedEx, USPS

Orders shipping_cost constraint: between 0 and 500

Place order_date: between 2005-1-1 and 2019-12-31

Answer 1

1. List names and sellers of products that are no longer available (quantity=0)

SELECT names,mid FROM Merchant as M,sell as S WHERE M.mid = S.sid AND S.quantity_available = 0;

2.List names and descriptions of products that are not sold.

SELECT names,description FROM Product as P,sell as S WHERE P.pid = S.pid AND S.quantity_available > 0;

3.How many customers bought SATA drives but not any routers between 2009 and 2019?

SELECT count(pid) FROM Product as P1,Product as P2,Contain as C,Place as P WHERE P.pid = C.pid AND P1.name = 'Hard drive' and P2.name != 'Router' AND C.oid = P.oid AND YEAR(P.order_date) between '2009' and '2019';

4.HP has a 20% sale on all its Networking products.

*********Question not clear***************

5.What did Uriel Whitney order from Acer on October 13, 2016? (make sure to at least retrieve product names and prices).

SELECT P.name, S.price FROM Product as P,Customer as C, Place as P1 , Contain as C, Sell as S, Merchant as M WHERE P1.cid = C.cid AND P1.oid = C.oid AND P.pid = C.pid AND P.pid = S.sid AND S.mid = M.mid AND M.name = 'Acer' AND C.fullname= 'Uriel Whitney' AND P1.order_date = '2016-*10-13';

6.List the annual total sales for each company (sort the results along the company and the year attributes).

*********Question not clear***************

7.On average, what was the cheapest shipping method used in 2016?

SELECT min(avg(shipping_cost)) FROM orders as O, Place as P WHERE O.oid = P.pid AND YEAR(P.order_date=2016) GROUP BY O.shipping_method;

8.What is the best sold ($) category for each company?

SELECT max(sum(S.price)) FROM Products as P1, SELL as S, Place as P, Contain as C WHERE P1.pid = S.pid AND S.pid = C.pid AND C.oid = P.oid GROUP BY P.category;

9.For each company find out which customers have spent the most and the least amounts.

SELECT min(sum(S.price)),max(sum(S.price)) FROM Customer as C,Place as P, Sell as S, Contain as C , Merchants as M WHERE S.cid = P.cid AND P.oid = C.oid AND C.pid = S.pid GROUP BY M.name;