-- Assignment1
use dhruvir_patel1;

-- 1. Write create table syntax for employee table 
CREATE TABLE Employee (
    EMPLOYEE_ID INT PRIMARY KEY,
    FIRST_NAME VARCHAR(20) NOT NULL,
    LAST_NAME VARCHAR(20) NOT NULL,
    SALARY INT NOT NULL,
    JOINING_DATE DATETIME NOT NULL,
    DEPARTMENT VARCHAR(20) NOT NULL
);
INSERT INTO Employee VALUES(1,"John", "Abraham", 1000000,"2013-01-13 12:00:00","Banking"),
(2 ,'Michael', 'Clarke' ,800000,'2013-01-01 12:00:00','Insurance'),
(3,"Roy", "Thomas" ,700000,"2013-02-01 12:00:00","Banking"),
(4,"Tom ","Jose" ,600000,"2013-02-01 12:00:00","Insurance"),
(5,"Jerry", "Pinto" ,650000,"2013-02-01 12:00:00 ","Insurance"),
(6,"Philip", "Mathew", 750000,"2013-01-01 12:00:00","Services"),
(7,"TestName1", "123" ,650000,"2013-01-01 12:00:00 ","Services"),
(8,"TestName2", "Lname%" ,600000,"2013-02-01 12:00:00 ","Insurance")
;

-- 2. Get all employee details from the employee table 
	select * from Employee;
-- 3. Get First_Name, Last_Name from employee table 
	select FIRST_NAME, LAST_NAME from Employee;
-- 4. Get First_Name from employee table using alias name “Employee Name” 
	select FIRST_NAME AS EmployeeName from Employee;
-- 5. Get employee details from employee table whose employee name is “John” 
	select * from Employee where FIRST_NAME = 'John';

-- 6. Get employee details from employee table whose employee name are “John” and “Roy” 
	select * from Employee where FIRST_NAME = 'John' OR FIRST_NAME = "Roy";

-- 7. Get employee details from employee table whose employee name are not “John” and “Roy” 
	select * from Employee where FIRST_NAME NOT IN ('John','Roy');
-- 8. Get employee details from employee table whose first name starts with 'J' 
	select * from Employee where FIRST_NAME LIKE 'J%';
-- 9. Get employee details from employee table whose first name contains 'o' 
	select * from Employee where FIRST_NAME LIKE '%o%';
-- 10. Get employee details from employee table whose Salary between 500000 and 800000 
	select * from Employee where SALARY BETWEEN 500000 AND 800000;
-- 11. Get unique DEPARTMENT from employee table 
	select DISTINCT DEPARTMENT from Employee;

-- 12. Select TOP 2 salary from employee table 
	select SALARY from Employee LIMIT 2; 
-- 13. Store the output of below query in common table expression, and then find out the average of their salary 
-- 14. Get employee details from employee table whose Salary between 500000 and 800000 

	WITH avg_salary(SALARY) AS (select SALARY from Employee where SALARY BETWEEN 500000 AND 800000)
	select avg(SALARY) as average_salary from avg_salary;
    
-- 15. Get names of employees from employee table who has '%' in Last_Name. 
	 select * from Employee where INSTR(LAST_NAME,"%");
-- 16. Give 10% incentive to each employee, find out the employee whose incentive amount is more than 1lac, using derived table
	 select * from (select * , (SALARY * 0.1) as SAL from Employee ) as Salarytbl  where SAL >=100000;
     
     
     
     
-- Assignment2



-- 1. Write a query that displays the first name and the length of the first name for all employees whose name starts with the letters 'A', 'J' or 'M'. 
-- Give each column an appropriate label. Sort the results by the employees first names. 

	select FIRST_NAME, LENGTH(FIRST_NAME) as LengthOfFirstName from Employee where FIRST_NAME like 'A%' OR FIRST_NAME like 'J%' 
    OR FIRST_NAME like 'M%' order by FIRST_NAME;
    
-- 2. Write a query to display the first name and salary for all employees. Format the salary to be 10 characters long, 
-- left-padded with the $ symbol. Label the column SALARY. 

	select FIRST_NAME, LPAD(SALARY,10,'$') as SALARY from Employee;
    
-- 3. Write a query to display the employees with their code, first name, last name and hire date who hired either on seventh 
-- day of any month or seventh month in any year. 

	select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, JOINING_DATE AS HIRE_DATE FROM Employee WHERE MONTH(JOINING_DATE) = 7 OR dayofmonth(JOINING_DATE) = 7;
    
-- 4. Write a query to display the length of first name for employees where last name contain character 'c' after 2nd position. 

	SELECT FIRST_NAME,LAST_NAME,LENGTH(FIRST_NAME) AS LengthOfFirstName FROM Employee WHERE LAST_NAME LIKE '_c%';
    
-- 5. Write a query to extract the last 4 character of phone numbers. 

	SELECT RIGHT(PHONE_NO,4) AS EXTRACT_NUMBER FROM Employee;
	
-- 6. Write a query to update the portion of the phone_number in the employees table, within the phone number the substring '124' will be replaced by '999'. 

	SELECT replace(PHONE_NO,'124','999') AS REPLACED_no FROM Employee;

-- 7. Write a query to calculate the age in year. 

	SELECT FIRST_NAME,LAST_NAME,DATE_OF_BIRTH,date_format(FROM_DAYS(DATEDIFF(NOW(),DATE_OF_BIRTH)),'%y') AS AGE FROM Employee;

-- 8. Write a query to get the distinct Mondays from hire_date in employees tables.

	SELECT  distinct(JOINING_DATE) FROM Employee WHERE weekday(JOINING_DATE) = 4;
	
-- 9. Write a query to get the first name and hire date from employees table where hire date between '1987-06-01' and '1987-07-30' 

	SELECT FIRST_NAME, JOINING_DATE AS HIRE_DATE FROM Employee WHERE JOINING_DATE BETWEEN '2013-01-01' /*1987-06-01*/ AND '2013-01-13' /*1987-07-30*/;

-- 10. Write a query to display the current date in the following format. 11. Sample output : 12:00 AM Sep 5, 2014
	SELECT date_format(CURRENT_DATE(),'%l:%i %p %b %d, %Y');

-- 12. Write a query to get the firstname, lastname who joined in the month of June. 

	select FIRST_NAME , LAST_NAME FROM Employee WHERE monthname(JOINING_DATE) = 'FEBRUARY'/* JUNE */;
-- 13. Write a query to get employee ID, last name, and date of first salary of the employees. 

	SELECT EMPLOYEE_ID, LAST_NAME, ADDDATE(JOINING_DATE, INTERVAL 1 MONTH) AS FIRST_SALARY_DAY FROM Employee;

-- 14. Write a query to get first name, hire date and experience of the employees. 

	SELECT FIRST_NAME, LAST_NAME, JOINING_DATE AS HIRE_DATE ,date_format(FROM_DAYS(DATEDIFF(CURRENT_DATE(),JOINING_DATE)),'%y') AS experience FROM Employee;

-- 15. Write a query to get first name of employees who joined in 1987. 

	SELECT FIRST_NAME, JOINING_DATE FROM Employee WHERE YEAR(JOINING_DATE) = 1987;

-- 16. Write a query to rank employees based on their salary for a month 

	SELECT SALARY,rank() OVER (ORDER BY SALARY) AS RANK_SALARY FROM Employee;

-- 17. Select 4th Highest salary from employee table using ranking function

	SELECT FIRST_NAME,LAST_NAME,SALARY AS FOURTH_HIGHEST_SALARY  FROM (SELECT FIRST_NAME,LAST_NAME,SALARY,dense_rank() OVER (ORDER BY SALARY)FOURTH_HIGHEST_SAL FROM Employee) AS SALARYTBL WHERE FOURTH_HIGHEST_SAL =4;