Create a query that sums the salaries for a given role.
DROP TABLE IF EXISTS test_people;
CREATE TABLE test_people (
id_index INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
name varchar(255),
salary BIGINT
);
insert into test_people (name, salary) values
('Sophia',10000000000),
('Emma',1000000000),
('Olivia',100000000),
('Isabella', 10000000),
('Jackson',1000000),
('Aiden',100000),
('Liam',10000),
('Noah', 1000),
('Charlotte', 100),
('Caden', 10),
('Harper', 1);
DROP TABLE IF EXISTS test_roles;
CREATE TABLE test_roles (
id_index INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
role varchar(255)
);
insert into test_roles (role) values
('Minion'),
('Worker'),
('Manager'),
('CEO'),
('Evil Overlord');
DROP TABLE IF EXISTS test_assignments;
CREATE TABLE test_assignments (
person_id INT,
role_id INT
);
insert into test_assignments (person_id, role_id) values
(1, 5),
(2, 4),
(3, 3),
(4, 2),
(5, 1),
(6, 1),
(7, 1),
(8, 1);
drop view IF EXISTS test_join;
create view test_join as
select p.name, r.role, p.salary
from test_assignments a
inner join test_people p on a.person_id = p.id_index
inner join test_roles r on a.role_id = r.id_index;
select * from test_join;
select sum(salary) from test_join where role = "Minion";
DROP TABLE IF EXISTS test_people;
DROP TABLE IF EXISTS test_roles;
DROP TABLE IF EXISTS test_assignments;
drop view IF EXISTS test_join;
You must be logged in to post a comment.