Emplyee and Manager management.sql

# CREATING DATABASE
CREATE DATABASE EMPLOYEE_DETAILS;
# USING DATABASE
USE EMPLOYEE_DETAILS;

# CREATING TABLES EMP AND DEPT
CREATE TABLE EMP (
    EMPID INT PRIMARY KEY,
    ENAME VARCHAR(20) NOT NULL, 
    JOB VARCHAR(20) DEFAULT NULL, 
    MGRID INT DEFAULT NULL,
    HIREDATE DATE DEFAULT NULL,
    SAL DECIMAL(8,2) DEFAULT NULL,
    COMM DECIMAL(8,2) DEFAULT NULL,
    DEPTNO INT DEFAULT NULL
);

CREATE TABLE DEPT(
    DEPTNO INT PRIMARY KEY,
    DNAME VARCHAR(20) NOT NULL, 
    LOC VARCHAR(20) DEFAULT NULL
);

# INSERTING VALUES INTO TABLES
INSERT INTO EMP (EMPID, ENAME, JOB, MGRID, HIREDATE, SAL, COMM, DEPTNO) VALUES
(7369,'SMITH','CLERK',7902,'1980-12-17',800.00,NULL,20),
(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600.00,300.00,30),
(7521,'WARD','SALESMAN',7698,'1981-02-22',1250.00,500.00,30),
(7566,'JONES','MANAGER',7839,'1981-04-02',2975.00,NULL,20),
(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250.00,1400.00,30),
(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850.00,NULL,30),
(7782,'CLARK','MANAGER',7839,'1981-06-09',2450.00,NULL,10),
(7788,'SCOTT','ANALYST',7566,'1982-12-09',3000.00,NULL,20),
(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000.00,NULL,10),
(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500.00,NULL,30),
(7876,'ADAMS','CLERK',7788,'1983-01-12',1100.00,NULL,20),
(7900,'JAMES','CLERK',7698,'1981-12-03',950.00,NULL,30),
(7902,'FORD','ANALYST',7566,'1981-12-03',3000.00,NULL,20),
(7934,'MILLER','CLERK',7782,'1982-01-23',1300.00,NULL,10);

INSERT INTO DEPT (DEPTNO, DNAME, LOC) VALUES
(10,'ACCOUNTING','NEW YORK'),
(20,'RESEARCH','DALLAS'),
(30,'SALES','CHICAGO'),
(40,'OPERATIONS','BOSTON');

# DISPLAY TABLE STRUCTURE
DESC EMP;
DESC DEPT;

# DISPLAYING TABLE DATA
SELECT * FROM EMP;
SELECT * FROM DEPT;

# QUERY TO RETRIEVE ALL EMPLOYEE DETAILS
SELECT * FROM EMP;

# QUERY TO RETRIEVE PARTICULAR COLUMNS 
SELECT EMPID, ENAME, JOB, SAL FROM EMP;

# QUERY USING WHERE CLAUSE
SELECT ENAME, JOB
FROM EMP 
WHERE JOB='ANALYST';

# EMPLOYEES WITH SALARY > 1500
SELECT ENAME, SAL
FROM EMP
WHERE SAL > 1500;

# SORT EMPLOYEES BY SALARY
SELECT * 
FROM EMP 
ORDER BY SAL DESC;

# TOTAL NUMBER OF EMPLOYEES
SELECT COUNT(EMPID) AS TOTAL_EMPLOYEES
FROM EMP;

# TOTAL NUMBER OF DEPARTMENTS
SELECT COUNT(DEPTNO) AS TOTAL_DEPARTMENTS
FROM DEPT;

# MAXIMUM SALARY IN EACH DEPARTMENT
SELECT DEPTNO, MAX(SAL) AS MAX_SALARY
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

# MINIMUM SALARY IN EACH DEPARTMENT
SELECT DEPTNO, MIN(SAL) AS MIN_SALARY
FROM EMP
GROUP BY DEPTNO
ORDER BY DEPTNO;

# NUMBER OF EMPLOYEES IN EACH JOB ROLE
SELECT JOB, COUNT(EMPID) AS TOTAL_EMPLOYEES
FROM EMP 
GROUP BY JOB;

# AVERAGE SALARY IN EACH DEPARTMENT > 1900
SELECT DEPTNO, AVG(SAL) AS AVG_SALARY
FROM EMP
GROUP BY DEPTNO
HAVING AVG(SAL) > 1900;

# EMPLOYEES WHO ARE NOT TAKING COMMISSION
SELECT ENAME
FROM EMP 
WHERE COMM IS NULL;

# MONTHLY AND ANNUAL SALARY
SELECT ENAME, SAL AS MONTHLY_SALARY, SAL * 12 AS ANNUAL_SALARY
FROM EMP;

# SECOND HIGHEST SALARY EMPLOYEE
SELECT *
FROM EMP
WHERE SAL =
(SELECT MAX(SAL)
 FROM EMP
 WHERE SAL < (SELECT MAX(SAL) FROM EMP));

# EMPLOYEES WORKING IN NEW YORK
SELECT ENAME
FROM EMP 
WHERE DEPTNO =
(SELECT DEPTNO FROM DEPT WHERE LOC = 'NEW YORK');

# 3rd HIGHEST SALARY EMPLOYEE (SUBQUERY)
SELECT *
FROM EMP E1
WHERE (SELECT COUNT(DISTINCT E2.SAL)
       FROM EMP E2
       WHERE E2.SAL >= E1.SAL) = 3;

# 3rd HIGHEST SALARY USING WINDOW FUNCTION
SELECT *
FROM (
    SELECT EMP.*, DENSE_RANK() OVER (ORDER BY SAL DESC) AS RNK
    FROM EMP
) AS T
WHERE RNK = 3;

# EMPLOYEE NAME WITH DEPARTMENT AND LOCATION
SELECT E.ENAME, D.DNAME, D.LOC
FROM EMP E 
JOIN DEPT D ON E.DEPTNO = D.DEPTNO;

# EMPLOYEE NAME WITH THEIR MANAGER NAME
SELECT E.ENAME AS EMPLOYEE_NAME, M.ENAME AS MANAGER_NAME
FROM EMP E 
LEFT JOIN EMP M ON E.MGRID = M.EMPID;

# EMPLOYEES WORKING IN SALES DEPARTMENT
SELECT E.ENAME
FROM EMP E 
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE D.DNAME = 'SALES';

# 2ND HIGHEST SALARY WITH DEPARTMENT DETAILS
SELECT E.*, D.DNAME, D.LOC
FROM EMP E 
JOIN DEPT D ON E.DEPTNO = D.DEPTNO
WHERE E.SAL =
(SELECT MAX(SAL)
 FROM EMP
 WHERE SAL < (SELECT MAX(SAL) FROM EMP));