Database System Assignment
computer science
Description
Please help me to formulate queries based on the schema attached:
1. For each Order list the ordernumber, order date, employeeID of salesperson receiving the order, total amount for products, and total amount for installation. The total amount for products is the sum of the unit price times quantity of the products ordered. The total amount for installation is the sum of numberofhours times the billingrate of the installation types.
2.
The revenuegenerated by a branch is the sum of the total amount generated by all orders received by salespersons working at that branch. The total amount of an order is given by the sum of the total amount for products and the total amount for installation. List the branch number, branch name, revenuetarget, and the revenuegenerated for each branch that meets its revenuetarget.
3.
List the list the employeeID and name of salespersons who have sold exclusively to customers located in the same state as the state in which the salesperson’s branch is located.
4.
List the list the employeeID and name of salespersons who have sold to every customer located in the same state as the state in which the salesperson’s branch is located.
