Formulate SQL queries for the following so that they produce correct results for all valid instances of the database:

  1. Formulate SQL queries for the following so that they produce correct results for all valid instances of the database:

(i)            For each employee who has received an order, list the employee’s employeeID, name, supervisor’s employeeID, and supervisor’s name.

(ii)           List the product code, stock level, and the total quantity ordered for those products where the total quantity ordered is more than 80% of the stock level.

(iii)         For each Order list the order number, order date, employeeID of salesperson receiving the order, total amount for products, and total amount for installation. The total amount for a product is the sum of the unit price times the quantity ordered. The total amount for installation is the sum of the billing rates times the hours required for installation.

(iv)         The revenue generated 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, revenue target, and the revenue generated for each branch that meets its revenue target.

(v)          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.

(vi)         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.

Leave a Reply