urgênte Prova de SQL

thiagosaadoficial · Junho 9, 2016

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;

Stoma · Junho 10, 2016

Olá!

a) Não é em ordem alfabética por nome apenas? Porque do jeito que você fez, vai acabar ordenando só pela matrícula (já que a matrícula é única/pk)...

b) Será que ele não quer que você utilize JOIN? Assim:

	CREATE VIEW vw_aluno_disciplina AS
	SELECT
	        A.matricula,A.nome,AD.periodo, COUNT(AD.coddisciplina) AS QTE_DISCIPLINA
	    FROM aluno AS A
	    INNER JOIN aluno_disciplina AS AD ON AD.matricula = A.matricula
	    INNER JOIN disciplina AS D ON AD.coddisciplina = D.codigo
	    GROUP BY A.matricula,A.nome,AD.periodo;

c) Se você utilizar chave primária auto incremental na tabela, você não precisaria usar o MAX. Mas da forma que você fez, pode ser assim:

	INSERT INTO professor VALUES(
	    (SELECT MAX(codigo)+1 FROM professor),
	    'Professor 4', 'SP', 'SP'
	);

d) Mesmo caso da b (JOIN). Mas você pode fazer o subselect mais simples, porque não precisa de informações de todas as tabelas que você usou. Assim:

	DELETE FROM professor WHERE codigo NOT IN(
	    SELECT
	            codprofessor
	        FROM professor_disciplina
	)

e) Mesmo caso da b (JOIN). A fórmula da média está errada, você deveria dividir por 3, e não por 2. Falta você filtrar para trazer apenas da disciplina "BDII". E precisa trazer o período também?

	SELECT
	        A.matricula,A.nome,AD.periodo, (COALESCE(A1,0)+COALESCE(A2,0)+COALESCE(A3,0))/3 AS media
	    FROM aluno AS A
	    INNER JOIN aluno_disciplina AS AD ON AD.matricula = A.matricula
	    INNER JOIN disciplina AS D ON D.codigo = AD.coddisciplina
	    WHERE D.nome = 'BDII'

f) Mesmo caso da b (JOIN)

g) Você não está contando alunos reprovados. Acho que você deveria verificar a quantidade de falta que o aluno tem e se é maior que o limite de faltas da disciplina, e só aí mostrar as disciplinas que tem mais de 10 alunos reprovados. Mas sua consulta não faz nada parecido.

h) Novamente sua consulta não faz o que o enunciado pede. A consulta deveria ser assim:

	SELECT
	        A.matricula, A.nome, AD.faltas
	    FROM aluno AS A
	    INNER JOIN aluno_disciplina AS AD ON AD.matricula = A.matricula
	    WHERE AD.faltas &gt; (
	        SELECT
	                MAX(faltas) as max_faltas
	            FROM disciplina AS D
	            INNER JOIN aluno_disciplina AS AD ON AD.coddisciplina = D.codigo
	            WHERE D.nome = 'BDII'
	    );

i) ok

j) Mesmo caso do b (JOIN). Mas tem outra questão, porque pelo enunciado você tem que mostrar também as disciplinas que não tem aluno, e para isso você usa o LEFT JOIN. Do jeito que você fez (usando o WHERE para fazer a junção), não vai listar disciplinas sem aluno (porque a comparação AD.matricula = A.matricula vai dar false, já que não tem o aluno)

k) Caso parecido com o j

Vish é muita coisa ahshahahaha
Mas acho que já dá para você ter uma idéia do que ele falou nos casos das junções, porque da forma que você fez (usno WHERE) fica todos como INNER JOIN, mas tem casos em que o certo é LEFT, outros RIGHT e outros INNER.

Olá!

a) Não é em ordem alfabética por nome apenas? Porque do jeito que você fez, vai acabar ordenando só pela matrícula (já que a matrícula é única/pk)...

b) Será que ele não quer que você utilize JOIN? Assim:

	CREATE VIEW vw_aluno_disciplina AS
	SELECT
	        A.matricula,A.nome,AD.periodo, COUNT(AD.coddisciplina) AS QTE_DISCIPLINA
	    FROM aluno AS A
	    INNER JOIN aluno_disciplina AS AD ON AD.matricula = A.matricula
	    INNER JOIN disciplina AS D ON AD.coddisciplina = D.codigo
	    GROUP BY A.matricula,A.nome,AD.periodo;

c) Se você utilizar chave primária auto incremental na tabela, você não precisaria usar o MAX. Mas da forma que você fez, pode ser assim:

	INSERT INTO professor VALUES(
	    (SELECT MAX(codigo)+1 FROM professor),
	    'Professor 4', 'SP', 'SP'
	);

d) Mesmo caso da b (JOIN). Mas você pode fazer o subselect mais simples, porque não precisa de informações de todas as tabelas que você usou. Assim:

	DELETE FROM professor WHERE codigo NOT IN(
	    SELECT
	            codprofessor
	        FROM professor_disciplina
	)

e) Mesmo caso da b (JOIN). A fórmula da média está errada, você deveria dividir por 3, e não por 2. Falta você filtrar para trazer apenas da disciplina "BDII". E precisa trazer o período também?

	SELECT
	        A.matricula,A.nome,AD.periodo, (COALESCE(A1,0)+COALESCE(A2,0)+COALESCE(A3,0))/3 AS media
	    FROM aluno AS A
	    INNER JOIN aluno_disciplina AS AD ON AD.matricula = A.matricula
	    INNER JOIN disciplina AS D ON D.codigo = AD.coddisciplina
	    WHERE D.nome = 'BDII'

f) Mesmo caso da b (JOIN)

g) Você não está contando alunos reprovados. Acho que você deveria verificar a quantidade de falta que o aluno tem e se é maior que o limite de faltas da disciplina, e só aí mostrar as disciplinas que tem mais de 10 alunos reprovados. Mas sua consulta não faz nada parecido.

h) Novamente sua consulta não faz o que o enunciado pede. A consulta deveria ser assim:

	SELECT
	        A.matricula, A.nome, AD.faltas
	    FROM aluno AS A
	    INNER JOIN aluno_disciplina AS AD ON AD.matricula = A.matricula
	    WHERE AD.faltas &gt; (
	        SELECT
	                MAX(faltas) as max_faltas
	            FROM disciplina AS D
	            INNER JOIN aluno_disciplina AS AD ON AD.coddisciplina = D.codigo
	            WHERE D.nome = 'BDII'
	    );

i) ok

j) Mesmo caso do b (JOIN). Mas tem outra questão, porque pelo enunciado você tem que mostrar também as disciplinas que não tem aluno, e para isso você usa o LEFT JOIN. Do jeito que você fez (usando o WHERE para fazer a junção), não vai listar disciplinas sem aluno (porque a comparação AD.matricula = A.matricula vai dar false, já que não tem o aluno)

k) Caso parecido com o j

Vish é muita coisa ahshahahaha
Mas acho que já dá para você ter uma idéia do que ele falou nos casos das junções, porque da forma que você fez (usno WHERE) fica todos como INNER JOIN, mas tem casos em que o certo é LEFT, outros RIGHT e outros INNER.

Obs: na h), onde está os símbolos > é um sinal de maior (>), mas o editor trocou

Entrar

urgênte Prova de SQL

Pergunta

thiagosaadoficial

Link para o comentário

Compartilhar em outros sites

1 resposta a esta questão

Posts Recomendados

Stoma

Link para o comentário

Compartilhar em outros sites

Participe da discussão

Estatísticas dos Fóruns

Navegação

Atividades