use DB1;

create table Employee1 (
EMPLOYEE_ID int not null primary key,
FIRST_NAME varchar(255) not null,
LAST_NAME varchar(255) not null,
SALARY int not null,
JOINING_DATE varchar(255) not null,
DEPARTMENT varchar(255) not null,
MANAGER_ID int
);

drop table Employee1;

INSERT INTO `DB1`.`Employee1`
(`EMPLOYEE_ID`,
`FIRST_NAME`,
`LAST_NAME`,
`SALARY`,
`JOINING_DATE`,
`DEPARTMENT`,
`MANAGER_ID`)
VALUES(1, "John","Abraham", 1000000, "01-JAN-13 12.00.00 AM", "Banking",Null),
(2, "Michael", "Clarke", 800000," 01-JAN-13 12.00.00 AM","Insurance",1),
( 3, "Roy"," Thomas", 700000," 01-FEB-13 12.00.00 AM","Banking",1 ),
( 4 ,"Tom"," Jose", 600000 ,"01-FEB-13 12.00.00 AM","Insurance",2),
(5 ,"Jerry"," Pinto", 650000, "01-FEB-13 12.00.00 AM", "Insurance",3),
( 6 ,"Philip"," Mathew", 750000, "01-JAN-13 12.00.00 AM","Services",3),
(7 ,"TestName1"," 123 ",650000," 01-JAN-13 12.00.00 AM","Services",2 ),
(8 ,"TestName2"," Lname%", 600000, "01-FEB-13 12.00.00 AM","Insurance",2 );

-- 1. Create a view Select Banking as 'Bank Dept', Insurance as 'Insurance Dept' and Services as 'Services Dept' from employee table 
	create view Bank_Dept as select * from Employee1 where DEPARTMENT = 'Banking';
    create view Insurance_Dept as select * from Employee1 where DEPARTMENT = 'Insurance';
    create view Services_Dept as select * from Employee1 where DEPARTMENT = 'Services';
    
    select * from Bank_Dept;
    select * from Insurance_Dept;
    select * from Services_Dept;
    
    

-- 2. Select employee details from employee table if data exists in incentive table ? 
	 create view emp_details as select e.EMPLOYEE_ID, e.FIRST_NAME, e.LAST_NAME, e.SALARY, e.JOINING_DATE, e.DEPARTMENT, e.MANAGER_ID from 
     Employee1 as e join Incentives as i on e.EMPLOYEE_ID =i.EMPLOYEE_REF_ID;
     
	 select * from emp_details;
-- 3. Find Salary of the employee whose salary is more than Roy Salary 
	select  * from Employee1 where SALARY > (select SALARY from Employee1 where FIRST_NAME = 'Roy');

-- 4. Create a view to select FirstName,LastName,Salary,JoiningDate,IncentiveDate and IncentiveAmount
	create view view1 as select e.FIRST_NAME, e.LAST_NAME, e.SALARY, e.JOINING_DATE, i.INCENTIVE_DATE,i.INCENTIVE_AMOUNT  from 
     Employee1 as e join Incentives as i on e.EMPLOYEE_ID =i.EMPLOYEE_REF_ID;
     
     select * from view1;

-- 5. Create a view to select Select first_name, incentive amount from employee and incentives table for those employees who have incentives
--  and incentive amount greater than 3000 
	create view view2 as select e.FIRST_NAME,i.INCENTIVE_AMOUNT  from 
     Employee1 as e join Incentives as i on e.EMPLOYEE_ID =i.EMPLOYEE_REF_ID where i.INCENTIVE_AMOUNT > 3000;
     
     select *from view2;
	use db2_tables;
-- 6. Create a View to Find the names (first_name, last_name), job, department number, and department name of 
-- the employees who work in London.

		create view emp_details as select e.FirstName, e.LastName, e.JobId,d.DepartmentID,d.DepartmentName,l.City
		 from Employees e join Departments d on e.DepartmentID = d.DepartmentID join
        Locations as l on d.LocationID = l.LocationID where l.City = 'London';
	
		select * from emp_details;
-- 7. Create a View to get the department name and number of employees in the department. 
	create view emp_dept as select d.DepartmentName,count(e.EmployeeID) as Total_Employee  from 
     Employees as e join Departments as d on e.DepartmentID =d.DepartmentID group by e.DepartmentID;
     
     select * from emp_dept;
	
-- 8. Find the employee ID, job title, number of days between ending date and starting date for all jobs in department 90 from job history. 
	
		select EmployeeID, JobID as Job_Title, datediff(EndDate,StartDate) as Number_Of_Days from JobHistory where DepartmentID=90;

-- 9. Write a query to display the department name, manager name, and city. 

		select d.DepartmentName, e.FirstName as ManagerName, l.City from Departments d join Employees e on d.ManagerID = e.EmployeeID join Locations as l on d.LocationID = l.LocationID;

-- 10. Create a View to display department name, name (first_name, last_name), hire date, salary of the manager 
-- for all managers whose experience is more than 15 years.

	create view experience_details as select d.DepartmentName, concat(e.FirstName,' ',e.LastName) as FullName, e.HireDate, e.Salary from Departments as d join Employees as e on
    d.ManagerID = e.EmployeeID where (year(curdate()) - year(e.HireDate)) > 15;
    
    select * from experience_details;
    
    select year(curdate()) - year(HireDate) from Employees;