TRABALHO DE SQL

•

UVA

3

0

3

0

Juliana Bond

26/10/2022

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

COMPONENTES: 
JULIANA BOND – 20201101127 
Paulo Cesar Marinho dos Santos - 20201102963 
 
 
 
 
 
 2021 
 
PARTE DOCUMETADA 
--------3.1 - Liste todos os funcionários que ganham entre R$ 1.000,00 e R$ 3.000,00. 
SELECT NOME FROM EMPREGADOS 
WHERE SALARIO BETWEEN '1000' AND '3000' 
 
-- Exercício 3.2) Qual o departamento que pertence o funcionário que possui o maior 
salário da empresa? 
SELECT TOP 1 t.Nome 'Nome', 
 t.Salario AS 'Salário', 
 Departamento AS 'Departamento' 
FROM ( SELECT CONCAT('R$', MAX(e.salario)) AS 'Salario', 
 d.DEPTONOME AS 'Departamento', 
 e.nome AS 'Nome' 
 FROM EMPREGADOS e 
 INNER JOIN DEPARTAMENTO d 
 ON e.Depto = d.DeptoNum 
 INNER JOIN DEPTO_LOCAL dl 
 ON d.DeptoNum = dl.Depto 
 INNER JOIN TRABALHA_EM te 
 ON e.Matricula = te.Matricula 
 GROUP BY e.Nome, d.DEPTONOME) T 
ORDER BY 1 DESC; 
 
-- 3.3) Liste a quantidade de projetos por departamento. 
SELECT d.DEPTONOME AS 'Departamento', 
 COUNT(p.PCodigo) AS 'Qtd Projetos' 
FROM Projeto p 
INNER JOIN Departamento d 
 ON p.Depto = d.DeptoNum 
INNER JOIN TRABALHA_EM te 
 ON p.PCodigo = te.PCodigo 
GROUP BY d.DEPTONOME; 
 
 
 
 
---------3.4 – Liste o nome do Funcionário e seu respectivo Supervisor. 
SELECT NOME,SUPERVISOR FROM EMPREGADOS 
ORDER BY SUPERVISOR 
 
 
--------3.5 – Qual é a média de horas trabalhadas no projeto de Sustentabilidade? 
SELECT AVG (T.HORAS) AS HORAS 
FROM TRABALHA_EM T INNER JOIN PROJETO P 
ON T.PCODIGO = P.PCODIGO 
WHERE P.PNOME = 'Sustentabilidade' 
GROUP BY P.PCODIGO 
 
 
 
 
 
--------3.6 – Quais os funcionários que possuem o sobrenome Nascimento? 
SELECT NOME FROM EMPREGADOS 
WHERE NOME LIKE '%NASCIMENTO%' 
 
--------3.7 – Quais os departamentos que não possuem até o momento nenhum projeto? 
SELECT DISTINCT D.DEPTONOME FROM DEPARTAMENTO D INNER JOIN 
PROJETO P 
ON D.DEPTONUM = P.DEPTO 
WHERE P.PNOME = '' 
 
--------3.8 – Liste o nome dos funcionários que possuem dependentes que nasceram em 
2021. 
SELECT E.NOME, D.NOMEDEPEND FROM DEPENDENTES D INNER JOIN 
EMPREGADOS E 
ON E.MATRICULA = D.MATRICULA 
WHERE D.DATANASC BETWEEN '2021-01-01' AND '2021-12-31' 
 
------3.9 – Quais os funcionários que trabalham no projeto Reciclagem? 
SELECT E.NOME, D.PNOME FROM PROJETO D INNER JOIN EMPREGADOS E 
ON D.DEPTO = E.DEPTO 
WHERE D.PNOME ='Reciclagem' 
 
 
--------3.10 – Quantos funcionários estão alocados no departamento de Tecnologia? 
SELECT COUNT(DEPTONOME) FROM DEPARTAMENTO 
WHERE DEPTONOME = 'Tecnologia' 
 
 
 
 
---3.11 – Liste o total de dependentes por sexo. 
SELECT * FROM DEPENDENTES 
ORDER BY SEXO 
 
--------3.12 – Para cada projeto liste os funcionários alocados. 
SELECT DISTINCT E.NOME, D.PNOME FROM PROJETO D INNER JOIN 
EMPREGADOS E 
ON D.DEPTO = E.DEPTO 
ORDER BY E.NOME 
 
 
/*2)- 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). */ 
 
 
 
 
 
 
 
--------2.1) SOME O TOTAL DE SALARIOS QUE A EMPRESA PAGA A TODOS 
OS FUNCIONARIOS E A MEDIA DE DEPARTAMENTO 
 
SELECT SUM(E.SALARIO) AS TOTAL_SALARIOS, AVG(D.DEPTONUM) AS 
NUMERO_DEPARTAMENTO 
FROM EMPREGADOS E INNER JOIN DEPARTAMENTO D 
ON E.DEPTO = D.DEPTONUM 
 
 
--------2.2) SLECIONE OS PROJETOS DE CADA DEPARTAMENTO E O NOME 
DE CADA PESSOA ALOCADA 
 
SELECT DISTINCT E.NOME, D.PNOME, A.DEPTONOME FROM 
DEPARTAMENTO A, PROJETO D INNER JOIN EMPREGADOS E 
ON D.DEPTO = E.DEPTO 
WHERE A.DEPTONUM = D.DEPTO 
ORDER BY E.NOME 
 
 
--------2.3)Liste a média salarial das funções onde o salário médio seja IGUAL A 
$3000.00. 
 select NOME, media 
 from (select NOME, round(avg(salario),2) as media 
 from EMPREGADOS 
 group by NOME) a where a.media = 3000; 
 
 
 
 
 
 
 
3.1) CREATE 
USE [A4] 
 
----tabela EMPREGADOS 
CREATE TABLE EMPREGADOS ( 
NOME VARCHAR (30), 
MATRICULA NUMERIC (38) NOT NULL PRIMARY KEY, 
DATANASC DATE, 
ENDERECO VARCHAR (38), 
SEXO CHAR(1), 
SALARIO DECIMAL(37,9), 
SUPERVISOR VARCHAR (20), 
DEPTO VARCHAR(30) 
); 
 
----tabela DEPTO_LOCAL 
CREATE TABLE DEPTO_LOCAL( 
DEPTO NUMERIC (30) NOT NULL PRIMARY KEY, 
LOCALIZACAO VARCHAR (30), 
); 
 
----tabela DEPARTAMENTO 
CREATE TABLE DEPARTAMENTO ( 
DEPTONUM NUMERIC (30), 
DEPTONOME VARCHAR (20), 
DATANASC DATE, 
MATRGERENTE NUMERIC (38), 
CONSTRAINT FK_MATRGERENTE FOREIGN KEY (MATRGERENTE) 
REFERENCES EMPREGADOS(MATRICULA), 
CONSTRAINT FK_DEPTONUM FOREIGN KEY (DEPTONUM) REFERENCES 
DEPTO_LOCAL(DEPTO) 
); 
 
----tabela TRABALHA_EM 
CREATE TABLE TRABALHA_EM ( 
MATRICULA NUMERIC (38) , 
HORAS time, 
PCODIGO NUMERIC (30) NOT NULL PRIMARY KEY, 
CONSTRAINT FK_MATRICULA FOREIGN KEY (MATRICULA) REFERENCES 
EMPREGADOS(MATRICULA) 
); 
 
 
 
 
 
 
 
 
----tabela PROJETO 
CREATE TABLE PROJETO ( 
PNOME VARCHAR (38), 
PLOCALIZACAO VARCHAR (38), 
PCODIGO NUMERIC (30), 
DEPTO NUMERIC (30) 
CONSTRAINT FK_PCODIGO FOREIGN KEY (PCODIGO) REFERENCES 
TRABALHA_EM(PCODIGO), 
CONSTRAINT FK_DEPTO FOREIGN KEY (DEPTO) REFERENCES 
DEPTO_LOCAL(DEPTO) 
); 
 
----tabela DEPENDENTES 
CREATE TABLE DEPENDENTES ( 
NOMEDEPEND VARCHAR (30), 
MATRICULA NUMERIC (38), 
DATANASC DATE, 
SEXO CHAR(1), 
PARENTESCO VARCHAR (20), 
CONSTRAINT FK_MATRICULA FOREIGN KEY (MATRICULA) REFERENCES 
EMPREGADOS(MATRICULA) 
); 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
3.2) INSERT 
------------------------------------------------------------------------------------------------- 
----DEPTO_LOCAL 
USE [A4] 
INSERT INTO DEPTO_LOCAL([DEPTO], LOCALIZACAO) 
VALUES ('1', 'BARRA TIJUCA'); 
USE [A4] 
INSERT INTO DEPTO_LOCAL([DEPTO], LOCALIZACAO) 
VALUES ('2', 'TIJUCA'); 
INSERT INTO DEPTO_LOCAL([DEPTO], LOCALIZACAO) 
VALUES ('3', 'CENTRO'); 
INSERT INTO DEPTO_LOCAL([DEPTO], LOCALIZACAO) 
VALUES ('4', 'BARRA TIJUCA'); 
INSERT INTO DEPTO_LOCAL([DEPTO], LOCALIZACAO) 
VALUES ('5', 'TIJUCA'); 
INSERT INTO DEPTO_LOCAL([DEPTO], LOCALIZACAO) 
VALUES ('6', 'CENTRO'); 
INSERT INTO DEPTO_LOCAL([DEPTO], LOCALIZACAO) 
VALUES ('7', 'BOTAFOGO'); 
INSERT INTO DEPTO_LOCAL([DEPTO], LOCALIZACAO) 
VALUES ('8', 'BARRA TIJUCA'); 
INSERT INTO DEPTO_LOCAL([DEPTO], LOCALIZACAO) 
VALUES ('9', 'TIJUCA'); 
INSERT INTO DEPTO_LOCAL([DEPTO], LOCALIZACAO) 
VALUES ('10', 'CENTRO'); 
 
SELECT * FROM DEPTO_LOCAL 
 
 
 
 
 
 
------------------------------------------------------------------------------------------------- 
----EMPREGADOS 
USE [A4] 
INSERT INTO EMPREGADOS(NOME, 
MATRICULA,DATANASC,ENDERECO,SEXO,SALARIO,SUPERVISOR,DEPTO) 
VALUES ('JULIANA BOND', '000000001','1996-04-22','LEILOIRO ERNANI 
MELLO 270','S','11000','JULIANA','1' ); 
USE [A4] 
INSERT INTO EMPREGADOS(NOME, 
MATRICULA,DATANASC,ENDERECO,SEXO,SALARIO,SUPERVISOR,DEPTO) 
VALUES ('GABRIEL SANTOS', '000000003','1997-03-
25',null,'M','3000','JULIANA','3' ); 
INSERT INTO EMPREGADOS(NOME, 
MATRICULA,DATANASC,ENDERECO,SEXO,SALARIO,SUPERVISOR,DEPTO) 
VALUES ('Isabala Oliveria', '000000004','2000-03-22',null,'S','4000','JULIANA','4' ); 
INSERT INTO EMPREGADOS(NOME, 
MATRICULA,DATANASC,ENDERECO,SEXO,SALARIO,SUPERVISOR,DEPTO) 
VALUES ('PAULO MARINHO', '000000005','1999-01-
25',null,'M','2000','JULIANA','5' ); 
INSERT INTO EMPREGADOS(NOME, 
MATRICULA,DATANASC,ENDERECO,SEXO,SALARIO,SUPERVISOR,DEPTO) 
VALUES ('LUISA BARBOSA', '000000006','1996-11-23',null,'S','5000','JULIANA','6' 
); 
INSERT INTO EMPREGADOS(NOME, 
MATRICULA,DATANASC,ENDERECO,SEXO,SALARIO,SUPERVISOR,DEPTO) 
VALUES ('STELLA SILVA', '000000007','1997-08-20',null,'S','1000','JULIANA','7'
); 
INSERT INTO EMPREGADOS(NOME, 
MATRICULA,DATANASC,ENDERECO,SEXO,SALARIO,SUPERVISOR,DEPTO) 
VALUES ('PAULO CESAR', '000000008','1998-09-23',null,'M','3000','JULIANA','8' ); 
INSERT INTO EMPREGADOS(NOME, 
MATRICULA,DATANASC,ENDERECO,SEXO,SALARIO,SUPERVISOR,DEPTO) 
VALUES ('LUCAS NUNES', '000000010','1999-05-28',null,'S','1000','JULIANA','10' ); 
INSERT INTO EMPREGADOS(NOME, 
MATRICULA,DATANASC,ENDERECO,SEXO,SALARIO,SUPERVISOR,DEPTO) 
VALUES ('SOPHIA CASSIANO', '000000002','2000-04-
05',null,'S','1000','JULIANA','2' ); 
USE [A4] 
INSERT INTO EMPREGADOS(NOME, 
MATRICULA,DATANASC,ENDERECO,SEXO,SALARIO,SUPERVISOR,DEPTO) 
VALUES ('TIFFANY CASSIANO', '000000009','2000-04-
05',null,'S','1000','JULIANA','9' ); 
SELECT * FROM EMPREGADOS 
 
 
 
 
 
------------------------------------------------------------------------------------------------- 
----DEPARTAMENTO 
USE [A4] 
INSERT INTO DEPARTAMENTO(DEPTONUM, DEPTONOME,MATRGERENTE) 
VALUES ('1', 'DONO','000000001' ); 
INSERT INTO DEPARTAMENTO(DEPTONUM, DEPTONOME,MATRGERENTE) 
VALUES ('2', 'TECNOLOGIA','000000001' ); 
USE [A4] 
INSERT INTO DEPARTAMENTO(DEPTONUM, DEPTONOME,MATRGERENTE) 
VALUES ('3', 'ADMINSTRACAO','000000001' ); 
USE [A4] 
INSERT INTO DEPARTAMENTO(DEPTONUM, DEPTONOME,MATRGERENTE) 
VALUES ('4', 'RH','000000001' ); 
USE [A4] 
INSERT INTO DEPARTAMENTO(DEPTONUM, DEPTONOME,MATRGERENTE) 
VALUES ('5', 'TECNOLOGIA','000000001' ); 
USE [A4] 
INSERT INTO DEPARTAMENTO(DEPTONUM, DEPTONOME,MATRGERENTE) 
VALUES ('6', 'TECNOLOGIA','000000001' ); 
USE [A4] 
INSERT INTO DEPARTAMENTO(DEPTONUM, DEPTONOME,MATRGERENTE) 
VALUES ('7', 'ADMINSTRACAO','000000001' ); 
USE [A4] 
INSERT INTO DEPARTAMENTO(DEPTONUM, DEPTONOME,MATRGERENTE) 
VALUES ('8', 'ADMINSTRACAO','000000001' ); 
USE [A4] 
INSERT INTO DEPARTAMENTO(DEPTONUM, DEPTONOME,MATRGERENTE) 
VALUES ('9', 'RH','000000001' ); 
USE [A4] 
INSERT INTO DEPARTAMENTO(DEPTONUM, DEPTONOME,MATRGERENTE) 
VALUES ('10', 'RH','000000001' ); 
SELECT * FROM DEPARTAMENTO 
 
 
 
 
 
 
------------------------------------------------------------------------------------------------- 
----TRABALHA_EM 
SELECT * FROM TRABALHA_EM 
DELETE FROM TRABALHA_EM WHERE HORAS= '00:00:00.0000000'; 
USE [A4] 
INSERT INTO TRABALHA_EM (MATRICULA,HORAS,PCODIGO) 
VALUES ('000000001','06:00:00.0000000','1'); 
USE [A4] 
INSERT INTO TRABALHA_EM (MATRICULA,HORAS,PCODIGO) 
VALUES ('000000002','06:00:00.0000000','2'); 
INSERT INTO TRABALHA_EM (MATRICULA,HORAS,PCODIGO) 
VALUES ('000000005','06:00:00.0000000','3'); 
INSERT INTO TRABALHA_EM (MATRICULA,HORAS,PCODIGO) 
VALUES ('000000006','06:00:00.0000000','4'); 
INSERT INTO TRABALHA_EM (MATRICULA,HORAS,PCODIGO) 
VALUES ('000000004','06:00:00.0000000','5'); 
INSERT INTO TRABALHA_EM (MATRICULA,HORAS,PCODIGO) 
VALUES ('000000010','06:00:00.0000000','6'); 
INSERT INTO TRABALHA_EM (MATRICULA,HORAS,PCODIGO) 
VALUES ('000000009','06:00:00.0000000','7'); 
INSERT INTO TRABALHA_EM (MATRICULA,HORAS,PCODIGO) 
VALUES ('000000007','06:00:00.0000000','8'); 
INSERT INTO TRABALHA_EM (MATRICULA,HORAS,PCODIGO) 
VALUES ('000000008','06:00:00.0000000','9'); 
INSERT INTO TRABALHA_EM (MATRICULA,HORAS,PCODIGO) 
VALUES ('000000003','06:00:00.0000000','10'); 
 
 
 
------------------------------------------------------------------------------------------------- 
----PROJETO 
SELECT * FROM PROJETO 
USE [A4] 
INSERT INTO PROJETO (PNOME,PLOCALIZACAO,PCODIGO,DEPTO) 
VALUES ('SUPERVISOR','BARRA TIJUCA','1','1') 
USE [A4] 
INSERT INTO PROJETO (PNOME,PLOCALIZACAO,PCODIGO,DEPTO) 
VALUES ('IMPLEMENTACAO','BARRA TIJUCA','2','2'); 
INSERT INTO PROJETO (PNOME,PLOCALIZACAO,PCODIGO,DEPTO) 
VALUES ('Reciclagem','BARRA TIJUCA','3','8'); 
INSERT INTO PROJETO (PNOME,PLOCALIZACAO,PCODIGO,DEPTO) 
VALUES ('IMPLEMENTACAO','BARRA TIJUCA','4','5'); 
INSERT INTO PROJETO (PNOME,PLOCALIZACAO,PCODIGO,DEPTO) 
VALUES ('IMPLEMENTACAO','CENTRO','5','6'); 
INSERT INTO PROJETO (PNOME,PLOCALIZACAO,PCODIGO,DEPTO) 
VALUES ('Sustentabilidade','BARRA TIJUCA','7','4'); 
INSERT INTO PROJETO (PNOME,PLOCALIZACAO,PCODIGO,DEPTO) 
VALUES ('Sustentabilidade','TIJUCA','6','9'); 
INSERT INTO PROJETO (PNOME,PLOCALIZACAO,PCODIGO,DEPTO) 
VALUES ('Sustentabilidade','CENTRO','8','10'); 
INSERT INTO PROJETO (PNOME,PLOCALIZACAO,PCODIGO,DEPTO) 
VALUES ('Reciclagem','BOTAFOGO','9','7'); 
INSERT INTO PROJETO (PNOME,PLOCALIZACAO,PCODIGO,DEPTO) 
VALUES ('Reciclagem','CENTRO','10','3'); 
 
 
 
 
 
 
 
 
 
 
------------------------------------------------------------------------------------------------- 
----DEPENDENTES 
USE [A4] 
INSERT INTO DEPENDENTES 
(NOMEDEPEND,MATRICULA,DATANASC,SEXO,PARENTESCO) 
VALUES ('KRIS BOND','1','2019-02-07','M','FILHO'); 
INSERT INTO DEPENDENTES 
(NOMEDEPEND,MATRICULA,DATANASC,SEXO,PARENTESCO) 
VALUES ('SIXX CASSIANO','2','2019-04-07','F','FILHO'); 
INSERT INTO DEPENDENTES 
(NOMEDEPEND,MATRICULA,DATANASC,SEXO,PARENTESCO) 
VALUES ('CLAUDIA SANTOS','3','2018-04-07','F','FILHO'); 
INSERT INTO DEPENDENTES 
(NOMEDEPEND,MATRICULA,DATANASC,SEXO,PARENTESCO) 
VALUES ('HELLENA OLIVEIRA','4','2019-04-18','F','FILHO'); 
INSERT INTO DEPENDENTES 
(NOMEDEPEND,MATRICULA,DATANASC,SEXO,PARENTESCO) 
VALUES ('LUIZ MARINHO','5','2019-04-27','M','FILHO'); 
INSERT INTO DEPENDENTES 
(NOMEDEPEND,MATRICULA,DATANASC,SEXO,PARENTESCO) 
VALUES ('MARCELO BARBOSA','6','2019-04-17','M','FILHO'); 
INSERT INTO DEPENDENTES 
(NOMEDEPEND,MATRICULA,DATANASC,SEXO,PARENTESCO) 
VALUES ('FLOR SILVA','7','2019-05-07','F','FILHO'); 
INSERT INTO DEPENDENTES 
(NOMEDEPEND,MATRICULA,DATANASC,SEXO,PARENTESCO) 
VALUES ('JESSICA CESSAR','8','2020-04-07','F','FILHO'); 
INSERT INTO DEPENDENTES 
(NOMEDEPEND,MATRICULA,DATANASC,SEXO,PARENTESCO) 
VALUES ('YURI CASSIANO','9','2020-05-07','M','FILHO'); 
INSERT INTO DEPENDENTES 
(NOMEDEPEND,MATRICULA,DATANASC,SEXO,PARENTESCO) 
VALUES ('EDU NUNES','10','2019-04-07','M','FILHO'); 
SELECT * FROM DEPENDENTES