You are asked to create a data managing system for a package delivery firm in MySQL. It is given that the firm manages salesmen who deliver packages to destination listed by the customer for a particular delivery. You have created the following tables in order to do so.
CREATE TABLE Salesman ( salesman_id INT NOT NULL, name VARCHAR(255) NOT NULL, city VARCHAR(255), comission DOUBLE(255, 2), PRIMARY KEY (salesman_id) ); CREATE TABLE Customer ( customer_id INT NOT NULL, cust_name VARCHAR(255) NOT NULL, city VARCHAR(255), grade INT, salesman_id INT, PRIMARY KEY (customer_id), FOREIGN KEY (salesman_id) REFERENCES Salesman(salesman_id) ); CREATE TABLE Orders ( ord_no INT NOT NULL, purch_amt DOUBLE(255, 2) NOT NULL, ord_date DATE, customer_id INT, PRIMARY KEY (ord_no), FOREIGN KEY (customer_id) REFERENCES Customer(customer_id) FOREIGN KEY (sales_id) REFERENCES Salesman(salesman_id) );
Orders
Customer
Salesman
Create a package as follows
1.Write a function that returns the level of the customer based on the grade(G) as follows:
Level is Silver if 50 <= G <= 150
Level is Gold if 151 <= G <= 300
Level is Diamond if 301 <= G <= 500
2.Write individual stored procedures to do the following
a. List out all the names of all customers with salesman_id equal to 5001, in alphabetical order.
b. Display the details of orders on 5th October 2012.
c. Increase the commission of a salesman by 0.2% if he/she works for a customer in a different city than the one he resides in.
3.Write a function with parameter argument given as sales_id and find the net purchase for the customer whose sales_id is equal to the parameter that is passed to the function.
Perform the above given tasks in your system and upload a doc file containing the solutions for the respective tasks.