query a2

Prévia do material em texto

-- 1)Listar o código e o nome das disciplinas que não estão no histórico ou que são da escola 
Informática 
select codigo,nome 
from disciplina 
where codigo not in (select codigo from historico) 
 or escola='Informática'; 
 
-- 2)Listar o nome do aluno e a nota na disciplina se a nota for maior ou igual a media da 
disciplina 
 
select aluno.nome as Aluno, d.nome as Disciplina, nota 
from aluno, disciplina d, historico h 
where aluno.matricula = h.matricula and d.codigo = h.codigo 
and nota >= (select avg(nota) from historico group by h.codigo) 
group by d.nome, aluno.nome,nota; 
 
-- 3)Listar o nome da disciplina, a nota obtida e o conceito final (APROVADO ou REPROVADO) 
para cada disciplina cursada por Maria. media 6 
 
select d.nome as Disciplina, h.nota, 
 case when h.nota >= 6 then 'APROVADO' else 'REPROVADO' end as Conceito 
from historico h, aluno, disciplina d 
where aluno.matricula = h.matricula and aluno.nome='Maria' and d.codigo=h.codigo; 
 
-- 4)Listar a média das notas de cada disciplina da escola informática 
 
select d.nome, trunc(avg(nota),2) as media 
from historico h, disciplina d 
where h.codigo=d.codigo and d.escola='Informática' 
group by nome; 
 
-- 5)Aumentar 1,0 ponto na nota de todos os alunos que sejam da escola de Direito 
 
update historico set nota = nota+1 
where codigo in (select codigo from disciplina where escola='Direito'); 
 
-- 6) Incluir um novo aluno com matricula 900 e nome Rui 
 
insert into aluno values (900,'Rui',''); 
 
-- 7)Excluir as disciplinas que não tenham registro no historico 
 
delete from disciplina using historico 
where disciplina.codigo not in (select codigo from historico); 
 
-- 8)Criar uma VIEW de nome 'Melhores_Alunos' que contenha a matrícula e o nome dos 
alunos com nota > 8 
 
create view Melhores_Alunos as 
select h.matricula, aluno.nome 
from historico h, aluno 
 where h.matricula = aluno.matricula and nota >= 8 
 group by aluno.nome,h.matricula; 
 
-- 9)Informar o resultado da consulta 
 
select nome, escola 
from disciplina d 
where exists (select 1 from historico h 
 where d.codigo=h.codigo and h.nota < 6); 
 
-- 10)Usando o mecanismo de ROLES associe os privilégios para leitura na tabela Aluno e 
exclusão na tabela historico aos usuários JOSE e ANA 
create role gerente; 
grant delete on historico to gerente; 
grant select on aluno to gerente; 
 
grant gerente to JOSE; 
grant gerente to ANA;