A4_Implementacao_de_Banco_de_Dados

Prévia do material em texto

A4 - Trabalho de SQL
Dupla: João Gabriel Vasconcellos Melão e Orlando Soares Amaral Chaves
Matrículas: 20201102196 e 20201106594
Orientações:
- Para cada comando SQL executado, deverá constar o print do resultado (conforme
apresentado na aula).
- Trabalho pode ser feito em dupla.
- Data de entrega: 27/06/2022 (via ATIVIDADE do TEAMS)
Modelo lógico:
 
1a Questão - Desenvolva as questões abaixo, em SQL.
Questão 1) Implemente o modelo físico no SQL Server:
-- CRIA O BANCO, MUDA PARA ELE E CONFIRMA QUE ESTÁ EM USO
CREATE DATABASE A4;
GO
USE A4;
GO
SELECT DB_NAME();
GO
 -- CRIANDO AS TABELAS
 -- Departamento
CREATE TABLE Departamento (
 DeptoNum INT NOT NULL,
 DeptNome NVARCHAR(50 ) NOT NULL,
 MatrGerente INT NULL,
CONSTRAINT PK_Departamento PRIMARY KEY (DeptoNum)
);
GO
ALTER TABLE Departamento ADD CONSTRAINT CHK_Departamento_Supervisor
CHECK (LEN([MatrGerente])=(8));
GO
ALTER TABLE Departamento ADD CONSTRAINT FK_Departamento
 FOREIGN KEY (MatrGerente) REFERENCES Empregado(Matricula);
GO
 -- Dependente
CREATE TABLE Dependente (
 Matricula INT NOT NULL,
 NomeDepend NVARCHAR(50 ) NOT NULL,
 Sexo CHAR(1 ) NOT NULL,
 DataNasc DATE NOT NULL,
 Parentesco varCHAR(20 ) NULL,
CONSTRAINT PK_Dependete PRIMARY KEY (NomeDepend)
);
GO
ALTER TABLE Dependente ADD CONSTRAINT CHK_Dependente_Sexo
CHECK ([Sexo]='M' OR [Sexo]='m' OR [Sexo]='F' OR [Sexo]='f');
GO
ALTER TABLE Dependente ADD CONSTRAINT CHK_Dependente_Matricula
CHECK (LEN([Matricula])=(8));
GO
ALTER TABLE Dependente ADD CONSTRAINT FK_Dependente
 FOREIGN KEY (Matricula) REFERENCES Empregado(Matricula);
GO
 -- DeptLocal
CREATE TABLE DeptoLocal (
 Depto INT NOT NULL,
 Localizacao CHAR(2 ) NOT NULL,
CONSTRAINT PK_Depto_Local PRIMARY KEY (Localizacao)
);
GO
ALTER TABLE DeptoLocal ADD CONSTRAINT FK_Depto_Local
 FOREIGN KEY (Depto) REFERENCES Departamento(DeptoNum);
GO
 -- Empregado
CREATE TABLE Empregado (
 Nome NVARCHAR(50 ) NOT NULL,
 Matricula INT NOT NULL,
 DataNasc DATE NOT NULL,
 Endereco NVARCHAR(100 ) NOT NULL,
 Sexo CHAR(1 ) NOT NULL,
 Salario FLOAT NOT NULL,
 Supervisor INT NULL,
 Depto INT NOT NULL,
CONSTRAINT PK_Empregado PRIMARY KEY (Matricula),
CONSTRAINT FK_Empregado_Supervisor FOREIGN KEY (Supervisor) REFERENCES Empregado(Matricula)
);
GO
ALTER TABLE Empregado ADD CONSTRAINT CHK_Empregado_Salario
CHECK ([Salario]>(0));
GO
ALTER TABLE Empregado ADD CONSTRAINT CHK_Empregado_Sexo
CHECK ([Sexo]='M' OR [Sexo]='m' OR [Sexo]='F' OR [Sexo]='f');
GO
ALTER TABLE Empregado ADD CONSTRAINT CHK_Empregado_Matricula
CHECK (LEN([Matricula])=(8));
GO
ALTER TABLE Empregado ADD CONSTRAINT CHK_Empregado_Supervisor
CHECK (LEN([Supervisor])=(8));
GO
 -- Projeto
CREATE TABLE Projeto (
 PNome NVARCHAR(50 ) NOT NULL,
 PCodigo INT NOT NULL,
 PLocalizacao CHAR(2 ) NULL,
 Depto INT NULL,
CONSTRAINT PK_Projeto PRIMARY KEY (PCodigo)
);
GO
ALTER TABLE Projeto ADD CONSTRAINT FK_Projeto
 FOREIGN KEY (Depto) REFERENCES Departamento(DeptoNum);
GO
 -- TrabalhaEm
CREATE TABLE TrabalhaEm (
 Matricula INT NOT NULL,
 PCodigo INT NOT NULL,
 Horas INT NOT NULL
);
GO
ALTER TABLE TrabalhaEm ADD CONSTRAINT CHK_Trabalha_Em_Horas
CHECK (LEN([Matricula])>(8));
GO
ALTER TABLE TrabalhaEm ADD CONSTRAINT FK_Trabalha_Em
 FOREIGN KEY (PCodigo) REFERENCES Projeto(PCodigo);
GO
ALTER TABLE TrabalhaEm ADD CONSTRAINT FK_Trabalha_Em_Matricula
 FOREIGN KEY (Matricula) REFERENCES Empregado(Matricula);
GO
 
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
Questão 2) Para cada tabela insira 10 registros:
-- Cargas Tabelas
-- Departamento
INSERT INTO Departamento(DeptoNum,DeptNome,MatrGerente)
VALUES(11,'Tecnologia',20221101),
(12,'Recursos Humanos',20221102),
(13,'Marketing',20221103),
(14,'Financeiro',20221104),
(15,'Jurídico',20221105),
(21,'Administrativo',20221106),
(22,'Operacional',20221107),
(23,'Comercial',20221108),
(24,'Projetos',20221109),
(25,'Contabilidade',20221110);
-- Dependente
INSERT INTO Dependente (Matricula,NomeDepend,Sexo,DataNasc,Parentesco)
VALUES(20221108,'Ana Beatriz','F','1989-11-22','Mãe'),
(20221109,'Estônia Abreu','F','1984-04-15','Mãe'),
(20221102,'Leonardo da Silva','M','2021-10-30','Filho'),
(20221101,'Lili Victoria','F','2010-07-22','Filha'),
(20221104,'Marco Polo','M','1998-05-24','Pai'),
(20221105,'Marta Mirian','F','1980-04-03','Mãe'),
(20221103,'Matheus Gerúndio','M','1973-03-13','Pai'),
(20221107,'Pedro Enrique','M','2021-10-30','Filho'),
(20221110,'Renato Gaúcho','M','1978-05-20','Pai'),
(20221106,'Vladmir Valmir','M','1985-06-06','Pai');
-- DeptoLocal
INSERT INTO DeptoLocal (Depto,Localizacao)
VALUES(13,'AC'),
(24,'AL'),
(22,'DF'),
(23,'MA'),
(15,'MG'),
(25,'RF'),
(11,'RJ'),
(21,'RR'),
(14,'SC'),
(12,'SP');
-- Empregado
INSERT INTO Empregado (Nome,Matricula,DataNasc,Endereco,Sexo,Salario,Supervisor,Depto)
VALUES('Sergio Nascimento',20221101,'1990-07-09','Rua ABC','M',4100.0,NULL,11),
('Cabral Nunes',20221102,'1991-10-05','Rua Apia 44','M',1800.0,20221101,12),
('Cidália Vale',20221103,'1992-12-23','Avenida das Americas','F',2500.0,20221102,13),
('Messias Barrico',20221104,'1997-06-09','Rua Marques de Abrantes 66','M',1900.0,NULL,14),
('Riana Pureza',20221105,'1999-04-12','Rua do Pamoios 105','F',5000.0,20221104,15),
('Fred Roriz',20221106,'2001-08-30','Avenida das Américas 24','M',9999.0,NULL,21),
('Michael Douglas',20221107,'2004-07-01','Rua do Musicista 99','M',7280.0,20221106,22),
('Antonio Nunes',20221108,'2002-01-03','Rua Voluntários da Pátria 108','M',3498.0,NULL,23),
('Rafael Camargo',20221109,'1999-03-17','Rua Cabo Herculano 10','M',4400.0,20221108,24),
('Cintia Domingos',20221110,'2000-06-10','Rua São Clemente 93','F',2999.0,NULL,25);
-- Projeto
INSERT INTO Projeto (PNome,PCodigo,PLocalizacao,Depto)
VALUES('Implementação de Modelo Ágil',11,'SC',24),
('Treinamento Para FP',12,'PR',25),
('Gestão do Desperdício na Produção',22,'RR',22),
('Apuração Balancete',33,'RF',21),
('Abater Casos 2006',44,'MG',15),
('DIRF',55,'DF',14),
('Psicologia Parental',66,'AL',12),
('Promoção de Produto',77,'AC',13),
('Migração Sistema Legado',88,'RJ',11),
('Sustentabilidade',99,'SP',23);
-- TrabalhaEm
INSERT INTO TrabalhaEm (Matricula,PCodigo,Horas)
VALUES(20221101,99,20),
(20221102,88,100),
(20221103,77,25),
(20221104,66,60),
(20221105,55,70),
(20221106,44,30),
(20221107,33,7),
(20221108,22,8),
(20221109,11,15),
(20221110,12,33);
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3) Desenvolva as consultas SQL abaixo.
3.1 – Liste todos os funcionários que ganham entre R$ 1.000,00 e R$ 3.000,00.
SELECT LEFT(NOME, CHARINDEX(' ', NOME) - 1) as nome,
RIGHT(NOME, CHARINDEX(' ', REVERSE(NOME)) - 1) as SOBRENOME,
CONCAT('R$', Salario) AS 'Salário Empregado',
Matricula AS 'Matrículas'
FROM Empregado
WHERE Salario
BETWEEN 1000 AND 3000;
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.2 – Qual o departamento que pertence o funcionário que possui o maior salário da 
empresa?
SELECT TOP 1 t.Nome AS 'Nome',
t.Salario AS 'Salário',
Departamento AS 'Departamento'
FROM ( SELECT CONCAT('R$', MAX(e.Salario)) AS 'Salario',
d.DeptNome AS 'Departamento',
e.Nome AS 'Nome'
FROM Empregado e
INNER JOIN Departamento d
ON e.Depto = d.DeptoNum
INNER JOIN DeptoLocal dl
ON d.DeptoNum = dl.Depto
INNER JOIN TrabalhaEm te
ON e.Matricula = te.Matricula
GROUP BY e.Nome, d.DeptNome) T
ORDER BY 1 DESC;
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.3 – Liste a quantidade de projetos por departamento. 
SELECT d.DeptNome AS 'Departamento',
COUNT(p.PCodigo)AS 'Qtd Projetos'
FROM Projeto p
INNER JOIN Departamento d
ON p.Depto = d.DeptoNum
INNER JOIN TrabalhaEm te
ON p.PCodigo = te.PCodigo
GROUP BY d.DeptNome;
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.4 – Liste o nome do Funcionário e seu respectivo Supervisor.
SELECT e1.Nome AS 'Nome Empregado',
e1.Matricula AS 'Matrícula Empregado',
e2.Nome AS 'Nome Supervisor',
e2.Supervisor AS 'Matrícula Supervisor'
FROM Empregado e1
INNER JOIN Empregado e2
ON e1.Matricula = e2.Supervisor;
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.5 – Qual é a média de horas trabalhadas no projeto de Sustentabilidade?
SELECT AVG(Horas) AS 'Média de Horas',
P.PNome AS 'Projeto'
FROM TrabalhaEm te
INNER JOIN Projeto p
ON te.PCodigo = p.PCodigo
WHERE p.PNome = 'Sustentabilidade'
GROUP BY P.PNome;
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.6 – Quais os funcionários que possuem o sobrenome Nascimento?
SELECT Nome AS 'Nome'
FROM Empregado e
WHERE NOME LIKE '%Nascimento';
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.7 – Quais os departamentos que não possuem até o momento nenhum projeto?
SELECT d.DeptoNum AS 'Código Departamento',
d.DeptNome AS 'Departamento'
FROM Departamento d
WHERE d.DeptoNum NOT IN (SELECT p.Depto
FROM Projeto p);
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.8 – Liste o nome dos funcionários que possuem dependentes que nasceram em 2021.
SELECT e.Nome AS 'Nome Funcionário',
e.Matricula AS 'Matrícula',
d.NomeDepend AS 'Nome Dependente',
d.Parentesco AS 'Parentesco',
REPLACE(FORMAT(d.DataNasc, 'dd-MM-yyyy'), '-', '/') AS 'Data de Nascimento'
FROM Empregado e
INNER JOIN Dependente d
ON e.Matricula = d.Matricula
WHERE d.DataNasc LIKE '2021%';
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.9 – Quais os funcionários que trabalham no projeto Reciclagem? 
SELECT e.Nome AS 'Nome',
e.Matricula AS 'Matrícula',
p.PNome AS 'Projeto'
FROM Empregado e
INNER JOIN TrabalhaEm te
ON e.Matricula = te.Matricula
INNER JOIN Projeto p
ON te.PCodigo = p.PCodigo
WHERE p.PNome = 'Reciclagem';
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.10 – Quantos funcionários estão alocados no departamento de Tecnologia? 
SELECT COUNT(e.Matricula) AS 'Matrícula',
e.Nome AS 'Nome',
d.DeptoNum AS 'Código Departamento',
d.DeptNome AS 'Departamento'
FROM Empregado e
INNER JOIN Departamento d
ON e.Depto = d.DeptoNum
WHERE d.DeptNome = 'Tecnologia'
GROUP BY d.DeptoNum,
e.Nome,
d.DeptNome;
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.11 – Liste o total de dependentes por sexo. 
SELECT COUNT(*) AS 'Total de Dependentes',
Sexo AS 'Sexo'
FROM Dependente d
WHERE Sexo LIKE 'F'
GROUP BY Sexo
UNION
SELECT COUNT(*) AS 'Total de Dependentes',
Sexo AS 'Sexo'
FROM Dependente d
WHERE Sexo LIKE 'M'
GROUP BY Sexo;
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
3.12 – Para cada projeto liste os funcionários alocados.
SELECT e.Nome AS 'Nome',
e.Matricula AS 'Matrícula',
p.PNome AS 'Projeto'
FROM Empregado e
INNER JOIN TrabalhaEm te
ON e.Matricula = te.Matricula
INNER JOIN Projeto p
ON te.PCodigo = p.PCodigo;
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
2a Questão - Conforme explicado em aula, chegou a hora de você propor 4 consultas 
pertinentes e relevantes ao cenário. Será levado em consideração a complexidade da 
consulta desenvolvida (Complexidade 1: envolve no máximo 2 tabelas. – Complexidade 2:
envolve mais de 2 tabelas, ou conceitos de conjuntos, ou subquery). 
Consulta Personalizada 1- Foi requisitado pelo RH a ficha completa dos funcionários 
separados por sexo.
SELECT *
FROM (SELECT e.Nome AS 'Nome Empregado',
e.Matricula AS 'Matrícula',
FORMAT(e.DataNasc, 'dd/MM/yyyy') AS 'Data de Nascimento',
e.Sexo AS 'Sexo',
CONCAT('R$', e.Salario) AS 'Salário',
d.DeptNome AS 'Nome Departamento',
p.PNome AS 'Projeto Alocado'
FROM Empregado e
INNER JOIN Departamento d
ON e.Depto = d.DeptoNum
INNER JOIN Projeto p
ON d.DeptoNum = p.Depto
WHERE Sexo = 'M'
UNION
SELECT e.Nome AS 'Nome Empregado',
e.Matricula AS 'Matrícula',
FORMAT(e.DataNasc, 'dd/MM/yyyy') AS 'Data de Nascimento',
e.Sexo AS 'Sexo',
CONCAT('R$', e.Salario) AS 'Salário',
d.DeptNome AS 'Nome Departamento',
p.PNome AS 'Projeto Alocado'
FROM Empregado e
INNER JOIN Departamento d
ON e.Depto = d.DeptoNum
INNER JOIN Projeto p
ON d.DeptoNum = p.Depto
WHERE Sexo = 'F') t
ORDER BY t.Sexo;
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
Consulta Personalizada 2- A empresa quer realocar alguns funcionários para outros 
estados, para isso, descubra o grau de parentesco com o funcionário matriculado, o 
estdado da empresa que ele trabalha atualmente e, caso seja filho/filha, retire-o da 
pesquisa. Obs: não separe dos pais.
SELECT e.Nome AS 'Nome Empregado',
e.Matricula AS 'Matrícula',
d.DeptNome AS 'Nome Departamento',
dl.Localizacao AS 'UF Departamento',
d2.Parentesco AS 'Parentesco com o Empregado',
d2.NomeDepend AS 'Nome Dependente'
FROM Empregado e
INNER JOIN Departamento d
ON e.Depto = d.DeptoNum
INNER JOIN Dependente d2
ON e.Matricula = d2.Matricula
INNER JOIN DeptoLocal dl
ON d.DeptoNum = dl.Depto
WHERE d2.Parentesco NOT IN ('Filho', 'Filha');
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
Consulta Personalizada 3- Liste onde cada departamento é sediado e quais projetos são 
compostos apenas por mulheres.
SELECT d.DeptNome AS 'Departamento',
dl.Localizacao AS 'UF Departamento'
FROM Empregado e
INNER JOIN Departamento d
ON e.Depto = d.DeptoNum
INNER JOIN DeptoLocal dl
ON d.DeptoNum = dl.Depto
WHERE e.Sexo = 'F';
 
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-
Voh9g?usp=sharing
Consulta Personalizada 4- A empresa deseja oferecer uma bonificação de R$ 150,00 para
os funcionários dos três setores que mais trabalharam.
SELECT TOP 3 e.Nome AS 'Empregado',
d.DeptNome AS 'Departamento',
te.Horas AS 'Horas Projeto',
e.Salario AS 'Salário Base',
SUM(e.Salario + 150) AS 'Salário C/ Bônus'
FROM Empregado e
INNER JOIN TrabalhaEm te
ON e.Matricula = te.Matricula
INNER JOIN Departamento d
ON e.Depto = d.DeptoNum
GROUP BY e.Salario,
d.DeptNome,
te.Horas,
e.Nome
ORDER BY te.Horas DESC;
 
 
Obs: link para o drive com as queries em .sql, visto que aqui no Office/PDF fica difícil de 
formatar → https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-Voh9g?usp=sharing
https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-Voh9g?usp=sharing
https://drive.google.com/drive/folders/1QHGZqANeLiE19L_GOuNAliGZ2m-Voh9g?usp=sharing