-- CRIANDO BANCO DE DADOS
mysql> CREATE DATABASE db_senac;
mysql> USE db_senac;
-- CRIANDO TABLEAS
mysql> CREATE TABLE tbl_funcionarios (
-> Codigo INT NOT NULL AUTO_INCREMENT,
-> PrimeiroNome VARCHAR(50) NOT NULL,
-> SegundoNome VARCHAR(50),
-> UltimoNome VARCHAR(50) NOT NULL,
-> DataNasci DATE NOT NULL,
-> CPF CHAR(11) NOT NULL UNIQUE,
-> RG VARCHAR(20) NOT NULL,
-> Endereco VARCHAR(255) NOT NULL,
-> CEP CHAR(8) NOT NULL,
-> Cidade VARCHAR(100) NOT NULL,
-> Fone VARCHAR(20) NOT NULL,
-> CodigoDepartamento INT NOT NULL,
-> Funcao VARCHAR(100) NOT NULL,
-> Salario DECIMAL(10,2) NOT NULL,
-> PRIMARY KEY (Codigo),
-> INDEX idx_nome (PrimeiroNome, UltimoNome),
-> INDEX idx_departamento_funcao (CodigoDepartamento, Funcao));
--INSERINDO DADOS
mysql> INSERT INTO tbl_funcionarios (
-> PrimeiroNome, SegundoNome, UltimoNome,
-> DataNasci, CPF, RG, Endereco, CEP,
-> Cidade, Fone, CodigoDepartamento, Funcao, Salario
-> ) VALUES
-> ('Maria', 'Silva', 'Barbosa', '1980-01-01', '12345678900', '12345678900', 'Rua da Paz, 123', '12345678', 'Sao Paulo', '1199999999', 1, 'Gerente', 10000.00),
-> ('João', NULL, 'Da Silva', '1990-02-02', '98765432100', '98765432100', 'Rua da Felicidade, 456', '98765432', 'Rio de Janeiro', '2188888888', 1, 'Vendedor', 2000.00),
-> ('José', 'Carlos', 'Santos', '1985-03-03', '76543210900', '76543210900', 'Rua da Esperança, 789', '76543210', 'Belo Horizonte', '3177777777', 2, 'Analista', 3000.00),
-> ('Ana', 'Paula', 'Pereira', '1970-04-04', '65432109800', '65432109800', 'Rua da Alegria, 101', '65432109', 'Porto Alegre', '5166666666', 3, 'Programadora', 4000.00),
-> ('Pedro', 'Luiz', 'Oliveira', '1965-05-05', '54321098700', '54321098700', 'Rua da Liberdade, 202', '54321098', 'Salvador', '7155555555', 1, 'Diretor', 5000.00),
-> ('Cristina', 'Maria', 'Souza', '1950-06-06', '43210987600', '43210987600', 'Rua da Amizade, 303', '43210987', 'Recife', '8144444444', 2, 'Designer', 6000.00),
-> ('Fabiano', NULL, 'Santos', '1975-07-07', '32109876500', '32109876500', 'Rua da Justiça, 404', '32109876', 'Fortaleza', '8533333333', 3, 'Analista de Sistemas', 7000.00)
-> ('João', 'Silva', 'Santos', '1990-05-15', '12345678901', '78901234', 'Rua A, 123', '12345678', 'São Paulo', '11-987654321', 1, 'Analista', 5000.00),
-> ('Maria', 'Ferreira', 'Souza', '1985-09-10', '98765432101', '56789012', 'Av. B, 456', '54321876', 'Rio de Janeiro', '21-987654321', 2, 'Gerente', 8000.00),
-> ('Carlos', NULL, 'Rocha', '1992-02-20', '56789012301', '12345678', 'Rua C, 789', '98765432', 'Belo Horizonte', '31-987654321', 1, 'Programador', 4000.00),
-> ('Ana', 'Sousa', 'Pereira', '1988-07-01', '90876543201', '87654321', 'Av. D, 789', '87654321', 'Brasília', '61-987654321', 3, 'Analista de RH', 5500.00),
-> ('Pedro', 'Oliveira', 'Almeida', '1995-12-18', '45678901201', '23456789', 'Rua E, 1011', '76543210', 'Curitiba', '41-987654321', 2, 'Desenvolvedor', 4500.00),
-> ('Fernanda', NULL, 'Gomes', '1991-03-25', '76543210901', '34567890', 'Av. F, 1213', '65432109', 'Porto Alegre', '51-987654321', 1, 'Analista', 5200.00),
-> ('Rafael', 'Martins', 'Pereira', '1987-08-05', '23456789001', '45678901', 'Rua G, 1415', '54321234', 'Salvador', '71-987654321', 2, 'Gerente', 7500.00),
-> ('Mariana', NULL, 'Cavalcanti', '1993-01-12', '87654321001', '56789012', 'Av. H, 1617', '43210543', 'Recife', '81-987654321', 3, 'Analista de RH', 5800.00),
-> ('Paulo', 'Lima', 'Santos', '1989-06-30', '34567890101', '67890123', 'Rua I, 1819', '32109876', 'Fortaleza', '85-987654321', 2, 'Desenvolvedor', 4300.00),
-> ('Amanda', NULL, 'Rodrigues', '1994-11-08', '65432109801', '78901234', 'Av. J, 2021', '21098765', 'Manaus', '92-987654321', 1, 'Programador', 4100.00);
-- VIEWS
mysql> CREATE VIEW nome_completo_funcionarios AS
-> SELECT Codigo,CONCAT(primeiroNome,' ', COALESCE(segundoNome,''),' ',UltimoNome) AS nome_Completo
-> FROM tbl_funcionarios;
-- CONSULTAS
1 - Listar nome e sobrenome ordenado por sobrenome:
mysql> SELECT primeiroNome, segundoNome FROM tbl_funcionarios ORDER BY segundoNome;
2 - Listar todos os campos de funcionários ordenados por cidade:
mysql> SELECT * FROM tbl_funcionarios ORDER BY cidade;
3 - Liste os funcionários que têm salário superior a R$ 1.000,00 ordenados pelo nome completo:
mysql> SELECT CONCAT(PrimeiroNome,' ',COALESCE(SegundoNome,''),' ', UltimoNome) AS NomeCompleto, Salario
-> FROM tbl_funcionarios
-> WHERE Salario > 1000.00
-> ORDER BY NomeCompleto ;
4 - Liste a data de nascimento e o primeiro nome dos funcionários ordenados do mais novo para o mais velho:
mysql> SELECT PrimeiroNome, DataNasci FROM tbl_funcionarios ORDER BY (SELECT (2023 - SUBSTRING(DataNasci,1,5)));
5 - Liste os funcionários como uma lista telefônica, com enedeço e cidade:
mysql> SELECT
-> nome_completo AS Nome,
-> func.Endereco,
-> func.Cidade,
-> func.fone
-> FROM nome_completo_funcionarios
-> INNER JOIN tbl_funcionarios as func
-> ON nome_completo_funcionarios.Codigo = func.Codigo
-> ORDER BY nome_completo;
6 - Liste o nome completo de todos os funcionários que não tenham segundo nome:
mysql> SELECT
-> CONCAT(PrimeiroNome,' ',COALESCE(SegundoNome,''),' ', UltimoNome) AS NomeCompleto
-> FROM tbl_funcionarios
-> WHERE segundoNome IS NULL
-> ORDER BY NomeCompleto;
7 - Liste os nomes dos funcionários que moram em Ituiutabae que exerçam a função deTelefonista:
mysql> SELECT primeiroNome
-> FROM tbl_funcionarios
-> WHERE cidade = 'Ituiutaba' AND funcao = 'Telefonista';
8 - Liste os nomes dos funcionários que trabalham no departamento 2:
mysql> SELECT CodigoDepartamento, nome_completo_funcionarios.nome_completo
FROM tbl_funcionarios
INNER JOIN nome_completo_funcionarios
ON nome_completo_funcionarios.Codigo = tbl_funcionarios.Codigo
WHERE CodigoDepartamento = 2
ORDER BY nome_completo;
9 - Liste o nome e o código do departamento de todos os funcionários que ganham mais do queR$ 2000,00:
mysql> SELECT CodigoDepartamento,func.nome_completo
FROM tbl_funcionarios
INNER JOIN nome_completo_funcionarios AS func
ON func.Codigo = tbl_funcionarios.Codigo
WHERE Salario > 2000.00;