#Collection dataset with customer details and assigned employees details
CREATE TABLE Collection(
CUST_ID VARCHAR(30) NOT NULL PRIMARY KEY, 
CUST_NAME VARCHAR(30) NOT NULL, 
CUST_CITY VARCHAR(30), 
WORKING_AREA VARCHAR(30) NOT NULL, 
CUST_COUNTRY VARCHAR(30) NOT NULL, 
GRADE INT, 
OPENING_AMT FLOAT NOT NULL, 
RECEIVE_AMT FLOAT NOT NULL, 
PAYMENT_AMT FLOAT NOT NULL,  
OUTSTANDING_AMT FLOAT NOT NULL, 
PHONE_NO VARCHAR(30) NOT NULL, 
Emp_ID  VARCHAR(30) NOT NULL
);

INSERT INTO Collection VALUES ('C00013', 'Holmes', 'London', 'London', 'UK', '2', '6000.00', '5000.00', '7000.00', '4000.00', 'BBBBBBB', '1'),
('C00001', 'Micheal', 'New York', 'New York', 'USA', '2', '30000.00', '50000.00', '20000.00', '60000.00', 'CCCCCCC', '2'),
('C00020', 'Albert', 'New York', 'New York', 'USA', '3', '50000.00', '70000.00', '60000.00', '60000.00', 'BBBBSBB', '3'),
('C00025', 'Ravindran', 'Bangalore', 'Bangalore', 'India', '2', '50000.00', '70000.00', '40000.00', '80000.00', 'AVAVAVA', '4'),
('C00024', 'Cook', 'London', 'London', 'UK', '2', '40000.00', '90000.00', '70000.00', '60000.00', 'FSDDSDF', '5'),
('C00015', 'Stuart', 'London', 'London', 'UK', '1', '60000.00', '80000.00', '30000.00', '110000.00', 'GFSGERS', '6'),
('C00002', 'Bolt', 'New York', 'New York', 'USA', '3', '50000.00', '70000.00', '90000.00', '30000.00', 'DDNRDRH', '7'),
('C00018', 'Fleming', 'Brisban', 'Brisban', 'Australia', '2', '70000.00', '70000.00', '90000.00', '50000.00', 'NHBGVFC', '8'),
('C00021', 'Jacks', 'Brisban', 'Brisban', 'Australia', '1', '70000.00', '70000.00', '70000.00', '70000.00', 'WERTGDF', '9'),
('C00019', 'Yearannaidu', 'Chennai', 'Chennai', 'India', '1', '80000.00', '70000.00', '70000.00', '80000.00', 'ZZZZBFV', '10'),
('C00005', 'Sasikant', 'Mumbai', 'Mumbai', 'India', '1', '70000.00', '110000.00', '70000.00', '110000.00', '147-25896312', '1'),
('C00007', 'Ramanathan', 'Chennai', 'Chennai', 'India', '1', '70000.00', '110000.00', '90000.00', '90000.00', 'GHRDWSD', '2'),
('C00022', 'Avinash', 'Mumbai', 'Mumbai', 'India', '2', '70000.00', '110000.00', '90000.00', '90000.00', '113-12345678','3'),
('C00004', 'Winston', 'Brisban', 'Brisban', 'Australia', '1', '50000.00', '80000.00', '70000.00', '60000.00', 'AAAAAAA', '4'),
('C00023', 'Karl', 'London', 'London', 'UK', '0', '40000.00', '60000.00', '70000.00', '30000.00', 'AAAABAA', '6'),
('C00006', 'Shilton', 'Torento', 'Torento', 'Canada', '1', '100000.00', '70000.00', '60000.00', '110000.00', 'DDDDDDD', '10'),
('C00010', 'Charles', 'Hampshair', 'Hampshair', 'UK', '3', '60000.00', '40000.00', '50000.00', '50000.00', 'MMMMMMM', '11'),
('C00017', 'Srinivas', 'Bangalore', 'Bangalore', 'India', '2', '80000.00', '40000.00', '30000.00', '90000.00', 'AAAAAAB', '6'),
('C00012', 'Steven', 'San Jose', 'San Jose', 'USA', '1', '50000.00', '70000.00', '90000.00', '30000.00', 'KRFYGJK', '7'),
('C00008', 'Karolina', 'Torento', 'Torento', 'Canada', '1', '70000.00', '70000.00', '90000.00', '50000.00', 'HJKORED', '2'),
('C00003', 'Martin', 'Torento', 'Torento', 'Canada', '2', '80000.00', '70000.00', '70000.00', '80000.00', 'MJYURFD', '6'),
('C00009', 'Ramesh', 'Mumbai', 'Mumbai', 'India', '3', '80000.00', '70000.00', '30000.00', '120000.00', 'Phone No', '9'),
('C00014', 'Rangarappa', 'Bangalore', 'Bangalore', 'India', '2', '80000.00', '110000.00', '70000.00', '120000.00', 'AAAATGF', '5'),
('C00016', 'Venkatpati', 'Bangalore', 'Bangalore', 'India', '2', '80000.00', '110000.00', '70000.00', '120000.00', 'JRTVFDD', '2'),
('C00011', 'Sundariya', 'Chennai', 'Chennai', 'India', '3', '70000.00', '110000.00', '70000.00', '110000.00', 'PPHGRTS', '8');

#Collection dataset with customer details and assigned employees details
select * from Collection;


CREATE TABLE Sales(
CUST_ID VARCHAR(20) NOT NULL PRIMARY KEY, 
CUST_NAME VARCHAR(20) NOT NULL, 
CUST_CITY VARCHAR(20), 
WORKING_AREA VARCHAR(20) NOT NULL, 
CUST_COUNTRY VARCHAR(20) NOT NULL, 
GRADE INT, 
SALE_AMT FLOAT NOT NULL, 
PHONE_NO VARCHAR(20) NOT NULL, 
Emp_ID  VARCHAR(20) NOT NULL
);

INSERT INTO Sales VALUES ('C00013', 'Daniel', 'London', 'London', 'UK', '2', '600000.00', 'BBBBBBB', '17'),
('C00001', 'Nancy', 'New York', 'New York', 'USA', '2', '300000.00', 'CCCCCCC', '25'),
('C00042', 'Carly', 'New York', 'New York', 'USA', '3', '512000.00', 'BBBBSBB', '14'),
('C00043', 'Rama', 'Bangalore', 'Bangalore', 'India', '2', '500000.00', 'AVAVAVA', '15'),
('C00044', 'Gary', 'London', 'London', 'UK', '2', '403000.00', 'FSDDSDF', '16'),
('C00045', 'Samantha', 'London', 'London', 'UK', '1', '600000.00',  'GFSGERS', '14'),
('C00002', 'Bill', 'New York', 'New York', 'USA', '3', '500000.00',  'DDNRDRH', '17'),
('C00046', 'Draco', 'Brisban', 'Brisban', 'Australia', '2', '330000.00', 'NHBGVFC', '18'),
('C00047', 'John', 'Brisban', 'Brisban', 'Australia', '1', '700000.00', 'WERTGDF', '19'),
('C00048', 'Rahul', 'Chennai', 'Chennai', 'India', '1', '800000.00', 'ZZZZBFV', '20'),
('C00041', 'Danish', 'Mumbai', 'Mumbai', 'India', '1', '420000.00', '147-25896312', '21'),
('C00049', 'Raman', 'Chennai', 'Chennai', 'India', '1', '700000.00', 'GHRDWSD', '22'),
('C00050', 'Arun', 'Mumbai', 'Mumbai', 'India', '2', '550000.00', '113-12345678','23'),
('C00051', 'Chris', 'Brisban', 'Brisban', 'Australia', '1', '500000.00', 'AAAAAAA', '24'),
('C00052', 'Kate', 'London', 'London', 'UK', '0', '400000.00', 'AAAABAA', '25'),
('C00053', 'Sally', 'Torento', 'Torento', 'Canada', '1', '1000000.00', 'DDDDDDD', '24'),
('C00054', 'Phill', 'Hampshair', 'Hampshair', 'UK', '3', '600000.00', 'MMMMMMM', '20'),
('C00055', 'Ramanathan', 'Bangalore', 'Bangalore', 'India', '2', '800000.00', 'AAAAAAB', '26');

#Sales dataset with customer details and assigned employees details
select * from Sales;

#1. Consolidate data from collection into Emp
select * from Emp INNER JOIN Collection on Emp.Emp_ID = Collection.Emp_ID;

#2(a). Consolidate all columns from Collection dataset into Emp:
select * from Emp LEFT OUTER JOIN Collection on Emp.Emp_ID = Collection.Emp_ID;

#2(b) .Display all Collecrtion Employes from 'Emp' and relate it to Emp_ID in 'Collection' Data
select * from Emp LEFT OUTER JOIN Collection on Emp.Emp_ID = Collection.Emp_ID WHERE Emp.Department = 'Collection';

#3. Top 5 Employees with more than average collected Amount and their commission.
select emp.Emp_ID, emp.Emp_Name, emp.commission, emp.Department, 
CUST_ID, CUST_NAME, CUST_CITY, CUST_COUNTRY, PAYMENT_AMT from collection 
LEFT OUTER JOIN Emp on emp.Emp_ID = Collection.Emp_ID 
where PAYMENT_AMT > (SELECT AVG(PAYMENT_AMT) from Collection) 
order by PAYMENT_AMT desc limit 5;

#4. Display all Collecrtion Employes from 'Emp' and relate it to Emp_ID in 'Sales' Data
select * from Emp LEFT OUTER JOIN Sales on Emp.Emp_ID = Sales.Emp_ID WHERE Emp.Department = 'Collection';

#5. Display all Sales Employes from 'Sales' dataset, relate it to Emp_ID in 'Emp' Data
select * from Emp RIGHT OUTER JOIN Sales on Emp.Emp_ID = Sales.Emp_ID;


#6. Achivement and commission paid by each employee in sales 7 collection department
SELECT a.Emp_ID, Emp_Name, commission, Department, b.SALE_AMT, c.PAYMENT_AMT, c.OUTSTANDING_AMT 
FROM Emp a LEFT OUTER JOIN Sales b ON b.Emp_ID = a.Emp_ID 
LEFT OUTER JOIN Collection c ON c.Emp_ID = a.Emp_ID GROUP By a.Emp_ID order by Department;