Scripts da Aula 26/10

Código:


CREATE TABLE TB_FUNCIONARIO(
   FUN_INT_ID INT IDENTITY(1,1) PRIMARY KEY,
   FUN_TXT_NOME VARCHAR(50) NOT NULL,
   FUN_BIT_STATUS BIT DEFAULT 1
)

ALTER TABLE TB_FUNCIONARIO
ADD DEP_INT_ID INT
FOREIGN KEY REFERENCES TB_DEPARTAMENTO(DEP_INT_ID)

CREATE TABLE TB_DEPARTAMENTO(
   DEP_INT_ID INT IDENTITY(1,1) PRIMARY KEY,
   DEP_TXT_NOME VARCHAR(30) NOT NULL
)

INSERT INTO tb_funcionario(fun_txt_nome)
VALUES ('Felippe Mendes')
INSERT INTO tb_funcionario(fun_txt_nome)
VALUES('Vanessa Mroz')
INSERT INTO tb_funcionario(fun_txt_nome)
VALUES('zina')
INSERT INTO tb_funcionario(fun_txt_nome)
VALUES(lower('FREEDY MERCURY'))

SELECT * FROM TB_FUNCIONARIO

INSERT INTO TB_DEPARTAMENTO(DEP_TXT_NOME)
VALUES ('VENDAS')
INSERT INTO TB_DEPARTAMENTO(DEP_TXT_NOME)
VALUES ('T.I')
INSERT INTO TB_DEPARTAMENTO(DEP_TXT_NOME)
VALUES ('COMERCIAL')
INSERT INTO TB_DEPARTAMENTO(DEP_TXT_NOME)
VALUES ('FINANCEIRO')

SELECT DEP_INT_ID AS [ID DEPARTAMENTO],
DEP_TXT_NOME AS [NOME DO DEPARTAMENTO]
FROM TB_DEPARTAMENTO

UPDATE TB_FUNCIONARIO
SET DEP_INT_ID = 3
WHERE FUN_INT_ID = 3 OR FUN_INT_ID = 4

SELECT * FROM TB_FUNCIONARIO

INSERT INTO TB_FUNCIONARIO(FUN_TXT_NOME)
VALUES('FELIPE RATTI')

DELETE FROM TB_FUNCIONARIO
WHERE FUN_INT_ID = 6

SELECT D.DEP_TXT_NOME, F.FUN_TXT_NOME
FROM TB_DEPARTAMENTO D FULL OUTER JOIN
TB_FUNCIONARIO F
ON D.DEP_INT_ID = F.DEP_INT_ID
ORDER BY F.FUN_TXT_NOME

Exercício em sala

-- Criando base de Dados
Create database db_teste

--Criando a tabela Departamento
Create table Departamento( id_dp int identity (1,1) primary key,
cod_depto int not null , nome_depto varchar (25) not null
)

--Criando a tabela Funcionário
Create table Funcionário(
cod_func int identity (1,1)primary key,
nome_func varchar (50)not null, salario int not null ,
id_dp int foreign key references Departamento(id_dp)not null
)

--Criando a Tabela Projeto

Create table Projeto (
cod_proj int foreign key references Funcionário(cod_Func) not null, nome_proj varchar(50)not null,
Duracao int not null
)
-- Deletando a tabela Projeto
drop table Projeto

-- Criando a Tabela Func_Proj
Create table Func_Proj (
cod_func int not null,
cod_proj int not null,
horas_trab int not null
)
-- Deletando a tabela Func_Proj
Drop table Func_Proj

--inserindo dados na tabela Departamento

insert into Departamento values (1, 'Marketing');
insert into Departamento values (2, 'Vendas');
insert into Departamento values (3, 'Dados');
insert into Departamento values (4, 'Pesquisa');

-- Mostrando registro da tabela Departamento

Select * from Departamento
go

-- Inserindo dados na tabela Funcionário

insert into Funcionário values ( 'Joao da Silva', 2.000, 2);
insert into Funcionário values ( 'Mario Souza', 1.500, 1);
insert into Funcionário values ( 'Sergio Santos', 2.400, 2);
insert into Funcionário values ( 'Maria Castro', 1.200, 1);
insert into Funcionário values ( 'Marcio Santana', 1.400, 4);

-- mostrando dados da tabela funcinário

Select * from Funcionário
go

-- inserindo valores na tabela Projeto

insert into Projeto values ( 'SistemaA', 2);
insert into Projeto values ( 'SistemaB', 6);
insert into Projeto values ( 'SistemaX', 4);

-- mostrando dados da tabela Projeto

select * from Projeto

-- Inserindo dados na tabela Func_Proj * verificar o relacionamento

insert into Func_Proj values (101, 1001, 24);
insert into Func_Proj values (101, 1002, 160);
insert into Func_Proj values (102, 1001, 56);
insert into Func_Proj values (102, 1003, 45);
insert into Func_Proj values (103, 1001, 86);
insert into Func_Proj values (103, 1003, 64);
insert into Func_Proj values (104, 1001, 46);
insert into Func_Proj values (105, 1001, 84);
insert into Func_Proj values (105, 1002, 86);


-- Mostrando os dados da tabela Func_Proj

select * from Func_Proj

create procedure sp_InsereFuncionario
@FUN_TXT_NOME VARCHAR(50),
@DEP_INT_ID INT,
@DEP_STR_NOME VARCHAR(50)
AS
DECLARE @ID_FUNCIONARIO INT

INSERT INTO TB_FUNCIONARIO(FUN_TXT_NOME,
DEP_INT_ID) VALUES (@FUN_TXT_NOME,@DEP_INT_ID)

SET @ID_FUNCIONARIO = @@IDENTITY

INSERT INTO TB_FUNCIONARIO_DEPENDENTE
(DEP_STR_NOME, FUN_INT_ID)
VALUES (@DEP_STR_NOME, @ID_FUNCIONARIO)[code]

Povo, dúvida no exercício 10, na minha mente é algo tipo eu não estar utilizando a função correta, mas não sei ao certo como!!! algum help, pls... a mensagem de erro ao final da execução informa que não se pode usar funções de agregação na cláusula where. E aí, se num pode, como faz???

10) Obtenha o nome de cada projeto e a quantidade total de horas trabalhadas pelos funcionários em cada um deles, em ordem decrescente do total de horas, mas desde que o total de horas trabalhadas no projeto seja maior que 200.

******************************************************************************************************************
SELECT P.NOME_PROJ, SUM(FP.HORAS_TRAB) HORAS, F.NOME_FUNC
FROM PROJETO P INNER JOIN FUNC_PROJ FP
ON P.COD_PROJ=FP.COD_PROJ INNER JOIN FUNCIONARIO F
ON F.COD_FUNC=FP.COD_FUNC
where sum(fp.horas_trab)>200
GROUP BY P.NOME_PROJ
ORDER BY 2 DESC
******************************************************************************************************************

******************************************************************************************************************
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
******************************************************************************************************************

Marciiao escreveu:Povo, dúvida no exercício 10, na minha mente é algo tipo eu não estar utilizando a função correta, mas não sei ao certo como!!! algum help, pls... a mensagem de erro ao final da execução informa que não se pode usar funções de agregação na cláusula where. E aí, se num pode, como faz???

10) Obtenha o nome de cada projeto e a quantidade total de horas trabalhadas pelos funcionários em cada um deles, em ordem decrescente do total de horas, mas desde que o total de horas trabalhadas no projeto seja maior que 200.

******************************************************************************************************************
SELECT P.NOME_PROJ, SUM(FP.HORAS_TRAB) HORAS, F.NOME_FUNC
FROM PROJETO P INNER JOIN FUNC_PROJ FP
ON P.COD_PROJ=FP.COD_PROJ INNER JOIN FUNCIONARIO F
ON F.COD_FUNC=FP.COD_FUNC
where sum(fp.horas_trab)>200
GROUP BY P.NOME_PROJ
ORDER BY 2 DESC
******************************************************************************************************************

******************************************************************************************************************
Msg 147, Level 15, State 1, Line 1
An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.
******************************************************************************************************************

tenta isso!

select P.nome_projeto_txt,sum(FP.horas_trab) AS 'SOMA'
from tb_projeto P,tb_func_projeto FP,tb_funcionario F
where F.cod_func_int = fp.cod_func_int
and fp.cod_proj_int = p.cod_proj_int
group by p.nome_projeto_txt
having sum (fp.horas_trab)>200
order by SUM(Fp.horas_trab)