Pesquisar na Comunidade

Olá, venho aqui na Humildade companheiros para pedir uma enorme ajuda para os senhores. Estou dando SQL na faculdade porém meu professor não tem muito compromisso com os alunos e não gosta de corrigir e muito menos retirar dúvidas, além disso ele passa a matéria toda jogada e temos que vir na internet buscar conhecimento. Ele passou os seguintes exercícios com a seguinte tabela abaixo, eu executei todos porem ele falou que estava errado o meu modo de fazer e as minhas junções, vocês com o nível maior que o meu poderiam me ajudar no assunto? Se realmente esta errado, o que devo mudar e onde posso aprender mais? Pois ele falou que não existe um exercício pronto já com dados inseridos para que possamos conferir então isso me deixa um pouco perdido pois nunca sei se esta correto. Contenho as seguintes tabelas QUE EU CRIEI USANDO AS SEGUINTES CONFIGURAÇÕES(NÃO SEI SE ESTÁ CORRETO) --TABELA CREATE TABLE aluno( matricula int not null, nome varchar(50) not null, endereco varchar(50) not null, cidade char(2) not null, idade int not null, uf char(2) not null, CONSTRAINT PK_ALUNO PRIMARY KEY (matricula)); CREATE TABLE disciplina( codigo int not null, nome varchar(50) not null, limite_faltas int not null, CONSTRAINT PK_DISCIPLINA PRIMARY KEY(codigo)); CREATE TABLE professor( codigo int not null, nome varchar(50) not null, cidade char(2) not null, uf char(2) not null, CONSTRAINT PK_PROFESSOR PRIMARY KEY (codigo)); CREATE TABLE professor_disciplina( codprofessor int not null, coddisciplina int not null, periodo int not null, CONSTRAINT PK_PROFESSOR_DISCIPLINA PRIMARY KEY(codprofessor,coddisciplina,periodo), CONSTRAINT FK_PROFESSOR_DISCIPLINA FOREIGN KEY(codprofessor) REFERENCES professor(codigo), CONSTRAINT FK_DISCIPLINA_PROFESSOR FOREIGN KEY(coddisciplina) REFERENCES disciplina(codigo)); CREATE TABLE aluno_disciplina( matricula int not null, coddisciplina int not null, periodo int not null, A1 numeric(4,2) not null, A2 numeric(4,2) not null, A3 numeric(4,2) not null, faltas int not null, CONSTRAINT PK_ALUNO_DISCIPLINA PRIMARY KEY(matricula,coddisciplina,periodo,A1,A2,A3,faltas), CONSTRAINT FK_ALUNO_DISCIPLINA FOREIGN KEY(matricula) REFERENCES aluno(matricula), CONSTRAINT FK_DISCIPLINA_ALUNO FOREIGN KEY(coddisciplina) REFERENCES disciplina(codigo)); --INFO TABELA ALUNOS INSERT INTO aluno VALUES(001,'ALUNO I','RUA I','RJ',18,'RJ'); INSERT INTO aluno VALUES(002,'ALUNO II','RUA II','SP',19,'SP'); INSERT INTO aluno VALUES(003,'João da Silva','RUA III','BH',20,'BH'); INSERT INTO aluno VALUES(004,'Fernando da Silva','RUA IV','RJ',21,'RJ'); INSERT INTO aluno VALUES(005,'Patricia da Silva','RUA V','RJ',22,'RJ'); -- INFO TABELA DISCIPLINA INSERT INTO disciplina VALUES(1,'BDI',15); INSERT INTO disciplina VALUES(2,'BDII',15); -- INFO TABELA PROFESSOR INSERT INTO professor VALUES(1,'PROFESSOR I','RJ','RJ'); INSERT INTO professor VALUES(2,'PROFESSOR II','SP','SP'); INSERT INTO professor VALUES(3,'PROFESSOR III','BH','BH'); -- INFO TABELA PROFESSOR DISCIPLINA INSERT INTO professor_disciplina VALUES(1,1,1) MEU CÓDIGO FEITO OBS: Sò não consegui fazer a C) Pois não sei inserir o MAX nas chaves no INSERT e a V) Pois não sei fazer aquela subconsulta para mostrar os dois resultados pedidos no enunciado. -- a) Mostre em ordem alfabética a matrícula e nome de todos os alunos. SELECT DISTINCT A.matricula,A.nome FROM aluno AS A ORDER BY A.matricula,A.nome; -- b) Crie uma visão que mostre a quantidade de disciplinas feitas por cada aluno em cada período. CREATE VIEW vw_aluno_disciplina AS SELECT A.matricula,A.nome,AD.periodo, COUNT(AD.coddisciplina) AS QTE_DISCIPLINA FROM aluno AS A, disciplina AS D, aluno_disciplina AS AD WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo GROUP BY A.matricula,A.nome,AD.periodo; -- c) Inclua dois professores na tabela professor. -- d) Exclua todos os professores que nunca lecionaram disciplinas. DELETE FROM professor WHERE codigo NOT IN (SELECT PD.codprofessor FROM professor AS P, professor_disciplina AS PD, disciplina AS D WHERE PD.codprofessor = P.codigo AND PD.coddisciplina = D.codigo); -- e) Mostre a matrícula,nome e média de todos os alunos de ‘BDII’.Utilize para o cálculo da média a regra da sua universidade. SELECT A.matricula,A.nome,AD.periodo, (COALESCE(A1,)+COALESCE(A2,)+COALESCE(A3,))/2 AS MÉDIA FROM aluno AS A, disciplina AS D, aluno_disciplina AS AD WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo; -- f) Mostre a média dos alunos de BDII na A1 e A2. SELECT A.matricula,A.nome, (COALESCE(A1,)+COALESCE(A2,))/2 AS MÉDIA FROM aluno AS A, aluno_disciplina AS AD, disciplina AS D WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND D.nome = 'BDII'; -- g) Mostre o código e nome das disciplinas que reprovaram mais que 10 alunos no período ’04-1’. SELECT codigo,nome FROM disciplina WHERE codigo IN (SELECT COUNT(AD.coddisciplina) AS QTE_ALUNO FROM disciplina AS D, aluno_disciplina AS AD, aluno AS A WHERE AD.coddisciplina = D.codigo AND AD.matricula = A.matricula AND AD.periodo='1' HAVING COUNT(AD.coddisciplina) > 10); -- h) Mostre todos os alunos que faltaram mais que o aluno que mais faltou em BDII. SELECT A.matricula,A.nome, MAX(AD.faltas) AS QTE_ALUNO FROM aluno AS A INNER JOIN aluno_disciplina AS AD ON AD.matricula = A.matricula WHERE AD.matricula IN (SELECT MAX(AD.faltas) FROM aluno AS A, aluno_disciplina AS AD, disciplina AS D WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND D.nome='BDII') GROUP BY A.matricula,A.nome; -- i) Quantos alunos existem com sobrenome ‘Silva’? SELECT COUNT(A.nome) AS QTE_ALUNO FROM aluno AS A WHERE nome like '% Silva %' or nome like '% Silva'; -- j) Mostre a quantidade de alunos por disciplina (suponha que uma disciplina pode não ter aluno). SELECT D.codigo,D.nome, (COALESCE(COUNT(AD.matricula),)) AS QTE_ALUNO FROM aluno AS A, disciplina AS D, aluno_disciplina AS AD WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo GROUP BY D.codigo,D.nome; -- k) Mostre a quantidade de disciplinas que cada aluno está matriculado. (Mostre os alunos sem disciplinas) SELECT D.codigo,D.nome, (COALESCE(COUNT(AD.matricula),)) AS QTE_ALUNO FROM disciplina AS D, aluno AS A, aluno_disciplina AS AD WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo GROUP BY D.codigo,D.nome; -- l) Mostre o nome de todos os alunos que tiraram nota maior que João da Silva em BDII. SELECT A.matricula,A.nome FROM aluno AS A, disciplina AS D, aluno_disciplina AS AD WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND AD.A1 > (SELECT (COALESCE(MAX(AD.A1),)) AS MAIOR_NOTA FROM aluno_disciplina AS AD, disciplina AS D, aluno AS A WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND A.nome='João da Silva' AND D.nome='BDII') AND AD.A2 > (SELECT (COALESCE(MAX(AD.A2),)) AS MAIOR_NOTA FROM aluno_disciplina AS AD, disciplina AS D, aluno AS A WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND A.nome='João da Silva' AND D.nome='BDII') AND AD.A3 > (SELECT (COALESCE(MAX(AD.A3),)) AS MAIOR_NOTA FROM aluno AS A, disciplina AS D, aluno_disciplina AS AD WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND A.nome='João da Silva' AND D.nome='BDII'); -- m) Quantos professores não lecionaram disciplinas em ’04-1’? SELECT COUNT(P.codigo) AS QTE_PROFESSOR FROM professor AS P WHERE P.codigo NOT IN (SELECT PD.codprofessor FROM professor AS P, professor_disciplina AS PD, disciplina AS D WHERE PD.codprofessor = P.codigo AND PD.coddisciplina = D.codigo); -- n) Em média, quantas disciplinas cada professor leciona por período. Mostre o código, nome e média de disciplinas lecionadas por cada professor. SELECT P.codigo,P.nome,PD.periodo, COUNT(PD.coddisciplina)/PD.Periodo AS MÉDIA FROM disciplina AS D, professor_disciplina AS PD, professor AS P WHERE PD.coddisciplina = D.codigo AND PD.codprofessor = P.codigo GROUP BY P.codigo,P.nome,PD.periodo; -- o) Exclua em ALUNO_DISCIPLINA todos os alunos que não realizaram prova em 2007. DELETE FROM aluno_disciplina WHERE matricula NOT IN (SELECT AD.matricula FROM aluno_disciplina AS AD, aluno AS A, disciplina AS D WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND AD.Periodo>= '01/01/2017' AND AD.Periodo<= '31/12/2017' AND A1 = AND A2 = AND A3 = ); -- p) Aumente em 10% o limite de faltas das disciplinas com mais de 20 alunos com 20 faltas. UPDATE disciplina SET limite_faltas = limite_faltas * 1.10 WHERE codigo IN (SELECT AD.coddisciplina AS QTE_DISCIPLINA FROM aluno_disciplina AS AD, disciplina AS D, aluno AS A WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo GROUP BY Ad.coddisciplina HAVING COUNT(AD.matricula) > 20 AND COUNT(AD.faltas) = 20); -- q) Mostre o nome de todos os alunos que já fizeram disciplinas com professores do seu estado de origem SELECT A.matricula,A.nome FROM aluno AS A, disciplina AS D,aluno_disciplina AS AD, professor_disciplina AS PD, professor AS P WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND PD.coddisciplina = D.codigo AND PD.codprofessor = P.codigo AND AD.coddisciplina = PD.coddisciplina AND A.uf = P.uf; -- e) Quais disciplinas (nome) tiveram mais de 40 alunos em 5 períodos diferentes? SELECT D.codigo,D.nome, COUNT(AD.matricula) AS QTE_ALUNO FROM disciplina AS D,aluno AS A, aluno_disciplina AS AD WHERE AD.coddisciplina = D.codigo AND AD.matricula = A.matricula GROUP BY D.codigo,D.nome HAVING COUNT(AD.matricula) > 40 AND COUNT(AD.Periodo)!=5; -- s) Mostre a matrícula e o nome dos alunos que fizeram a primeira disciplina no período ’03-2’. SELECT A.matricula,A.nome FROM aluno AS A, disciplina AS D, aluno_disciplina AS AD WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND AD.Periodo = '03-2' -- t) Mostre a matrícula e o nome de todos os alunos de ‘BDII’ que foram reprovados por faltas. SELECT A.matricula,A.nome FROM aluno AS A, disciplina AS D, aluno_disciplina AS AD WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND D.nome = 'BDII' AND Faltas > Limite_Faltas; -- u) Quantos alunos terão que fazer prova final na disciplina ‘BDII’ em ’04-1’. Suponha que você só tenha as notas da A1 e A2. SELECT COUNT(A.matricula) AS QTE_ALUNO FROM aluno AS A WHERE A.matricula IN (SELECT AD.matricula FROM aluno AS A, disciplina AS D,aluno_disciplina AS AD WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND D.nome = 'BDII' AND AD.Periodo='04-1' AND A1 < 5 OR A2 < 5); -- v) Mostre a quantidade de alunos em cada disciplina lecionada em ’04-1’, colocando no final o total de alunos matriculados. SELECT D.codigo,D.nome,((SELECT COUNT(AD.matricula) FROM aluno_disciplina) AS QTE_ALUNOS),((SELECT SUM(QTE_ALUNOS) AS ALUNOS_MATRICULADOS FROM aluno_disciplina) AS ALUNOS_MATRICULADOS) FROM aluno AS A, disciplina AS D, aluno_disciplina AS AD WHERE AD.matricula = A.matricula AND AD.coddisciplina = D.codigo AND AD.Periodo = '04-1' GROUP BY D.codigo,D.nome;