Exercício 8 – SQL – Join – Funções de agregação ALUNO #IDMATR NOME MATRÍCULA #IDMATR #IDDISCIP #IDTURMA PROFESSOR #IDPROF NOME TURMA #IDDISCIP #IDTURMA HORAS IDPROF DISCIPLINA #IDDISCIP NOME
CREATE TABLE ALUNO (
IDMATR CHAR(9) NOT NULL, NOME VARCHAR(30),
CONSTRAINT PK_ALUNO PRIMARY KEY (IDMATR) );
CREATE TABLE DISCIPLINA ( IDDISCIP CHAR(6) NOT NULL, NOME VARCHAR(30),
CONSTRAINT PK_DISCIPLINA PRIMARY KEY (IDDISCIP) );
CREATE TABLE MATRICULA ( IDMATR CHAR(9) NOT NULL, IDDISCIP CHAR(6) NOT NULL, IDTURMA CHAR(1) NOT NULL,
CONSTRAINT PK_MATRICULA PRIMARY KEY (IDMATR, IDDISCIP, IDTURMA),
CONSTRAINT FK_MATRICULA1_TURMA FOREIGN KEY (IDDISCIP, IDTURMA) REFERENCES TURMA (IDDISCIP, IDTURMA) ON DELETE CASCADE,
CONSTRAINT FK_MATRICULA2_ALUNO FOREIGN KEY (IDMATR) REFERENCES ALUNO (IDMATR) ON DELETE CASCADE
);
CREATE TABLE PROFESSOR ( IDPROF CHAR(3) NOT NULL, NOME VARCHAR(30),
CONSTRAINT PK_PROFESSOR PRIMARY KEY (IDPROF) );
IDDISCIP CHAR(6) NOT NULL, IDTURMA CHAR(1) NOT NULL, HORAS INTEGER NOT NULL, IDPROF CHAR(3) NOT NULL,
CONSTRAINT PK_TURMA PRIMARY KEY (IDDISCIP, IDTURMA),
CONSTRAINT FK_TURMA1_DISCI FOREIGN KEY (IDDISCIP) REFERENCES DISCIPLINA (IDDISCIP) ON DELETE CASCADE,
CONSTRAINT FK_TURMA2_PROFE FOREIGN KEY (IDPROF) REFERENCES PROFESSOR (IDPROF)
);
INSERT INTO ALUNO (IDMATR, NOME) VALUES ('200215005', 'JOAO'); INSERT INTO ALUNO (IDMATR, NOME) VALUES ('200215006', 'LUCAS'); INSERT INTO ALUNO (IDMATR, NOME) VALUES ('200215007', 'PEDRO'); INSERT INTO ALUNO (IDMATR, NOME) VALUES ('200215008', 'ALICE'); INSERT INTO ALUNO (IDMATR, NOME) VALUES ('200215009', 'FELIX'); INSERT INTO ALUNO (IDMATR, NOME) VALUES ('200215010', 'OLAVO'); INSERT INTO ALUNO (IDMATR, NOME) VALUES ('200215011', 'ANGELA');
INSERT INTO DISCIPLINA (IDDISCIP, NOME) VALUES ('INF003', 'Engenharia de Software'); INSERT INTO DISCIPLINA (IDDISCIP, NOME) VALUES ('INF004', 'Banco de Dados');
INSERT INTO DISCIPLINA (IDDISCIP, NOME) VALUES ('INF005', 'Algoritmos');
INSERT INTO DISCIPLINA (IDDISCIP, NOME) VALUES ('INF006', 'Estrutura de Dados'); INSERT INTO PROFESSOR (IDPROF, NOME) VALUES ('001', 'PAULO');
INSERT INTO PROFESSOR (IDPROF, NOME) VALUES ('002', 'RICARDO'); INSERT INTO PROFESSOR (IDPROF, NOME) VALUES ('003', 'ROBERTA'); INSERT INTO PROFESSOR (IDPROF, NOME) VALUES ('004', 'SONIA'); INSERT INTO PROFESSOR (IDPROF, NOME) VALUES ('005', 'ANA LUCIA');
INSERT INTO TURMA (IDDISCIP, IDTURMA, HORAS, IDPROF) VALUES ('INF003', 'A', 4, '001'); INSERT INTO TURMA (IDDISCIP, IDTURMA, HORAS, IDPROF) VALUES ('INF003', 'B', 4, '002'); INSERT INTO TURMA (IDDISCIP, IDTURMA, HORAS, IDPROF) VALUES ('INF004', 'A', 5, '003'); INSERT INTO TURMA (IDDISCIP, IDTURMA, HORAS, IDPROF) VALUES ('INF004', 'B', 5, '003'); INSERT INTO TURMA (IDDISCIP, IDTURMA, HORAS, IDPROF) VALUES ('INF005', 'A', 4, '001'); INSERT INTO TURMA (IDDISCIP, IDTURMA, HORAS, IDPROF) VALUES ('INF005', 'B', 4, '004'); INSERT INTO TURMA (IDDISCIP, IDTURMA, HORAS, IDPROF) VALUES ('INF006', 'A', 4, '002'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215005', 'INF003', 'A'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215005', 'INF004', 'B'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215005', 'INF005', 'B'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215006', 'INF003', 'A'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215006', 'INF004', 'A'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215006', 'INF005', 'B'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215006', 'INF006', 'A'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215007', 'INF003', 'B'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215007', 'INF004', 'B'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215007', 'INF006', 'A');
INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215008', 'INF003', 'A'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215008', 'INF006', 'A'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215009', 'INF006', 'A'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215009', 'INF005', 'B'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215010', 'INF004', 'A'); INSERT INTO MATRICULA (IDMATR, IDDISCIP, IDTURMA) VALUES ('200215011', 'INF004', 'B'); Questões:
1. Total de horas de aula do aluno 200215005. select sum(horas)
from aluno A, matricula M, turma T where
A.idmatr = '200215005' and A.idmatr = M.idmatr and
(M.idturma = T.idturma and M.iddiscip = T.iddiscip); Ou
select sum(horas) from aluno A
inner join matricula M on A.idmatr = M.idmatr
inner join turma T on (M.idturma = T.idturma and M.iddiscip = T.iddiscip) where
A.idmatr = 200215005;
2. O aluno João possui quantos professores diferentes? select count(T.idprof)
from aluno A, matricula M, turma T where
A.nome = 'JOAO' and A.idmatr = M.idmatr and
(M.idturma = T.idturma and M.iddiscip = T.iddiscip); Ou
select count(T.idprof) from aluno A
inner join matricula M on A.idmatr = M.idmatr
inner join turma T on (M.idturma = T.idturma and M.iddiscip = T.iddiscip) where
A.nome = 'JOAO';
3. Mostrar, para a disciplina "INF005", a quantidade de alunos matriculados (nas diversas turmas).
select iddiscip, idturma, count(idmatr) from matricula
where iddiscip = 'INF005' group by iddiscip, idturma;
select d.nome
from professor p , turma t , disciplina d where p.nome = 'PAULO' and
p.idprof = t.idprof and t.iddiscip = d.iddiscip; ou
select d.nome from professor p
inner join turma t on p.idprof = t.idprof
inner join disciplina d on t.iddiscip = d.iddiscip where p.nome = 'PAULO';
5. Quais os nomes dos alunos do prof. RICARDO? select a.nome
from professor p , turma t , matricula m , aluno a where p.nome = 'RICARDO' and
p.idprof = t.idprof and
(t.iddiscip = m.iddiscip and t.idturma = m.idturma) and m.idmatr = a.idmatr;
ou
select a.nome from professor p
inner join turma t on p.idprof = t.idprof
inner join matricula m on (t.iddiscip = m.iddiscip and t.idturma = m.idturma) inner join aluno a on m.idmatr = a.idmatr
where p.nome = 'RICARDO';
6. Quais disciplinas têm mais de uma turma? select t.iddiscip, d.nome
from turma t , disciplina d where t.iddiscip = d.iddiscip group by t.iddiscip, d.nome having count(*) > 1;
ou
select t.iddiscip, d.nome from turma t
inner join disciplina d on t.iddiscip = d.iddiscip group by t.iddiscip, d.nome
7. Quantos professores estão lecionando atualmente? select count(distinct idprof)
from turma t;
8. Quantos alunos estão matriculados em "Algoritmos"? select count(idmatr)
from disciplina d , turma t, matricula m where d.nome = 'Algoritmos' and
d.iddiscip = t.iddiscip and
(t.iddiscip = m.iddiscip and t.idturma = m.idturma); Ou
select count(idmatr) from disciplina d
inner join turma t on d.iddiscip = t.iddiscip
inner join matricula m on (t.iddiscip = m.iddiscip and t.idturma = m.idturma) where d.nome = 'Algoritmos';
9. Quantos professores diferentes lecionam "Banco de Dados" ?
select count(distinct idprof) from turma t , disciplina d
where d.nome = 'Banco de Dados' and d.iddiscip = t.iddiscip;
ou
select count(distinct idprof) from turma t
inner join disciplina d on d.iddiscip = t.iddiscip where d.nome = 'Banco de Dados';
10.Quantos alunos estão matriculados em cada disciplina? select m.iddiscip, count(distinct m.idmatr)
from matricula m group by m.iddiscip;
Incluindo o nome da disciplina:
select d.iddiscip, d.nome, count(distinct m.idmatr) from matricula m , turma t , disciplina d
where m.iddiscip = t.iddiscip and t.iddiscip = d.iddiscip group by d.iddiscip, d.nome;
11.Qual o total de horas de aula de cada professor? select idprof, sum(t.horas)
group by t.idprof;
Incluindo o nome do professor: select t.idprof, p.nome, sum(t.horas) from turma t , professor p
where t.idprof = p.idprof group by t.idprof, p.nome;
12.Quantas aulas o prof. PAULO ministra para o aluno LUCAS? select t.idprof, sum(horas)
from aluno a , matricula m, turma t , professor p where a.nome = 'LUCAS' and p.nome = 'PAULO' and a.idmatr = m.idmatr and p.idprof = t.idprof and (m.iddiscip = t.iddiscip and m.idturma = t.idturma) group by t.idprof;
Incluindo o nome do professor e do aluno: select t.idprof, p.nome, a.nome, sum(horas)
from aluno a , matricula m, turma t , professor p where a.nome = 'LUCAS' and p.nome = 'PAULO' and
a.idmatr = m.idmatr and p.idprof = t.idprof and (m.iddiscip = t.iddiscip and m.idturma = t.idturma) group by t.idprof, p.nome, a.nome;
13.Qual o total de aulas de cada disciplina? select t.iddiscip, sum(horas)
from turma t , disciplina d where d.iddiscip = t.iddiscip group by t.iddiscip, d.nome;
