VISÕES (VIEW)
01 - Crie uma view que mostre a pesquisa que retorna o resultado
abaixo:
R: ALTER VIEW VW_ALUNO
AS
SELECT
REPLICATE('0',5-(LEN(Cod_Aluno)))+CONVERT(VARCHAR(5),COD_ALUNO) AS CODALUNO,
Nome_Aluno AS
NOME_ALUNO,
CONVERT(CHAR,Data_Nascimento,103) AS DATA
FROM ALUNO
WHERE Data_Nascimento IS NOT NULL
02 - Crie uma view que aproveitando a view anterior mostre além
dos dados do aluno, os dados da disciplina que ele está cursando, como na
figura abaixo:
R: CREATE VIEW VW_ALUNO_NOTA
AS
SELECT DISTINCT
A.CODALUNO,
A.NOME_ALUNO,
A.DATA,
REPLICATE('0',5-(LEN(D.CODIGO_DISCIPLINA)))+CONVERT(VARCHAR(5),D.codigo_disciplina) AS CODDISCIPLINA,
CONVERT(DECIMAL(10,2),M.NOTA) AS NOTA,
CASE M.SITUACAO
WHEN 'NOK' THEN 'REPROVADO'
WHEN 'OK' THEN 'APROVADO'
ELSE 'NÃO DEFINIDO'
END AS SITUACAO
FROM VW_ALUNO A, Matricula M, Disciplina D
WHERE A.CODALUNO=M.cod_Aluno
AND M.cod_Disciplina=D.codigo_disciplina
03 - Crie uma view que aproveitando a view anterior mostre além
dos dados do aluno e os dados da disciplina que ele está cursando, mostre os
dados da disciplina como na figura abaixo:
R: CREATE VIEW VW_ALUNO_DISCIPLINA_NOTA
AS
SELECT DISTINCT
V.CODALUNO,
V.NOME_ALUNO,
V.DATA,
V.CODDISCIPLINA,
D.NOME AS DISCIPLINA,
(SELECT CASE
WHEN D.pre_requisito IS NOT NULL THEN (SELECT D2.NOME FROM DISCIPLINA D2 WHERE D2.codigo_disciplina=D.pre_requisito)
WHEN D.pre_requisito IS NULL THEN 'NÃO POSSUI'
END) AS PREREQUISITO,
D.creditos AS CREDITOS,
V.NOTA,
V.SITUACAO
FROM VW_ALUNO_NOTA V,Disciplina D
WHERE D.codigo_disciplina=V.CODDISCIPLINA
04 - Crie uma view que aproveitando a view anterior mostre além
dos dados do aluno, os dados da disciplina que ele está cursando, mostre os
dados do professor da disciplina como na figura abaixo:
R: CREATE VIEW VW_ALUNO_NOTA_PROFESSOR
AS
SELECT DISTINCT
V.CODALUNO,
V.NOME_ALUNO,
V.DATA,
V.CODDISCIPLINA,
V.DISCIPLINA,
V.PREREQUISITO,
V.CREDITOS,
V.NOTA,
V.SITUACAO,
REPLICATE('0',5-(LEN(P.cod_professor)))+CONVERT(VARCHAR(5),P.cod_professor) AS CODPROFESSOR,
P.nome_professor AS PROFESSOR
FROM VW_ALUNO_DISCIPLINA_NOTA V, DISCIPLINA D, Professor P
WHERE P.cod_professor=D.cod_prof
AND D.codigo_disciplina=V.CODDISCIPLINA
05 - Crie uma view com o nome de VW_BOLETIM que aproveitando a
view com os dados do aluno e os dados da disciplina que ele está cursando,
mostre os dados da Média Geral da disciplina e o Desvio Padrão da nota do aluno
como na figura abaixo:
R: CREATE VIEW VW_BOLETIM
AS
SELECT DISTINCT
V.CODALUNO,
V.NOME_ALUNO,
V.DATA,
V.CODDISCIPLINA,
V.DISCIPLINA,
V.PREREQUISITO,
V.CREDITOS,
V.NOTA,
V.SITUACAO,
(SELECT CONVERT(DECIMAL(10,2),AVG(NOTA)) FROM MATRICULA WHERE cod_Disciplina=V.CODDISCIPLINA) AS MEDIA_GERAL,
(V.NOTA-(SELECT CONVERT(DECIMAL(10,2),AVG(NOTA)) FROM MATRICULA WHERE cod_Disciplina=V.CODDISCIPLINA)) AS DESVIO_PADRAO
FROM VW_ALUNO_DISCIPLINA_NOTA V
--WHERE CODDISCIPLINA=1
06 - Crie uma view com o nome de VW_BOLETIM_COMPARATIVO que aproveitando
a view anterior, mostre os dados da Menor e da Maior nota da disciplina como na
figura abaixo:
R: CREATE VIEW VW_BOLETIM_DETALHADO
AS
SELECT DISTINCT
V.CODALUNO,
V.NOME_ALUNO,
V.DATA,
V.CODDISCIPLINA,
V.DISCIPLINA,
V.PREREQUISITO,
V.CREDITOS,
V.NOTA,
V.SITUACAO,
V.MEDIA_GERAL,
V.DESVIO_PADRAO,
(SELECT MIN(V2.NOTA) FROM VW_BOLETIM V2 WHERE V2.CODDISCIPLINA=V.CODDISCIPLINA) AS MENOR_NOTA,
(SELECT MAX(V2.NOTA) FROM VW_BOLETIM V2 WHERE V2.CODDISCIPLINA=V.CODDISCIPLINA) AS MAIOR_NOTA
FROM VW_BOLETIM V
TRIGGERS (GATILHOS)
01 - Crie uma trigger que ao inserir um novo professor ela exiba
na tela a seguinte mensagem:
FOI INSERIDO UM PROFESSOR COM OS SEGUINTES DADOS:
CÓDIGO: <código>
NOME: <nome>
R: create trigger NovoProfessor on [Professor]
for insert as
DECLARE @CODIGO INT, @NOME VARCHAR(50)
SELECT @CODIGO=cod_professor, @NOME=nome_professor
FROM inserted
print 'FOI INSERIDO
UM PROFESSOR COM OS SEGUINTES DADOS:'
print ''
print 'Codigo: '+convert(varchar(10),@codigo)
print 'Professor: '+@nome
insert into Professor
values (1, 'William')
02 - Crie uma trigger que ao alterar os dados de um professor ela
exiba na tela a seguinte mensagem:
FOI ALTERADO OS DADOS DO PROFESSOR:
CÓDIGO: <código>
NOME: <nome>
QUE DÁ AULA EM 0
DISCIPLINAS.
R: CREATE TRIGGER TR_PROFESSOR
ON PROFESSOR
FOR UPDATE
AS BEGIN
DECLARE @CODIGO
INT, @PROFESSOR VARCHAR(50), @QTD INT
SELECT @CODIGO=cod_professor,
@PROFESSOR=nome_professor FROM deleted
SELECT @QTD=COUNT(*)
FROM Disciplina WHERE cod_prof=@CODIGO
PRINT 'FOI
ALTERADO OS DADOS DO PROFESSOR: '
PRINT ''
PRINT 'CÓDIGO:
'+CONVERT(VARCHAR(10),@CODIGO)
PRINT 'NOME:
'+@PROFESSOR
PRINT 'QUE
DÁ AULA EM '+CONVERT(VARCHAR(5),@QTD)+' DISCIPLINAS.'
END
update Professor set nome_professor='Daiane' where cod_professor
= 1
insert into Professor values (1, 'William')
SELECT * FROM Professor
03 - Crie uma trigger que ao inserir/alterar dados na tabela
disciplina e não for atribuído um professor a mesma exiba uma pesquisa com
todos os professores cadastrados no banco.
R: CREATE TRIGGER TR_DISCIPLINA
ON DISCIPLINA
FOR INSERT, UPDATE
AS BEGIN
IF ((SELECT
COUNT(*) FROM inserted WHERE cod_prof IS NULL)>0) BEGIN
SELECT
* FROM Professor ORDER BY nome_professor
END
END
INSERT INTO Disciplina (codigo_disciplina,nome,pre_requisito,creditos,cod_prof)
VALUES (8, 'Engenharia de Software',null,2,null)
update Disciplina set cod_prof=222 where codigo_disciplina=8
select * from Disciplina
select * from Professor
04 - Crie uma trigger que ao apagar os dados da tabela matrícula,
mostre na tela a seguinte mensagem:
OS DADOS ABAIXO FORAM APAGADOS DA TABELA MATRICULA:
CÓDIGO DO ALUNO: <código_aluno>
NOME DO ALUNO: <nome_aluno>
CÓDIGO DA DISCIPLINA: <código_disciplina>
NOME DA DISCIPLINA: <nome_disciplina>
CÓDIGO DO PROFESSOR: <código_professor>
NOME DO PROFESSOR: <nome_professor>
SITUAÇÃO: <APROVADO
/ REPROVADO>
R: CREATE TRIGGER TR_APAGAR_MATRICULA
ON MATRICULA
FOR DELETE
AS BEGIN
DECLARE @CODIGOALUNO
INT, @ALUNO VARCHAR(50),
@CODIGODISC INT, @DISCIPLINA VARCHAR(50),
@CODIGOPROF INT, @PROFESSOR VARCHAR(50),
@SITUACAO CHAR(10)
SELECT @CODIGOALUNO=COD_ALUNO,
@CODIGODISC=COD_DISCIPLINA, @SITUACAO=CASE(situacao)
WHEN
'OK' THEN 'APROVADO'
WHEN
'NOK' THEN 'REPROVADO'
END
FROM deleted
SELECT @ALUNO=ALUNO.Nome_Aluno FROM Aluno WHERE ALUNO.Cod_Aluno=@CODIGOALUNO
SELECT @DISCIPLINA=DISCIPLINA.nome,
@CODIGOPROF=PROFESSOR.cod_professor, @PROFESSOR=PROFESSOR.nome_professor
FROM Disciplina,
Professor
WHERE Disciplina.cod_prof=Professor.cod_professor
AND DISCIPLINA.codigo_disciplina=@CODIGODISC
PRINT 'OS
DADOS ABAIXO FORAM APAGADOS DA TABELA MATRICULA: '
PRINT ''
PRINT 'CÓDIGO
DO ALUNO: '+CONVERT(VARCHAR(10),@CODIGOALUNO)
PRINT 'NOME
DO ALUNO: '+@ALUNO
PRINT 'CÓDIGO
DA DISCIPLINA: '+CONVERT(VARCHAR(10),@CODIGODISC)
PRINT 'NOME
DA DISCIPLINA: '+@DISCIPLINA
PRINT 'CÓDIGO
DO PROFESSOR: '+CONVERT(VARCHAR(10),@CODIGOPROF)
PRINT 'NOME
DO PROFESSOR: '+@PROFESSOR
PRINT 'SITUACAO:
'+@SITUACAO
END
DELETE FROM Matricula WHERE cod_Aluno=222 AND cod_Disciplina=2
SELECT * FROM Matricula
05 - Crie uma trigger que altere/insira automaticamente a
situação de uma disciplina quando a nota dessa disciplina for modificada ou
inserida. Para nota abaixo de 5
a situação de ser igual a ‘NOK’, quando a nota for igual
ou maior que 5, a
situação deve ser igual a ‘OK’.
R: CREATE TRIGGER TR_MATRICULA
ON MATRICULA
FOR INSERT, UPDATE
AS BEGIN
DECLARE @CODALUNO
INT, @CODDISC INT, @NOTA DECIMAL(4,2)
SELECT @CODALUNO=COD_ALUNO,
@CODDISC=COD_DISCIPLINA, @NOTA=NOTA FROM inserted
IF (@NOTA>=5)
BEGIN
UPDATE
Matricula SET situacao='OK' WHERE cod_Aluno=@CODALUNO AND cod_Disciplina=@CODDISC
END
ELSE BEGIN
UPDATE
Matricula SET situacao='NOK' WHERE cod_Aluno=@CODALUNO AND cod_Disciplina=@CODDISC
END
END
SELECT * FROM Matricula
INSERT INTO Matricula (cod_Aluno, cod_Disciplina, nota, situacao)
VALUES (111,8,4,'OK')
UPDATE Matricula SET NOTA=5 WHERE cod_Aluno=222 AND cod_Disciplina=6
06 - Crie uma tabela de Log (TB_LOG) que contenha DATA
(DATETIME), AÇÃO (VARCHAR(10)), TABELA (NVARCHAR(30)), USUÁRIO (NVARCHAR(15)) e OBSERVAÇÃO
(NVARCHAR(1000)) e as triggers que a alimente quando for realizada qualquer
operação nas tabelas do BD. Exemplo de conteúdo da tabela de log:
R: ALTER TRIGGER TR_LOG_ALUNO
ON ALUNO
FOR INSERT, UPDATE, DELETE
AS BEGIN
DECLARE @QTDI
INT, @QTDD INT,
@CODI INT, @NOMEI VARCHAR(50), @DATAI DATETIME,
@CODD
INT, @NOMED VARCHAR(50), @DATAD DATETIME,
@ACAO
VARCHAR(10),@OBSERVACAO VARCHAR(1000), @USUARIO VARCHAR(15), @TABELA VARCHAR(30),
@DATAT DATETIME
SELECT @QTDI=COUNT(*)
FROM inserted
SELECT @QTDD=COUNT(*)
FROM deleted
SELECT @USUARIO=SYSTEM_USER
SELECT @DATAT=CONVERT(VARCHAR(10),GETDATE(),103)
IF (@QTDI>0
AND @QTDD>0) BEGIN
SET
@ACAO='ALTEROU'
SELECT
@CODI=COD_ALUNO, @NOMEI=NOME_ALUNO, @DATAI=DATA_NASCIMENTO FROM inserted
SELECT
@CODD=COD_ALUNO, @NOMED=NOME_ALUNO, @DATAD=DATA_NASCIMENTO FROM deleted
SELECT
@OBSERVACAO='NOVO = CODIGO: '+CONVERT(VARCHAR(10), @CODI)+' - NOME: '+@NOMEI+'
- DATA NASCIMENTO: '+CONVERT(VARCHAR(10),@DATAI,103)+
' | VELHO = CODIGO: '+CONVERT(VARCHAR(10),
@CODD)+' - NOME: '+@NOMED+' - DATA NASCIMENTO: '+CONVERT(VARCHAR(10),@DATAD,103)
END
ELSE
BEGIN
IF
(@QTDI>0) BEGIN
SET
@ACAO='INSERIU'
SELECT
@CODI=COD_ALUNO, @NOMEI=NOME_ALUNO, @DATAI=DATA_NASCIMENTO FROM inserted
SELECT
@OBSERVACAO='CODIGO: '+CONVERT(VARCHAR(10), @CODI)+' - NOME: '+@NOMEI+' - DATA
NASCIMENTO: '+CONVERT(VARCHAR(10),@DATAI,103)
END
ELSE
BEGIN
SET
@ACAO='APAGOU'
SELECT
@CODI=COD_ALUNO, @NOMEI=NOME_ALUNO, @DATAI=DATA_NASCIMENTO FROM deleted
SELECT
@OBSERVACAO='CODIGO: '+CONVERT(VARCHAR(10), @CODD)+' - NOME: '+@NOMED+' - DATA
NASCIMENTO: '+CONVERT(VARCHAR(10),@DATAD,103)
END
SELECT
@TABELA='ALUNO'
INSERT
INTO TB_LOG (DATA, ACAO, TABELA, USUARIO, OBSERVACAO)
VALUES
(@DATAT, @ACAO, @TABELA, @USUARIO, @OBSERVACAO)
END
END
select * from Aluno
select * from tb_log
insert into Aluno values (4, 'William', '')
SELECT * FROM sys.tables
STORED PROCEDURE (PROCEDIMENTOS)
01 - Crie uma stored procedure que gerencie a inserção de um novo
professor, ela deve tratar as informações, buscando garantir que não exista um
professor com o mesmo nome (inserção duplicada) e após a inserção exiba na tela
os dados inseridos.
R: create procedure ins_prof
@nome nvarchar (50),
@codigo int
as begin
declare @nro
int
select @nro=count(*)
from professor
where cod_professor=@codigo
or nome_professor=@nome
if (@nro>0)
begin
print
'NÃO PODE SER CADASTRADO!'
END
ELSE BEGIN
INSERT
INTO Professor (cod_professor, nome_professor) values (@codigo, @nome)
select
* from Professor where cod_professor=@codigo
end
End
exec ins_prof 'Maria', '1111'
select * from professor
02 - Crie uma stored procedure que gerencie a inserção de um novo
aluno, ela deve tratar as informações, buscando garantir que não exista uma
inserção duplicada, para isso utilize a data de nascimento do aluno e o nome do
mesmo e após a inserção exiba na tela a seguinte os dados inseridos.
R: create procedure ins_aluno
@nomealuno nvarchar (50),
@dtnasc date
as begin
declare @nro
int, @codaluno int
select @nro=count(*)
from Aluno
where Data_Nascimento=@dtnasc
and Nome_Aluno=@nomealuno
if (@nro>0)
begin
print
'ALUNO JA CADASTRADO!'
END
ELSE BEGIN
select @codaluno=MAX(cod_aluno)+1 from Aluno
INSERT
INTO Aluno (Cod_Aluno, Nome_Aluno, Data_Nascimento) values (@codaluno,@nomealuno,@dtnasc)
select
* from Aluno where Data_Nascimento=@dtnasc and Nome_Aluno=@nomealuno
end
End
exec ins_aluno 'Fernanda', '16-12-1988'
select * from Aluno
select * from aluno
--delete from Aluno where Cod_Aluno>=1379
03 - Crie uma stored procedure que gerencie a inserção de uma nova
disciplina, ela deve tratar as informações, buscando garantir que não exista
uma inserção duplicada, para isso utilize o nome da disciplina e após a
inserção exiba na tela a seguinte os dados inseridos.
R: create procedure ins_disciplina
@nomedisciplina nvarchar (50)
as begin
declare @nro
int, @coddisc int, @credito int
select @nro=count(*)
from Disciplina
where nome =
@nomedisciplina
if (@nro>0)
begin
print
'DISCIPLINA JA CADASTRADA!'
END
ELSE BEGIN
select @coddisc=MAX(codigo_disciplina)+1,
@credito=MAX(creditos)+1
from Disciplina
INSERT
INTO Disciplina(codigo_disciplina, nome, creditos) values (@coddisc,@nomedisciplina,
@credito)
select
* from Disciplina where nome = @nomedisciplina
end
End
exec ins_disciplina 'Contabilidade'
select * from Disciplina
04 - Crie uma stored procedure que gerencie a inserção de uma nova
matricula numa disciplina, ela deve tratar as informações, buscando garantir
que não exista uma inserção duplicada, para isso utilize o código do aluno e o
código da disciplina e após a inserção exiba na tela a seguinte os dados
inseridos.
R: create procedure ins_matricula
@cod_aluno int, @cod_disc int
as begin
declare @nro
int
select @nro=count(*)
from Matricula
where cod_Aluno
= @cod_aluno
and cod_Disciplina = @cod_disc
if (@nro>0)
begin
print
'MATRICULA JA CADASTRADA!'
END
ELSE BEGIN
INSERT
INTO Matricula(cod_Aluno, cod_Disciplina) values (@cod_aluno, @cod_disc)
select
* from Matricula where cod_Aluno = @cod_aluno and cod_Disciplina = @cod_disc
end
End
exec ins_matricula '100', '1'
select * from Matricula
05 - Crie uma stored procedure que através da passagem do código
do professor exiba quais disciplinas o mesmo ministra.
R: create procedure PROFESSOR_DISCIPLINA
@cod int
as begin
select CODPROFESSOR=p.cod_professor,
PROFESSOR=p.nome_professor,
CODIGODISCIPLINA=d.codigo_disciplina, DISCIPLINA=d.nome
from Professor
p, Disciplina d
where d.cod_prof=p.cod_professor
and p.cod_professor=@cod
if (@@ROWCOUNT=0)
begin
print
'NENHUM PROFESSOR MATRICULADO NESSA DISCIPLINA!'
END
End
exec PROFESSOR_DISCIPLINA 111
select * from professor
06 - Crie uma stored procedure que através da passagem do código
da disciplina exiba quais alunos estão matriculados na mesma e o seu respectivo
professor.
R: create procedure ALUNO_DISCIPLINA_PROFESSOR
@cod int
as begin
select coddisciplina=d.codigo_disciplina,
Disciplina=d.nome, codigoaluno=a.Cod_Aluno, Aluno=a.Nome_Aluno,
codprofessor=p.cod_professor, Professor=p.nome_professor
from Aluno a,
Matricula m, Disciplina d, Professor p
where a.Cod_Aluno=m.cod_Aluno
and m.cod_Disciplina=d.codigo_disciplina
and d.cod_prof=p.cod_professor
and m.cod_Disciplina=@cod
if (@@ROWCOUNT=0)
begin
print
'NENHUM ALUNO MATRICULADO NESSA DISCIPLINA!'
END
End
exec ALUNO_DISCIPLINA_PROFESSOR 2
07 - Crie uma stored procedure que através da passagem do código
do aluno exiba o boletim escolar do aluno conforme o layout abaixo:
R: create procedure BOLETIM
@cod int
as begin
select CODALUNO=replicate('0',5-len(a.Cod_Aluno))+CONVERT(varchar(5),a.Cod_Aluno),
ALUNO=a.Nome_Aluno,
DATA=convert(char,a.Data_Nascimento,103),
CODDISCIPLINA=replicate('0',5-len(d.codigo_disciplina))+CONVERT(varchar(5),d.codigo_disciplina),
DISCIPLINA=d.nome,PREREQUISITO=case(d.pre_requisito)
when 1 then 'Banco de Dados I' when 4 then 'Sistemas de Informação I'
else 'NÃO POSSUI' end, CREDITOS=d.creditos,
NOTA=m.nota,
SITUACAO=case(m.situacao) when 'OK' then
'APROVADO' when 'NOK' then 'REPROVADO' else 'NÃO DEFINIDO' end,
MEDIA_GERAL=(SELECT CONVERT(CHAR(5),CONVERT(NUMERIC(4,2),AVG(M2.NOTA)))
FROM MATRICULA M2 WHERE M.COD_DISCIPLINA=M2.COD_DISCIPLINA),
DESVIO_PADRAO=(m.nota-(select CONVERT(CHAR(5),CONVERT(NUMERIC(4,2),AVG(M2.NOTA)))
FROM MATRICULA M2 WHERE M.COD_DISCIPLINA=M2.COD_DISCIPLINA)),
MENOR_NOTA =(SELECT CONVERT(CHAR(5),CONVERT(NUMERIC(4,2),MIN(M2.NOTA)))
FROM MATRICULA M2 WHERE M.COD_DISCIPLINA=M2.COD_DISCIPLINA),
MAIOR_NOTA =(SELECT CONVERT(CHAR(5),CONVERT(NUMERIC(4,2),MAX(M2.NOTA)))
FROM MATRICULA M2 WHERE M.COD_DISCIPLINA=M2.COD_DISCIPLINA)
from Aluno a,
Matricula m, Disciplina d
where a.Cod_Aluno=m.cod_Aluno
and m.cod_Disciplina=d.codigo_disciplina
and m.cod_Aluno=@cod
group by a.Cod_Aluno,
a.Nome_Aluno, a.Data_Nascimento, d.codigo_disciplina, d.nome,
d.pre_requisito, d.creditos, m.nota, m.situacao,
m.cod_Disciplina
if (@@ROWCOUNT=0)
begin
print
'ALUNO NAO CADASTRADO!'
END
End
exec BOLETIM 00222
08 - Crie uma stored procedure que através da passagem da data
atual, execute o procedimento de backup full do banco de dados.
R: create procedure SP_BACKUP
@DATA DATETIME
AS BEGIN
DECLARE @DIA
SMALLINT, @MES SMALLINT, @ANO INT, @ARQ VARCHAR(50)
SELECT @DIA=DAY(@DATA),
@MES=MONTH(@DATA), @ANO=YEAR(@DATA)
SET @ARQ='C:\BKP\ESCOLA_'
+CONVERT(VARCHAR(2),@DIA+''+CONVERT(VARCHAR(2), @MES)+''+CONVERT(VARCHAR(4), @ANO)+'.BAK')
BACKUP DATABASE
ESCOLA
TO DISK ='C:\BKP\ESCOLA.BAK'
WITH STATS=1
END
DECLARE @D DATETIME
SELECT @D=GETDATE()
EXEC SP_BACKUP (SELECT GETDATE())
09 - Crie uma stored procedure que através da passagem do nome do
arquivo de backup, execute o procedimento de restaurar o backup do banco de
dados.
R: CREATE PROCEDURE SP_RESTORE
@NOME VARCHAR(50)
AS BEGIN
RESTORE DATABASE TESTE
FROM DISK='D:\BKP\TESTE.BAK'
WITH REPLACE, NORECOVERY, STATS=1
END
