romulomourao/Trabalho BD.md

## Trabalho BD.md

      
    Raw
  

              Trabalho BD.md
            
          
    TRABALHO BD


Listar o nome e a cidade das escolas onde todos os alunos residam na mesma cidade onde a escola está localizada.


SELECT e.nome Escola, c.nome Cidade 
FROM escola e 
JOIN cidade c ON c.cidade_id = e.cidade_id
JOIN turma  t ON t.escola_id = e.escola_id 
JOIN aluno  a ON a.turma_id  = t.turma_id 
JOIN pessoa p ON p.pessoa_id = a.aluno_id 
WHERE e.cidade_id = p.cidade_id
GROUP BY e.escola_id


Listar o nome e matrícula dos alunos sem nenhum contato cadastrado.


SELECT nome, matricula 
FROM pessoa p, (
  SELECT aluno_id, matricula 
  FROM aluno 
  WHERE matricula NOT IN (
   SELECT matricula 
   FROM aluno a, contato c 
   WHERE a.aluno_id = c.aluno_id )
  ) r 
  
WHERE pessoa_id = aluno_id


Listar o código e nome das turmas com mais de 5 alunos.


SELECT t.turma_id, t.nome 
FROM aluno a, turma t 
WHERE a.turma_id = t.turma_id 
GROUP BY t.nome 
HAVING COUNT(a.matricula) > 5


Listar o código, nome e titulação dos professores que ministram aulas para pelo menos três turmas diferentes.


SELECT temp.professor_id, temp.nome, temp.titulacao 
FROM ministra_turma m, (
	SELECT professor_id, nome, titulacao 
	FROM professor, pessoa  
	WHERE professor_id = pessoa_id) temp
WHERE temp.professor_id = m.professor_id  
GROUP BY temp.professor_id 
HAVING COUNT(DISTINCT m.turma_id) >= 3


Listar por disciplina o número de professores que podem ministrá-la e quantos efetivamente ministram a mesma para uma turma.


SELECT d.nome nome_disciplina, COUNT(DISTINCT p.professor_id) podem_ministrar, temp.ministrando 
FROM professor p 
  JOIN professor_disciplina pd 
    ON p.professor_id = pd.professor_id 
  JOIN disciplina d 
    ON pd.disciplina_id = d.disciplina_id 
  JOIN (
    SELECT mt.disciplina_id, COUNT(DISTINCT p.professor_id) ministrando 
    FROM professor p 
      LEFT JOIN ministra_turma mt 
        ON p.professor_id = mt.professor_id 
    GROUP BY mt.disciplina_id) temp 
   ON temp.disciplina_id = d.disciplina_id 
   GROUP BY nome_disciplina 


Listar o nome da escola e o nome dos diretores de escola que residem em cidades diferentes da cidade da escola.


 SELECT r.nome_escola, pe.nome 
 FROM pessoa pe, (
 	SELECT e.nome nome_escola, p.professor_id, e.cidade_id 
	FROM escola e, professor p 
	WHERE e.diretor_id = p.professor_id) r 
 WHERE pe.pessoa_id = r.professor_id AND r.cidade_id != pe.cidade_id


Listar por escola o número de turmas e o número de professores que ministram alguma disciplina para turmas da escola em questão.


SELECT e.nome, COUNT(t.turma_id) numero_de_turmas, temp.professores_ministrando 
FROM turma t JOIN escola e on t.escola_id = e.escola_id JOIN (
        SELECT e.escola_id id_da_escola, COUNT(DISTINCT p.professor_id) professores_ministrando 
        FROM professor p 
        JOIN ministra_turma mt 
        	ON p.professor_id = mt.professor_id 
        JOIN turma t 
        	ON t.turma_id = mt.turma_id 
        JOIN escola e 
        	ON e.escola_id = t.escola_id 
        GROUP BY id_da_escola) temp 
    ON id_da_escola = e.escola_id
    GROUP BY e.nome


Listar por escola a razão entre o número de alunos da escola e o número de professores que ministram alguma disciplina na escola em questão.


SELECT e.nome, ( temp.qtd_alunos / COUNT(DISTINCT p.professor_id) ) razao_prof_aluno 
FROM professor p JOIN ministra_turma mt 
	ON p.professor_id = mt.professor_id 
JOIN turma t 
	ON t.turma_id = mt.turma_id 
JOIN escola e 
	ON e.escola_id = t.escola_id 
JOIN (SELECT esc.escola_id, COUNT(DISTINCT a.aluno_id) qtd_alunos 
		FROM aluno a JOIN turma t 
      		ON t.turma_id = a.turma_id 
		JOIN escola esc 
      		ON esc.escola_id = t.escola_id 
		GROUP BY esc.escola_id) temp
    ON temp.escola_id = e.escola_id 
GROUP BY e.escola_id 


Listar todos os contatos dos alunos informando a matrícula e nome do aluno, nome e telefone do contato, ordenado por matrícula do aluno e nome do contato.


SELECT temp.matricula,pessoa.nome aluno, temp.nome nome_do_contato, temp.telefone telefone_do_contato 
FROM pessoa, (
	SELECT a.aluno_id, a.matricula, c.nome, c.telefone 
	FROM aluno a, contato c 
	WHERE a.aluno_id = c.aluno_id ) temp 
WHERE pessoa.pessoa_id = temp.aluno_id 
ORDER BY temp.matricula, nome_do_contato


Listar todos os professores que ministram disciplinas para apenas uma turma.


SELECT pessoa.nome 
FROM pessoa, (
	SELECT m.turma_id, p.professor_id 
	FROM professor p , ministra_turma m 
	WHERE p.professor_id = m.professor_id) temp 
WHERE pessoa.pessoa_id = temp.professor_id 
GROUP BY pessoa.nome 
HAVING COUNT(DISTINCT temp.turma_id) = 1

Update


Alterar todos os contatos vinculados a um aluno A para um aluno B.


UPDATE contato c
SET c.aluno_id = 22
WHERE c.aluno_id = 17


Consultas que utilizamos ao longo do TRABALHO


Quantidade de alunos por turma

 SELECT COUNT(a.matricula), t.nome FROM aluno a, turma t WHERE a.turma_id = t.turma_id GROUP BY t.nome
 

Utilizando join


SELECT p.nome, matricula 
FROM pessoa p, contato c
JOIN aluno a on c.aluno_id = a.aluno_id
WHERE pessoa_id = a.aluno_id
Mostrar todos os alunos que tem contato
SELECT nome, matricula 
FROM pessoa,(
 SELECT a.aluno_id, matricula 
 FROM aluno a, contato c 
 WHERE a.aluno_id = c.aluno_id 
 ) r 
WHERE pessoa_id = r.aluno_id


Quantidade de alunos por escola

SELECT e.nome nome_da_escola, COUNT(DISTINCT a.aluno_id) qtd_alunos 
FROM aluno a JOIN turma t ON t.turma_id = a.turma_id 
JOIN escola e ON e.escola_id = t.escola_id 
GROUP BY nome_da_escola