In SQL
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) Find all undervalued sales that have the term xbox in their descriptions; a sale s is undervalued if it is associated to a product p and p is associated with another sale s'and the highest bid for s'is more than twice the amount of the highest bid for s. Matching of the keyword xbox must be case-insensitive. For each qualifying sale s, list sid, lister of the sale, pid of the product associated with the sale and the maximum bid amount offered for the sale. 9. Create a view called product_info with columns pid, descr, revcnt, rating, rating, 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. 10. 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. 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) Find all undervalued sales that have the term xbox in their descriptions; a sale s is undervalued if it is associated to a product p and p is associated with another sale s'and the highest bid for s'is more than twice the amount of the highest bid for s. Matching of the keyword xbox must be case-insensitive. For each qualifying sale s, list sid, lister of the sale, pid of the product associated with the sale and the maximum bid amount offered for the sale. 9. Create a view called product_info with columns pid, descr, revcnt, rating, rating, 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. 10. 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
