Find Jobs
Hire Freelancers

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

$10-30 USD

已取消
已发布超过 5 年前

$10-30 USD

货到付款
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}
项目 ID: 17893705

关于此项目

远程项目
活跃6 年前

想赚点钱吗?

在Freelancer上竞价的好处

设定您的预算和时间范围
为您的工作获得报酬
简要概述您的提案
免费注册和竞标工作

关于客户

UNITED STATES的国旗
United States
0.0
0
付款方式已验证
会员自10月 16, 2017起

客户认证

谢谢!我们已通过电子邮件向您发送了索取免费积分的链接。
发送电子邮件时出现问题。请再试一次。
已注册用户 发布工作总数
Freelancer ® is a registered Trademark of Freelancer Technology Pty Limited (ACN 142 189 759)
Copyright © 2024 Freelancer Technology Pty Limited (ACN 142 189 759)
加载预览
授予地理位置权限。
您的登录会话已过期而且您已经登出,请再次登录。