atividade-bd

atvd.sql

-- 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;