Baixe o app para aproveitar ainda mais
Prévia do material em texto
Unidade 2: SQL SQL Manipulação e controle de bancos de dados relacionais Acesso ao banco de dados: – Ambiente interativo de consultas – Embutida em linguagens hospedeiras Recursos: – Alto poder de consulta – Gerenciamento de índices – Construção de visões – Execução de instruções em blocos 2 Grupos de comandos SQL Linguagem de Definição de Dados (DDL): comandos para definir, alterar e remover tabelas e índices; Linguagem de Manipulação de Dados (DML): comandos para inserir, remover, atualizar e consultar os dados armazenados nas tabelas; Linguagem de Controle de Dados (DCL): comandos para se trabalhar em ambiente multi-usuário, permitindo estabelecer níveis de segurança e manipular transações. 3 Tabelas Exemplo CodC NomeC DuracaoC MensC C1 Análise Sist 4 400 C2 Eng Mecatrônica 5 600 C3 Ciência Comp 4 450 C4 Eng Elétrica 4 600 C5 Turismo 3 350 Curso 4 Tabelas Exemplo Disciplina CodD NomeD CargaD AreaD PreReqD D1 TLP1 2 Computação D2 D2 Cálculo1 4 Matemática null D3 Inglês 2 Humanas null D4 Ed Física 3 Saúde null D5 G Analítica 5 Matemática D2 D6 Projeto Final 6 null D1 5 Tabelas Exemplo CodP NomeP CidadeP TituloP P1 Joaquim Rib Preto Mestre P2 Paulo Batatais Espec P3 André Rib Preto Doutor P4 Gil S Carlos Doutor P5 Juliana S Carlos Pós Doc Professor 6 Tabelas Exemplo CodC CodD CodP Sala C1 D6 P1 305 C2 D2 P2 305 C3 D2 P2 305 C4 D1 P5 201 C4 D3 P3 204 C5 D4 P3 204 C5 D4 P4 207 Grade 7 Linguagem de Definição de Dados (DDL) Tipos de dados mais usuais: – - CHAR(n) ou CHARACTER(n): cadeia de caracteres de tamanho fixo, com o tamanho n definido pelo usuário; – - VARCHAR(n): cadeia de caracteres de tamanho variável, com o tamanho máximo n definido pelo usuário; – - INT ou INTEGER: número inteiro, geralmente de -32768 a 32767; – - SMALLINT: número inteiro pequeno, geralmente de 0 a 65535; – - NUMERIC(t,d): número de ponto decimal fixo, que consiste de t dígitos totais e d dígitos à direita do ponto decimal, com t e d definidos pelo usuário; – - REAL: número de ponto flutuante, geralmente de 1.17E-38 a 3.4E+38; – - DOUBLE PRECISION: número de ponto flutuante de dupla precisão, geralmente de 2.2E-308 a 1.7E+308; – - FLOAT(n): número de ponto flutuante com a precisão n definida pelo usuário; – - DATE: formato de data, contendo dia, mês e ano; – - TIME: formato de horário, contendo horas, minutos e segundos. 8 Criação de uma tabela Sintaxe: CREATE TABLE nome_tabela (nome_coluna tipo [NOT NULL] [SET DEFAULT valor], .... , PRIMARY KEY (nome_colunas), [UNIQUE (nome_coluna)], [FOREIGN KEY (nome_coluna) REFERENCES nome_tabela (nome_coluna) ON DELETE CASCADE/SET NULL/SET DEFAULT ON UPDATE CASCADE/SET NULL/SET DEFAULT ] [CHECK condição]) 9 Criação de uma tabela Exemplo 1 : Criação da tabela Disciplina CREATE TABLE Disciplina ( CodD CHAR(5), NomeD VARCHAR(20) NOT NULL, CargaD INTEGER NOT NULL, AreaD VARCHAR(20), PreReqD CHAR(5), UNIQUE (NomeD), PRIMARY KEY (CodD) CHECK CargaD > 0 AND CargaD <= 6) 10 Criação de uma tabela – Exemplo 2: Criação da tabela Grade CREATE TABLE Grade ( CodC CHAR(5), CodD CHAR(5), CodP CHAR(5), Sala INTEGER, PRIMARY KEY (CodC, CodD, CodP), FOREIGN KEY (CodC) REFERENCES Curso (CodC) ON DELETE CASCADE, FOREIGN KEY (CodD) REFERENCES Disciplina (CodD) ON DELETE CASCADE, FOREIGN KEY (CodP) REFERENCES Professor (CodP) ON DELETE SET NULL) OOOPS !!! 11 Alteração de uma tabela Sintaxe: ALTER TABLE nome_tabela [ADD COLUMN nome_coluna tipo [NOT NULL] ] [DROP COLUMN nome_coluna [CASCADE/RESTRICT] ] Exemplo : Adição da coluna MensC à tabela Curso ALTER TABLE Curso ADD COLUMN MensC NUMERIC(6,2); 12 Remoção de uma tabela Sintaxe: DROP TABLE nome_tabela CASCADE/RESTRICT ; 13 Truncando uma tabela Sintaxe: TRUNCATE TABLE nome_tabela; 14 Criação e remoção de Índices Sintaxe para criação: CREATE INDEX nome_índice ON nome_tabela ( nome_coluna [ASC|DESC],...); Exemplo : Criação de índice sobre a coluna CidadeP da tabela Professor CREATE INDEX Xcidadeprof ON Professor (CidadeP ASC); Sintaxe para remoção: DROP INDEX nome_índice 15 Linguagem de Manipulação de Dados (DML) Inclusão de tuplas Alteração de tuplas Remoção de tuplas Consultas ao banco 16 Inclusão de tuplas em uma tabela Sintaxe: INSERT INTO nome_tabela ( nome_coluna, .....) VALUES (valor, ...) Exemplo: Inserir a linha 1 da tabela Professor INSERT INTO Professor (CodP, NomeP, CidadeP, TituloP) VALUES (‘P1', 'Joaquim', 'Rib Preto', 'Mestre') 17 Atualização dos dados de uma tabela Sintaxe: UPDATE nome_tabela SET nome_coluna = valor, ..... WHERE (condição de localização) Exemplo : Alterar o valor da mensalidade do curso de Ciência da Computação para 650,00 UPDATE Curso SET MensC = 650 WHERE (NomeC = ‘Ciência Comp’) 18 Remoção de dados de uma tabela Sintaxe: DELETE FROM nome_tabela WHERE (condição de localização) Exemplo 1 : Remover da tabela Professor todos os professores que têm título de Doutor DELETE FROM Professor WHERE (TituloP = ‘Doutor’) 19 Exercícios Considere o esquema da Empresa: Func (cod_func, nome, dta_nasc, salário, nro_depto, cod_superv) Depto (nro_depto, nome, cod_ger) Projeto (cod_proj, nome, duração, nro_depto) Participa (cod_proj, cod_func, horas_trab) 20 Exercícios 1. Criar as tabelas Func1, Projeto1 e Participa1 com o mesmo layout das tabelas Func, Projeto e Participa. 2. Adicionar a coluna dta_inicio à tabela Projeto1. 3. Criar índice para o atributo dta_nasc da tabela Func1. 4. Inserir valores na tabela Projeto1 e Func1. 5. Zerar a duração de todos os projetos do depto 5. 6. Remover todas as participações em projetos do funcionário 20. 7. Remover as tabelas Projeto1, Func1 e Partipa1. 21 Conceito de Transação Uma TRANSAÇÃO é uma unidade de execução programada que acessa e provavelmente atualiza vários itens de dados. Uma transação deve acessar uma base de dados consistente. Durante a execução da transação é possível que a base de dados passe por um estado de inconsistência Quando a transação é confirmada (COMMIT), a base de dados deverá estar consistente. O gerenciamento de transações lida com 2 situações principais: – Falhas de vários tipos: hardware, energia, travamento de aplicações, etc. – Execução concorrente de múltiplas transações 22 Propriedades ACID Atomicidade. Ou todas ou nenhuma das operações da transação serão devidamente refletidas na base de dados. Consistência. Após a execução de uma transação, a base de dados deverá retornar a um estado consistente dos dados. Isolamento. Apesar de múltiplas transações poderem ser executadas concorrentemente, cada uma delas deverá estar isolada dos efeitos da execução das outras. Resultados intermediários de uma transação deverão ficar ocultos para as outras. Durabilidade. Depois que uma transação termina com sucesso, as alterações que ela realizou sobre o bd persistirão mesmo que haja falhas do sistema. Para preservar a integridade dos dados, o SGBD deve assegurar: 23 Controle de Transações Todas as transações realizadas com o banco deverão ser confirmadas pelo usuário para que sejam realmente efetivadas, ou então descartadas, caso o usuário deseje. Para confirmar uma transação - ou um bloco de transações - utiliza-se o comando COMMIT. Para descartar uma transação - ou um bloco de transações - utiliza-se o comando ROLLBACK. O bloco de transações é definido a partir da última ocorrência de COMMIT, ou a partir do início da seção. 24 Comando SELECT Sintaxe: SELECT [DISTINCT] nome_coluna,.... FROM nome_tabela, .... [WHERE (condições) [ sub-select] ] [GROUP BY nome_coluna, ....] [HAVING (condições)] [{INTERSECT | MINUS | UNION} comando_select] [ORDER BY nome_coluna {ASC | DESC}, ....] 25 SELECT - Operadores Aritméticos + - * / Lógicos AND OR NOT Relacionais = <> < <= > >= LIKE BETWEEN Conjuntu- rais IN IS NULL ANY ALL EXISTS Conjuntos UNION INTERSECT MINUS 26 SELECT – Algumas Funções Grupo Função Descrição Agregação AVG (col) média dos valores da coluna SUM (col) soma de valores da coluna MAX (col) valor máximo da coluna MIN (col) valor mínimo da coluna COUNT total de tuplas Caracter UPPER (col) converte caracteres minúsculos em maiúsculos LOWER (col) converte caracteres maiúsculos em minúsculos SUBSTR (col, pos, n) substring da coluna, iniciando em pos, com n caracteres Números ROUND (col/const, n) arredondamento em n da coluna (ou da constante TRUNC (col/const, n) truncamento em n da coluna (ou da constante) ABS (col/const) valor absoluto da coluna ou da constante Data/Hora MONTH (data) mês da data YEAR (data) ano da data MINUTE (hora) minuto da hora Conversões TO_CHAR (num/data) número (ou data) para caracter TO_NUMBER (char) caracter para número TO_DATE (char) caracter para data SELECT – Consulta Simples Nomes das disciplinas: SELECT NomeD FROM Disciplina NomeD TLP1 Cálculo1 Inglês Ed Física G Analítica Projeto Final 28 SELECT – Consultas Simples Salas onde as aulas serão ministradas: SELECT Sala FROM Grade Sala 305 305 305 201 204 204 207 29 SELECT – Consultas Simples Salas onde as aulas serão ministradas, sem repetição: SELECT DISTINCT Sala FROM Grade Sala 305 201 204 207 30 SELECT – Consultas Simples Nome e código dos professores de Ribeirão Preto: SELECT NomeP, CodP FROM Professor WHERE CidadeP = 'Rib Preto' NomeP CodP Joaquim P1 André P3 31 SELECT – Consultas Simples Todas as colunas da grade do curso C4: SELECT * FROM Grade WHERE CodC = 'C4' CodC CodD CodP Sala C4 D1 P5 201 C4 D3 P3 204 32 SELECT – Uso de Operadores Nome e duração em meses de cada curso: SELECT NomeC, (DuracaoC * 12) FROM Curso NomeC DuracaoC * 12 Análise Sist 48 Eng Mecatrônica 60 Ciência Comp 48 Eng Elétrica 48 Turismo 36 33 SELECT – Uso de Operadores Nomes dos cursos cuja mensalidade é inferior a 500 reais: SELECT NomeC FROM Curso WHERE MensC < 500 NomeC Análise Sist Ciência Comp Turismo 34 SELECT – Uso de Operadores Código e carga horária das disciplinas da área de Matemática, com carga horária maior ou igual a 5: SELECT CodD, CargaD FROM Disciplina WHERE AreaD = 'Matemática' AND CargaD >= 5 CodD CargaD D5 5 35 SELECT – Uso de Operadores O código e o nome de todos os cursos de engenharia: SELECT CodC, NomeC FROM Curso WHERE NomeC LIKE 'Eng%' CodC NomeC C2 Eng Mecatrônica C4 Eng Elétrica 36 SELECT – Uso de Operadores As salas do segundo andar (nro fica na casa dos 200) onde serão ministradas aulas: SELECT DISTINCT Sala FROM Grade WHERE TO_CHAR(Sala) LIKE '2__' Sala 201 204 207 37 SELECT – Uso de Operadores Código dos cursos cuja mensalidade está entre 400 e 550 reais: SELECT CodC FROM Curso WHERE MensC BETWEEN 400 AND 550 CodC C1 C3 38 SELECT – Uso de Operadores Nome das disciplinas que são da área de Computação ou de Humanas ou de Saúde: SELECT NomeD FROM Disciplina WHERE AreaD IN ('Computação', 'Humanas', 'Saúde') NomeD TLP1 Inglês Ed Física 39 SELECT – Uso de Operadores Nome das disciplinas que não pertencem a nenhuma área específica: SELECT NomeD FROM Disciplina WHERE AreaD IS NULL NomeD Projeto Final 40 SELECT – Uso de Alias Nome e duração em meses de cada curso: SELECT NomeC AS Curso, (DuracaoC * 12) AS “Duração em Meses” FROM Curso CURSO Duração em Meses Análise Sist 48 Eng Mecatrônica 60 Ciência Comp 48 Eng Elétrica 48 Turismo 36 41 SELECT – Concatenação de campos Códigos dos cursos com seu nome e mensalidade concatenados: SELECT CodC, NomeC || ' ' || MensC || ' reais' AS Info_Curso FROM Curso CodC INFO_CURSO C1 Análise Sist 400 reais C2 Eng Mecatrônica 600 reais C3 Ciência Comp 450 reais C4 Eng Elétrica 600 reais C5 Turismo 350 reais 42 SELECT – Ordenação do resultado Nomes dos cursos ordenados de forma ascendente: SELECT NomeC FROM Curso ORDER BY NomeC ASC NomeC Análise Sist Ciência Comp Eng Elétrica Eng Mecatrônica Turismo 43 Exercícios 44 Considere o esquema da Empresa: Func (cod_func, nome, dta_nasc, salário, nro_depto, cod_superv) Depto (nro_depto, nome, cod_ger) Projeto (cod_proj, nome, duração, nro_depto) Participa (cod_proj, cod_func, horas_trab) Exercícios 1. Nome e salário dos funcionários do departamento 5. 2. Nome e salário dos funcionários do departamento 5, com salário na faixa entre 3000 e 4000. 3. Todas as informações sobre projetos do departamento 5. 4. Salário líquido (85% do salário bruto) dos funcionários supervisionados pelo funcionário 23. 5. Código dos funcionários que são supervisores. 6. Código dos funcionários que não têm supervisores. 7. Nome completo, em ordem alfabética, dos funcionários que têm “MAR” no nome. 8. Nome, duração e depto dos projetos “FATURAMENTO”, “INTRANET” e “NOVOS PRODUTOS”. 45 SELECT – Funções Valor mínimo, máximo e a média das mensalidades dos cursos cuja duração é de 4 anos: SELECT MIN(MensC), MAX(MensC), AVG(MensC) FROM Curso WHERE DuracaoC = 4 MIN(MensC) MAX(MensC) AVG(MensC) 400 600 483 46 SELECT - Agrupamentos Carga horária total de cada área: SELECT AreaD, SUM(CargaD) FROM Disciplina GROUP BY AreaD AreaD SUM(CargaD) Computação 2 Matemática 9 Humanas 2 Saúde 3 Null 6 47 SELECT - Agrupamentos Salas que são utilizadas para mais de uma aula e as respectivas quantidades de aulas: SELECT Sala, COUNT(*) FROM Grade GROUP BY Sala HAVING COUNT(*) > 1 Sala COUNT(*) 305 3 204 2 48 Exercícios 49 Considere o esquema da Empresa: Func (cod_func, nome, dta_nasc, salário, nro_depto, cod_superv) Depto (nro_depto, nome, cod_ger) Projeto (cod_proj, nome, duração, nro_depto) Participa (cod_proj, cod_func, horas_trab) Exercícios 1. O maior e o menor salário da empresa. 2. O maior e o menor salário de cada depto. 3. O maior e o menor salário dos deptos cuja média salarial é maior ou igual a 2000. 4. Total de horas trabalhadas de cada projeto. 50 SELECT - Junção Todas as informações sobre a grade e as disciplinas do curso C4: SELECT * FROM Grade, Disciplina WHERE CodC = 'C4' AND Disciplina.CodD = Grade.CodD CodC CodD CodP Sala CodD NomeD CargaD AreaD C4 D1 P5 201 D1 TLP1 2 Computação C4 D3 P3 204 D3 Inglês 2 Humanas 51 SELECT – Junção Natural Todas as informações sobre a grade e as disciplinas do curso C4, sem repetição de colunas: SELECT * FROM Grade NATURAL JOIN Disciplina ON Grade.CodD = Disciplina.CodD WHERE CodC = 'C4' CodCCodD CodP Sala NomeD CargaD AreaD C4 D1 P5 201 TLP1 2 Computação C4 D3 P3 204 Inglês 2 Humanas 52 SELECT - Junção Código, nome, carga horária, sala e curso das disciplinas que estão na grade: SELECT Grade.CodD, NomeD, CargaD, Sala, CodC FROM Disciplina, Grade WHERE Disciplina.CodD = Grade.CodD CodD NomeD CargaD Sala CodC D1 TLP1 2 201 C4 D2 Cálculo1 4 305 C2 D2 Cálculo1 4 305 C3 D3 Inglês 2 204 C4 D4 Ed Física 3 204 C5 D4 Ed Física 3 207 C5 D6 Projeto Final 6 305 C1 53 SELECT - Junção Nome das disciplinas e dos respectivos professores do curso C4: SELECT NomeD, NomeP FROM Disciplina, Grade, Professor WHERE CodC = 'C4' AND Disciplina.CodD = Grade.CodD AND Grade.CodP = Professor.CodP NomeD NomeP TLP1 Juliana Inglês André 54 SELECT – Auto-Junção Nomes das disciplinas cujo pré-requisito é “Cálculo1”: SELECT Disc1.NomeD FROM Disciplina AS Disc1, Disciplina AS Disc2 WHERE Disc2.CodD = Disc1.PreReqD AND Disc2.NomeD = “Cálculo1” Disc1.NomeD LP1 G Analítica 55 SELECT – Auto-Junção Pares de professores que são da mesma cidade: SELECT X.NomeP, Y.NomeP FROM Professor AS X, Professor AS Y WHERE X.CidadeP = Y.CidadeP AND X.CodP <> Y.CodP X.NomeP Y.NomeP Joaquim André André Joaquim Gil Juliana Juliana Gil 56 Exercícios Considere o esquema da Empresa: Func (cod_func, nome, dta_nasc, salário, nro_depto, cod_superv) Depto (nro_depto, nome, cod_ger) Projeto (cod_proj, nome, duração, nro_depto) Participa (cod_proj, cod_func, horas_trab) 57 Exercícios 1. Nomes dos deptos e nomes dos respectivos gerentes. 2. Nomes dos funcionários que participaram de projetos e respectivos códigos dos projetos e horas trabalhadas, somente das participações que excederam 50 horas. 3. Nomes dos funcionários que participaram de projetos e respectivos totais de horas trabalhadas. 4. Nomes de todos os funcionários e respectivos totais de horas trabalhadas. 5. Nomes dos funcionários que participaram de projetos e nomes dos respectivos projetos. 6. Nomes dos funcionários e nomes dos respectivos supervisores. 7. Pares de deptos que possuem o mesmo gerente. 58 SELECT - Subconsulta Nome e área das disciplinas do curso C4: SELECT NomeD, AreaD FROM Disciplina WHERE CodD IN (SELECT CodD FROM Grade WHERE CodC = 'C4') CodD D1 D3 NomeD AreaD TLP1 Computação Inglês Humanas 59 SELECT - Subconsulta Nome das disciplinas que são ministradas simultaneamente pelos professores P3 e P4: SELECT NomeD FROM Disciplina WHERE CodD IN (SELECT CodD FROM Grade WHERE CodP = 'P3') AND CodD IN (SELECT CodD FROM Grade WHERE CodP = 'P4') NomeD Ed Física CodD D3 D4 CodD D4 60 SELECT - Exists Nomes dos professores que ministram alguma disciplina: SELECT NomeP FROM Professor WHERE EXISTS (SELECT * FROM Grade WHERE Grade.CodP = Professor.CodP) NomeP Joaquim Paulo André Gil Juliana 61 SELECT - Exists Nomes das disciplinas que não fazem parte da grade: SELECT NomeD FROM Disciplina WHERE NOT EXISTS (SELECT * FROM Grade WHERE Grade.CodD = Disciplina.CodD) NomeD G Analítica 62 SELECT - ALL Nomes dos cursos que possuam duração maior que todos os cursos de mensalidade inferior a 500 reais: SELECT NomeC FROM Curso WHERE DuracaoC > ALL (SELECT DuracaoC FROM Curso WHERE MensC < 500) Nomec Eng Mecatronica DuracaoC 4 4 3 63 SELECT - ANY Nomes dos cursos que possuam duração maior que qualquer um dos cursos de mensalidade inferior a 500 reais: SELECT NomeC FROM Curso WHERE DuracaoC > ANY (SELECT DuracaoC FROM Curso WHERE MensC < 500) Nomec Análise Sist Eng Mecatrônica Ciência Comp Eng Elétrica DuracaoC 4 4 3 64 Exercícios 65 Considere o esquema da Empresa: Func (cod_func, nome, dta_nasc, salário, nro_depto, cod_superv) Depto (nro_depto, nome, cod_ger) Projeto (cod_proj, nome, duração, nro_depto) Participa (cod_proj, cod_func, horas_trab) Exercícios 1. Lista com nome e salário dos funcionários cujo salário é maior que a média salarial da empresa. 2. Códigos dos projetos onde trabalharam juntos os funcionários 19 e 20. 3. Nomes dos funcionários que não são gerentes. 4. Códigos dos projetos cuja duração foi maior que a duração de todos os projetos do depto 5. 5. Códigos dos funcionários que são supervisores e são gerentes de depto. 66 Linguagem de Controle de Dados (DCL) Comandos de administração de acesso: – Grant – Revoke Privilégios para cada usuário, de acesso a um banco de dados, definidos para diferentes operações sobre o mesmo: – SELECT – INSERT – UPDATE – ALTER – etc 67 GRANT Sintaxe: GRANT privilégio(s) ON objeto TO usuário(s) Exemplo: Liberar o acesso ao usuário Joao para realizar consultas à tabela Disciplina: GRANT SELECT ON Disciplina TO Joao 68 GRANT Permitir a todos os usuários realizar atualizações das colunas CidadeP e TituloP da tabela Professor: GRANT UPDATE (CidadeP, TituloP) ON Professor TO PUBLIC Permitir todos os privilégios na tabela Grade ao usuário Joao, permitindo também que o mesmo possa liberar esses privilégios a outros usuários: GRANT ALL ON Grade TO Joao WITH GRANT OPTION 69 REVOKE Sintaxe: REVOKE privilégio(s) ON tabela ou visão FROM usuário(s) Exemplo: Retirar todos os privilégios na tabela Grade para os usuários Joao e Paulo: REVOKE ALL ON Grade FROM Joao, Paulo 70 VISÕES Uma visão é um objeto do Banco de Dados criado a partir da execução de uma consulta sobre outras tabelas existentes. Após a sua criação, as consultas à visão podem ser realizadas de forma idêntica às tabelas comuns do banco. As visões podem ser muito úteis em várias situações: - substituição de consultas longas e complexas por outras mais simples, obtendo-se o mesmo resultado; - utilização de funções de agregação na definição de condições de pesquisa; - delimitação prática do acesso dos usuários a um conjunto específico dos dados contidos nas tabelas do banco, tornando os outros inacessíveis e invisíveis; 71 CRIAÇÃO DE VISÕES Sintaxe: CREATE VIEW nome_visão [(nome_coluna, ...)] AS SELECT ... – O comando SELECT de criação da visão não pode conter a cláusula ORDER BY. Exemplo 1 : Visão para alunos consultarem os dados dos cursos, sem mostrar os valores das respectivas mensalidades CREATE VIEW Vis_Curso_Aluno (Código, Curso, Duração) AS SELECT CodC, NomeC, DuracaoC FROM Curso 72 CRIAÇÃO DE VISÕES Exemplo 2 : Visão para a secretária consultar a grade de forma amigável e e eficiente CREATE VIEW Vis_Grade_Secr (Cod_Curso, Curso, Cod_Disciplina, Disciplina, Cod_Professor, Professor, Sala) AS SELECT CodC, NomeC, CodD, NomeD, CodP, NomeP, Sala FROM Grade, Curso, Disciplina, Professor WHERE Grade.CodC = Curso.CodC AND Grade.CodD = Disciplina.CodD AND Grade.CodP = Professor.CodP 73 ATUALIZAÇÕES ATRAVÉS DAS VISÕES Os dados do banco podem ser atualizados através da visão, ou seja, uma vezexecutado um comando INSERT, UPDATE ou DELETE sobre a visão, os dados da tabela que originou a mesma também serão atualizados. Porém essas operações somente poderão ser realizadas se a visão atender aos seguintes requisitos: - não possuir junções, ou seja, ter sido criada sobre uma única tabela; - não possuir colunas derivadas de funções de agregação; - não possuir a cláusula DISTINCT na sua consulta; - não possuir as cláusulas GROUP BY ou HAVING na sua consulta. 74 ATUALIZAÇÕES ATRAVÉS DAS VISÕES Caso contrário, a visão será "read-only" e as atualizações deverão ser realizadas sobre as tabelas originais. A visão do exemplo 1 poderá ser usada para atualizar a tabela Curso, desde que o usuário possua privilégios para isso. Porém, a visão do exemplo 2 é "read-only", pois é formada a partir de uma junção. 75 REMOÇÃO DE VISÕES Sintaxe: DROP VIEW nome_visão Exemplo: Remover a visão Vis_Curso_Aluno DROP VIEW Vis_Curso_Aluno 76 Exercícios 77 Considere o esquema da Empresa: Func (cod_func, nome, dta_nasc, salário, nro_depto, cod_superv) Depto (nro_depto, nome, cod_ger) Projeto (cod_proj, nome, duração, nro_depto) Participa (cod_proj, cod_func, horas_trab) Exercícios 1. Crie uma visão chamada Func_Proj, contendo o nome do funcionário, nome do seu departamento e nome dos projetos que ele participou. 2. Crie um usuário chamado Pedro. 3. Crie privilégios para o usuário Pedro poder se conectar, acessar recursos e consultar dados da visão Func_Proj. 4. Crie privilégios para o usuário Pedro poder inserir dados na tabela Func, podendo repassar estes direitos. 5. Crie privilégios para que todos possam consultar a coluna nome da tabela Projeto. 6. Elimine todos os privilégios do usuário Pedro. 78 EXERCÍCIOS PROPOSTOS 1) Crie a tabela Aluno, contendo dados pessoais dos alunos e o curso onde estão inscritos. 2) Crie a tabela Matricula, relacionando cada aluno com as disciplinas que o mesmo já cursou ou está cursando. 3) Armazene, altere e remova dados das tabelas criadas. 4) Altere a tabela Disciplina, acrescentando uma coluna para armazenar o código da disciplina que é pré-requisito da disciplina corrente (suponha que uma disciplina possui no máximo um pré- requisito). 5) Crie um índice para a coluna Pre_req da tabela Disciplina. 6) Crie uma visão para a consulta dos alunos ao seu histórico. 7) Crie privilégios para o usuário Sílvio poder consultar e inserir dados na tabela Matricula. 8) Elimine todos os privilégios do usuário Sílvio. 79 EXERCÍCIOS PROPOSTOS 9) Crie as seguintes consultas em SQL: a) Código e nome das disciplinas com carga horária entre 3 e 5 inclusive e que não sejam da área de Saúde. b) Nome das disciplinas com carga horária menor que 5 e que sejam ministradas por professores doutores. c) Salas onde haverá aulas cujos cursos possuam duração superior a 3 anos e cujas disciplinas não sejam nem da área de Matemática, nem de Saúde e nem de Humanas e cujo professor seja o Paulo, o Joaquim ou a Juliana. d) Nome dos cursos e das disciplinas oferecidas em cada curso. e) Nomes dos professores que não ministram nenhuma disciplina. f) Nomes das disciplinas e o nome dos respectivos pré- requisitos. Considere a tabela Disciplina resultante do exercício 4. 80 EXERCÍCIOS PROPOSTOS g) Nome das disciplinas que possuam carga horária maior que todas as disciplinas da área de Matemática. h) Código de cada disciplina, com a quantidade de cursos em que a mesma é oferecida. i) Carga horária média das áreas com carga horária média maior ou igual a 3. j) Nome das disciplinas que não têm pré-requisito e que não são pré-requisito. k) Nome dos cursos que têm a mesma duração do curso de Engenharia Elétrica. l) Nome dos cursos que têm a mesma duração do curso de Engenharia Elétrica e que têm mensalidade maior que a sua. m) Nome dos cursos que têm mensalidade maior que a média de todos os cursos. 81
Compartilhar