Question

Create a procedure to find out information about the most premium customer whose number of items sold is the max using mysql server.

-here are some hints In Sale table, find out the highest number of items sold by a seller. Then in customer table, find the customer.

The tables are as follows

Customer (CID, F_name, m_name, l_name, type, street, city, state, zip, password, email)

Item (ITEMNum, name, description, asking_price, notification_price, status, expiration_date, posting_date, condition, type, picture, order_point, qty_onhold, CID)

Bid (ID, price, time, CID, ITEMNum)

Sale (ID, sale_date, win_price, seller_approval, buyer_acceptance, completion_date, ITEMNum, BID, shippingticket)

Answer 1

//This might be the solution of your problem // the basic intuition is first we count item numbers where it is the most we select that item number //with that item number we cid from the item table //with the cid we select customer from customer table the procedure for that is create procedure p1 As Begin declare @num int select top 1 @num=ItemNumber,count(ItemNumber) as num from sale order by num desc select * from customer where cid=(select cid from item where ItemNumber=@num); end