Question

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)

Based on the information given , write a SQL solution to each of the following problem:

a). Return the item IDs of those items that are posted by users who are banned now.

b). Return the item IDs of those items that contain a comment that is given by a user who

is banned now.

c). Return the item IDs of those items, each comment of which is given by a user who is

banned now.

d). Return the item IDs of all the items that have no categories.

e). Return the item IDs of the items that have both categories of “electronics” and

“iphone”.

f). Return the user IDs of those female users who never posted any items but gave some

comments to some items.

g). Return the user IDs of those male users who only posted items but never gave any

comments.

h). Write an UPDATE statement to ban the users who neither posted any items nor gave

any comments.

i). Write a DELETE statement to delete all the users who posted at least 10 empty items

– those items that have no description.

j). Return the user IDs of the users who have posted items as well as some comments to

some items.

Answer 1

Database Create

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(userId),FOREIGN KEY(sellerId) references Users(userId));

CREATE TABLE Items (itemId integer,title varchar (50),descibe 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(itemId));
CREATE TABLE FavItems (itemId integer,userId varchar (30),PRIMARY KEY (itemId, userId),FOREIGN KEY(itemId) references Items(itemId),FOREIGN KEY(userId) references Users(userId));

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

a)

select itemId from Items where posterId in(Select userId from Users where banned ='FALSE');

b)

Select itemId from Reviews where userId in (Select userId from Users where banned ='FALSE');
c)
Select distinct itemId from Reviews where userId in (Select userId from Users where banned ='FALSE');
d)

Select itemId from Items where itemId not in(Select distinct itemId from ItemCategories)

e)
Select itemId from ItemCategories where category='electronics'and itemId in(Select itemId from ItemCategories where category='iphone');

f)
Select userId from users where gender='female' and userId not in (Select posterId from Items) and userId in(Select userId from Reviews);

g)

Select userId from users where gender='male' and userId in (Select posterId from Items) and userId not in(Select userId from Reviews);

h)
update users set banned='False' where userId not in(Select userId from Reviews);

i)

Delete from Users where userId in(Select posterId from items where descibe=''group by posterId having count(*)>10);

j)

Select posterId "UserID" from items where posterId in(Select distinct userId from Reviews)

if you still have any Problem regarding this question please comment and if you like my code please appreciate me by thumbs up thank you.........