-- 1. Create the Tables
CREATE TABLE DESIGNATION (
    DESIG_CODE CHAR(16) PRIMARY KEY,
    DESIG_DESC CHAR(20)
);

CREATE TABLE DEPARTMENT (
    DEPT_CODE CHAR(16) PRIMARY KEY,
    DEPT_NAME CHAR(20)
);

CREATE TABLE EMPLOYEE (
    EMP_CODE CHAR(16) PRIMARY KEY,
    EMP_NAME CHAR(20),
    DEPT_CODE CHAR(16),
    DESIG_CODE CHAR(16),
    SEX CHAR(1),
    ADDRESS CHAR(25),
    CITY CHAR(20),
    STATE CHAR(20),
    PIN CHAR(6),
    BASIC DECIMAL(10,2), -- MySQL uses DECIMAL (or INT) instead of Oracle's NUMBER
    JN_DT DATE
);

-- 2. Display the structure of each table
-- DESCRIBE EMPLOYEE;
-- DESCRIBE DESIGNATION;
-- DESCRIBE DEPARTMENT;

-- 3. Insert rows into the tables
INSERT INTO DESIGNATION VALUES ('MGR', 'Manager');
INSERT INTO DESIGNATION VALUES ('EXEC', 'Executive');
INSERT INTO DESIGNATION VALUES ('OFF', 'Officer');
INSERT INTO DESIGNATION VALUES ('CLK', 'Clerk');
INSERT INTO DESIGNATION VALUES ('HLP', 'Helper');

INSERT INTO DEPARTMENT VALUES ('PERS', 'Personnel');
INSERT INTO DEPARTMENT VALUES ('PROD', 'Production');
INSERT INTO DEPARTMENT VALUES ('PURC', 'Purchase');
INSERT INTO DEPARTMENT VALUES ('FIN', 'Finance');
INSERT INTO DEPARTMENT VALUES ('RES', 'Research');

INSERT INTO EMPLOYEE VALUES ('E001', 'Amit Sharma', 'FIN', 'MGR', 'M', '12 MG Road', 'Mumbai', 'Maharashtra', '400001', 85000, '2005-04-12');
INSERT INTO EMPLOYEE VALUES ('E002', 'Priya Singh', 'PROD', 'EXEC', 'F', '34 Park Street', 'Kolkata', 'West Bengal', '700016', 62000, '2012-01-15');
INSERT INTO EMPLOYEE VALUES ('E003', 'Rahul Verma', 'PURC', 'OFF', 'M', '56 Tech Park', 'Bangalore', 'Karnataka', '560001', 45000, '2018-08-22');
INSERT INTO EMPLOYEE VALUES ('E004', 'Sneha Roy', 'PERS', 'CLK', 'F', '78 Lake View', 'Chennai', 'Tamil Nadu', '600001', 0, '2009-11-05'); 
INSERT INTO EMPLOYEE VALUES ('E005', 'Vikram Das', 'RES', 'HLP', 'M', '90 Ring Road', 'Delhi', 'Delhi', '110001', 25000, '2015-01-20');

-- 4. Insert rows without DEPT_CODE and BASIC
INSERT INTO EMPLOYEE (EMP_CODE, EMP_NAME, DESIG_CODE, SEX, ADDRESS, CITY, STATE, PIN, JN_DT) 
VALUES ('E006', 'Kiran Patel', 'CLK', 'F', '123 New Ave', 'Ahmedabad', 'Gujarat', '380001', '2019-06-14');

INSERT INTO EMPLOYEE (EMP_CODE, EMP_NAME, DESIG_CODE, SEX, ADDRESS, CITY, STATE, PIN, JN_DT) 
VALUES ('E007', 'Manoj Kumar', 'HLP', 'M', '456 Old Town', 'Jaipur', 'Rajasthan', '302001', '2021-01-10');

-- 5. Find the rows with unassigned DEPT_CODE
SELECT * FROM EMPLOYEE WHERE DEPT_CODE IS NULL;

-- 6. Find the rows with BASIC equal to zero
SELECT * FROM EMPLOYEE WHERE BASIC = 0;

-- 7. Find the rows with unassigned BASIC
SELECT * FROM EMPLOYEE WHERE BASIC IS NULL;

-- 8. Find the average basic of the employees
SELECT AVG(BASIC) AS AVERAGE_BASIC FROM EMPLOYEE;

-- 9. Replace the BASIC with 0 for the rows with unassigned Basic
UPDATE EMPLOYEE SET BASIC = 0 WHERE BASIC IS NULL;

-- 10. Again, find the average Basic
SELECT AVG(BASIC) AS NEW_AVERAGE_BASIC FROM EMPLOYEE;

-- 11. Delete the rows with unassigned DEPT_CODE
DELETE FROM EMPLOYEE WHERE DEPT_CODE IS NULL;

-- 12. Show the employee name & Net pay for all employees
SELECT EMP_NAME, (BASIC + (BASIC * 0.5) + (BASIC * 0.4)) AS NET_PAY FROM EMPLOYEE;

-- 13. Show the EMP_NAME & BASIC in the ascending order of DEPT_CODE
SELECT EMP_NAME, BASIC 
FROM EMPLOYEE 
ORDER BY DEPT_CODE ASC;

-- 14. Find the employees who have joined after 1st January 2010
SELECT * FROM EMPLOYEE WHERE JN_DT > '2010-01-01';

-- 15. Find, how many employees have joined in the month of January
-- Note: MySQL has a dedicated MONTH() function which makes this cleaner
SELECT COUNT(*) AS JAN_JOINERS 
FROM EMPLOYEE 
WHERE strftime('%m', JN_DT) = '01';

--16.
SELECT MAX(BASIC) AS MAX_BASIC, MIN(BASIC) AS MIN_BASIC 
FROM EMPLOYEE;

-- 17.
SELECT COUNT(*) AS FEMALE_EMPLOYEE_COUNT 
FROM EMPLOYEE 
WHERE SEX = 'F';

-- 18.
UPDATE EMPLOYEE 
SET CITY = UPPER(CITY);

-- 19.
SELECT COUNT(DISTINCT CITY) AS UNIQUE_CITIES_COUNT 
FROM EMPLOYEE;

--20.
SELECT * FROM EMPLOYEE 
ORDER BY DEPT_CODE ASC, BASIC DESC;