Reference no: EM131231548 , Length:
Please submit SQL statements as text file. You do not need to show screen shot. Make sure you have tested your SQL statements in Oracle.
Problem 1. Please create the following tables with appropriate primary keys & foreign keys.
The list of tables is:
- Users table with 3 columns: userid (user id), username, password;
- Category table with 2 columns: cid (category id), cname (category name, for example, TV, cell phone, laptop).
- Product table with 5 columns: pid (product id), cid (category id),pname (product name), brand (brand of product), price.
- Feature table with 2 columns: fid (feature id), fname (feature name). E.g., screen size, weight, memory size, hardisk size.
- Product_feature table with 3 columns: pid (product id), fid (feature id), fvalue (feature value), you can assume that feature values are numerical
- Review table with 6 columns: rid (review id), userid (user id), pid (product id), rdate (review date), score (review score from 1 to 5), rcomment (review comments)
Problem 2. Insert at least three rows of data to each table. Make sure you keep the primary key and foreign key constraints.
Problem 3. Please write ONE SQL statement for each of the following tasks using tables created in Problem 1. [50 points, 10 points each]
Task 1: Return the total number of reviews
Task 2: Return all reviews submitted after 2016-5-1.
Task 3: Return product names under the category TV.
Task 4: return the product name and average score.
Task 5: Return the names of product under the category TV and with average rating above 4.0
|
What are the annual payments of a self-amortization loan
: If the initial extra cost of a solar system is $5,000, what are the annual payments of a self-amortization loan if the interest rate is 12%, the inflation rate is 4%, and the mortgage term is 15 years.
|
|
Create mock-ups of all expected deliverables
: Define the problem in a business point of view as well as what you want to achieve out of this such as OLAP activities (Aggregation, roll-ups, slicing, dicing, etc.) and reports. Create mock-ups of all expected deliverables (Charts, graphs, diagrams,..
|
|
Law relate to the concept of price elasticity
: The phenomenon of ‘faster, cheaper’ computing is often referred to as Moore’s Law after Gordon Moore, a cofounder of Intel. What is Moore’s Law? What does it apply to? What is price elasticity and how does Moore’s Law relate to the concept of price e..
|
|
Prepare the total cost curve and specify cost optimal system
: prepare the total cost curve and specify the cost optimal system. Assume that the backup fuel is fuel oil at $7/GJ and that the fuel price is inflating at 8%/year. The total energy demand for which the solar system is constructed is estimated to b..
|
|
Please create the tables with appropriate
: Please create the tables with appropriate primary keys & foreign keys - Insert at least three rows of data to each table. Make sure you keep the primary key and foreign key constraints.
|
|
Describe a person or object that has special meaning in life
: Describe a person, place, or object that has special meaning in your life. Describe a particular moment or occurrence in your life that had special meaning to you.
|
|
Compare the capital costs and levelized costs of operation
: The PV system is to use thin film technology currently available. Neglect the cost of nuclear fuel. Comment on environmental impact and availability of water.
|
|
Explain how you would write a job description
: Why is strategic planning important to all managers? List five specific examples of what should be included in the management planning process. Explain how you would write a job description.
|
|
Describe governors roles in influencing budgetary process
: Discuss three of the governor's legislative powers. Explain which power is most important and why. Describe the governor's roles in influencing the budgetary process.
|