Sheet 5 - Problem 1

BME B59 🫡
 avatar
itsLu
sql
2 months ago
5.5 kB
33
Indexable
Never
--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', 'San Jose', 0.12);
INSERT INTO salesman VALUES (5007, 'Paul Adam', 'Rome', 0.13);
--UPDATE salesman SET CITY = 'San Jose' WHERE SALESMAN_ID = 5003;

--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 query to filter those salesmen with all information who comes from any of the cities Paris and Rome:
SELECT * FROM salesman WHERE CITY = 'Paris' OR CITY = 'Rome';
SELECT * FROM salesman WHERE CITY IN ('Paris', 'Rome');

--Query b) Write a query to produce a list of salesman_id, name, city and commission of each salesman who live in cities other than Paris and Rome:
SELECT * FROM salesman WHERE CITY <> 'Paris' AND CITY <> 'Rome';
SELECT * FROM salesman WHERE CITY NOT IN ('Paris', 'Rome');

--Query c) Write a query to filter all those orders with all information which purchase amount value is within the range 500 and 4000 except those orders of purchase amount value 948.50 and 1983.43:
SELECT * FROM orders WHERE (PURCH_AMT BETWEEN 500 AND 4000) AND (PURCH_AMT NOT IN (948.50, 1983.43));

--Query d) Write a SQL statement to find the list of customers who appointed a salesman for their jobs who gets a commission from the company is more than 12:
SELECT C.* FROM customer AS C, salesman AS S WHERE C.salesman_id = S.SALESMAN_ID AND S.COMMISSION > 0.12;
SELECT C.*, S.COMMISSION FROM customer C INNER JOIN salesman S ON C.salesman_id = S.SALESMAN_ID WHERE S.COMMISSION > 0.12;

--Query e) Write a SQL statement to make a list in ascending order for the salesmen who works either for one or more customer or not yet join under any of the customers:
SELECT S.* FROM salesman S LEFT OUTER JOIN customer C ON C.salesman_id = S.SALESMAN_ID ORDER BY S.NAME ASC;
SELECT C.CUST_NAME 'Customer Name', C.CITY 'Customer City', C.GRADE 'Customer Grade', S.NAME 'Salesman Name', S.CITY 'Salesman City' FROM customer C RIGHT OUTER JOIN salesman S ON C.salesman_id = S.SALESMAN_ID ORDER BY S.SALESMAN_ID;

--Query f) Write a SQL statement to make a cartesian product between salesman and customer i.e. each salesman will appear for all customer and vice versa for that customer who belongs to a city:
SELECT * FROM salesman S, customer C WHERE C.CITY IS NOT NULL;
SELECT * FROM salesman S CROSS JOIN customer C WHERE C.CITY IS NOT NULL;

--Query g) Write a query to display all the orders from the orders table issued by the salesman 'Paul Adam':
SELECT O.* FROM salesman S, orders O WHERE (O.salesman_id = S.SALESMAN_ID) AND (S.NAME = 'Paul Adam');
SELECT * FROM orders WHERE salesman_id = (SELECT salesman_id FROM salesman WHERE NAME = 'Paul Adam');

--Query h) Write a query to display all the orders that had amounts that were greater than at least one of the orders on September 10th 2012:
SELECT * FROM orders WHERE PURCH_AMT > ANY(SELECT PURCH_AMT FROM orders WHERE ORD_DATE = '2012-09-10');

--Query i) Write a query to display all salesmen and customer located in London:
(SELECT SALESMAN_ID 'ID', NAME 'Name', 'Salesman' 'Person' FROM salesman WHERE CITY = 'London')
UNION
(SELECT CUSTOMER_ID 'ID', CUST_NAME 'Name', 'Customer' 'Person' FROM customer WHERE CITY = 'London');
Leave a Comment