Sheet 4 - Problem 1

--Creating Tables:
CREATE TABLE salesman (SALESMAN_ID NUMERIC(5) PRIMARY KEY NOT NULL, NAME VARCHAR(30), CITY VARCHAR(15), COMMISSION DECIMAL(5,2));
CREATE TABLE customer (CUSTOMER_ID NUMERIC(5) PRIMARY KEY NOT NULL, CUST_NAME VARCHAR(30), CITY VARCHAR(15), GRADE NUMERIC(3), salesman_id NUMERIC(5) FOREIGN KEY REFERENCES salesman(SALESMAN_ID));
CREATE TABLE orders (ORD_NO NUMERIC(5) PRIMARY KEY NOT NULL, PURCH_AMT DECIMAL(8,2), ORD_DATE DATE, customer_id NUMERIC(5) FOREIGN KEY REFERENCES customer(CUSTOMER_ID), salesman_id NUMERIC(5) FOREIGN KEY REFERENCES salesman(SALESMAN_ID));

--Populating salesman table:
INSERT INTO salesman VALUES (5001, 'James Hoog', 'New York', 0.15);
INSERT INTO salesman VALUES (5002, 'Nail Knite', 'Paris', 0.13);
INSERT INTO salesman VALUES (5005, 'Pit Alex', 'London', 0.11);
INSERT INTO salesman VALUES (5006, 'Mc Lyon', 'Paris', 0.14);
INSERT INTO salesman VALUES (5003, 'Lauson Hen', NULL, 0.12);
INSERT INTO salesman VALUES (5007, 'Paul Adam', 'Rome', 0.13);

--Populating customer table:
INSERT INTO customer VALUES (3002, 'Nick Rimando', 'New York', 100, 5001);
INSERT INTO customer VALUES (3005, 'Graham Zusi', 'California', 200, 5002);
INSERT INTO customer VALUES (3001, 'Brad Guzan', 'London', NULL, 5005);
INSERT INTO customer VALUES (3004, 'Fabian Johns', 'Paris', 300, 5006);
INSERT INTO customer VALUES (3007, 'Brad Davis', 'New York', 200, 5001);
INSERT INTO customer VALUES (3009, 'Geoff Camero', 'Berlin', 100, 5003);
INSERT INTO customer VALUES (3008, 'Julian Green', 'London', 300, 5002);
INSERT INTO customer VALUES (3003, 'Jozy Altidor', 'Moscow', 200, 5007);

--Populating orders table:
INSERT INTO orders VALUES (70001, 150.5, '2012-10-05', 3005, 5002);
INSERT INTO orders VALUES (70009, 270.65, '2012-09-10', 3001, 5005);
INSERT INTO orders VALUES (70002, 65.26, '2012-10-05', 3002, 5001);
INSERT INTO orders VALUES (70004, 110.5, '2012-08-17', 3009, 5003);
INSERT INTO orders VALUES (70007, 948.5, '2012-09-10', 3005, 5002);
INSERT INTO orders VALUES (70005, 2400.6, '2012-07-27', 3007, 5001);
INSERT INTO orders VALUES (70008, 5760, '2012-09-10', 3002, 5001);
INSERT INTO orders VALUES (70010, 1983.43, '2012-10-10', 3004, 5006);
INSERT INTO orders VALUES (70003, 2480.4, '2012-10-10', 3009, 5003);
INSERT INTO orders VALUES (70012, 250.45, '2012-06-27', 3008, 5002);
INSERT INTO orders VALUES (70011, 75.29, '2012-08-17', 3003, 5007);
INSERT INTO orders VALUES (70013, 3045.6, '2012-04-25', 3002, 5001);

--Query a) Write a SQL statement to display all the information of all salesmen:
SELECT * FROM salesman;

--to show data as it was entered:
DROP TABLE orders;
DROP TABLE customer;
DROP TABLE salesman;
CREATE TABLE salesman (SALESMAN_ID NUMERIC(5) PRIMARY KEY NOT NULL, NAME VARCHAR(30), CITY VARCHAR(15), COMMISSION DECIMAL(5,2), Rec_No INT IDENTITY(1,1));
CREATE TABLE customer (CUSTOMER_ID NUMERIC(5) PRIMARY KEY NOT NULL, CUST_NAME VARCHAR(30), CITY VARCHAR(15), GRADE NUMERIC(3), salesman_id NUMERIC(5) FOREIGN KEY REFERENCES salesman(SALESMAN_ID), Rec_No INT IDENTITY(1,1));
CREATE TABLE orders (ORD_NO NUMERIC(5) PRIMARY KEY NOT NULL, PURCH_AMT DECIMAL(8,2), ORD_DATE DATE, customer_id NUMERIC(5) FOREIGN KEY REFERENCES customer(CUSTOMER_ID), salesman_id NUMERIC(5) FOREIGN KEY REFERENCES salesman(SALESMAN_ID), Rec_No INT IDENTITY(1,1));
SELECT * FROM salesman ORDER BY Rec_No;

--Query b) Write a SQL statement to display specific columns like name and commission for all the salesmen:
SELECT NAME, COMMISSION FROM salesman;

--Query c) Write a query which will retrieve the value of salesman id of all salesmen, getting orders from the customers in orders table without any repeats:
SELECT DISTINCT salesman_id FROM orders;

--Query d) Write a SQL query to display the order number followed by order date and the purchase amount for each order which will be delivered by the salesman who is holding the ID 5001:
SELECT ORD_NO, ORD_DATE, PURCH_AMT FROM orders WHERE salesman_id = 5001;

--Query e) Write a query to display all customers with a grade above 100:
SELECT * FROM customer WHERE GRADE > 100;

--Query f) Write a SQL statement to display all customers, who are either, belongs to the city New York or had a grade above 100:
SELECT * FROM customer WHERE CITY = 'New York' OR GRADE > 100;

--Query g) Write a SQL query to display all orders where purchase amount less than 200 or exclude those orders which order date is on or greater than 10th Feb, 2012 and customer id is below 3009:
SELECT * FROM orders WHERE (PURCH_AMT < 200) OR NOT (ORD_DATE >= '2012-02-10' AND customer_id < 3009);

--Query h) Write a SQL query to display order number, purchase amount, achieved = (100*purch_amt)/6000 and unachieved = (100*(6000 - purch_amt)/6000) percentage of purchase amount from the target value 6000 which exceeds the 50 of the target value of 6000:
SELECT ORD_NO, PURCH_AMT, ((100*PURCH_AMT)/6000) AS 'ACHIEVED %', (100 - ((100*PURCH_AMT)/6000)) AS 'UNACHIEVED %' FROM ORDERS WHERE ((100*PURCH_AMT)/6000) > 50;

--Query i) Write a SQL statement to find those salesmen with all other information and name started with other than any latter within 'A’ and 'L’:
SELECT * FROM salesman WHERE NAME NOT LIKE '[A-L]%';
SELECT * FROM salesman WHERE NAME LIKE '[^A-L]%';

--Query j) Write a SQL statement to find those salesmen with all information whose name containing the 1st character is 'N’ and the 4th character is 'I’ and rests may be any character:
SELECT * FROM salesman WHERE NAME LIKE 'N__L%';

--Query k) Write a query to find those customers with their name and those salesmen with their name and city who lives in the same city:
SELECT customer.CUST_NAME, salesman.NAME AS 'SALESMAN_NAME', customer.CITY FROM customer, salesman WHERE customer.CITY = salesman.CITY;

-- Query l)	Write a SQL statement that return the customer and their grade who made an order. Each of the customers must have a grade and served by at least a salesman, who belongs to a city:
SELECT customer.CUST_NAME AS 'NAME', customer.GRADE AS 'GRADE' FROM customer, salesman, orders WHERE orders.customer_id = customer.CUSTOMER_ID AND orders.salesman_id = salesman.SALESMAN_ID AND customer.GRADE IS NOT NULL AND salesman.CITY IS NOT NULL;

--Query m) Write a SQL statement that produces all orders with the order number, customer name, commission rate and earned commission amount for those customers who carry their grade is 200 or more and served by an existing salesman:
SELECT orders.ORD_NO, customer.CUST_NAME, salesman.COMMISSION AS 'Commission Rate', (orders.PURCH_AMT * salesman.COMMISSION) AS 'Earned Commission' FROM orders, customer, salesman WHERE customer.GRADE >= 200 AND orders.salesman_id = salesman.SALESMAN_ID AND orders.customer_id = customer.CUSTOMER_ID;

--Query n) Write a SQL statement to display either those orders which are not issued on date 2012-09-10 and issued by the salesman whose ID is 5005 and below or those orders which purchase amount is 1000.00 and below:
SELECT * FROM orders WHERE (ORD_DATE <> '2012-09-10' AND salesman_id <= 5005) OR (PURCH_AMT <= 1000);
SELECT * FROM orders WHERE (NOT ORD_DATE = '2012-09-10' AND salesman_id <= 5005) OR (PURCH_AMT <= 1000);