MySQL – You are asked to create a data managing system for a package delivery firm in MySQL…

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.