Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Dont copy from others!!!!!! if you copy others, ill report 2. Specify the following 5 queries in SQL on the Salesrep database schema of Figure
Dont copy from others!!!!!! if you copy others, ill report
2. Specify the following 5 queries in SQL on the Salesrep database schema of Figure 1.1. (Total for que 2 is 10 marks) i. List all your 5 queries in the table below first in SQL. (5 marks) ii. Implement the answering of your 5 queries in 2(i) using Salplus and the same database you created in question 1, providing your execution and answers to these questions in a script file called username_assn3que2.txt. (5 marks) (a) Retrieve the names, hours and revenue of products sold by sales rep Melanie Good. (b) Retrieve the city and budget of all areas that have more than one product sold in it. (c) For each sales rep, retrieve their name, age, salary, and number of areas they sold in. (d) Retrieve the product name, sales rep's name, and sales area name of each sold product. (e) Retrieve the product name, product description and the sales rep's name of all products that are not sold by more than one sales rep. (may use nested query here) 1 Solution 2 (i): Queries(5 marks) and 2 (ii) Results (5 marks) Rep RID 11111 22222 33333 44444 RNAME Mary Peters Steve Markel Melanie Good John Doe RAGE 42 25 61 36 SALARY 150000 221000 188000 85000 Area ARID WDT SW TN ANAME CITY Down Town Windsor South Windsor Windsor Tecumseh North Tecumseh BUDGET 500000.99 950000.00 420000.50 Product PRID 1 2 3 4 PNAME pots towels apple basket potato bag PDESC cookware bath supply fruit food PRICE 101.00 45.50 30.30 5.50 RepSellsArea Rid 11111 11111 33333 44444 Arid TN SW SW WDT hours 12 10 33 revenue 20000.00 1500.00 40000.00 37200.00 28 RepSells Product Rid 11111 11111 33333 44444 Prid 1 2 2 4 hours 6 10 20 12 revenue 2000.00 4500.00 9000.00 3300.00 AreaSells Product revenue ARid WDT WDT SW Prid 1 2 2 4 hours 18 12 20 12 2000.00 6600.00 9000.00 3300.00 TN Fig 1.2: Schema of the 'Sales rep sells products in sales areas' database of Figure 1.1 Rep (Rid, Rname, Rage, salary) Area (Arid, Aname, city, budget) Product (Prid, Pname, Pdesc, price) RepSellsArea (Rid, Arid, hours, revenue) RepSells Product (Rid, Prid, hours, revenue) AreaSells Product (Arid, Prid, hours, revenue)Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started