queries.sql

-- DEPARTMENTS TABLE -----------------

CREATE TABLE IF NOT EXISTS departments
(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  location VARCHAR(255)
);

------ INSERTING DATA INTO departments table -------------


-- EMPLOYEES TABLE -----------------
CREATE TABLE IF NOT EXISTS employees 
(
  id SERIAL PRIMARY KEY,
  first_name VARCHAR(255),
  last_name VARCHAR(255),
  salary INTEGER,
  department_id INTEGER,
  hire_date DATE,

  CONSTRAINT emp_depart_fk FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL
);


------- INSERTING DATA INTO employees TABLE --------------


-- PROJECTS TABLE -----------------
CREATE TABLE IF NOT EXISTS projects 
(
  id SERIAL PRIMARY KEY,
  name VARCHAR(255),
  budget INTEGER,
  department_id INTEGER,

  CONSTRAINT proj_depart_fk FOREIGN KEY(department_id) REFERENCES departments(id) ON DELETE SET NULL
);


------- INSERTING DATA INTO projects TABLE ---------------


-- PIVOT TABLE EMPLOYEE _ PROJECT -----------------
CREATE TABLE IF NOT EXISTS employee_projects
(
  employee_id INTEGER,
  project_id INTEGER,
  hours_worked INTEGER,

  CONSTRAINT emp_proj_fk FOREIGN KEY(project_id) REFERENCES projects(id) ON DELETE SET NULL,
  CONSTRAINT emp_proj_emp_id FOREIGN KEY(employee_id) REFERENCES employees(id) ON DELETE SET NULL
);

------- INSERTING DATA INTO employee_projects TABLE ------



--------- ================================================ ----------------------
--------- ==========  QUERIES AND EXERCIESES ============= ----------------------
--------- ================================================ ----------------------



----- EXERCISE 1 : GET ALL EMPLOYEES WITH THEIR DEPARTMENT NAMES

SELECT 
  e.first_name as first_name,
  e.last_name as last_name,
  d.name as departmen_name
FROM employees e
LEFT JOIN departments d 
  ON e.department_id = d.id
ORDER BY d.name;
  

----- EXERCISE 2 : GET ALL PROJECTS AND THEIR DEPARTMENT NAMES

SELECT DISTINCT
  p.name as project_name, 
  d.name as department_name
FROM projects p 
LEFT JOIN departments d
  ON p.department_id = d.id
ORDER BY d.name;



----- EXERCISE 3 : GET TOTAL NUMBER OF EMPLOYEES PER DEPARTMENT

SELECT
  d.name as department_name,
  COUNT(e.id) as number_of_employees
FROM employees e
LEFT JOIN departments d 
  ON e.department_id = d.id
GROUP BY d.name
ORDER BY number_of_employees DESC;



----- EXERCISE 4 : GET ALL EMPLOYEES WITH SALARY ABOVE AVERAGE


SELECT 
  first_name,
  last_name,
  salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC;


----- EXERCISE 5 : GET TOP 3 HIGHEST PAID EMPLOYEES PER DEPARTMENT

SELECT 
  first_name,
  last_name,
  salary,
  department_name
FROM (
    SELECT
      e.first_name as first_name,
      e.last_name as last_name, 
      e.salary as salary,
      d.name as department_name,
      ROW_NUMBER() over(PARTITION BY d.id ORDER BY e.salary DESC) as rn
    FROM employees e
    LEFT JOIN departments d
    ON e.department_id = d.id
)
WHERE rn <= 3 
ORDER BY department_name, salary DESC;




------ EXERCISE 6 : AVERAGE SALARY PER DEPARTMENT

SELECT 
  d.name as department_name,
  ROUND(AVG(e.salary)) as average_salary
FROM employees e 
LEFT JOIN departments d
  ON e.department_id = d.id
GROUP BY d.name
ORDER BY average_salary;



------ EXERCISE 7 : TOTAL BUDGET PER LOCATION

SELECT 
  d.location as d_location,
  ROUND(SUM(p.budget), 2) as total_budget
FROM projects p 
LEFT JOIN departments d
  ON p.department_id = d.id
GROUP BY d.location;



------ EXERCISE 8 : COUNT OF EMPLOYEES WORKING ON MORE THAN 2 PROJECTS

SELECT COUNT(*)
FROM (
    SELECT employee_id
    FROM employee_projects
    GROUP BY employee_id
    HAVING count(project_id) >= 2
);



------ EXERCISE 9 : JOIN EMPLOYEES DEPARTMENTS PROJECTS deps that have proj

SELECT DISTINCT
  e.first_name as first_name,
  e.last_name as last_name,
  d.name as department_name,
  p.name as project_name
FROM employees e 
INNER JOIN departments d
  ON e.department_id = d.id
INNER JOIN projects p 
  ON p.department_id = d.id;



------ EXERCISE 10 : SHOW EACH EMPLOYEE WITH THEIR PROJECTS AND WORKED HOURS

SELECT DISTINCT
  e.first_name as first_name, 
  e.last_name as last_name,
  p.name as project_name,
  ep.hours_worked as hours_worked
FROM employee_projects ep
INNER JOIN projects p 
  ON ep.project_id = p.id
INNER JOIN employees e
  ON ep.employee_id = e.id
ORDER BY p.name;


------ EXERCISE 11 : CTE THAT SHOWS EACH DEPARTMENT WITH TOTAL SALARY EXPENCES 
------ EXERCISE 12 : CTE THAT LISTS DEPS WITH SAL ABOVE 10000

SELECT * FROM employees;

WITH
  total_sal_expences AS (
    SELECT 
      SUM(e.salary) as total_salary,
      d.name as department_name
    FROM employees e 
    INNER JOIN departments d
      ON e.department_id = d.id
    GROUP BY d.name
    ORDER BY total_salary
  ),
  more_salary AS (
    SELECT *
    FROM total_sal_expences
    WHERE total_salary > 10000
  )


------ EXERCISE 13 : CTE TO FIND EMPLOYEES WORKING ON PROJECTS WITH BUDGET > 10000


SELECT * FROM projects;

WITH 
  everything AS (
    SELECT 
      e.first_name AS first_name,
      e.last_name AS last_name, 
      p.budget as project_budget,
      p.name as project_name
    FROM employee_projects as ep
    INNER JOIN employees e
      ON ep.employee_id = e.id
    INNER JOIN projects p 
      ON ep.project_id = p.id
  )
SELECT DISTINCT *
FROM everything
WHERE project_budget > 10000
ORDER BY project_budget;



------ EXERCISE 14 : CREATE A VIEW high_salary_employees 
------ employees with salary higher than their department average

CREATE OR REPLACE VIEW high_salary_employees AS (
  WITH added_avg_salaries AS (
    SELECT distinct
      e.first_name as first_name,
      e.last_name as last_name,
      e.salary as salary,
      e.department_id as department_id,
      ROUND(AVG(e.salary) OVER(PARTITION BY e.department_id), 2) as avg_salary
    FROM employees e
    ORDER BY e.department_id
  )
  SELECT 
    first_name,
    last_name,
    salary
  FROM added_avg_salaries
  WHERE salary > avg_salary
);
SELECT * FROM high_salary_employees;


------ EXERCISE 15 : CREATE VIEW project_loads showing project name 
------ and total hours worked on it by all employees

CREATE OR REPLACE VIEW project_loads AS (
	SELECT DISTINCT
		p.name as project_name,
		SUM(ep.hours_worked) AS total_worked_hours
	FROM employee_projects ep 
	INNER JOIN employees e 
		ON e.id = ep.employee_id
	INNER JOIN projects p
		ON p.id = ep.project_id
	GROUP BY project_name
	ORDER BY project_name ASC
);




------ EXERCISE 16 : CREATE A FUNCTION 
------ increase_salary(emp_id INT , percent NUMERIC)
------ that increases an employees salary

CREATE OR REPLACE FUNCTION increase_salary(emp_id INTEGER, percent NUMERIC)
RETURNS TABLE (
	id INTEGER,
	first_name VARCHAR,
	last_name VARCHAR,
	salary NUMERIC)
AS $$
	BEGIN 
		UPDATE employees
		SET salary = salary * (1 + percent / 100.0)
		WHERE employees.id = emp_id;

		RETURN QUERY (
			SELECT 
				id,
				first_name,
				last_name,
				salary
			FROM employees
			WHERE employees.id = emp_id
		);
	END;
$$ LANGUAGE plpgsql;



------ EXERCISE 17 : CREATE A FUNCTION total_hours that returns
------ total hours worked by an employee across all projects

DROP FUNCTION total_hours;
CREATE OR REPLACE FUNCTION total_hours(emp_id INTEGER)
RETURNS TABLE (
	first_name VARCHAR,
	last_name VARCHAR,
	total_hours BIGINT)
AS $$ 
	BEGIN 
		RETURN QUERY
			SELECT
				e.first_name AS first_name,
				e.last_name AS last_name,
				SUM(ep.hours_worked) as total_hours
			FROM employee_projects ep 
			INNER JOIN employees e 
				ON e.id = ep.employee_id
			WHERE ep.employee_id = emp_id
			GROUP BY e.id;
	END;
$$ LANGUAGE plpgsql;



------ EXERCISE 18 : Show every employee with the average salary per department next to them.
SELECT 
	e.first_name as first_name,
	e.last_name as last_name,
	ROUND(AVG(e.salary) OVER(PARTITION BY(e.department_id)), 2) as department_avg_salary
FROM employees e;