Prática de exercícios avançados. https://www.db-fiddle.com/f/ueUCT8ks6Y17j3EX5yCUkB/3

exercicio_2oi345b.sql

-- schema do banco

CREATE TABLE IF NOT EXISTS departamento (
  id INT NOT NULL AUTO_INCREMENT,
  nome VARCHAR(128) NOT NULL,
  sobrenome VARCHAR(128) NOT NULL,
  localizacao VARCHAR(255) NOT NULL,
  PRIMARY KEY(id)
);


CREATE TABLE IF NOT EXISTS funcionario (
  id INT NOT NULL AUTO_INCREMENT,
  cod_emp VARCHAR(45) NOT NULL,
  nome VARCHAR(45) NOT NULL,
  sobrenome VARCHAR(45) NOT NULL,
  posto VARCHAR(45) NOT NULL,
  data_alta DATETIME NOT NULL,
  salario BIGINT NOT NULL,
  comissao BIGINT NOT NULL,
  id_departamento INT NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (id_departamento) REFERENCES departamento(id)
);


-- Inserindo dados na tabela departamento
INSERT INTO departamento (id, nome, sobrenome, localizacao) VALUES
(1, 'Software', 'Desenvolvimento', 'Los Tigres'),
(2, 'Sistemas', 'TI', 'Guadalupe'),
(3, 'Contabilidade', 'Financeiro', 'La Roca'),
(4, 'Vendas', 'Comercial', 'Plata'),
(5, 'Recursos Humanos', 'Gestão de Pessoas', 'Buenos Aires'),
(6, 'Marketing', 'Publicidade e Propaganda', 'São Paulo');

-- Inserindo dados na tabela funcionario
INSERT INTO funcionario (cod_emp, nome, sobrenome, posto, data_alta, salario, comissao, id_departamento) VALUES
-- Departamento: Vendas
('E-0001', 'César', 'Piñero', 'Vendedor', '2018-05-12 08:30:00', 80000, 15000, 4),
('E-0004', 'Jonathan', 'Aguilera', 'Vendedor', '2015-06-03 09:15:00', 85000, 10000, 4),
('E-0005', 'Daniel', 'Brezezicki', 'Vendedor', '2018-03-03 10:45:00', 83000, 10000, 4),
('E-0010', 'Nome', 'Sobrenome', 'Diretor', '2020-07-21 08:50:00', 78000, 12000, 4),
('E-0010', 'Fernando', 'López', 'Vendedor', '2020-07-21 08:50:00', 78000, 12000, 4),
('E-0011', 'Lucia', 'Fernández', 'Coordenador de Vendas', '2017-02-28 09:30:00', 95000, 8000, 4),

-- Departamento: Sistemas
('E-0002', 'Yosep', 'Kowaleski', 'Analista', '2015-07-14 11:00:00', 140000, 0, 2),
('E-0012', 'Rosa', 'Silva', 'Engenheira de Software', '2016-04-12 14:20:00', 135000, 0, 2),
('E-0012', 'Amara', 'Zuck', 'Engenheira de Software', '2016-04-12 14:20:00', 135000, 0, 2),
('E-0012', 'Katharina', 'Nippon', 'Engenheira de Software', '2016-04-12 14:20:00', 135000, 0, 2),
('E-0012', 'Yolanda', 'Zswin', 'Engenheira de Software', '2016-04-12 14:20:00', 135000, 0, 2),
('E-0013', 'Marcos', 'Alves', 'Administrador de Banco de Dados', '2019-06-23 12:10:00', 125000, 0, 2),

-- Departamento: Contabilidade
('E-0003', 'Mariela', 'Barrios', 'Diretor', '2014-06-05 13:30:00', 185000, 0, 3),
('E-0006', 'Mito', 'Barchuk', 'Presidente', '2014-06-05 14:00:00', 190000, 0, 3),
('E-0014', 'Carla', 'Caduq', 'Contador', '2017-09-10 10:10:00', 120000, 7000, 3),
('E-0014', 'Mariana', 'Moran', 'Contador', '2017-09-10 10:10:00', 100000, 5000, 3),
('E-0014', 'Bruna', 'Amsterdam', 'Contador', '2017-09-10 10:10:00', 90000, 6000, 3),
('E-0015', 'Roberto', 'Castro', 'Analista Financeiro', '2018-02-20 15:45:00', 105000, 3000, 3),

-- Departamento: Software
('E-0007', 'Nome', 'Sobrenome', 'Diretor', '2014-08-02 16:30:00', 60000, 0, 1),
('E-0007', 'Emilio', 'Galarza', 'Desenvolvedor', '2014-08-02 16:30:00', 60000, 0, 1),
('E-0016', 'Clara', 'Gomez', 'Engenheira de Software', '2019-11-14 09:50:00', 85000, 0, 1),
('E-0017', 'Ricardo', 'Pereira', 'Tech Lead', '2020-03-19 11:10:00', 125000, 0, 1),
('E-0018', 'Sofia', 'Martinez', 'Arquiteta de Software', '2016-12-03 08:40:00', 150000, 0, 1),

-- Departamento: Recursos Humanos
('E-0019', 'Bruna', 'Silveira', 'Coordenadora de RH', '2015-05-11 10:20:00', 95000, 5000, 5),
('E-0020', 'Diego', 'Farias', 'Analista de RH', '2019-09-05 14:10:00', 75000, 2000, 5),
('E-0021', 'Marta', 'Camargo', 'Gerente de RH', '2013-11-25 12:30:00', 120000, 0, 5),

-- Departamento: Marketing
('E-0022', 'Camilo', 'Torres', 'Designer Gráfico', '2018-06-01 13:45:00', 70000, 0, 6),
('E-0023', 'Fernanda', 'Meireles', 'Gerente de Marketing', '2016-02-07 08:00:00', 135000, 5000, 6),
('E-0024', 'Julio', 'Fernandez', 'Analista de Publicidade', '2020-01-15 09:15:00', 85000, 0, 6);

consultas feitas

-- q5 usando MIN() com subquery
SELECT f.nome, f.sobrenome, f.salario
FROM funcionario f
WHERE salario = (
  -- subquery
  SELECT MIN(salario) FROM funcionario
);

-- q5
SELECT f.nome, f.sobrenome, f.salario
FROM funcionario f
ORDER BY f.salario ASC
LIMIT 1;

-- q4 usando filtro direto no JOIN
SELECT d.nome, f.*
FROM funcionario f
JOIN departamento d ON f.id_departamento = d.id AND d.nome = 'Contabilidade'
ORDER BY f.nome;

-- q4
SELECT d.nome, f.*
FROM funcionario f
JOIN departamento d ON d.id = f.id_departamento
WHERE d.nome = 'Contabilidade'
ORDER BY f.nome;


-- q3 usando WITH (Common Table Expressions, CTE)
WITH cargo_mariela AS (
    SELECT posto FROM funcionario 
  	WHERE nome = 'Mariela' AND sobrenome = 'Barrios'
)
SELECT f.nome, f.salario, d.nome AS departamento
FROM funcionario f
JOIN departamento d ON f.id_departamento = d.id
JOIN cargo_mariela cm ON f.posto = cm.posto;


-- q3 usando SELF JOIN
SELECT f.nome, f.sobrenome, f.salario, d.nome
FROM funcionario f
JOIN funcionario f2 ON f2.posto = f.posto
JOIN departamento d ON d.id = f.id_departamento
WHERE f2.nome = 'Mariela' AND f2.sobrenome = 'Barrios';


-- q3 usando SUBQUERY
SELECT f.nome, f.sobrenome, f.salario, d.nome
FROM funcionario f
JOIN departamento d ON d.id = f.id_departamento
WHERE f.posto = (
	-- subquery
  	SELECT subf.posto FROM funcionario subf
  	WHERE subf.nome = 'Mariela' AND subf.sobrenome = 'Barrios'
);


-- q2
SELECT 
    COUNT(f.id) AS total_funcionarios,
	d.nome, d.localizacao
FROM departamento d
JOIN funcionario f ON d.id = f.id_departamento
GROUP BY d.id, d.nome, d.sobrenome, d.localizacao
HAVING total_funcionarios > 5;


-- q1
SELECT 
	d.nome, d.localizacao,
    f.posto, f.nome, f.sobrenome
FROM departamento d, funcionario f;