EXERCÍCIOS Procedure¶
- Neste exercício vamos criar um banco de dados para armazenar os dados dos alunos de uma universidade. Além de desenhar o diagrama, criar o banco de dados e seus objetos, você deverá criar os scripts de população básica. Em seguida deverá criar as procedures que irão executar as operações de manipulação das notas e faltas. Abaixo uma sugestão de parte da solução:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 USE MASTER ALTER DATABASE Universidade SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO DROP DATABASE Universidade; GO USE master; CREATE DATABASE Universidade; GO USE Universidade; GO CREATE TABLE ALUNOS (MATRICULA INT NOT NULL IDENTITY CONSTRAINT PK_ALUNO PRIMARY KEY, NOME VARCHAR(50) NOT NULL); GO CREATE TABLE CURSOS (CURSO CHAR(3) NOT NULL CONSTRAINT PK_CURSO PRIMARY KEY, NOME VARCHAR(50) NOT NULL); GO CREATE TABLE PROFESSOR (PROFESSOR INT IDENTITY NOT NULL CONSTRAINT PK_PROFESSOR PRIMARY KEY, NOME VARCHAR(50) NOT NULL); GO CREATE TABLE MATERIAS (SIGLA CHAR(3) NOT NULL, NOME VARCHAR(50) NOT NULL, CARGAHORARIA INT NOT NULL, CURSO CHAR(3) NOT NULL, PROFESSOR INT CONSTRAINT PK_MATERIA PRIMARY KEY (SIGLA,CURSO,PROFESSOR) CONSTRAINT FK_CURSO FOREIGN KEY (CURSO) REFERENCES CURSOS(CURSO), CONSTRAINT FK_PROFESSOR FOREIGN KEY (PROFESSOR) REFERENCES PROFESSOR (PROFESSOR) ); GO INSERT ALUNOS (NOME) VALUES ('Pedro') GO INSERT CURSOS (CURSO, NOME) VALUES ('SIS','SISTEMAS'),('ENG','ENGENHARIA') GO INSERT PROFESSOR (NOME ) VALUES ('DORNEL'),('WALTER') GO INSERT MATERIAS (SIGLA, NOME, CARGAHORARIA, CURSO,PROFESSOR) VALUES ('BDA','BANCO DE DADOS',144,'SIS',1), ('PRG','PROGRAMAÇÃO',144,'SIS',2) GO INSERT MATERIAS (SIGLA, NOME, CARGAHORARIA, CURSO,PROFESSOR) VALUES ('BDA','BANCO DE DADOS',144,'ENG',1), ('PRG','PROGRAMAÇÃO',144,'ENG',2) GO CREATE TABLE MATRICULA (MATRICULA INT, CURSO CHAR(3), MATERIA CHAR(3), PROFESSOR INT, PERLETIVO INT, N1 FLOAT, N2 FLOAT, N3 FLOAT, N4 FLOAT, TOTALPONTOS FLOAT, MEDIA FLOAT, F1 INT, F2 INT, F3 INT, F4 INT, TOTALFALTAS INT, PERCFREQ FLOAT, RESULTADO VARCHAR(20) CONSTRAINT PK_MATRICULA PRIMARY KEY (MATRICULA,CURSO,MATERIA,PROFESSOR,PERLETIVO), CONSTRAINT FK_ALUNOS_MATRICULA FOREIGN KEY (MATRICULA) REFERENCES ALUNOS (MATRICULA), CONSTRAINT FK_CURSOS_MATRICULA FOREIGN KEY (CURSO) REFERENCES CURSOS (CURSO), --CONSTRAINT FK_MATERIAS FOREIGN KEY (MATERIA) REFERENCES MATERIAS (SIGLA), CONSTRAINT FK_PROFESSOR_MATRICULA FOREIGN KEY (PROFESSOR) REFERENCES PROFESSOR(PROFESSOR) ) CREATE PROCEDURE sp_MatriculaAluno ( @NOMEALUNO VARCHAR(50), @CURSOALUNO VARCHAR(50) ) AS BEGIN DECLARE @MATRICULAALUNO INT, @CODIGOCURSO VARCHAR(3) SET @MATRICULAALUNO = (SELECT MATRICULA FROM ALUNOS WHERE NOME = @NOMEALUNO) SET @CODIGOCURSO = (SELECT CURSO FROM CURSOS WHERE NOME = @CURSOALUNO) INSERT MATRICULA ( MATRICULA, CURSO, MATERIA, PROFESSOR, PERLETIVO ) SELECT @MATRICULAALUNO AS MATRICULA, CURSO, SIGLA,PROFESSOR, YEAR(GETDATE()) AS PERLETIVO FROM MATERIAS WHERE CURSO ='ENG' END --Calculo do percentual de Frequencia (144-NrFaltas*100)/144 EXEC sp_MatriculaAluno @NOMEALUNO = 'Guilherme', -- varchar(50) @CURSOALUNO = 'Sistemas' -- varchar(50)
Exemplo de INSERT com SELECT
1 2 3 4 5 6 7 8 9 10 INSERT MATRICULA ( MATRICULA, CURSO, MATERIA, PROFESSOR, PERLETIVO ) SELECT 1 AS MATRICULA, CURSO, SIGLA,PROFESSOR, YEAR(GETDATE()) FROM MATERIAS WHERE CURSO ='ENG'
Exemplo de PROCEDURE para inserir (atualizar) as notas
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 CREATE PROCEDURE [dbo].[sp_CadastraNotas] ( @MATRICULA INT, @CURSO CHAR(3), @MATERIA CHAR(3), @PERLETIVO CHAR(4), @NOTA FLOAT, @FALTA INT, @PARAMETRO INT ) AS BEGIN IF @PARAMETRO = 1 BEGIN UPDATE MATRICULA SET N1 = @NOTA, F1 = @FALTA, TOTALPONTOS = @NOTA, TOTALFALTAS = @FALTA, MEDIA = @NOTA WHERE MATRICULA = @MATRICULA AND CURSO = @CURSO AND MATERIA = @MATERIA AND PERLETIVO = @PERLETIVO; END; ELSE IF @PARAMETRO = 2 BEGIN UPDATE MATRICULA SET N2 = @NOTA, F2 = @FALTA, TOTALPONTOS = @NOTA + N1, TOTALFALTAS = @FALTA + F1, MEDIA = (@NOTA + N1) / 2 WHERE MATRICULA = @MATRICULA AND CURSO = @CURSO AND MATERIA = @MATERIA AND PERLETIVO = @PERLETIVO; END; ELSE IF @PARAMETRO = 3 BEGIN UPDATE MATRICULA SET N3 = @NOTA, F3 = @FALTA, TOTALPONTOS = @NOTA + N1 + N2, TOTALFALTAS = @FALTA + F1 + F2, MEDIA = (@NOTA + N1 + N2) / 3 WHERE MATRICULA = @MATRICULA AND CURSO = @CURSO AND MATERIA = @MATERIA AND PERLETIVO = @PERLETIVO; END; ELSE IF @PARAMETRO = 4 BEGIN DECLARE @RESULTADO VARCHAR(50), @FREQUENCIA FLOAT, @MEDIAFINAL FLOAT; UPDATE MATRICULA SET N4 = @NOTA, F4 = @FALTA, TOTALPONTOS = @NOTA + N1 + N2 + N3, TOTALFALTAS = @FALTA + F1 + F2 + F3, MEDIA = (@NOTA + N1 + N2 + N3) / 4, @MEDIAFINAL = (@NOTA + N1 + N2 + N3) / 4, MEDIAFINAL = @MEDIAFINAL, @FREQUENCIA = 100 - (((@FALTA + F1 + F2 + F3) * 144) / 100), PERCFREQ = @FREQUENCIA, RESULTADO = CASE WHEN @FREQUENCIA >= 75 AND @MEDIAFINAL >= 7 THEN 'APROVADO' WHEN @FREQUENCIA >= 75 AND @MEDIAFINAL >= 3 THEN 'EXAME' ELSE 'REPROVADO' END WHERE MATRICULA = @MATRICULA AND CURSO = @CURSO AND MATERIA = @MATERIA AND PERLETIVO = @PERLETIVO; END; ELSE IF @PARAMETRO = 5 BEGIN DECLARE @MEDIA FLOAT = ( SELECT MEDIA FROM MATRICULA WHERE MATRICULA = @MATRICULA AND CURSO = @CURSO AND MATERIA = @MATERIA AND PERLETIVO = @PERLETIVO AND RESULTADO = 'EXAME' ); UPDATE MATRICULA SET NOTAEXAME = @NOTA, RESULTADO = CASE WHEN (@NOTA + @MEDIA) >= 10 THEN 'APROVADO' ELSE 'REPROVADO' END WHERE MATRICULA = @MATRICULA AND CURSO = @CURSO AND MATERIA = @MATERIA AND PERLETIVO = @PERLETIVO AND RESULTADO = 'EXAME'; END; END; GO
Exemplo de execução da PROCEDURE para inserir (atualizar) as notas
1 2 3 4 5 6 7 8 9 10 11 12 --ALTER TABLE MATRICULA ADD MEDIAFINAL FLOAT --ALTER TABLE MATRICULA ADD NOTAEXAME FLOAT EXEC sp_CadastraNotas @MATRICULA = 4, -- int @CURSO = 'ENG', -- char(3) @MATERIA = 'BDA', -- char(3) @PERLETIVO = '2018', -- char(4) @NOTA = 7.0, -- float @FALTA = 2, @PARAMETRO = 4; -- int
Exemplo de INSERT - SELECT
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 CREATE TABLE pedidos ( idpedido INT, idproduto INT, valorpedido float ) CREATE TABLE itenspedido ( idpedido INT, iditem int, idproduto int ) CREATE TABLE itens ( iditem INT, nome varchar(50) ) INSERT itens ( iditem, nome ) VALUES ( 1, -- iditem - int 'AR CONDICIONADO' -- nome - varchar(50) ) CREATE TABLE subitens ( idsubitem INT, iditem INT, nomesubitem VARCHAR(50) ) INSERT subitens ( idsubitem, iditem, nomesubitem ) VALUES ( 2, -- idsubitem - int 1, -- iditem - int 'MOTOR' -- nomesubitem - varchar(50) ) SELECT * FROM itens SELECT * FROM subitens SELECT * FROM PEDIDOS INSERT pedidos ( idpedido, idproduto, valorpedido ) VALUES ( 1, -- idpedido - int 1, -- idproduto - int 1000.00 -- valorpedido - float ) DECLARE @produto INT SET @produto = (SELECT idproduto FROM pedidos WHERE idpedido =1) SELECT @produto AS 'AR COND' INSERT itenspedido ( idpedido, iditem, idproduto ) SELECT IDPEDIDO=1, idsubitem, iditem FROM subitens WHERE iditem = 1--@CURSO --VALUES --( 0, -- idpedido - int -- 0, -- iditem - int -- 0 -- idproduto - int -- )