Certificacoes Cisco 2023 - Esquema do Banco de Dados e Instrucoes SQL

CERTIFICACOES CISCO 2023 Esquema do BD Controle de Certificacoes Modulo siglamoduloPK Nomemodulo CargaHoraria SiglacertFK Aluno nummatriculapk nomealuno sexoaluno endaluno fonealuno Certificacao SiglacertPK NomeCert Descrcert CargaHorariaTotal AlunoModulo nummatriculaPKFK siglamoduloPKFK trimestrecursadoPK notafinal freqfinal resultadofinal nummatricula referencia Aluno siglamodulo referencia Modulo Instrutor codinstrutorpk nomeinstr sexoinstr endinstr foneinstr Alunocertificacao nummatriculaPKFK siglacertPKFK dtconclusao nummatricula referencia Aluno siglacert referencia certificacao Instrutormodulo codinstrPKFK siglamoduloPKFK dtiniciolecionaPK dtterminoleciona codinstr referencia Instrutor siglamodulo referencia Modulo Instrutorcertificacao codinstrPKFK siglacertPKFK localcert dtconclusaocert codinstr referencia Instrutor siglacert referencia certificacao use tti204 SET GLOBAL lctimenamesptBR SET SQLSAFEUPDATES 1 SET FOREIGNKEYCHECKS 1 desabilita SET foreignkeychecks 0 SET SQLSAFEUPDATES 0 excluindo as tabelas que tenham FKs DROP TABLE IF EXISTS alunomodulo CASCADE DROP TABLE IF EXISTS alunocertificacao CASCADE DROP TABLE IF EXISTS instrutormodulo CASCADE DROP TABLE IF EXISTS instrutorcertificacao CASCADE DROP TABLE IF EXISTS certificacoesinstrutor CASCADE DROP TABLE IF EXISTS modulo CASCADE DROP TABLE IF EXISTS aluno CASCADE DROP TABLE IF EXISTS certificacao CASCADE DROP TABLE IF EXISTS instrutor CASCADE SHOW TABLES tabela aluno CREATE TABLE aluno nummatricula INTEGER autoincrement PRIMARY KEY NomeAluno VARCHAR 30 Sexoaluno CHAR1 NOT NULL endaluno VARCHAR 60 fonealuno NUMERIC11 ENGINE InnoDB gatilho para validar sexo de aluno DROP TRIGGER checkalunosexo DELIMITER CREATE TRIGGER checkalunosexo BEFORE INSERT ON aluno FOR EACH ROW BEGIN IF NEWSexoaluno NOT IN M F THEN SIGNAL SQLSTATE 45000 SET MESSAGETEXT Valor não permitido para sexo de aluno END IF END DESC aluno INSERT INTO aluno VALUES nullJoão RicardomRua Alfa10 1199887761 INSERT INTO aluno VALUES 0Maria SilvaFRua Beta101199887762 INSERT INTO aluno VALUES nullAri Pereira JrMRua Beta10 1199887763 SELECT FROM aluno UPDATE aluno SET sexoaluno M WHERE nummatricula 1 INSERT INTO aluno VALUES 0Rita TavaresFRua Delta20 1199887764 INSERT INTO aluno VALUES 0Jorge MatosMRua Beta10 1199887765 INSERT INTO aluno VALUES 0Talita FrancoFRua Zeta Beta30 1199887766 INSERT INTO aluno VALUES 0Walter SeveroMRua Beta10 1199887767 INSERT INTO aluno VALUES 0João GomesMRua Omega20 1199887726 INSERT INTO aluno VALUES 0Cristina SilvaFRua Gama10 1199887366 INSERT INTO aluno VALUES 0Alvaro SusukiMRua Gama10 1199887466 INSERT INTO aluno VALUES 0Petronio PeresFRua Zeta30 1199887566 INSERT INTO aluno VALUES 0Pedro Tadeu BokerMRua Phi30 1199887066 INSERT INTO aluno VALUES 0Carla Talarico FreitasFRua Kapa30 1199881766 INSERT INTO aluno VALUES 0Aldo MartinsMRua Phi30 1199887366 INSERT INTO aluno VALUES 0Sandra Rosa e CruzFRua Zeta30 1199887866 INSERT INTO aluno VALUES 0Ana Carolina SaFRua Alfa10 1199817766 INSERT INTO aluno VALUES 0Ricardo NevesMRua Gama10 1199827766 INSERT INTO aluno VALUES 0Roberto Carlos SchimidtMRua Zeta30 1199387766 INSERT INTO aluno VALUES 0Cristovao Buarque de HolandaMRua Omega20 1199487766 SELECT FROM aluno verificando o proximo numero da sequencia SELECT Autoincrement FROM informationschematables WHERE tablenamealuno tabela certificacao CREATE TABLE certificacao siglacert CHAR5 PRIMARY KEY nomecert VARCHAR60 NOT NULL descrcert VARCHAR200 NOT NULL cargahorariatotal SMALLINT ENGINE InnoDB INSERT INTO certificacao VALUES CCENT Cisco Certified Entry Networking Technician Redes 140 INSERT INTO certificacao VALUES CCNP Cisco Certified Network Professional Redes 280 INSERT INTO certificacao VALUES CCNA Cisco Certified Network Associate Redes 280 SELECT FROM certificacao tabela modulo CREATE TABLE modulo siglamodulo CHAR5 PRIMARY KEY Nomemodulo VARCHAR60 NOT NULL descrmodulo VARCHAR200 NOT NULL Cargahorariamodulo SMALLINT siglacert CHAR5 NOT NULL FOREIGN KEY siglacert REFERENCES certificacao siglacert ON DELETE CASCADE ON UPDATE CASCADE ENGINE InnoDB INSERT INTO modulo VALUES ICND1Interconexão de Dispositivos de Redes Cisco Básico Redes 160 CCNA INSERT INTO modulo VALUES ICND2Interconexão de Dispositivos de Redes Cisco AvançadoRedes160 CCNP INSERT INTO modulo VALUES CCNA1Noções Básicas de RedesRedes80 CCNA INSERT INTO modulo VALUES CCNA2Conceitos e Protocolos de roteamentoRedes80 CCNA INSERT INTO modulo VALUES CCNA3LAN Switching e WirelessRedes80 CCNP INSERT INTO modulo VALUES CCNA4Acesso à WANRedes80 CCNP INSERT INTO modulo VALUES CCNP1Capacitação em RoteamentoRouting AvançadoRedes 80 CCENT INSERT INTO modulo VALUES CCNP2 Capacitação em Acesso RemotoRedes80 CCENT INSERT INTO modulo VALUES CCNP3 Capacitação em Swithces Multi LayerRedes80 CCENT INSERT INTO modulo VALUES CCNP4 Capacitação em Resolução de Problemas de RedesRedes80 CCNP tabela aluno cursa modulo CREATE TABLE alunomodulo nummatricula integer NOT NULL siglamodulo CHAR5 NOT NULL trimestrecursado CHAR6 NOT NULL notafinal NUMERIC52 freqfinal NUMERIC52 resultadofinal CHAR15 CONSTRAINT cursamodulopkey PRIMARY KEY nummatricula siglamodulo trimestrecursado FOREIGN KEY nummatricula REFERENCES aluno nummatricula ON DELETE CASCADE ON UPDATE CASCADE FOREIGN KEY siglamodulo REFERENCES modulo siglamodulo ON DELETE CASCADE ON UPDATE CASCADE INSERT INTO alunomodulo VALUES 1ICND1202019054ReproFalta ok INSERT INTO alunomodulo VALUES 2ICND2202024095ReproNota INSERT INTO alunomodulo VALUES 3ICND2202117583Aprovado INSERT INTO alunomodulo VALUES 4CCNP1202017092 Aprovado INSERT INTO alunomodulo VALUES 5ICND2202218069ReproFalta INSERT INTO alunomodulo VALUES 6ICND1202025067ReproFalta INSERT INTO alunomodulo VALUES 7CCNA2202126079 Aprovado INSERT INTO alunomodulo VALUES 8ICND2202217589Aprovado INSERT INTO alunomodulo VALUES 9ICND1202028092Aprovado INSERT INTO alunomodulo VALUES 10CCNP2202117083 Aprovado INSERT INTO alunomodulo VALUES 11ICND2202228054Cursando INSERT INTO alunomodulo VALUES 12ICND1202035067ReproFalta INSERT INTO alunomodulo VALUES 12ICND1202016092Aprovado INSERT INTO alunomodulo VALUES 13CCNA3202226083 Cursando INSERT INTO alunomodulo VALUES 14ICND1202129067ReproFalta INSERT INTO alunomodulo VALUES 15CCNA3202344083ReproNota INSERT INTO alunomodulo VALUES 15ICND1202029092Aprovado INSERT INTO alunomodulo VALUES 16ICND2202019589Aprovado INSERT INTO alunomodulo VALUES 16CCNA2202144095ReproNota INSERT INTO alunomodulo VALUES 17CCNA1202017595Aprovado INSERT INTO alunomodulo VALUES 18CCNA22021170100 Aprovado INSERT INTO alunomodulo VALUES 19CCNA3202019092 Aprovado INSERT INTO alunomodulo VALUES 1CCNA42023110079 Aprovado INSERT INTO alunomodulo VALUES 4CCNA1202027089Aprovado INSERT INTO alunomodulo VALUES 4CCNA3202049083 Aprovado INSERT INTO alunomodulo VALUES 5CCNA1202129079 Aprovado INSERT INTO alunomodulo VALUES 5CCNA2202116589Aprovado INSERT INTO alunomodulo VALUES 6ICND12023260100Aprovado INSERT INTO alunomodulo VALUES 6CCNA3202023079 ReproNota INSERT INTO alunomodulo VALUES 7ICND2202214579ReproNota INSERT INTO alunomodulo VALUES 7CCNA1202129095 Aprovado INSERT INTO alunomodulo VALUES 11ICND2202132595ReproNota INSERT INTO alunomodulo VALUES 12ICND2202016064ReproFalta INSERT INTO alunomodulo VALUES 12ICND22020260100Aprovado SELECT FROM alunomodulo tabela aluno cursa certificacao precisou turar os nomes das constraints CREATE TABLE alunocertificacao nummatricula integer NOT NULL siglacert CHAR5 NOT NULL dtmatricula DATE dtconclusao DATE CONSTRAINT cursacertpkey PRIMARY KEY nummatricula siglacert FOREIGN KEY siglacert REFERENCES certificacao siglacert ON DELETE CASCADE ON UPDATE CASCADE FOREIGN KEY nummatricula REFERENCES aluno nummatricula ON DELETE CASCADE ON UPDATE CASCADE INSERT INTO alunocertificacao VALUES 1 CCNA currentdate INTERVAL 100 DAY currentdate INTERVAL 70 DAY INSERT INTO alunocertificacao VALUES 2 CCNA currentdate INTERVAL 90 DAY currentdate INTERVAL 50 DAY INSERT INTO alunocertificacao VALUES 3 CCNA currentdate INTERVAL 80 DAY currentdate INTERVAL 40 DAY INSERT INTO alunocertificacao VALUES 4 CCNP currentdate INTERVAL 30 DAY currentdate INTERVAL 10 DAY INSERT INTO alunocertificacao VALUES 5 CCENT currentdate INTERVAL 20 DAY currentdate INTERVAL 5 DAY INSERT INTO alunocertificacao VALUES 1 CCNP currentdate INTERVAL 10 DAY null INSERT INTO alunocertificacao VALUES 2 CCNP currentdate INTERVAL 7 DAY null INSERT INTO alunocertificacao VALUES 3 CCNP currentdate INTERVAL 3 DAY null INSERT INTO alunocertificacao VALUES 4 CCENT currentdate INTERVAL 1 DAY null tabela instrutor CREATE TABLE instrutor codinstrutor NUMERIC5 PRIMARY KEY nomeinstrutor VARCHAR 30 Sexoinstrutor CHAR1 endinstrutor VARCHAR 60 foneinstrutor NUMERIC11 INSERT INTO instrutor VALUES 10Julio RicardoMRua Alfa40 1199427760 INSERT INTO instrutor VALUES 11Ana SilvaFRua Beta601199787761 INSERT INTO instrutor VALUES 12Pedro Pereira JrMRua Beta50 11999377662 INSERT INTO instrutor VALUES 13Alessandra TavaresFRua Delta23 1199757763 INSERT INTO instrutor VALUES 14Ryck MatosMRua Beta20 1199127764 INSERT INTO instrutor VALUES 15Tania FrancoFRua Zeta Beta40 1198807765 SELECT FROM instrutor tabela instrutor leciona modulo DROP TABLE instrutormodulo CASCADE CREATE TABLE instrutormodulo codinstrutor NUMERIC5 NOT NULL siglamodulo CHAR5 NOT NULL dtinicioleciona DATE NOT NULL dtterminoleciona DATE PRIMARY KEY codinstrutor siglamodulo dtinicioleciona FOREIGN KEY siglamodulo REFERENCES modulo siglamodulo ON DELETE CASCADE ON UPDATE CASCADE FOREIGN KEY codinstrutor REFERENCES instrutor codinstrutor ON DELETE CASCADE ON UPDATE CASCADE SELECT currentdate INTERVAL 1 MONTH INSERT INTO instrutormodulo VALUES 10 ICND1 STRTODATE10032023 dmY null INSERT INTO instrutormodulo VALUES 10 ICND2 currentdate INTERVAL 68 DAY currentdate INTERVAL 38 DAY INSERT INTO instrutormodulo VALUES 11 CCNA1 currentdate INTERVAL 100 DAY currentdate INTERVAL 70 DAY INSERT INTO instrutormodulo VALUES 11 CCNA2 currentdate INTERVAL 78 DAY currentdate INTERVAL 48 DAY INSERT INTO instrutormodulo VALUES 12 CCNA3 currentdate INTERVAL 100 DAY currentdate INTERVAL 70 DAY INSERT INTO instrutormodulo VALUES 12 CCNA4 currentdate INTERVAL 58 DAY currentdate INTERVAL 38 DAY INSERT INTO instrutormodulo VALUES 13 CCNP1 currentdate INTERVAL 100 DAY currentdate INTERVAL 70 DAY INSERT INTO instrutormodulo VALUES 13 CCNP2 currentdate INTERVAL 48 DAY currentdate INTERVAL 28 DAY INSERT INTO instrutormodulo VALUES 14 CCNP3 currentdate INTERVAL 100 DAY currentdate INTERVAL 70 DAY INSERT INTO instrutormodulo VALUES 14 CCNP4 currentdate INTERVAL 38 DAY currentdate INTERVAL 18 DAY INSERT INTO instrutormodulo VALUES 15 ICND1 currentdate INTERVAL 10 DAY null INSERT INTO instrutormodulo VALUES 11 ICND2 currentdate INTERVAL 8 DAY null DELETE FROM instrutormodulo TRUNCATE TABLE instrutormodulo SELECT FROM instrutormodulo show tables describe instrutor tabela instrutor habilitado na certificacao CREATE TABLE instrutorcertificacao codinstrutor NUMERIC5 NOT NULL siglacert CHAR5 NOT NULL dtconclusao DATE NOT NULL localcertificacao VARCHAR50 CONSTRAINT instrcertpkey PRIMARY KEY codinstrutor siglacert CONSTRAINT instrcertfkey2 FOREIGN KEY siglacert REFERENCES certificacao siglacert ON DELETE CASCADE ON UPDATE CASCADE CONSTRAINT instrcertfkey20 FOREIGN KEY codinstrutor REFERENCES instrutor codinstrutor ON DELETE CASCADE ON UPDATE CASCADE ENGINE INNODB INSERT INTO instrutorcertificacao VALUES 10 CCNA currentdate INTERVAL 1000 DAY Academia Cisco Rio de Janeiro INSERT INTO instrutorcertificacao VALUES 11 CCNA currentdate INTERVAL 500 DAY Academia Cisco Curitiba INSERT INTO instrutorcertificacao VALUES 12 CCNP currentdate INTERVAL 500 DAY Academia Cisco Rio de Janeiro INSERT INTO instrutorcertificacao VALUES 13 CCNP currentdate INTERVAL 400 DAY Academia Cisco São Paulo INSERT INTO instrutorcertificacao VALUES 14 CCENT currentdate INTERVAL 400 DAY Academia Cisco Campinas INSERT INTO instrutorcertificacao VALUES 15 CCENT currentdate INTERVAL 300 DAY Academia Cisco Santos SELECT FROM instrutorcertificacao Alteração na estrutura das tabelas adicionando e excluindo novas colunas ALTER TABLE modulo ADD qtdeaulas TINYINT NOT NULL ADD duracaoaula TINYINT NOT NULL desc modulo ALTER TABLE modulo DROP column qtdeaulas DROP COLUMN duracaoaula mudando o tipo de dado DESCRIBE instrutor ALTER TABLE instrutor MODIFY nomeinstrutor CHAR30 adicionando nova coluna ALTER TABLE alunocertificacao ADD situacaomatricula CHAR15 NOT NULL DEFAULT CURSANDO desc alunocertificacao aumentando o tamanho da coluna ALTER TABLE aluno MODIFY endaluno VARCHAR70 mudando o tipo de dado ALTER TABLE certificacao MODIFY cargahorariatotal integer colocando valor default ALTER TABLE alunocertificacao MODIFY dtmatricula TIMESTAMP DEFAULT currenttimestamp ALTER TABLE instrutorcertificacao MODIFY dtconclusao TIMESTAMP DEFAULT currenttimestamp renomeando a coluna clausula CHANGE DESC alunocertificacao ALTER TABLE alunocertificacao CHANGE dtmatricula dthoramatricula TIMESTAMP ALTER TABLE alunocertificacao CHANGE dthoramatricula dtmatricula TIMESTAMP renomeando uma tabela SHOW TABLES ALTER TABLE instrutorcertificacao RENAME instrutorcertificacaonovo ALTER TABLE instrutorcertificacaonovo RENAME instrutorcertificacao check em situação da matricula DROP TRIGGER checkalunomatr DELIMITER CREATE TRIGGER checkalunomatr BEFORE INSERT ON alunocertificacao FOR EACH ROW BEGIN IF NEWsituacaomatricula NOT IN CURSANDO CONCLUIDO CANCELADA TRANCADA THEN SIGNAL SQLSTATE 45000 SET MESSAGETEXT Valor não permitido para situação do aluno na certificação END IF END SELECT FROM aluno SELECT FROM certificacao desc alunocertificacao SELECT FROM alunocertificacao WHERE dtconclusao IS NOT NULL atualizando SET SQLSAFEUPDATES 0 UPDATE alunocertificacao SET situacaomatricula CONCLUIDO WHERE dtconclusao IS NOT NULL populacao aluno matriculado na certificacao testando o gatilho do check da situacao deprecated ALTER TABLE alunocertificacao CHANGE situacaomatricula situacaomatricula CHAR15 BINARY NOT NULL ALTER TABLE alunocertificacao MODIFY situacaomatricula CHAR15 COLLATE utf8mb4bin INSERT INTO alunocertificacao VALUES 5 CCENT currentdate INTERVAL 10 DAY null teste INSERT INTO alunocertificacao VALUES 5 CCNP currentdate INTERVAL 10 DAY null cursando INSERT INTO alunocertificacao VALUES 5 CCNA currentdate INTERVAL 10 DAY null CURSANDO SELECT FROM alunocertificacao DESC alunocertificacao testes de integridade referencial show engines select FOREIGNKEYCHECKS falta completar testes alterando uma PK excluindo uma PK e verificando as Fks correspondentes SELECT FROM modulo SELECT FROM certificacao UPDATE certificacao SET siglacert XYZW WHERE siglacert CCENT DELETE FROM certificacao WHERE siglacert XYZW instrutor modulo não tem CASCADE SELECT FROM instrutormodulo UPDATE modulo SET siglamodulo 1234 WHERE siglamodulo CCNA1 DELETE modulo WHERE siglamodulo CCNA1