Scribd
Upload
5 views3 pages

Employee SQL Project Report

The document outlines SQL queries for employee performance mapping, including creating a database, selecting employee details, filtering by ratings, and aggregating salary information. It also includes advanced SQL features such as creating views, nested queries, stored functions, and indexing. The queries cover various aspects of employee data, including roles, departments, and bonus calculations.

Uploaded by

nels.sharma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views3 pages

Employee SQL Project Report

The document outlines SQL queries for employee performance mapping, including creating a database, selecting employee details, filtering by ratings, and aggregating salary information. It also includes advanced SQL features such as creating views, nested queries, stored functions, and indexing. The queries cover various aspects of employee data, including roles, departments, and bonus calculations.

Uploaded by

nels.sharma
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

Employee Performance Mapping - SQL

Project
1. Create Database
CREATE DATABASE employee;
USE employee;

3. Employee Basic Details


SELECT EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPT
FROM emp_record_table;

4(a). EMP_RATING < 2


SELECT EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPT, EMP_RATING
FROM emp_record_table
WHERE EMP_RATING < 2;

4(b). EMP_RATING > 4


SELECT EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPT, EMP_RATING
FROM emp_record_table
WHERE EMP_RATING > 4;

4(c). EMP_RATING BETWEEN 2 AND 4


SELECT EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPT, EMP_RATING
FROM emp_record_table
WHERE EMP_RATING BETWEEN 2 AND 4;

5. Finance Employee Names


SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS NAME
FROM emp_record_table
WHERE DEPT = 'FINANCE';

6. Leadership Roles
SELECT EMP_ID, FIRST_NAME, ROLE, DEPT
FROM emp_record_table
WHERE ROLE IN ('MANAGER', 'PRESIDENT', 'CEO');
7. Healthcare and Finance Employees
SELECT * FROM emp_record_table WHERE DEPT = 'HEALTHCARE'
UNION
SELECT * FROM emp_record_table WHERE DEPT = 'FINANCE';

8. Max Rating per Department


SELECT EMP_ID, FIRST_NAME, LAST_NAME, ROLE, DEPT, EMP_RATING,
MAX(EMP_RATING) OVER (PARTITION BY DEPT) AS MAX_DEPT_RATING
FROM emp_record_table;

9. Min & Max Salary per Role


SELECT ROLE, MIN(SALARY) AS MIN_SALARY, MAX(SALARY) AS MAX_SALARY
FROM emp_record_table
GROUP BY ROLE;

10. Rank by Experience


SELECT EMP_ID, FIRST_NAME, EXP,
RANK() OVER (ORDER BY EXP DESC) AS RANK_EXP
FROM emp_record_table;

11. View Creation


CREATE VIEW high_salary_employees AS
SELECT * FROM emp_record_table
WHERE SALARY > 6000;

12. Nested Query


SELECT * FROM emp_record_table
WHERE EMP_ID IN (
SELECT EMP_ID FROM emp_record_table WHERE EXP > 10
);

13. Stored Function


CREATE FUNCTION check_role(exp INT)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
IF exp <= 2 THEN RETURN 'JUNIOR DATA SCIENTIST';
ELSEIF exp <= 5 THEN RETURN 'ASSOCIATE DATA SCIENTIST';
ELSEIF exp <= 10 THEN RETURN 'SENIOR DATA SCIENTIST';
ELSEIF exp <= 12 THEN RETURN 'LEAD DATA SCIENTIST';
ELSEIF exp <= 16 THEN RETURN 'MANAGER';
ELSE RETURN 'UNKNOWN';
END IF;
END;

14. Create Index


CREATE INDEX idx_first_name
ON emp_record_table(FIRST_NAME);

15. Bonus Calculation


SELECT EMP_ID, FIRST_NAME, SALARY, EMP_RATING,
(SALARY * 0.05 * EMP_RATING) AS BONUS
FROM emp_record_table;

16. Avg Salary by Location


SELECT CONTINENT, COUNTRY, AVG(SALARY) AS AVG_SALARY
FROM emp_record_table
GROUP BY CONTINENT, COUNTRY;

You might also like