Practice SQL

exercises.sql

/* Exercises */

/* List employees (names) who have a bigger salary than their boss */
--terse
select employees.name
from employees
join employees as bosses
on bosses.id = employees.boss_id
where employees.salary > bosses.salary;

--verbose
select
  employees.name as employee_name,
  employees.salary as employee_salary,
  bosses.name as boss_name,
  bosses.salary as boss_salary
from employees
join employees as bosses
on bosses.id = employees.boss_id
where employees.salary > bosses.salary;



/* List employees who have the biggest salary in their departments */
--terse
select name
from employees
join (
  select department_id, max(salary) as salary
  from employees
  group by department_id ) m
using (department_id, salary);

--verbose
select departments.name, employees.name, employees.salary
from employees
join departments on departments.id = employees.department_id
join (
  select department_id, max(salary) as salary
  from employees
  group by department_id ) a
using (department_id, salary);



/* List departments that have less than 3 people in it */
--terse
select department_id, count(*)
from employees
group by department_id
having count(*) < 3;

--verbose
select departments.name, c.count
from (
  select department_id, count(*)
  from employees
  group by department_id
  having count(*) < 3 ) c
join departments on departments.id = c.department_id;



/* List all departments along with the number of people there */
select departments.name, count(employees.id)
from departments
left join employees
on departments.id = employees.department_id
group by departments.name;



/* List employees that don't have a boss in the same department */
--terse
select employees.name
from employees
join employees as bosses
on employees.boss_id = bosses.id
where employees.department_id != bosses.department_id;

--verbose
select
  emp.name as employee_name,
  empdept.name as employee_department,
  bosses.name as boss_name,
  bossdept.name as boss_department
from employees as emp
join employees as bosses
  on emp.boss_id = bosses.id
join departments as empdept
  on emp.department_id = empdept.id
join departments as bossdept
  on bosses.department_id = bossdept.id
where emp.department_id != bosses.department_id;



/* List all departments along with the total salary there */
select departments.name, sum(employees.salary)
from departments
join employees on employees.department_id = departments.id
group by departments.name;

setup.sql

/* Schema
employees
  id | department_id | boss_id | name  | salary
  ----+---------------+---------+-------+--------
   1 |             1 |       2 | Joe   |     10
   2 |             1 |       3 | Steve |      9
   3 |             2 |       2 | Mike  |      3
  (cont.)

departments
  id | name
  ----+-------------
   1 | HR
   2 | Accounting
   3 | Sales
   4 | Design
   5 | Engineering
*/


/* Basic commands to seed a postgres database */
CREATE DATABASE sandbox;
\c sandbox;

CREATE TABLE employees(
  id SERIAL UNIQUE,
  department_id INTEGER NOT NULL,
  boss_id INTEGER,
  name VARCHAR(36) NOT NULL,
  salary INTEGER NOT NULL
);

INSERT INTO employees (department_id, boss_id, name, salary)
  VALUES (1, 2, 'Mike', 64),
         (4, 4, 'John', 58),
         -- etc. ,
         (3, 2, 'Lindsey', 70);

CREATE TABLE departments(
  id SERIAL UNIQUE,
  name VARCHAR(36) NOT NULL
);

INSERT INTO departments (name)
  VALUES ('HR'),
         ('Accounting'),
         -- etc. ,
         ('Sales');