SQL: I have this tables below I want to find; •The most active seller (the one w

Question

SQL: I have this tables below I want to find;

•The most active seller (the one who has offered the most number of items)

•The most active buyer (the one who has bought the most number of items)

•The most popular seller (the one who sold the most number of items)

•The most expensive item sold ever

•The most expensive item available

•The cheapest item sold ever

The cheapest item available

create table creditCardInfo

(
   credit_CardID       int IDENTITY(1000,1)   NOT NULL,
   credit_CardNo       varchar(16) UNIQUE       NOT NULL,
   credit_ExptDate       date                   NOT NULL,
   credit_OwnerName   varchar(20)               NOT NULL,
  
   PRIMARY KEY(credit_CardID));

create table Address
(
addressID int IDENTITY(1000,1)   NOT NULL,
Street       varchar(50)               NOT NULL,
City       varchar(20)               NOT NULL,
State       char(2)                   NOT NULL,
ZIP           char(10)               NOT NULL,

primary key (addressID));

create table contactInfo
(
   contactID   int IDENTITY (100,1) NOT NULL,
   telephone   char(10) NOT NULL UNIQUE,
   Email varchar(50)   NOT NULL UNIQUE,
   address_ID   int ,

   PRIMARY KEY(contactID),
FOREIGN KEY (address_ID) references Address(addressID));

create table seller
(
   sellerID       INT IDENTITY(1000,1) NOT NULL,
   sellerName       varchar(20) NOT NULL,
   sellerUserName   varchar(20) UNIQUE,
   sellerPassword   varchar(32) NOT NULL,

   item_ID       int NOT NULL,
   contact_ID   int NOT NULL,
   creditCardID int NOT NULL,
   primary key(sellerID),

   Foreign Key(contact_ID) references contactInfo(contactID),
   Foreign Key(creditCardID) references creditCardInfo(credit_CardID));

create table item

(
   itemID               int IDENTITY(1000,1)   NOT NULL,
   itemName           varchar(15)               NOT NULL,
   Item_desc           varchar(255),
   Item_initialPrice   MONEY,
   ItemQty               int,
   ownerID            int                       NOT NULL,
   condition           varchar(20)               NOT NULL,

   PRIMARY KEY (itemID),

   FOREIGN KEY (ownerID) references seller (sellerID));

create table buyer

(
   buyerID           INT IDENTITY(1000,1) NOT NULL,
   buyerName       varchar(20)           NOT NULL,
   buyerUsername   varchar(20) UNIQUE   NOT NULL,
   buyerPassowrd   varchar(32)           NOT NULL,

   contact_ID       int,
   creditCardID   int,

   primary key(buyerID),

   Foreign Key(contact_ID) references contactInfo(contactID),
   Foreign Key(creditCardID) references creditCardInfo(credit_CardID));

create table buyerItem
(
   buyer_ID   int NOT NULL,
   item_ID       int NOT NULL,
  
   PRIMARY KEY(buyer_ID,item_ID),

   FOREIGN KEY (buyer_ID) REFERENCES buyer(buyerID),
   FOREIGN KEY (item_ID) REFERENCES item(itemID));

create table picture

(
   pictureID   int IDENTITY (1,1) NOT NULL,
   pictureIMG   VarBinary(max),
   item_ID       int,

   PRIMARY KEY(pictureID),
   FOREIGN KEY(item_ID) references item(itemID));

create table bid

(
   bidID           int IDENTITY (1000,1) NOT NULL,
   bid_startTime   date DEFAULT GETDATE()NOT NULL,
   bid_endTime       date DEFAULT GETDATE()NOT NULL,
   item_ID           int                   NOT NULL,

   PRIMARY KEY(bidID),

   Foreign Key(item_ID) references item(itemID));

create table buyerBid

(
   buyer_ID   int NOT NULL,
   bid_ID       int NOT NULL,
  
   PRIMARY KEY(buyer_ID,bid_ID),
   FOREIGN KEY (buyer_ID) REFERENCES buyer(buyerID),
   FOREIGN KEY (bid_ID) REFERENCES bid(bidID));

Explanation / Answer

-- The most active seller(the one who has offered the most number of items)
SELECT a.ownerID, b.sellerName FROM item AS a
INNER JOIN seller AS b ON a.ownerID = b.sellerID
GROUP BY a.ownerID ORDER BY COUNT(a.itemID) DESC LIMIT 1;

-- The most active buyer(the one who has bought the most number of items)
SELECT a.buyer_ID, b.buyerName FROM buyeritem AS a
INNER JOIN buyer AS b ON a.buyer_ID = b.buyerID
GROUP BY a.buyer_ID ORDER BY COUNT(a.item_ID) DESC LIMIT 1;

-- The most popular seller (the one who sold the most number of items)
SELECT b.ownerID, c.sellerName FROM buyeritem AS a
INNER JOIN item AS b ON a.item_ID = b.itemID
INNER JOIN seller AS c ON b.ownerID = c.sellerID
GROUP BY b.ownerID ORDER BY COUNT(a.item_ID) DESC LIMIT 1;

-- The most expensive item sold ever
SELECT c.itemID, c.itemName
FROM item AS c
JOIN (
SELECT b.itemID as 'itemid', MAX(b.item_initialPrice) AS 'MaxPrice' FROM buyeritem AS a
INNER JOIN item AS b ON a.item_ID = b.itemID ) as d
ON c.itemID = d.itemid;

-- The Most expensive item available
SELECT a.itemID, a.itemName
FROM item AS a
JOIN ( SELECT itemID, MAX(item_initialPrice) AS 'MaxPrice' FROM item ) as b
ON a.itemID = b.itemID;

-- The Cheapest item sold ever
SELECT c.itemID, c.itemName
FROM item AS c
JOIN (
SELECT b.itemID as 'itemid', MIN(b.item_initialPrice) AS 'MinPrice' FROM buyeritem AS a
INNER JOIN item AS b ON a.item_ID = b.itemID ) as d
ON c.itemID = d.itemid;

-- The cheapest item available
SELECT a.itemID, a.itemName
FROM item AS a
JOIN ( SELECT itemID, MIN(item_initialPrice) AS 'MinPrice' FROM item ) as b
ON a.itemID = b.itemID;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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