Question

Given the mySQL tables created below...

   Create a mySQL solution to return the itemIDs of items posted by user X, such that all the reviews are “Excellent” or “Good” for these items

CREATE TABLE Users (
   userId varchar (30) NOT NULL,
   pass varchar (30),
   fname varchar (50),
   lname varchar (50),
   email varchar (50),
   gender char(1),
   age integer,
   banned boolean,
   PRIMARY KEY (userId),
   UNIQUE(email))

CREATE TABLE FavSellers (
   userId varchar (30),
   sellerId varchar (30),
   PRIMARY KEY (userId, sellerId),
   FOREIGN KEY(userId) references Users,
   FOREIGN KEY(sellerId) references Users(userId))

CREATE TABLE Items (
   itemId integer,
   title varchar (50),
   desc varchar (255),
   posterId varchar (30),
   postTime DATETIME,
   price integer,
   PRIMARY KEY (itemId),
   FOREIGN KEY(posterId) references Users(userId))

CREATE TABLE ItemCategories (
   itemId integer,
   category varchar (50),
   PRIMARY KEY (itemId, category),
   FOREIGN KEY(itemId) references Items)

CREATE TABLE FavItems (
   itemId integer,
   userId varchar (30),
   PRIMARY KEY (itemId, userId),
   FOREIGN KEY(itemId) references Items,
   FOREIGN KEY(userId) references Users)

CREATE TABLE Reviews (
   reviewId integer,
   score varchar (50),
   remark varchar (255),
   submitTime Datetime,
   userId varchar (30) NOT NULL,
   itemId integer NOT NULL,
   PRIMARY KEY (reviewId),
   CHECK score IN ('Excellent, 'Good', ...),
   FOREIGN KEY (userId) references Users
   FOREIGN KEY (itemId) references Items)

Answer 1

Here I am providing the answer for the above question:

Query:

(SELECT itemID

FROM Reviews

WHERE score="Excelent" OR score="Good")

INTERSECT

(SELECT itemID

FROM Items , Users

WHERE userID=posterID AND fname="X");

Explanation:

First Query will return all itemids which are reviewed as "Excellent" ,"Good".

Second Query will return all items which are posted by user="X".

intersection of above two queries will return the all itemIDs which are posted by user"X" and reviewed as "Excellent" and "Good"

Hoping that the above answer will help you...Thank you...