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

TRABALHO DE BANCO DE DADOS 2º. BIMESTRE.

GRUPO:

Andre Toscano

Daniel de Freitas
Limberg Fuentes
Guilherme Martins
Marcos Neves
Vitor Hugo Vendite
Willian Mialich

Links para download:

Apresentação em Slides - SGBDD

Conteúdo em PDF - SGBDD

Faculdade São Luis

Sistemas de Informação

Jaboticabal

2013

Exercícios 2º. BIMESTRE

VISÕES (VIEW)

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

 Resposta:

create view VW_ALUNO

AS

      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

AS

      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:

R

        Resposta:
create view VW_ALUNO_NOMEDISCIPLINA

AS

      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

AS

      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

AS

      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

AS

      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

     

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

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

           

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