Given the schema item(itemid, name, category, price) itemsale(transid, itemid, q

Question

Given the schema item(itemid, name, category, price) itemsale(transid, itemid, qty) transaction(transid, custid, date) customer (custid, name, street-addr, city) where primary keys are underlined, write the following queries in SQL: a. Find the name and price of the most expensive item (if more than one item is the most expensive, print them all) b. Print the total sales (in terms of units and total price) of every item category in every customer-city. c. Find items with no sales at all to customers in Mumbai d. Find customers who bought the same quantity of the same item on subse- quent dates. e. Find all customers who did not buy any item in category "Electronics"... .5

Explanation / Answer

A.

select name from item where price=(select max(price) from item);

B.

select city , count(category), sum(price) from item join itemsale on item.itemid=itemsale.itemid

join transaction on itemsale.transid=transaction.transid

join customer on transaction.custid=customer.custid group by city;

C.

select item name from item where itemid in  

(select itemid from itemsale where transid in  

(select transid from transaction where custid in

(select custid from customer where city <> 'mumbai')));

D.

select name from customers where custid in(select custid from transaction where transid in(

(select i1.transid from itemsaleit1 join itemsale i2 on i1.transid<>i2.transid and i1.itemid=i2.itemid and i1.qty=i2.qty) intersect

(select t1.transid from transaction t1 join transaction t2 on t1.custid=t2.custid and t1.date <> t2.date);

E.

select name from customers where custid in

(select custid from transaction where transid in

(select transid from itemsale where itemid in

(select itemid from item where category<>'electronics')));

Related Questions

Navigate

• Browse (All)
• Browse G
• Subjects

---

---

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