Skip to content

Instantly share code, notes, and snippets.

@mikeknep

mikeknep/exercises.sql

Created February 28, 2015 21:19
Show Gist options
  • Save mikeknep/2f1e57376579ca810dd8 to your computer and use it in GitHub Desktop.
Save mikeknep/2f1e57376579ca810dd8 to your computer and use it in GitHub Desktop.
Download ZIP
Practice SQL
Raw
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;
Raw
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');
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment