Sistemas de Informação - Grupo Insight!: 2013

VISÕES (VIEW)

1) Crie uma view que mostre a pesquisa que retorna o resultado abaixo:

Resposta:

create view VW_ALUNO

SELECT CODALUNO=replicate('0',5-len(Cod_Aluno))+CONVERT(varchar(5),Cod_Aluno), NOME_ALUNO,

DATA=convert(char,Data_Nascimento,103)

FROM ALUNO

WHERE Data_Nascimento IS NOT NULL

select * from VW_ALUNO

select * from Aluno

2) 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:

Resposta:

CREATE view VW_ALUNO_DISCIPLINA

SELECT CODALUNO=replicate('0',5-len(a.Cod_Aluno))+CONVERT(varchar(5),a.Cod_Aluno),

NOME_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),

m.NOTA, SITUACAO=CASE(m.SITUACAO) when 'OK' THEN 'APROVADO' WHEN 'NOK' THEN 'REPROVADO'

ELSE 'NÃO DEFINIDO' end

FROM ALUNO a, Disciplina d, Matricula m

WHERE a.Cod_Aluno=m.cod_Aluno

and d.codigo_disciplina=m.cod_Disciplina

select * from VW_ALUNO_DISCIPLINA

select * from Disciplina

select * from Matricula

3) 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:

Resposta:
create view VW_ALUNO_NOMEDISCIPLINA

SELECT CODALUNO=replicate('0',5-len(a.Cod_Aluno))+CONVERT(varchar(5),a.Cod_Aluno),

NOME_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,

m.NOTA, SITUACAO=CASE(m.SITUACAO) when 'OK' THEN 'APROVADO' WHEN 'NOK' THEN 'REPROVADO'

ELSE 'NÃO DEFINIDO' end

FROM ALUNO a, Disciplina d, Matricula m

WHERE a.Cod_Aluno=m.cod_Aluno

and d.codigo_disciplina=m.cod_Disciplina

select * from VW_ALUNO_NOMEDISCIPLINA order by coddisciplina, codaluno

select * from Disciplina

select * from Matricula

4) 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:

Resposta:

create view VW_ALUNO_DISCIPLINA_PROFESSOR

SELECT CODALUNO=replicate('0',5-len(a.Cod_Aluno))+CONVERT(varchar(5),a.Cod_Aluno),

NOME_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,

m.NOTA, SITUACAO=CASE(m.SITUACAO) when 'OK' THEN 'APROVADO' WHEN 'NOK' THEN 'REPROVADO'

ELSE 'NÃO DEFINIDO' end, CODPROFESSOR=replicate('0',5-len(p.cod_professor))+CONVERT(varchar(5),p.cod_professor),

PROFESSOR=p.nome_professor

FROM ALUNO a, Disciplina d, Matricula m, Professor p

WHERE a.Cod_Aluno=m.cod_Aluno

and d.codigo_disciplina=m.cod_Disciplina

and p.cod_professor=d.cod_prof

select * from VW_ALUNO_DISCIPLINA_PROFESSOR order by coddisciplina, codaluno

select * from Disciplina

select * from Matricula

select * from professor

5) 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:

Resposta:

create view VW_BOLETIM

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,

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))

from Aluno a, Matricula m, Disciplina d

where a.Cod_Aluno=m.cod_Aluno

and m.cod_Disciplina=d.codigo_disciplina

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

select * from VW_BOLETIM order by coddisciplina, codaluno

6) 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:

Resposta:

create view VW_BOLETIM_COMPARATIVO

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,

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

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

select * from VW_BOLETIM_COMPARATIVO order by coddisciplina, CODALUNO

TRIGGERS (GATILHOS)

1) 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>

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')

2) 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.

create trigger AlteraProfessor on [Professor]

for update as

DECLARE @CODIGOi INT, @NOMEi VARCHAR(50),@CODIGOd INT, @NOMEd VARCHAR(50)

SELECT @CODIGOi=cod_professor, @NOMEi=nome_professor

FROM inserted

SELECT @CODIGOd=cod_professor, @NOMEd=nome_professor

FROM deleted

declare @qtDisciplina int

select @qtDisciplina=COUNT(*)

from Professor, Disciplina

where Professor.cod_professor =Disciplina.cod_prof

and Professor.cod_professor=@CODIGOi

print 'FOI ALTERADO OS DADOS DO PROFESSOR:'

print ''

print 'Codigo: '+convert(varchar(10),@codigod)

print 'Professor(a): '+@nomed

print 'QUE DÁ AULA EM '+convert(varchar(10),@qtDisciplina)+ ' DISCIPLINAS.'

update Professor set nome_professor='Mauricio' where cod_professor = 1

--- insert into Professor values (1, 'William')

SELECT * FROM Professor

select * from Disciplina

CORREÇÃO DO MAURÍCIO

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

3) 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.

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

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

4) 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>

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

5) 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’.

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

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

6) 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:

DATA	ACAO	TABELA	USUARIO	OBSERVAÇÃO
2009-05-13 20:21:23.013	INSERIU	ALUNO	sa	CODIGO: 4532 - NOME: MAURICIO - DATA_NASCIMENTO: 13/05/2009
2009-05-13 20:33:03.043	APAGOU	ALUNO	sa	CODIGO: 4532 - NOME: MAURICIO - DATA_NASCIMENTO: 13/05/2009
2009-05-13 20:34:46.420	INSERIU	ALUNO	sa	CODIGO: 4532 - NOME: MAURICIO - DATA_NASCIMENTO: 13/05/2009
2009-05-13 20:34:57.483	ALTEROU	ALUNO	sa	NOVO = CODIGO: 4532 - NOME: MAURICIO PERECIM - DATA_NASCIMENTO: 13/05/2009 \| VELHO = CODIGO: 4532 - NOME: MAURICIO PERECIM - DATA_NASCIMENTO: 13/05/2009

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

select * from Aluno

select * from tb_log

insert into Aluno values (4, 'William', '')

STORED PROCEDURE (PROCEDIMENTOS)

1) 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.

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

2) 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.

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

3) 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.

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

4) 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.

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

5) Crie uma stored procedure que através da passagem do código do professor exiba quais disciplinas o mesmo ministra.

Resposta:
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

6) 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.

Resposta:
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

7) Crie uma stored procedure que através da passagem do código do aluno exiba o boletim escolar do aluno conforme o layout abaixo:

Resposta:
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

8) Crie uma stored procedure que através da passagem da data atual, execute o procedimento de backup full do banco de dados.

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 DATETIM

SELECT @D=GETDATE()

EXEC SP_BACKUP (SELECT GETDATE())

9) 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.

CREATE PROCEDURE SP_RESTORE

@NOME VARCHAR(50)

AS BEGIN

RESTORE DATABASE TESTE

FROM DISK='D:\BKP\TESTE.BAK'

WITH REPLACE, NORECOVERY, STATS=1

END

Sistemas de Informação - Grupo Insight!

Pesquisar este blog

sexta-feira, 21 de junho de 2013

Trabalho BDII - 2BIM (Faculdade de Educação São Luís)

quarta-feira, 19 de junho de 2013

Exercícios 2º. BIMESTRE

Grupo

Matérias Publicadas