Atividade - DML SELECT (MySQL)

Fork
DROP TABLE IF EXISTS Dependente;
DROP TABLE IF EXISTS Funcionario;


CREATE TABLE Funcionario (
    id_funcionario INT PRIMARY KEY,
    nome VARCHAR(100),
    data_contratacao DATE,
    salario DECIMAL(10,2),
    departamento VARCHAR(100),
    email VARCHAR(100),
    telefone VARCHAR(20)
);


CREATE TABLE Dependente (
    id_dependente INT PRIMARY KEY,
    id_funcionario INT,
    nome_dependente VARCHAR(100),
    parentesco VARCHAR(50),
    data_nascimento_dependente DATE,

    FOREIGN KEY (id_funcionario)
    REFERENCES Funcionario(id_funcionario)
);


INSERT INTO Funcionario VALUES
(1, 'Ana Silva', '2020-01-15', 5500.00, 'Recursos Humanos', 'ana.silva@empresa.com', '(11) 98765-4321'),
(2, 'Bruno Santos', '2019-03-20', 7200.00, 'TI', 'bruno.santos@empresa.com', '(11) 98765-4322'),
(3, 'Carla Oliveira', '2021-06-10', 6000.00, 'Marketing', 'carla.oliveira@empresa.com', '(11) 98765-4323'),
(4, 'Daniel Pereira', '2018-11-01', 8500.00, 'TI', 'daniel.pereira@empresa.com', '(11) 98765-4324'),
(5, 'Eduarda Costa', '2022-02-28', 4800.00, 'Financeiro', 'eduarda.costa@empresa.com', '(11) 98765-4325'),
(6, 'Fernando Martins', '2019-07-05', 7000.00, 'Vendas', 'fernando.martins@empresa.com', '(11) 98765-4326'),
(7, 'Gabriela Rodrigues', '2020-09-12', 5800.00, 'Recursos Humanos', 'gabriela.rodrigues@empresa.com', '(11) 98765-4327'),
(8, 'Henrique Almeida', '2023-01-01', 6500.00, 'TI', 'henrique.almeida@empresa.com', '(11) 98765-4328'),
(9, 'Isabela Gonçalves', '2021-04-18', 5200.00, 'Marketing', 'isabela.goncalves@empresa.com', '(11) 98765-4329'),
(10, 'João Lima', '2017-08-25', 9000.00, 'TI', 'joao.lima@empresa.com', '(11) 98765-4330'),
(11, 'Karen Fernandes', '2022-05-30', 4500.00, 'Financeiro', 'karen.fernandes@empresa.com', '(11) 98765-4331'),
(12, 'Lucas Gomes', '2018-02-14', 7800.00, 'Vendas', 'lucas.gomes@empresa.com', '(11) 98765-4332'),
(13, 'Mariana Nunes', '2020-10-01', 6100.00, 'Recursos Humanos', 'mariana.nunes@empresa.com', '(11) 98765-4333'),
(14, 'Nuno Ribeiro', '2023-03-08', 7000.00, 'TI', 'nuno.ribeiro@empresa.com', '(11) 98765-4334'),
(15, 'Olivia Soares', '2021-01-20', 5000.00, 'Marketing', 'olivia.soares@empresa.com', '(11) 98765-4335'),
(16, 'Pedro Vieira', '2019-06-03', 8200.00, 'TI', 'pedro.vieira@empresa.com', '(11) 98765-4336'),
(17, 'Quiteria Dias', '2022-07-19', 4900.00, 'Financeiro', 'quiteria.dias@empresa.com', '(11) 98765-4337'),
(18, 'Rafael Pinto', '2018-09-07', 7500.00, 'Vendas', 'rafael.pinto@empresa.com', '(11) 98765-4338'),
(19, 'Sofia Melo', '2020-04-22', 5900.00, 'Recursos Humanos', 'sofia.melo@empresa.com', '(11) 98765-4339'),
(20, 'Thiago Coelho', '2023-02-01', 6800.00, 'TI', 'thiago.coelho@empresa.com', '(11) 98765-4340'),
(21, 'Ursula Rocha', '2021-08-15', 5300.00, 'Marketing', 'ursula.rocha@empresa.com', '(11) 98765-4341'),
(22, 'Victor Freitas', '2019-05-10', 8000.00, 'TI', 'victor.freitas@empresa.com', '(11) 98765-4342'),
(23, 'Walderez Sousa', '2022-09-25', 4700.00, 'Financeiro', 'walderez.sousa@empresa.com', '(11) 98765-4343'),
(24, 'Xavier Barbosa', '2018-03-01', 7900.00, 'Vendas', 'xavier.barbosa@empresa.com', '(11) 98765-4344'),
(25, 'Yara Carvalho', '2020-12-05', 6200.00, 'Recursos Humanos', 'yara.carvalho@empresa.com', '(11) 98765-4345');


INSERT INTO Dependente VALUES
(101, 1, 'João Silva Jr.', 'Filho', '2015-07-20'),
(102, 1, 'Maria Silva', 'Filha', '2018-03-10'),
(103, 2, 'Clara Santos', 'Cônjuge', '1990-01-05'),
(104, 3, 'Rafael Oliveira', 'Filho', '2019-11-22'),
(105, 4, 'Sofia Pereira', 'Filha', '2017-09-01'),
(106, 4, 'Gustavo Pereira', 'Filho', '2020-04-15'),
(107, 5, 'Lara Costa', 'Filha', '2023-01-30'),
(108, 6, 'Beatriz Martins', 'Cônjuge', '1988-06-12'),
(109, 7, 'Leo Rodrigues', 'Filho', '2016-08-05'),
(110, 8, 'Alice Almeida', 'Filha', '2022-10-10'),
(111, 10, 'Julia Lima', 'Filha', '2014-02-28'),
(112, 10, 'Carlos Lima', 'Filho', '2016-07-11'),
(113, 12, 'Pedro Gomes', 'Filho', '2017-04-03'),
(114, 13, 'Helena Nunes', 'Cônjuge', '1991-09-19'),
(115, 14, 'Artur Ribeiro', 'Filho', '2022-06-01'),
(116, 16, 'Manuela Vieira', 'Filha', '2015-11-25'),
(117, 18, 'Tiago Pinto', 'Filho', '2019-01-08'),
(118, 20, 'Bruna Coelho', 'Filha', '2021-03-14'),
(119, 22, 'Mariana Freitas', 'Filha', '2018-10-01'),
(120, 24, 'Gabriel Barbosa', 'Filho', '2017-05-19');

SELECT * FROM Funcionario;
SELECT * FROM Dependente;


-- i) Mostrar todos os funcionarios
SELECT * FROM Funcionario;

-- ii) Mostrar nome e departamento
SELECT nome, departamento
FROM Funcionario;


-- i) Funcionarios do departamento de TI
SELECT * FROM Funcionario
WHERE departamento = 'TI';

-- ii) Funcionarios com salario maior que 7000
SELECT * FROM Funcionario
WHERE salario > 7000;

-- iii) Funcionarios contratados apos 2021
SELECT * FROM Funcionario
WHERE data_contratacao >= '2022-01-01';

-- iv) Dependentes que sao Filho
SELECT * FROM Dependente
WHERE parentesco = 'Filho';

-- v) Funcionarios cujo nome comeca com A
SELECT * FROM Funcionario
WHERE nome LIKE 'A%';

-- vi) Funcionarios com salario entre 6000 e 8000
SELECT * FROM Funcionario
WHERE salario BETWEEN 6000 AND 8000;


-- i) Funcionarios ordenados por nome
SELECT * FROM Funcionario
ORDER BY nome ASC;

-- ii) Funcionarios ordenados por salario decrescente
SELECT * FROM Funcionario
ORDER BY salario DESC;

-- iii) Dependentes ordenados por data de nascimento
SELECT * FROM Dependente
ORDER BY data_nascimento_dependente ASC;


-- i) Numero total de funcionarios
SELECT COUNT(*) AS total_funcionarios
FROM Funcionario;

-- ii) Media salarial da empresa
SELECT AVG(salario) AS media_salarial
FROM Funcionario;

-- iii) Salario maximo e minimo
SELECT
MAX(salario) AS maior_salario,
MIN(salario) AS menor_salario
FROM Funcionario;

-- iv) Total de salarios pagos no departamento de Vendas
SELECT SUM(salario) AS total_salarios_vendas
FROM Funcionario
WHERE departamento = 'Vendas';

-- v) Numero total de dependentes
SELECT COUNT(*) AS total_dependentes
FROM Dependente;

-- i) Numero de funcionarios por departamento
SELECT departamento, COUNT(*) AS quantidade
FROM Funcionario
GROUP BY departamento;

-- ii) Media salarial por departamento
SELECT departamento, AVG(salario) AS media_salarial
FROM Funcionario
GROUP BY departamento;

-- iii) Quantidade de dependentes por parentesco
SELECT parentesco, COUNT(*) AS quantidade
FROM Dependente
GROUP BY parentesco;

-- i) Departamentos com mais de 5 funcionarios
SELECT departamento, COUNT(*) AS quantidade
FROM Funcionario
GROUP BY departamento
HAVING COUNT(*) > 5;

-- ii) Departamentos onde a media salarial e superior a 6500
SELECT departamento, AVG(salario) AS media_salarial
FROM Funcionario
GROUP BY departamento
HAVING AVG(salario) > 6500;

-- i) Os 5 funcionarios com maior salario
SELECT * FROM Funcionario
ORDER BY salario DESC
LIMIT 5;

-- ii) Os 3 funcionarios mais recentemente contratados
SELECT * FROM Funcionario
ORDER BY data_contratacao DESC
LIMIT 3;


-- a) Listar funcionarios e seus dependentes
SELECT
    Funcionario.nome AS funcionario,
    Dependente.nome_dependente AS dependente,
    Dependente.parentesco
FROM Funcionario
INNER JOIN Dependente
ON Funcionario.id_funcionario = Dependente.id_funcionario;

-- b) Listar todos os funcionarios,
-- mesmo aqueles sem dependentes
SELECT
    Funcionario.nome AS funcionario,
    Dependente.nome_dependente AS dependente
FROM Funcionario
LEFT JOIN Dependente
ON Funcionario.id_funcionario = Dependente.id_funcionario;

-- c) Listar todos os dependentes e seus funcionarios
SELECT
    Dependente.nome_dependente AS dependente,
    Funcionario.nome AS funcionario
FROM Funcionario
RIGHT JOIN Dependente
ON Funcionario.id_funcionario = Dependente.id_funcionario;

-- d) Funcionarios que nao possuem dependentes
SELECT
    Funcionario.id_funcionario,
    Funcionario.nome
FROM Funcionario
LEFT JOIN Dependente
ON Funcionario.id_funcionario = Dependente.id_funcionario
WHERE Dependente.id_dependente IS NULL;


-- a) Funcionarios que ganham mais que a media salarial
SELECT *
FROM Funcionario
WHERE salario > (
    SELECT AVG(salario)
    FROM Funcionario
);

-- b) Dependentes de funcionarios do departamento de Marketing
SELECT *
FROM Dependente
WHERE id_funcionario IN (
    SELECT id_funcionario
    FROM Funcionario
    WHERE departamento = 'Marketing'
);


-- a) Funcionarios de TI com salario superior a 7500
SELECT *
FROM Funcionario
WHERE departamento = 'TI'
AND salario > 7500;

-- b) Funcionarios de Vendas ou Marketing
SELECT *
FROM Funcionario
WHERE departamento = 'Vendas'
OR departamento = 'Marketing';


-- Funcionarios que NAO pertencem ao departamento de TI
SELECT *
FROM Funcionario
WHERE NOT departamento = 'TI';
Fork
Embed on website
