Simplilearn SQL project

/* 1. Create a database named employee, then import data_science_team.csv proj_table.csv and emp_record_table.csv
into the

employee database from the given resources. */

create database employee;

use employee;

-- Imported table data manually

show tables;

select * from data_science_team;

-- count using sub queries, here 'a' holds subquery data for temporarily basis

select count(*) from (select * from data_science_team)a;

-- count w/o subqueries

select count(*) from data_science_team;

-- Table 2

select * from emp_record_table;

select count(*) from emp_record_table;

-- Table 3

select * from proj_table;

2. Create an ER diagram for the given employee database.

/* 3. Write a query to fetch EMP_ID, FIRST_NAME, LAST_NAME, GENDER, and DEPARTMENT from the employee record
table,

and make a list of employees and details of their department. */

select * from emp_record_table;

select EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPT from emp_record_table;

/* 4. Write a query to fetch EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPARTMENT, and EMP_RATING if the
EMP_RATING is:

less than two, greater than four, between two and four */

select EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPT, EMP_RATING from emp_record_table where EMP_RATING<2;

select EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPT, EMP_RATING from emp_record_table where EMP_RATING>4;

select EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPT, EMP_RATING from emp_record_table where EMP_RATING>2
AND EMP_RATING<4;
/* 5. Write a query to concatenate the FIRST_NAME and the LAST_NAME of employees in the Finance department from
the employee table

and then give the resultant column alias as NAME. */

select * from emp_record_table;

select FIRST_NAME, LAST_NAME, concat(FIRST_NAME,' ',LAST_NAME) as NAME from emp_record_table where DEPT
='FINANCE';

/* 6. Write a query to list only those employees who have someone reporting to them.

Also, show the number of reporters (including the President). */

select EMP_ID, Concat(FIRST_NAME,' ',LAST_NAME) as NAME, MANAGER_ID from emp_record_table;

select concat(m.FIRST_NAME, ' ', m.LAST_NAME) as MANAGER, group_concat(concat(e.FIRST_NAME,' ', e.LAST_NAME)) as

EMPLOYEES

from emp_record_table as m

JOIN emp_record_table as e

ON m.EMP_ID=e.MANAGER_ID

group by concat(m.FIRST_NAME, ' ', m.LAST_NAME)

order by 1;

/* 7. Write a query to list down all the employees from the healthcare and finance departments using union.

Take data from the employee record table. */

select * from emp_record_table;

select concat(FIRST_NAME,' ', LAST_NAME) as NAME from emp_record_table

where DEPT = 'FINANCE'

UNION

select concat(FIRST_NAME,' ', LAST_NAME) as NAME from emp_record_table

where DEPT = 'HEALTHCARE';

/* 8. Write query to list down employee details such as EMP_ID, FIRST_NAME, LAST_NAME, ROLE, DEPARTMENT,and
EMP_RATING grouped by dept.

Also include the respective employee rating along with the max emp rating for the department. */

select * from emp_record_table;

SELECT e.EMP_ID, e.FIRST_NAME, e.LAST_NAME, [Link], [Link], e.EMP_RATING,

d.max_rating AS DEPT_MAX_RATING

FROM emp_record_table as e

JOIN (

SELECT DEPT, MAX(EMP_RATING) AS max_rating

FROM emp_record_table

GROUP BY DEPT

) as d ON [Link] = [Link];

/* 9. Write a query to calculate the minimum and the maximum salary of the employees in each role.

Take data from the employee record table. */

select ROLE, min(SALARY) as min_salary, max(SALARY) as max_salary from emp_record_table

group by ROLE

order by ROLE;

/* 10. Write a query to assign ranks to each employee based on their experience. Take data from the employee record
table.

experience =<2 Y'JUNIOR DATA SCIENTIST',< 2 to =<5 Y'ASSOCIATE DATA SCIENTIST', <5 to =<10 Y 'SENIOR DATA
SCIENTIST',

<10 to =<12 Y 'LEAD DATA SCIENTIST', <12 to =<16 Y 'MANAGER'. */

select EMP_ID, concat(FIRST_NAME, ' ', LAST_NAME) as NAME, EXP,

CASE

WHEN EXP <=2 THEN 'JUNIOR DATA SCIENTIST'

WHEN EXP >2 AND EXP <=5 THEN 'ASSOCIATE DATA SCIENTIST'

WHEN EXP >5 AND EXP <=10 THEN 'SENIOR DATA SCIENTIST'

WHEN EXP >10 AND EXP <=12 THEN 'LEAD DATA SCIENTIST'

WHEN EXP >12 AND EXP <=16 THEN 'MANAGER'

ELSE 'EXP OUT OF RANGE'

END as RANKING

from emp_record_table

order by 3;
/* 11. Write a query to create a view that displays employees in various countries whose salary is more than six
thousand.

Take data from the employee record table. */

create view SALARY_VIEW as select * from emp_record_table

where SALARY>6000;

select concat(FIRST_NAME,' ',LAST_NAME) as NAME, COUNTRY, SALARY from salary_view order by 2;

/* 12 Write a nested query to find employees with experience of more than ten years.

Take data from the employee record table. */

select * from emp_record_table

where EMP_ID IN (

select EMP_ID from emp_record_table

where EXP>10

order by EXP;

/* 13. Write a query to create a stored procedure to retrieve the details of the employees whose experience is more than
three years.

Take data from the employee record table. */

delimiter $$

create procedure emp_details(experience INT)

begin

select * from emp_record_table

where EXP>experience

order by EXP;

end

$$

call emp_details(3);
/* 14. Write a query using stored functions in the project table to check

whether the job profile assigned to each employee in the data science team matches the organization’s set standard. */

select * from proj_table;

select * from data_science_team;

DELIMITER $$

CREATE FUNCTION check_profile_match(EXP INT, ROLE VARCHAR(50))

RETURNS VARCHAR(10)

DETERMINISTIC

BEGIN

DECLARE expected_profile VARCHAR(50);

IF exp <= 2 THEN

SET expected_profile = 'JUNIOR DATA SCIENTIST';

ELSEIF exp <= 5 THEN

SET expected_profile = 'ASSOCIATE DATA SCIENTIST';

ELSEIF exp <= 10 THEN

SET expected_profile = 'SENIOR DATA SCIENTIST';

ELSEIF exp <= 12 THEN

SET expected_profile = 'LEAD DATA SCIENTIST';

ELSE

SET expected_profile = 'MANAGER';

END IF;

IF expected_profile = ROLE THEN

RETURN 'MATCH';

ELSE

RETURN 'MISMATCH';

END IF;

END $$

DELIMITER ;
/* 15. Create an index to improve the cost and performance of the query to find the employee whose

FIRST_NAME is ‘Eric’ in the employee table after checking the execution plan. */

explain select * from emp_record_table where FIRST_NAME='Eric';

create INDEX name_index ON emp_record_table(FIRST_NAME(20));

/* 16. Write a query to calculate the bonus for all the employees,

based on their ratings and salaries (Use the formula: 5% of salary * employee rating). */

select concat(FIRST_NAME,' ',LAST_NAME) as NAME, SALARY, EMP_RATING,

round((0.5*SALARY + EMP_RATING),1) as BONUS from emp_record_table;

/* 17. Write a query to calculate the average salary distribution based on the continent and country.

Take data from the employee record table. */

select CONTINENT, COUNTRY, avg(SALARY) as AVERAGE_SALARY

from emp_record_table

group by CONTINENT, COUNTRY;