SHOW DATABASES;

-- -----------------
-- Creating Table
-- -----------------
CREATE TABLE Employee (
    E_No CHAR(3) PRIMARY KEY,
    E_Name VARCHAR(50) NOT NULL,
    Job_Type VARCHAR(50) NOT NULL,
    Manager CHAR(3),
    Hire_Date DATE NOT NULL,
    D_No CHAR(3) NOT NULL,
    Commission DECIMAL(10 , 2 ),
    Salary DECIMAL(10 , 2 ) NOT NULL,
    Total_Salary Decimal(10,2) as (Commission+Salary)
);
CREATE TABLE Department (
    D_No CHAR(3) PRIMARY KEY,
    D_Name VARCHAR(50),
    Location VARCHAR(50) NOT NULL
);
-- --------------------------
-- For deleting the tables
-- --------------------------
drop table Employee;
drop table department;


-- ------------------------------------
-- For Inserting values in the table
-- ------------------------------------
insert into Employee
(E_No, E_Name, Job_Type, Manager, Hire_Date, D_No, Commission, Salary)
values
('E01','Debabrata','Engineer','YES','2021-4-4','D01',4578.5,78000),
('E02','Debapriya','Engineer','NO','2021-4-4','D01',69878.5,68000),
('E03','Ananta','Programmer','NO','2021-4-8','D02',978.5,58000),
('E04','Rick','Editor','NO','2021-4-14','D03',51078.5,50000),
('E05','Debajeet','Editor','NO','2021-8-16','D03',1878.5,48000),
('E06','Prithiviraj','Receptionist','NO','2021-8-16','D04',0.0,28000),
('E07','Anish','GateMan','NO','2022-4-24','D05',20000.0,18000);


insert into Department
values
('D01','Department1','Mumbai'),
('D02','Department2','Delhi'),
('D03','Department3','Delhi'),
('D04','Department4','Delhi'),
('D05','Department5','Delhi');


-- ------------------------------------
-- Displaying the values in the table
-- ------------------------------------
SELECT
    *
FROM
    Employee;
SELECT
    *
FROM
    department;


-- -------------------------------
-- Different Types OF Query
-- -------------------------------

SELECT
    Employee.E_No,
    Employee.E_Name,
    Employee.Job_Type,
    Employee.Hire_Date,
    Employee.Total_Salary,
    Department.D_Name,
    Department.Location
FROM
    Employee
        JOIN
    Department ON Employee.D_No = Department.D_No
ORDER BY Employee.E_Name ASC;

 -- 1. Query to display Employee Name, Job, Hire Date, Employee Number; for each employee with the
    SELECT
    E_No, E_Name, Job_Type, Hire_Date
FROM
    Employee;

 -- 2. Query to display unique Jobs from the Employee Table.

SELECT
    E_No, E_Name, Job_Type, Hire_Date
FROM
    Employee
WHERE
    Job_Type = 'Editor';

 -- Example 2
SELECT
    E_No, E_Name, Job_Type, Hire_Date
FROM
    Employee
WHERE
    Job_Type = 'Engineer';

-- 3. Query to display the Employee Name concatenated by a Job separated by a comma.

SELECT
    CONCAT(E_Name, ' , ', Job_Type) AS Concatenated_Name
FROM
    Employee;

-- 4. Query to display all the data from the Employee Table. Separate each Column by a comma and
--    name the said column as THE_OUTPUT.
SELECT
    CONCAT(E_No,
            ' , ',
            E_Name,
            ' , ',
            Job_Type,
            ' , ',
            Manager,
            ' , ',
            Hire_Date,
            ' , ',
            D_No,
            ' , ',
            Commission,
            ' , ',
            Salary,
            ' , ',
            Total_Salary) AS THE_OUTPUT
FROM
    Employee;

-- 5. Query to display the Employee Name and Salary of all the employees
SELECT
    E_Name, Total_Salary
FROM
    Employee
WHERE
    Total_Salary >= 50000;

-- 6. Query to display Employee Name and Department Number for the Employee No= E04.
SELECT
    E_Name, D_No
FROM
    Employee
WHERE
    E_No = 'E04';

-- 7. Query to display Employee Name and Salary for all employees whose salary
SELECT
    E_Name, Total_Salary
FROM
    Employee
WHERE
    NOT (Total_Salary > 40000
        AND Total_Salary < 50000);

-- 8. Query to display Employee Name and Department No. of all the employees
SELECT
    E_Name, D_No
FROM
    Employee
WHERE
    D_No = 'D01' OR D_No = 'D03'
ORDER BY E_Name ASC;


-- 9. Query to display Name and Hire Date of every Employee who
--    was hired in 1981.

SELECT
    E_Name, Hire_Date
FROM
    employee
WHERE
    YEAR(Hire_Date) = '2021';

-- 10. Query to display Name and Job of all employees who don’t
--     have a current Manager.
SELECT
    E_Name, Job_Type
FROM
    employee
WHERE
    Manager = 'NO'

-- 11. Query to display the Name, Salary and Commission for all the
--     employees who earn commission.
SELECT
    E_Name, Salary, Commission
FROM
    employee
WHERE
    Commission > 0.0

-- 12. Sort the data in descending order of Salary and Commission.

SELECT
    Salary, Commission
FROM
    employee
ORDER BY salary DESC , commission DESC

-- *** 13. Query to display Name of all the employees where the third
--     letter of their name is ‘A’ or 'B'.

SELECT
    Total_Salary, E_Name
FROM
    Employee
WHERE
    SUBSTRING(E_Name, 3, 1) = 'A'
        OR SUBSTRING(E_Name, 3, 1) = 'B'*** 14. Query to display Name of all employees either have two ‘R’s
--     or have two ‘ A’s in their name and are either in Dept No = 30
--     or their Manger’s Employee No = 7788.
SELECT
    E_Name
FROM
    Employee
WHERE
    ((LENGTH(E_Name) - LENGTH(REPLACE(LOWER(E_Name), 'r', ''))) >= 2
        OR (LENGTH(E_Name) - LENGTH(REPLACE(LOWER(E_Name), 'a', ''))) >= 2)
        AND (D_No = 'D01' OR Manager = 'YES');


-- 15. Query to display Name, Salary and Commission for all employees
--     whose Commission Amount is greater than their Salary increased by 5%.

SELECT
    E_Name, Salary, Commission
FROM
    Employee
WHERE
    (Commission > (Salary * 21 / 20));

 -- 16. Query to display the Current Date.

SELECT CURDATE() AS Todays_Date;

 -- 17. Query to display Name, Hire Date and Salary Review Date which is the
 --     1st Monday after six months of employment.

SELECT
    E_Name,
    Hire_Date,
    DATE_ADD(DATE_ADD(DATE_ADD(Hire_Date, INTERVAL 6 MONTH),
            INTERVAL (9 - WEEKDAY(DATE_ADD(Hire_Date, INTERVAL 6 MONTH))) DAY),
        INTERVAL IF(WEEKDAY(DATE_ADD(Hire_Date, INTERVAL 6 MONTH)) >= 1,
            7,
            0) - WEEKDAY(DATE_ADD(Hire_Date, INTERVAL 6 MONTH)) DAY) AS salary_review_date
FROM
    Employee;



-- 18. Query to display Name and calculate the number of months between today
--     and the date each employee was hired.

SELECT
    E_Name,
    TIMESTAMPDIFF(MONTH,
        Hire_Date,
        CURDATE()) AS months_since_hire
FROM
    Employee;


-- 19. Query to display the following for each employee <E-Name> earns < Salary>
--     monthly but wants < 3 * Current Salary >. Label the Column as Dream Salary.

SELECT
    CONCAT(E_Name,
            ' earns ',
            FORMAT(Salary, 2),
            ' monthly but wants ',
            FORMAT(3 * Salary, 2)) AS 'Dream Salary'
FROM
    Employee;



-- 20. Query to display Name with the 1st letter capitalized and all other letter
--     lower case and length of their name of all the employees whose name starts
--     with ‘J’, ’A’ and ‘M’.




-- 21. Query to display Name, Hire Date and Day of the week on which the employee started.




-- 22. Query to display Name, Department Name and Department No for all the employees.



-- 23. Query to display Unique Listing of all Jobs that are in Department # 30.



-- 24. Query to display Name, Dept Name of all employees who have an ‘A’ in their name.




-- 25. Query to display Name, Job, Department No. And Department Name for all
--     the employees working at the Dallas location.




-- 26. Query to display Name and Employee no. Along with their Manger’s Name and
--     the Manager’s employee no; along with the Employees’ Name who do not have a Manager.




-- 27. Query to display Name, Dept No. And Salary of any employee whose department
--     No. and salary matches both the department no. And the salary of any employee
--     who earns a commission.




-- 28. Query to display Name and Salaries represented by asterisks, where each asterisk
--     (*) signifies $100.





-- 29. Query to display the Highest, Lowest, Sum and Average Salaries of all the employees
SELECT
    MAX(Total_Salary) AS highest_salary,
    MIN(Total_Salary) AS lowest_salary,
    SUM(Total_Salary) AS total_salary,
    AVG(Total_Salary) AS average_salary
FROM
    Employee;



-- *** 30. Query to display the number of employees performing the same Job type functions.

SELECT
    Job_Type, COUNT(*) AS NoOF_Employees
FROM
    Employee
GROUP BY Job_Type;



-- 31. Query to display the no. of managers without listing their names.




-- 32. ***Query to display the Department Name, Location Name, No. of Employees and the average
--     salary for all employees in that department.

SELECT
    Department.D_name,
    Department.Location,
    COUNT(Employee.E_No) AS No_Of_employees,
    AVG(Employee.Total_Salary) AS Avg_Salary
FROM
    Department
        JOIN
    Employee ON Department.D_No = Employee.D_No
GROUP BY Department.D_Name , Department.Location;


-- 33. Query to display Name and Hire Date for all employees in the same dept. as Blake.

SELECT
    E_Name, Hire_Date
FROM
    Employee
WHERE
    D_No = (SELECT
            D_No
        FROM
            Employee
        WHERE
            E_Name = 'Rick');



-- *** 34. Query to display the Employee No. And Name for all employees who earn more than
--     the average salary.
SELECT
    E_No, E_Name
FROM
    Employee
WHERE
    Total_Salary > (SELECT
            AVG(Total_Salary)
        FROM
            Employee);


-- 35. Query to display Employee Number and Name for all employees who work in a department
--     with any employee whose name contains a ‘T’.





-- 36. Query to display the names and salaries of all employees who report to King.





-- 37. Query to display the department no, name and job for all employees in the Sales department.

SELECT
    Employee.D_No, Employee.E_Name, Employee.Job_Type
FROM
    Employee
        JOIN
    Department ON Employee.D_No = Department.D_No
WHERE
    Department.D_Name = 'Department2';


-- **Use to delete tables from database**

drop table employee;
drop table department;