EXP6_CSE2004Lab_20BRS1064

CREATE TABLE salesman_20BRS1064(
salesman_id varchar2(4) PRIMARY KEY,
name varchar2(20) NOT NULL,
city varchar2(15) NOT NULL,
commission NUMBER NOT NULL);

CREATE TABLE customer_20BRS1064(
customer_id varchar2(4) PRIMARY KEY,
cust_name varchar2(20) NOT NULL,
city varchar2(15) NOT NULL,
grade NUMBER NOT NULL,
salesman_id  varchar2(4));

CREATE TABLE orders_20BRS1064(
ord_no varchar2(5) PRIMARY KEY,
purchase_amt NUMBER NOT NULL,
ord_date DATE NOT NULL,
customer_id varchar2(4),
salesman_id  varchar2(4));

INSERT INTO salesman_20BRS1064 VALUES(5001,'James Hong','New York',0.15);
INSERT INTO salesman_20BRS1064 VALUES(5002,'Nail Knite','Paris',0.13);
INSERT INTO salesman_20BRS1064 VALUES(5005,'Pit Alex','London',0.11);
INSERT INTO salesman_20BRS1064 VALUES(5006,'Mc Lyon','Paris',0.14);
INSERT INTO salesman_20BRS1064 VALUES(5007,'Paul Adam','Rome',0.13);
INSERT INTO salesman_20BRS1064 VALUES(5003,'Lauson Hen','San Jose',0.12);

INSERT INTO customer_20BRS1064 VALUES(3002,'Nick Rimando','New York',100,5001);
INSERT INTO customer_20BRS1064 VALUES(3003,'Brad Davis','New York',200,5001);
INSERT INTO customer_20BRS1064 VALUES(3004,'Graham Zusi','California',200,5002);
INSERT INTO customer_20BRS1064 VALUES(3005,'Julian Green','London',300,5002);
INSERT INTO customer_20BRS1064 VALUES(3006,'Fabian Johnson','Paris',300,5006);
INSERT INTO customer_20BRS1064 VALUES(3007,'Goeff Cameron','Berlin',100,5003);
INSERT INTO customer_20BRS1064 VALUES(3008,'Jozy Altidor','Moscow',200,5007);
INSERT INTO customer_20BRS1064 VALUES(3009,'Brad Guzan','London',100,5005);

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

--1
SELECT salesman_20BRS1064.name AS "Salesman",
customer_20BRS1064.cust_name, customer_20BRS1064.city
FROM salesman_20BRS1064,customer_20BRS1064
WHERE salesman_20BRS1064.city=customer_20BRS1064.city;

--2
SELECT a.ord_no,a.purchase_amt,
b.cust_name,b.city
FROM orders_20BRS1064 a , customer_20BRS1064 b
WHERE a.customer_id=b.customer_id
AND a.purchase_amt BETWEEN 500 AND 2000;

--3
SELECT name,city FROM salesman_20BRS1064
WHERE salesman_id = (SELECT salesman_id FROM customer_20BRS1064 WHERE customer_id=3001);

--4
SELECT a.cust_name AS "Customer Name",
a.city, b.name AS "Salesman",b.commission
FROM customer_20BRS1064 a
INNER JOIN salesman_20BRS1064 b
ON a.salesman_id=b.salesman_id
WHERE b.commission>.12;

--5
SELECT *
FROM salesman_20BRS1064 a
CROSS JOIN customer_20BRS1064 b;

--6
SELECT a.ord_no,a.ord_date,a.purchase_amt,
b.cust_name AS "Customer Name", b.grade,
c.name AS "Salesman",c.commission
FROM orders_20BRS1064 a
INNER JOIN customer_20BRS1064 b
ON a.customer_id=b.customer_id
INNER JOIN salesman_20BRS1064 c
ON a.salesman_id=c.salesman_id;

--7
SELECT a.cust_name, a.city,a.grade,
b.name AS "Salesman", b.city
FROM customer_20BRS1064 a
LEFT JOIN salesman_20BRS1064 b
ON a.salesman_id=b.salesman_id
ORDER BY a.customer_id;

--8
SELECT a.cust_name,a.city,a.grade,
b.name AS "Salesman", b.city
FROM customer_20BRS1064 a
LEFT OUTER JOIN salesman_20BRS1064 b
ON a.salesman_id=b.salesman_id
WHERE a.grade<300
ORDER BY a.customer_id;

--9
SELECT a.cust_name AS "Customer Name",
a.city, b.name AS "Salesman", b.commission
FROM customer_20BRS1064 a
INNER JOIN salesman_20BRS1064 b
ON a.salesman_id=b.salesman_id;

--10
SELECT a.cust_name,a.city,a.grade,
b.name AS "Salesman", b.city
FROM customer_20BRS1064 a
RIGHT OUTER JOIN salesman_20BRS1064 b
ON b.salesman_id=a.salesman_id
ORDER BY b.salesman_id;

--11
SELECT a.cust_name,a.city,a.grade,
b.name AS "Salesman",
c.ord_no,c.ord_date,c.purchase_amt
FROM customer_20BRS1064 a
RIGHT OUTER JOIN salesman_20BRS1064 b
ON b.salesman_id=a.salesman_id
RIGHT OUTER JOIN orders_20BRS1064 c
ON c.salesman_id=a.salesman_id;

DROP TABLE salesman_20BRS1064;
DROP TABLE customer_20BRS1064;
DROP TABLE orders_20BRS1064;