HR_Database_Practice.sql

SELECT datname FROM pg_database WHERE datistemplate = FALSE;
--Table 1
CREATE TABLE employee(
emp_id SERIAL PRIMARY KEY,
emp_name VARCHAR(20) NOT NULL,
dept VARCHAR(20) NOT NULL DEFAULT 'new',
salary DECIMAL(10,2) NOT NULL DEFAULT 30000,
hire_date DATE NOT NULL DEFAULT CURRENT_DATE
);
SELECT * FROM EMPLOYEE;
INSERT INTO EMPLOYEE(EMP_NAME,DEPT,SALARY) VALUES('HUSSAIN','IT',50000), ('ALI','FINANCE',55000),('MUHAMMAD','DATA-ANALYST',50000) RETURNING *;
-- Insert 100 employees with real names
INSERT INTO employee (emp_name, dept, salary, hire_date)
SELECT 
    (ARRAY[
        'Hussain','Ali','Muhammad','Fatima','Ayesha','Sara','Bilal','Usman','Zain','Hamza',
        'Imran','Khalid','Nadia','Sana','Amna','Shahid','Tariq','Farhan','Rashid','Kamran',
        'Asif','Adnan','Saad','Omar','Yasir','Hina','Mehwish','Lubna','Shazia','Bushra',
        'Javed','Noman','Arif','Waqar','Rameez','Sajid','Anwar','Shahbaz','Iqbal','Tanveer',
        'Kashif','Fahad','Muneeb','Hassan','Nouman','Rauf','Sami','Zeeshan','Atif','Majid',
        'Rabia','Mahnoor','Anam','Mariam','Kiran','Sadia','Aqsa','Nimra','Hoor','Areeba',
        'Danish','Rehan','Salman','Naeem','Sarfaraz','Azhar','Junaid','Moiz','Taha','Adeel',
        'Fiza','Hafsa','Emaan','Laiba','Aqeel','Shafiq','Nashit','Abrar','Rizwan','Shahzad',
        'Ayaz','Nabeel','Arslan','Haris','Adil','Qasim','Sufyan','Talha','Saima','Sehrish',
        'Anila','Samina','Farzana','Parveen','Kalsoom','Zubair','Irfan','Waseem','Younis','Akram'
    ])[g] AS emp_name,
    (ARRAY['IT','FINANCE','HR','DATA-ANALYST','MARKETING'])[floor(random()*5)+1] AS dept,
    round((random() * 25000 + 30000)::numeric, 2) AS salary, -- salaries 30k–55k
    CURRENT_DATE - (floor(random()*1825))::int AS hire_date -- random date within last 5 years
FROM generate_series(1,100) g
RETURNING *;
--Updating
UPDATE EMPLOYEE SET EMP_NAME = UPPER(EMP_NAME) RETURNING *;
UPDATE EMPLOYEE
SET SALARY = CASE
		  		  WHEN DEPT = 'IT' THEN 50000
		  		  WHEN DEPT = 'FINANCE' THEN 60000
		  		  WHEN DEPT = 'MARKETING' THEN 45000
		  		  WHEN DEPT = 'HR' THEN 45000
		  		  WHEN DEPT = 'DATA-ANALYST' THEN 55000
					ELSE SALARY
	        END
RETURNING *;
--SELECT PRACTICE
SELECT *,ROUND((SALARY*0.10)) AS BONUS,ROUND((SALARY + (SALARY*0.10))) AS TOTAL_SALARY FROM EMPLOYEE ORDER BY EMP_ID;
SELECT DEPT,COUNT(DEPT) AS TOTAL_MEMBERS,SUM(SALARY) AS TOTAL_SALARY,ROUND(AVG(SALARY)) AS AVG_SALARY FROM EMPLOYEE GROUP BY DEPT ORDER BY TOTAL_SALARY DESC;
SELECT COUNT(EMP_NAME) AS TOTAL_EMPLOYEES FROM EMPLOYEE;
SELECT * FROM EMPLOYEE WHERE SALARY >=50000 ORDER BY EMP_ID;
SELECT COUNT(EMP_NAME) AS TOTAL_EMPLOYEE FROM EMPLOYEE WHERE SALARY >=50000;
SELECT * FROM EMPLOYEE WHERE SALARY BETWEEN 55000 AND 60000 ORDER BY EMP_ID;
SELECT * FROM EMPLOYEE WHERE EMP_NAME LIKE '%M%' ORDER BY EMP_ID;
SELECT * FROM EMPLOYEE ORDER BY EMP_ID;
SELECT *,CONCAT_WS('-',EMP_ID,EMP_NAME,DEPT) AS ID_NAME_DEPT FROM EMPLOYEE ORDER BY EMP_ID;
SELECT * FROM EMPLOYEE WHERE SALARY>=60000 ORDER BY EMP_ID LIMIT 10;

--Table 2
CREATE TABLE JOBS(
EMP_ID INT PRIMARY KEY,
JOB VARCHAR(20) NOT NULL DEFAULT 'in-traning',
PHONE_NUMBER VARCHAR(15) NOT NULL DEFAULT 'NOT-PROVIDED',
EMAIL VARCHAR(50) NOT NULL UNIQUE,
CONSTRAINT FK_EMP FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID) ON DELETE CASCADE
);
SELECT * FROM JOBS;
-- Bulk insert jobs for all employees
INSERT INTO jobs (emp_id, job, phone_number, email)
SELECT 
    e.emp_id,
    (ARRAY['Manager','Accountant','Data Scientist','HR Officer',
           'Software Engineer','Marketing Executive','Analyst',
           'Coordinator','Consultant','Assistant'])[floor(random()*10)+1] AS job,
    '0300' || lpad((floor(random()*9999999))::text, 7, '0') AS phone_number,
    lower(e.emp_name) || '.' || e.emp_id || '@example.com' AS email
FROM employee e
RETURNING *;
--JOINS
SELECT * FROM EMPLOYEE E INNER JOIN JOBS J ON E.EMP_ID = J.EMP_ID;
SELECT E.EMP_ID,E.EMP_NAME,E.DEPT,J.JOB,J.PHONE_NUMBER,J.EMAIL,E.SALARY,E.HIRE_DATE FROM EMPLOYEE E INNER JOIN JOBS J ON E.EMP_ID = J.EMP_ID ORDER BY E.EMP_ID;
SELECT E.EMP_ID,E.EMP_NAME,E.DEPT,J.JOB,J.PHONE_NUMBER,J.EMAIL,E.SALARY,E.HIRE_DATE FROM EMPLOYEE E LEFT JOIN JOBS J ON E.EMP_ID = J.EMP_ID ORDER BY E.EMP_ID;
SELECT E.EMP_ID,E.EMP_NAME,E.DEPT,J.JOB,J.PHONE_NUMBER,J.EMAIL,E.SALARY,E.HIRE_DATE FROM EMPLOYEE E RIGHT JOIN JOBS J ON E.EMP_ID = J.EMP_ID ORDER BY E.EMP_ID;
--Table 3
CREATE TABLE rates(
EMP_ID INTEGER PRIMARY KEY,
RATING DECIMAL(2,1) NOT NULL DEFAULT 0.0,
PERFORMANCE TEXT NOT NULL DEFAULT 'NEW',
WORKING_DAYS INT,
HOLIDAYS INT,
CONSTRAINT FK_EMP FOREIGN KEY(EMP_ID) REFERENCES EMPLOYEE(EMP_ID) ON DELETE CASCADE 
);
SELECT * FROM RATES;
-- DELETING TABLE
DROP TABLE RATES;
-- Bulk insert performance ratings for all employees
INSERT INTO rates (emp_id, rating, performance, working_days, holidays)
SELECT 
    e.emp_id,
    round((random()*4 + 1)::numeric, 1) AS rating, -- ratings between 1.0 and 5.0
    (ARRAY['EXCELLENT','GOOD','AVERAGE','POOR','NEW'])[floor(random()*5)+1] AS performance,
    floor(random()*250 + 100) AS working_days, -- between 100 and 350 days
    floor(random()*30) AS holidays -- between 0 and 30 holidays
FROM employee e
RETURNING *;
--JOINING ALL 3 TABLES
SELECT * FROM EMPLOYEE E JOIN JOBS J ON E.EMP_ID=J.EMP_ID JOIN RATES R ON E.EMP_ID = R.EMP_ID; 
SELECT E.EMP_ID, E.EMP_NAME, E.DEPT, J.JOB, R.WORKING_DAYS, R.HOLIDAYS, R.PERFORMANCE, R.RATING, E.SALARY, E.HIRE_DATE, J.PHONE_NUMBER, J.EMAIL FROM EMPLOYEE E JOIN JOBS J ON E.EMP_ID = J.EMP_ID JOIN RATES R ON E.EMP_ID = R.EMP_ID ORDER BY EMP_ID;
--JOINING ALL TABLES AND CALCULATIONS AND CREATING VIEW
CREATE VIEW FINAL_DATA AS
SELECT E.EMP_ID, E.EMP_NAME, E.DEPT, J.JOB, R.WORKING_DAYS, R.HOLIDAYS, R.PERFORMANCE, R.RATING, E.SALARY, ROUND((SALARY * 0.10)) AS BONUS, ROUND((SALARY + (SALARY * 0.10))) AS TOTAL_SALARY, J.PHONE_NUMBER, J.EMAIL FROM EMPLOYEE E JOIN JOBS J ON E.EMP_ID = J.EMP_ID JOIN RATES R ON E.EMP_ID = R.EMP_ID ORDER BY EMP_ID;
--USING VIEW
SELECT * FROM FINAL_DATA;
--ROLL UP
SELECT COALESCE(DEPT, 'GRAND TOTAL') AS DEPT,COUNT(DEPT) AS MEMBERS, SUM(TOTAL_SALARY) AS SALARY_DEPT FROM FINAL_DATA GROUP BY ROLLUP(DEPT) ORDER BY SALARY_DEPT;
--FINDING EMP WHOSE SALARY IS ABOVE AVG FROM EACH DEPARTMENT
WITH AVG_SAL AS (
SELECT DEPT, COUNT(DEPT) AS MEMBERS, ROUND(AVG(TOTAL_SALARY)) AS DEPT_SALARY_AVG FROM FINAL_DATA GROUP BY DEPT)
SELECT F.EMP_ID, F.EMP_NAME, F.DEPT, F.JOB, F.TOTAL_SALARY, A.DEPT_SALARY_AVG FROM FINAL_DATA F JOIN AVG_SAL A ON F.DEPT = A.DEPT WHERE F.TOTAL_SALARY >= A.DEPT_SALARY_AVG;
--FINDING EMP WHOSE SALARY LESS THEN MAX IN EACH DEPARTMENT
WITH MAX_SAL AS (
SELECT DEPT,COUNT(DEPT) AS MEMBERS, MAX(TOTAL_SALARY) AS MAX_SAL FROM FINAL_DATA GROUP BY DEPT)
SELECT F.EMP_ID, F.EMP_NAME, F.DEPT, F.JOB, F.SALARY, F.PERFORMANCE, F.RATING, M.MAX_SAL FROM FINAL_DATA F JOIN MAX_SAL M ON F.DEPT = M.DEPT WHERE F.SALARY < M.MAX_SAL;