a. List edward\'s total sales amount in different states (query result: ename, s
ID: 3751956 • Letter: A
Question
a. List edward's total sales amount in different states (query result: ename, state, total_sales)
b. List each of the employees and the total number of employees directly managed by him/her (if no one is directly managed, then return 0) -->hint: manages stores the "managing" information in the company (e.g., the employee with eid1 is the direct manager of the employee with eid2)
c. List all the customers (cid and cname) which have completed orders with Chris and all the employees directly managed by him. hint: manages stores the "managing" information in the company (e.g., the employee with eid1 is the direct manager of the employee with eid2)
Employee Customer salary city state eid ename 0011Chris01/03/1986 100,000 0012 Edward |07/24/1973120,000 0013 Anna08/12/1992 80,000 0014 Kim08/26/1986 95,000 0015Carol04/15/1988 97,000 dob cidcname A101 ChaseNew YorkNY New YorkNY A103 TD BankBoston MA IL Disney Orlando FL A102 Citi A104Moto Chicago Product pid pname unit price C17811Desk C17812Chair C23453PC C34451Mac 500 200 1,200 1,300 Sales eidcid 0011A101C17811 0011A102 C17812 0012 A102 C23453 0013 A101C34451 Manage:s eid1 eid2 00110013 0013 0017 0012 0013 pid quantity 700 200 100 50Explanation / Answer
Below are the relational algebra query-
a) Aggregate function sum is used to find the total price of sales.
ename, state, Gsum(unit_price * quantity) ( ename = ‘Edward’ (Employee X Sales X Customer X Product))
b) Aggregate function count is used to count the number of employees working under each employee.
ename, Gcount(eid2) (Employee X Manages)
c) The project operator is used to project customer id and name and select operator is used to select all customer of employee 'Chris'.
cid, cname ( ename = ‘Chris’ (Employee X Customer X Manages X Sales))
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.