In SQL

Here is the SQL statements:

-- users(email, name, pwd, city, gender) -- products(pid, descr) -- sales(sid, lister, pid, edate, descr, cond, rprice) -- bids(bid, bidder, sid, bdate, amount) -- items(sid, no, pid, descr) -- reviews(reviewer, reviewee, rating, rtext, rdate) -- previews(rid, pid, reviewer, rating, rtext, rdate) -- endorses(rid, endorser) drop table if exists endorses; drop table if exists previews; drop table if exists reviews; drop table if exists items; drop table if exists bids; drop table if exists sales; drop table if exists products; drop table if exists users; PRAGMA foreign_keys = ON; create table users ( email char(20), name char(16), pwd char(4), city char(15), gender char(1), primary key (email) ); create table products ( pid char(4), descr char(20), primary key (pid) ); create table sales ( sid char(4), lister char(20) not null, pid char(4), edate date, descr char(25), cond char(10), rprice int, primary key (sid), foreign key (lister) references users, foreign key (pid) references products ); create table bids ( bid char(20), bidder char(20) not null, sid char(4) not null, bdate date, amount float, primary key (bid), foreign key (bidder) references users, foreign key (sid) references sales ); create table items ( sid char(4), no int, pid char(4), descr char(25), primary key (sid,no), foreign key (sid) references sales on delete cascade, foreign key (pid) references products ); create table reviews ( reviewer char(20), reviewee char(20), rating float, rtext char(20), rdate date, primary key (reviewer, reviewee), foreign key (reviewer) references users, foreign key (reviewee) references users ); create table previews ( rid int, pid char(4), reviewer char(20) not null, rating float, rtext char(20), rdate date, primary key (rid), foreign key (pid) references products, foreign key (reviewer) references users ); create table endorses ( rid int, endorser char(20), primary key (rid, endorser), foreign key (rid) references previews, foreign key (endorser) references users );

Here is some data:

insert into users values ('mc@hotmail.com','Michael Choi','abcd','Edmonton, AB','M'); insert into users values ('tedwalsh@td.com','Ted Walsh','7632','Calgary, Ab','M'); insert into users values ('hm@mah.com','Harry Mah','1453','Waterloo, ON','M'); insert into users values ('ks@hotmail.com','Kaitlyn Scott','pqwe','Toronto, ON','F'); insert into users values ('angels@hotmail.com','Angel Silverman','anlo','Vancouver, BC','F'); insert into users values ('mk@abc.com','Maximillion Kung','0931','Burnaby, BY','F');

insert into products values ('N01', 'Nikon F100'); insert into products values ('N02', 'Nikon D3500'); insert into products values ('B01', 'BMW M8'); insert into products values ('P01', 'Porsche 911'); insert into products values ('P02', 'Porsche 918'); insert into sales values ('S01', 'mc@hotmail.com', 'N01', '2016-03-24', 'Camera Sale', 'Brand new', 1400); insert into sales values ('S02', 'mc@hotmail.com', 'N02', '2018-09-02', 'Great deal', 'Used', 698); insert into sales values ('S03', 'hm@mah.com', 'N02', '2015-12-12', 'End year', 'New', 530); insert into sales values ('S04', 'ks@hotmail.com', 'P01', '2019-01-11', 'Amazing', 'New', 30000000); insert into bids values ('B01', 'hm@mah.com', 'S01', '2016-04-01', 1405.02); insert into bids values ('B02', 'ks@hotmail.com', 'S01', '2016-04-02', 1407.99); insert into bids values ('B03', 'hm@mah.com', 'S02', '2018-09-11', 999); insert into bids values ('B04', 'angels@hotmail.com', 'S03', '2016-01-03', 430); insert into bids values ('B05', 'tedwalsh@td.com', 'S04', '2019-05-19', 39099999); insert into items values ('S01', 1, 'N01', 'Nikon F100 body'); insert into items values ('S01', 2, 'N01', 'Nikon 50mm, f/1.4 lens'); insert into items values ('S03', 3, 'N02', 'Nikon 55mm, f/3.5-5.6G VR lens'); insert into reviews values ('mc@hotmail.com', 'tedwalsh@td.com', 4.9, 'great guy!', '2016-05-02'); insert into reviews values ('hm@mah.com', 'ks@hotmail.com', 5.0, 'car is amazing', '2015-09-02'); insert into reviews values ('angels@hotmail.com', 'mc@hotmail.com', 0.5, '', date('now','-4 years')); insert into previews values (1, 'N01', 'hm@mah.com', 1.5, 'definitly used', '2016-04-25'); insert into previews values (2, 'N02','ks@hotmail.com', 2, 'great quality', '2018-09-11'); insert into previews values (3, 'P02', 'mk@abc.com', 5, 'amazing car', date('now','-9 months')); insert into endorses values (1, 'mc@hotmail.com'); insert into endorses values (1, 'ks@hotmail.com'); insert into endorses values (3, 'angels@hotmail.com');

users(email, name, pwd, city, gender)

products(pid, descr)

sales(sid, lister, pid, edate, descr, cond, rprice)

bids(bid, bidder, sid, bdate, amount)

items(sid, no, pid, descr)

reviews(reviewer, reviewee, rating, rtext, rdate)

previews(rid, pid, reviewer, rating, rtext, rdate)

Question:

1. Create a view called product_info with columns pid, descr, revcnt, rating, rating6, and salecnt. The view includes for each product the product id, the description, the number of reviews, the average rating, the average rating based on the reviews written within the past 6 months, and the number of different sales that are either associated to the product or have an item that is associated to the product. Include products with no reviews or sales in the output with zero counts (if applicable) or null values.
2. Using the view created in the previous question, find users whose sales are all associated to hot products. A product is hot if its average rating is larger than 4 and its sale count is larger than the average sale count.