SQL query in oracle - 03/10/2018 21:56 EDT

Part A and B Part-A. Basic SQL Dataset: ComputerStore The schema is as follows: ComputerStore manufacturers code, name products code, name, price, manufacturer The manufacturers table stores information about computer parts manufacturers as the manufacturer code and manufacturer name. The products table stores information about product as a product code, product name, product’s price and the code of the manufacturer who manufactured the product. Answer all 7 questions. 1. Select the names of the products with a price less than or equal to $200 2. Select all the products with a price between $60 and $120 3. Select the name and price in cents (i.e., the price is in dollars). 4. Select the product name, price, and manufacturer name of all the products. 5. Select all manufactures who currently do not have any listed products. 6. Select the name of each manufacturer along with the name and price of its most expensive product. 7. Select the names and average prices of manufacturer whose products have an average price larger than or equal to $150. Part-B. Analysis SQL Dataset: Restaurants The schema is as follows: Restaurants restaurant rID, name, address, cuisine reviewer vID, name rating vID, rID, stars, ratingdate 1. Find the name of all restaurants offering Indian cuisine 2. Find restaurant names that received a rating of 4 or 5, sort them in increasing order. 3. Find the names of all restaurants that have no rating. 4. Some reviewers didn't provide a date with their rating. Find the names of all reviewers who have ratings with a NULL value for the date. 5. For all cases where the same reviewer rated the same restaurant twice and gave it a higher rating the second time, return the reviewer's name and the name of the restaurant. 6. For each restaurant that has at least one rating, find the highest number of stars that a restaurant received. Return the restaurant name and number of stars. Sort by restaurant name. 7. For each restaurant, return the name and the 'rating spread', that is, the difference between highest and lowest ratings given to that restaurant. Sort by rating spread from highest to lowest, then by restaurant name. 8. Find the difference between the average rating of Indian restaurants and the average rating of Chinese restaurants. (Make sure to calculate the average rating for each restaurant, then the average of those averages for Indian and Chinese restaurants. Don't just calculate the overall average rating for Indian and Chinese restaurants.) Part C: Functional Dependencies C-1 Transitive Dependency and Keys You have a relation R(L,M,N,O,P,Q) R(A,B,C,D,E,F) and a set of functional dependencies F = {LNO→M, MN→LOP, N→O, OP→LN}. • Can we infer NP → LM from F ? • Can we infer NQ → LO from F ? C-2 Keys (i) Find all the candidate keys of the Relation R(ABCDE) with FD's: D → C, CE → A, D → A, and AE → D (ii) Determine all the candidate and superkeys of the relation R(ABCDEF) with FD's: AEF → C, BF → C, EF → D, and ACDE → F C-3 Minimal Cover Find a minimal cover for the following set F of functional dependencies. A→BC AB→D C→AD D→B Show your working clearly. Points will be deducted if you do not show the extraneous attributes, and their elimination. C-4 Equivalence (15 points) Consider the following set of F.Ds. Determine if FD1 is equivalent to FD2 or to FD3: FD1: {BC->D,ACD->B,CG->B,CG->D,AB->C,C->A,D->E,BE->C,D->G,CE->A,CE->G} FD2: {AB->C,C->A,BC->D,CD->B,D->E,D->G,BE->C,CG->D} FD3: {AB->C,C->A,D->G,BE->C,CG->D,CE->G,BC->D,CD->B,D->E}