bd_laboratorio07.sql

-- Lab 07

CREATE DATABASE bd_laboratorio07

USE bd_laboratorio07

DROP TABLE TB_SOLICITACAO
DROP TABLE TB_TECNICO
DROP TABLE TB_FUNCIONARIO
DROP TABLE TB_SETOR
DROP TABLE TB_DEPARTAMENTO


CREATE TABLE TB_DEPARTAMENTO (
  CD_DEPARTAMENTO INT NOT NULL PRIMARY KEY,
  NM_DEPARTAMENTO VARCHAR(40) NOT NULL
)

INSERT INTO TB_DEPARTAMENTO (CD_DEPARTAMENTO, NM_DEPARTAMENTO)
VALUES (10,'TI')

INSERT INTO TB_DEPARTAMENTO (CD_DEPARTAMENTO, NM_DEPARTAMENTO)
VALUES (20,'RH')

INSERT INTO TB_DEPARTAMENTO (CD_DEPARTAMENTO, NM_DEPARTAMENTO)
VALUES (30,'MA')



CREATE TABLE TB_SETOR (
  CD_SETOR INT NOT NULL PRIMARY KEY,
  NM_SETOR VARCHAR(40) NOT NULL,
  NM_GERENTE VARCHAR(50) NOT NULL,
  CD_DEPARTAMENTO INT NOT NULL
)

ALTER TABLE TB_SETOR ADD CONSTRAINT FK_SETOR 
FOREIGN KEY (CD_DEPARTAMENTO) REFERENCES TB_DEPARTAMENTO(CD_DEPARTAMENTO)

INSERT INTO TB_SETOR (CD_SETOR, NM_SETOR,NM_GERENTE, CD_DEPARTAMENTO)
VALUES (100,'SETOR 100','JOSE CARLOS',10)

INSERT INTO TB_SETOR (CD_SETOR, NM_SETOR, NM_GERENTE, CD_DEPARTAMENTO)
VALUES (200,'SETOR 200','JOSEFA', 20)

INSERT INTO TB_SETOR (CD_SETOR, NM_SETOR, NM_GERENTE, CD_DEPARTAMENTO)
VALUES (201,'SETOR 201','PAULO SILVA', 20)

INSERT INTO TB_SETOR (CD_SETOR, NM_SETOR, NM_GERENTE, CD_DEPARTAMENTO)
VALUES (202,'SETOR 202','RICARDO ARAUJO', 20)


CREATE TABLE TB_FUNCIONARIO (
  MATRICULA INT NOT NULL PRIMARY KEY,
  NM_FUNCIONARIO VARCHAR(40) NOT NULL,
  SEXO VARCHAR(1) NOT NULL,
  CD_SETOR INT NULL
)

ALTER TABLE TB_FUNCIONARIO ADD CONSTRAINT FK_FUNCIONARIO 
FOREIGN KEY (CD_SETOR) REFERENCES TB_SETOR(CD_SETOR)

INSERT INTO TB_FUNCIONARIO (MATRICULA, NM_FUNCIONARIO, SEXO, CD_SETOR)
VALUES (1,'JOAO','M',100)

INSERT INTO TB_FUNCIONARIO (MATRICULA, NM_FUNCIONARIO, SEXO, CD_SETOR)
VALUES (2,'MARIA','F',200)

INSERT INTO TB_FUNCIONARIO (MATRICULA, NM_FUNCIONARIO, SEXO, CD_SETOR)
VALUES (3,'PEDRO','M',200)

INSERT INTO TB_FUNCIONARIO (MATRICULA, NM_FUNCIONARIO, SEXO, CD_SETOR)
VALUES (4,'RICARDO','M',201)

CREATE TABLE TB_TECNICO (
  CD_TECNICO INT NOT NULL PRIMARY KEY,
  NM_TECNICO VARCHAR(40) NOT NULL,
)

INSERT INTO TB_TECNICO (CD_TECNICO, NM_TECNICO)
VALUES (101,'RITA')

INSERT INTO TB_TECNICO (CD_TECNICO, NM_TECNICO)
VALUES (102,'PATRICIA')

INSERT INTO TB_TECNICO (CD_TECNICO, NM_TECNICO)
VALUES (103,'JOANA')


CREATE TABLE TB_SOLICITACAO (
  CD_SOLICITACAO INT NOT NULL PRIMARY KEY,
  MATRICULA INT NOT NULL,
  CD_TECNICO INT NULL,
  DATA_ABERTURA DATETIME NOT NULL,
  DESCRICAO VARCHAR(50) NOT NULL,
  DATA_FECHAMENTO DATETIME NULL,
  DESCRICAO_FECHAMENTO VARCHAR(50) NULL
)

ALTER TABLE TB_SOLICITACAO ADD CONSTRAINT FK_SOLICITACAO_FUNCIONARIO 
FOREIGN KEY (MATRICULA) REFERENCES TB_FUNCIONARIO(MATRICULA)

ALTER TABLE TB_SOLICITACAO ADD CONSTRAINT FK_SOLICITACAO_TECNICO 
FOREIGN KEY (CD_TECNICO) REFERENCES TB_TECNICO(CD_TECNICO)

INSERT INTO TB_SOLICITACAO (CD_SOLICITACAO, MATRICULA,CD_TECNICO,DATA_ABERTURA,
                            DESCRICAO, DATA_FECHAMENTO, DESCRICAO_FECHAMENTO)
VALUES(1, 1,101,'20190620','IMPRESSORA COM PROBLEMA',NULL, NULL)

INSERT INTO TB_SOLICITACAO (CD_SOLICITACAO, MATRICULA,CD_TECNICO,DATA_ABERTURA,
                            DESCRICAO, DATA_FECHAMENTO, DESCRICAO_FECHAMENTO)
VALUES(2, 2,101,'20190620','MS WORD COM PROBLEMA','20/06/2007', 'REINSTALADO')

INSERT INTO TB_SOLICITACAO (CD_SOLICITACAO, MATRICULA,CD_TECNICO,DATA_ABERTURA,
                            DESCRICAO, DATA_FECHAMENTO, DESCRICAO_FECHAMENTO)
VALUES(3, 3,102,'20190620','MOUSE COM PROBLEMA',NULL, NULL)

INSERT INTO TB_SOLICITACAO (CD_SOLICITACAO, MATRICULA,CD_TECNICO,DATA_ABERTURA,
                            DESCRICAO, DATA_FECHAMENTO, DESCRICAO_FECHAMENTO)
VALUES(4, 4,102,'20190620','MONITOR COM PROBLEMA',NULL, NULL)

INSERT INTO TB_SOLICITACAO (CD_SOLICITACAO, MATRICULA,CD_TECNICO,DATA_ABERTURA,
                            DESCRICAO, DATA_FECHAMENTO, DESCRICAO_FECHAMENTO)
VALUES(5, 1,101,'20190622','MICRO TRAVANDO',NULL, NULL)

INSERT INTO TB_SOLICITACAO (CD_SOLICITACAO, MATRICULA,CD_TECNICO,DATA_ABERTURA,
                            DESCRICAO, DATA_FECHAMENTO, DESCRICAO_FECHAMENTO)
VALUES(6, 2,101,'20190622','MS EXCEL COM PROBLEMA',NULL, NULL)

--Selecionar todos os Departamentos e seus Setores quando existirem. O resultado da
--consulta deve apresentar o Código do Departamento, o Nome do Departamento, o
--Nome do Setor e o Nome do Gerente.
SELECT D.cd_departamento, D.nm_departamento, S.nm_setor, S.nm_gerente
FROM TB_DEPARTAMENTO D INNER JOIN TB_SETOR S
ON (D.cd_departamento = S.cd_departamento)


--Selecionar todos os Setores e seus respectivos funcionários quando existirem. O
--resultado da consulta deve apresentar o Nome do Departamento, o Código do Setor,
--o Nome do Gerente e o Nome do Funcionário.
SELECT D.nm_departamento, S.cd_setor, S.nm_gerente,F.nm_funcionario
FROM TB_SETOR S INNER JOIN TB_FUNCIONARIO F
ON (S.cd_setor = F.cd_setor)
	INNER JOIN TB_DEPARTAMENTO D
	ON(S.cd_departamento = D.cd_departamento)
	


--Selecionar todos os Gerentes e os seus respectivos funcionários. O resultado da
--consulta deve apresentar o Nome do Gerente, a Matricula do Funcionário e o Nome
--do Funcionário.
SELECT S.nm_gerente, F.matricula, F.nm_funcionario
FROM TB_SETOR S INNER JOIN TB_FUNCIONARIO F
ON (S.cd_setor = F.cd_setor)


--Selecionar o número de solicitações por Funcionário.
SELECT COUNT(S.cd_solicitacao), F.nm_funcionario 
FROM TB_FUNCIONARIO F INNER JOIN TB_SOLICITACAO S
ON (F.matricula  = S.matricula)
GROUP BY cd_solicitacao, F.nm_funcionario 

--Selecionar o número de solicitações por Setor.
SELECT COUNT(S.cd_solicitacao), SE.nm_setor
FROM TB_SOLICITACAO S INNER JOIN TB_FUNCIONARIO F
ON (S.matricula = F.matricula )
	INNER JOIN TB_SETOR SE
	ON(F.cd_setor = SE.cd_setor)
	GROUP BY S.cd_solicitacao, SE.nm_setor

--Selecionar o número de solicitações por Departamento.
SELECT COUNT(S.cd_solicitacao), D.nm_departamento
FROM TB_SOLICITACAO S INNER JOIN TB_FUNCIONARIO F
ON(S.matricula = F.matricula )
	INNER JOIN TB_SETOR SE 
	ON(F.cd_setor = SE.cd_setor)
		INNER JOIN TB_DEPARTAMENTO D
		ON(SE.cd_departamento = D.cd_departamento)
		GROUP BY S.cd_solicitacao, D.nm_departamento
		
--Selecionar o número de solicitações em aberto por Departamento. Apresentar
--somente os Departamentos que possuem mais de 2 solicitações em aberto.
SELECT COUNT(S.data_fechamento), D.nm_departamento
FROM TB_SOLICITACAO S INNER JOIN TB_FUNCIONARIO F
ON(S.matricula = F.matricula )
	INNER JOIN TB_SETOR SE
	ON(SE.cd_setor = F.cd_setor)
		INNER JOIN TB_DEPARTAMENTO D
		ON(SE.cd_departamento = D.cd_departamento)
		GROUP BY D.nm_departamento
		HAVING COUNT(S.data_fechamento) IS NULL 

--Selecionar todas as Solicitações em Aberto. O resultado da consulta deve apresentar
--o Nome do Técnico Responsável, a Matrícula do Funcionário, o Nome do
--Funcionário, a Data de Abertura da Solicitação e a Descrição da Solicitação.
SELECT T.nm_tecnico, F.matricula, F.nm_funcionario, S.data_abertura, S.descricao
FROM TB_SOLICITACAO S INNER JOIN TB_FUNCIONARIO F
ON(S.matricula = F.matricula)
	INNER JOIN TB_TECNICO T
	ON(T.cd_tecnico = S.cd_tecnico)
WHERE S.data_fechamento IS NULL


--Selecionar os técnicos que nunca atenderam a uma solicitação.
SELECT T.nm_tecnico 
FROM TB_TECNICO T LEFT OUTER JOIN TB_SOLICITACAO S
ON(T.cd_tecnico = S.cd_tecnico)