37 EJ

/*1. Obter os datos completos dos alumnos que nunca escribiron no foro.*/
SELECT *
FROM alumno a
WHERE email NOT IN (SELECT email FROM MEN_FORO WHERE email IS NOT NULL)

/*2. Obtén o profesor (nome) que máis mensaxes escribiu.*/
SELECT nome
FROM profesor p JOIN men_foro mf ON p.nss = mf.nss
GROUP BY nome, p.nss
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM men_foro WHERE nss IS NOT NULL GROUP BY nss)

/*3. Para cada profesor (nome) mostra o nome do curso, de entre todos os que está
capacitado para impartir, que dura máis horas*/
SELECT p.nome, c.nome
FROM profesor p JOIN capacitado ca ON p.nss = ca.nss JOIN curso c ON ca.cod_c = c.cod_c
WHERE horas >= ALL (SELECT horas FROM curso c2 JOIN capacitado ca2 ON c2.COD_C =ca2.COD_C WHERE ca2.nss= ca.nss)

/*4. Indica cantos estudantes se rexistraron en cada edición de cada curso. Mostra o código
e nome do curso, o número e ano da edición, e o número de estudantes (0, se non hai
ningún).*/
SELECT c.cod_c, c.nome, e.numero, TO_CHAR (e.data_comezo,'yyyy'), COUNT(r.email)
FROM curso c JOIN edicion e ON c.COD_C = e.COD_C LEFT JOIN rexistrase r ON e.cod_c = r.cod_c AND e.numero = r.numero
GROUP BY c.cod_c, c.nome, e.numero, e.data_comezo

/*5. Indica, para cada curso, aquelas edicións caras (con prezo superior a 100 euros).
Mostra o código e nome do curso, e a data de inicio da edición. Se un curso non ten
edicións caras, debes mostralo igualmente, cos datos da edición en branco*/
SELECT c.cod_c, c.nome, e.prezo
FROM curso c LEFT JOIN edicion e ON c.COD_C = e.COD_C
AND e.PREZO > 100

/*6. Indica, para cada curso, a edición que tivo máis estudantes rexistrados. Mostra o
código e nome do curso, e o número da edición. (Só cursos con estudantes
rexistrados).*/
SELECT c.cod_c, c.nome, e.numero
FROM curso c JOIN edicion e ON c.COD_C = e.COD_C JOIN rexistrase r ON e.COD_C = r.cod_c AND e.NUMERO = r.numero
GROUP BY c.cod_c, c.nome, e.numero
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM rexistrase WHERE cod_c = c.cod_c GROUP BY numero)

/*7. Mostra o código e nome de cada curso impartido, e o nome do método de pagamento
máis utilizado para rexistrarse no dito curso. (Só cursos con estudantes rexistrados).*/
SELECT c.cod_c, c.nome, r.metpago
FROM curso c JOIN edicion e ON c.COD_C = e.cod_c JOIN REXISTRASE r ON e.cod_c=r.cod_c AND e.NUMERO = r.numero
GROUP BY c.cod_c, c.nome, r.metpago
HAVING COUNT(*) >= ALL (SELECT COUNT(*) FROM rexistrase WHERE cod_c = c.cod_c GROUP BY metpago)

/*8. Mostrar para cada curso, o seu nome e a edición/s con maior prezo*/
SELECT c.nome, e.prezo, e.numero
FROM curso c JOIN edicion e ON c.COD_C = e.cod_c
WHERE e.prezo >= ALL (SELECT prezo FROM edicion WHERE cod_c = c.COD_C)

/*9. Mostrar o tema/s (o seu identificador e asunto) con menor número de mensaxes sen
resposta.*/
SELECT t.id_tema, t.asunto, COUNT(*) men_sin_resp
FROM tema t JOIN pertenece p ON t.ID_TEMA = p.ID_tema
JOIN men_foro m ON p.ID_MEN = m.ID_MEN
WHERE m.id_men NOT IN (SELECT resposta_de FROM men_foro WHERE resposta_de IS NOT NULL)
GROUP BY t.id_tema, t.asunto
HAVING COUNT(*) <= ALL (SELECT COUNT(*) FROM pertenece p1 JOIN men_foro m1 ON p1.ID_MEN = m1.ID_MEN
WHERE m1.ID_MEN NOT IN (SELECT resposta_de FROM MEN_FORO WHERE resposta_de IS NOT NULL)
GROUP BY p1.ID_TEMA )

/*10. Nome de curso e edición que teña o menor número de alumnos rexistrados.*/
SELECT c.nome, e.numero, COUNT(r.EMAIL)
FROM curso c JOIN edicion e ON c.COD_C = e.COD_C LEFT JOIN REXISTRASE r ON e.COD_C = r.COD_C AND e.numero = r.numero
GROUP BY c.nome, e.COD_C, e.NUMERO
HAVING COUNT(r.email) <= ALL(SELECT COUNT(r2.email)
FROM curso c2
JOIN edicion e2 ON e2.cod_c = c2.cod_c
LEFT JOIN rexistrase r2 ON r2.cod_c = e2.cod_c AND
r2.numero = e2.numero
GROUP BY e2.cod_c, e2.numero, c2.nome);

/*11. Nomes dos alumnos que escribiron en temas de cursos nos que xamais estiveron rexistrados.*/
SELECT DISTINCT nome
FROM alumno a JOIN men_foro m ON a.EMAIL = m.EMAIL
JOIN pertenece p ON m.ID_MEN = p.ID_MEN JOIN tema t ON p.ID_TEMA = t.ID_TEMA
WHERE t.cod_c NOT IN (SELECT cod_c FROM rexistrase WHERE email = a.email)

/*12. Para cada tema mostrar os profesores que máis mensaxes escribiron relacionados con el.*/
SELECT t.id_tema, t.asunto, p.nss, p.nome
FROM tema t JOIN pertenece pt ON t.ID_TEMA = pt.id_tema
JOIN men_foro m ON pt.ID_MEN = m.ID_MEN
JOIN profesor p ON m.nss = p.nss
GROUP BY t.id_tema, t.asunto, p.nss, p.nome
HAVING COUNT(*)>= ALL (SELECT COUNT(*)
FROM men_foro m1 JOIN pertenece pe1
 ON m1.id_men = pe1.id_men
WHERE m1.nss IS NOT NULL AND
pe1.id_tema=t.id_tema
 GROUP BY m1.nss)

 /*13. Numero medio de mensaxes ao día.*/
 SELECT avg(men_dia)
 FROM (SELECT COUNT(*) men_dia FROM men_foro GROUP BY DATA)

 /*14. Queremos saber cantas edicións existen en promedio para un curso*/
 SELECT avg(ed_medias)
 FROM (SELECT COUNT(*) ed_medias FROM edicion GROUP BY cod_c)

/*15. Por cada día con mensaxes, indica cantos tiveron respostas no mesmo día. Se hai
mensaxes, pero ningún foi respondido no día, indícao cun cero*/
 SELECT COUNT(m2.ID_MEN), m1.DATA
 FROM men_foro m1 LEFT JOIN men_foro m2 ON m2.ID_MEN = m1.RESPOSTA_DE AND m1.DATA=m2.DATA
 GROUP BY m1.DATA

 /*16. Considerando os días onde houbo mensaxes, indica o número medio de mensaxes con
resposta no mesmo día.*/
 SELECT avg(men_resp)
 FROM (SELECT COUNT(m2.id_men) men_resp
 FROM men_foro m1 LEFT JOIN men_foro m2 ON
 m1.resposta_de = m2.ID_MEN AND m1.DATA = m2.DATA
 GROUP BY m1.DATA)

 /*17. Queremos saber, para cada estudante, cantos temas diferentes abordou en todas as
súas mensaxes. Mostra o email e nome de cada estudante, o número de temas
tratados (0, se ningún), e o número total de mensaxes publicadas polo/a estudante (0,
se ningunha).*/
 SELECT a.email, nome, COUNT(DISTINCT p.id_tema), COUNT(m.ID_MEN)
 FROM alumno a LEFT JOIN men_foro m ON a.EMAIL = m.EMAIL
 LEFT JOIN pertenece p ON m.ID_MEN = p.ID_MEN
 GROUP BY a.email, nome

 /*18. Queremos saber, para cada edición de cada curso, cantos temas diferentes foron
abordados, e cantas mensaxes en total foron publicadas. Mostra o código e nome do
curso, o número da edición, o número de temas tratados (0, se ningún), e o número
total de mensaxes publicadas (0, se ningunha).*/
 SELECT c.cod_c, c.nome, e.numero, COUNT(DISTINCT t.id_tema), COUNT(m.id_men)
 FROM curso c JOIN edicion e ON c.cod_c = e.cod_c LEFT JOIN tema t ON e.COD_C = t.COD_C AND e.NUMERO = t.NUMERO
 LEFT JOIN pertenece p ON t.ID_TEMA = p.ID_tema LEFT JOIN men_foro m ON p.ID_MEN = m.ID_MEN
 GROUP BY c.cod_c, c.nome, e.numero

/*19. Mostrar, para cada curso que sexa prerrequisito de algún outro, o seu nome e cántos
cursos de entre os que o requiren, teñen unha duración en horas maior que el. Se non
hai ningún, mostrarase un 0.*/
 SELECT c.cod_c, c.nome, COUNT(c2.COD_C)
 FROM curso c RIGHT JOIN curso c2 ON c.COD_C = c2.REQUISITO
 AND c2.horas>c.horas
 WHERE c.COD_C IN (SELECT requisito FROM curso)
 GROUP BY c.cod_c, c.nome

 /*20. Para cada tema, amosa os alumnos (nome) que non escribiron mensaxes sobre el.*/
 SELECT t.id_tema, t.asunto, a.nome
 FROM alumno a CROSS JOIN tema t
 WHERE (a.email, t.id_tema) NOT IN (SELECT email, id_tema FROM men_foro m JOIN pertenece p ON m.ID_MEN= p.ID_MEN
 WHERE p.ID_TEMA= t.id_tema AND nss IS NULL)

/*21. Para cada tema, amosa os alumnos e profesores que non escribiron mensaxes sobre el*/
 SELECT t.id_tema, t.asunto, a.nome
 FROM alumno a CROSS JOIN tema t
 WHERE (a.email, t.id_tema) NOT IN (SELECT email, id_tema FROM men_foro m JOIN pertenece p ON m.ID_MEN= p.ID_MEN
 WHERE p.ID_TEMA= t.id_tema AND nss IS NULL)
 UNION
  SELECT t.id_tema, t.asunto, p.nome
 FROM profesor p CROSS JOIN tema t
 WHERE (p.nss, t.id_tema) NOT IN (SELECT nss, id_tema FROM men_foro m JOIN pertenece p ON m.ID_MEN= p.ID_MEN
 WHERE p.ID_TEMA= t.id_tema AND email IS NULL)

 /*22. Queremos coñecer cales son os cursos TOP da nosa academia. É dicir, cursos que non
tiveron nunca edicións sen alumnos (obviamente, se un curso non tivo edicións non
será considerado) e nas que sempre houbera matriculado máis de 2 alumnos. */
SELECT DISTINCT c.cod_c
FROM curso c JOIN edicion e ON c.COD_C = e.COD_C
WHERE c.COD_C NOT IN (SELECT COD_C
FROM edicion
WHERE (cod_c, numero) NOT IN
(SELECT DISTINCT cod_c, numero
FROM rexistrase))
AND NOT EXISTS (SELECT r.numero
FROM rexistrase r
WHERE r.cod_c = c.cod_c
GROUP BY r.numero
HAVING COUNT(*)<=2)

/*23. Mostra aqueles docentes (nss, nome) capacitados para impartir TODOS os cursos
rexistrados na BD.*/
SELECT DISTINCT nss, nome FROM profesor
WHERE nss NOT IN
(SELECT DISTINCT pnc.nss FROM
 (
 (SELECT p.nss, c.cod_c FROM profesor p CROSS JOIN curso c)
 MINUS
 (SELECT c.nss, c.COD_C FROM capacitado c)
 ) pnc
)

/*24. Mostra aqueles docentes (nss, nome) que NON ESTÁN capacitados para impartir
TODOS os cursos rexistrados na BD.*/
SELECT DISTINCT nss, nome FROM profesor
WHERE nss IN
(SELECT DISTINCT pnc.nss FROM
 (
 (SELECT p.nss, c.cod_c FROM profesor p CROSS JOIN curso c)
 MINUS
 (SELECT c.nss, c.COD_C FROM capacitado c)
 ) pnc
)

/*25. Indica, para cada posible combinación curso/profesor, se o docente está capacitado
para impartir o curso. Mostra o código e nome do curso, o nss e nome do docente, e
se está capacitado (‘Si’ ou ‘Non’)*/

(SELECT c.cod_c, c.nome, p.nss, p.nome, 'Si' AS capacitado
FROM curso c JOIN capacitado cp ON c.COD_C =cp.COD_C
JOIN profesor p ON cp.nss=p.nss)

UNION

(SELECT c.cod_c, c.nome, p.nss, p.nome, 'No' AS capacitado
FROM curso c CROSS JOIN profesor p)
MINUS

(SELECT c.cod_c, c.nome, p.nss, p.nome, 'No'
FROM curso c JOIN capacitado cp ON c.COD_C =cp.COD_C
JOIN profesor p ON cp.nss=p.nss)

/*26. Indica, para cada curso, cantas edicións caras tivo (con prezo superior a 100 euros).
Mostra o código e nome do curso, o número de edicións caras que tivo (0, se
ningunha), e o total de estudantes matriculados nelas (0, se ningún).*/
SELECT c.cod_c, c.nome, COUNT(DISTINCT e.numero), COUNT(r.email)
FROM curso c LEFT JOIN edicion e ON c.COD_C = e.COD_C AND e.PREZO >100
LEFT JOIN rexistrase r ON e.COD_C = r.COD_C AND e.NUMERO = r.NUMERO
GROUP BY c.cod_c, c.nome

/*27. Para cada edición mostrar o número de temas e alumnos rexistrados. No caso de que
non teñan temas ou alumnos ten que aparecer un 0. Debe aparecer o nome do curso.*/
SELECT c.nome, COUNT(DISTINCT t.id_tema), COUNT(DISTINCT r.email)
FROM curso c JOIN edicion e ON c.COD_C = e.COD_C LEFT JOIN tema t ON e.cod_c = t.COD_C AND e.NUMERO = t.NUMERO
LEFT JOIN rexistrase r ON e.COD_C = r.COD_C AND e.NUMERO = r.NUMERO
GROUP BY c.COD_C ,c.NOME , e.numero

/*28. Nomes de todos os profesores e número de mensaxes que escribiron en temas pertencentes a
cursos que eles imparten.*/
SELECT p.nome, COUNT(DISTINCT m.ID_MEN)
FROM profesor p LEFT JOIN men_foro m ON p.NSS = m.nss
LEFT JOIN edicion e ON e.nss = p.nss
LEFT JOIN tema t ON e.COD_C = t.COD_C AND e.numero = t.numero
GROUP BY p.nome, p.NSS

/*29. Para cada edición de cada curso, mostrar o número de mensaxes publicados dentro da
duración da edición (data comezo e data fin, inclusive). Mostrar 0 cando non haxa
mensaxes.*/
SELECT e.cod_c, e.numero, COUNT(DISTINCT m.id_men)
FROM edicion e LEFT JOIN tema t ON e.COD_C = t.COD_C AND e.NUMERO = t.NUMERO
LEFT JOIN pertenece p ON t.ID_TEMA = p.ID_TEMA LEFT JOIN men_foro m ON p.ID_MEN = m.ID_MEN
AND m.DATA >= e.DATA_COMEZO AND m.DATA <= e.DATA_FIN
GROUP BY e.COD_C , e.numero

/*30. Para cada edición de un curso, mostrar o profesor que a imparte (nome), e cal é a súa
diferenza de antigüidade con respecto ao profesor con maior experiencia que teña
impartido ese mesmo curso en calquera outra edición.*/
SELECT p.nome, to_char(p.data_alta, 'YYYY')- maximo
FROM profesor p JOIN edicion e ON p.NSS = e.nss JOIN
(SELECT e1.cod_c, to_char(MIN(p1.DATA_ALTA), 'YYYY') maximo
FROM profesor p1 JOIN edicion e1
ON p1.NSS = e1.nss
GROUP BY e1.cod_c) f
ON e.COD_C = f.cod_c

/*31. Mostra os cursos nos que un alumno se matriculou algunha vez (independentemente
da edición á que fose o de se foi a varias edicións dun mesmo curso) con un texto ‘SI’ e
aqueles nos que nunca se matriculou co texto ‘NON’. Ter en conta todos los alumnos
(incluso se un alumno aínda non se matriculou en ningún curso). Ordenar o resultado,
primeiro, por nome do alumno, e segundo, por nome de curso.*/
SELECT DISTINCT a.nome nombre_alumno, c.nome nombre_curso, 'Si' AS matriculado
FROM alumno a JOIN rexistrase r ON a.EMAIL = r.EMAIL
JOIN curso c ON r.cod_c = c.COD_C

UNION

SELECT a.nome, c.nome, 'No' AS matriculado
FROM alumno a CROSS JOIN curso c
MINUS
SELECT a.nome, c.nome , 'No'
FROM alumno a JOIN rexistrase r ON a.EMAIL = r.EMAIL
LEFT JOIN curso c ON r.cod_c = c.COD_C NATURAL JOIN alumno

ORDER BY nombre_alumno, nombre_curso;


/*32. Para cada edicion de curso cantos mensaxes de alumnos foron respondidos polos
profesores. Mostrar nome do curso e número de edición.*/
SELECT c.nome, e.numero, COUNT(DISTINCT m.ID_MEN)
FROM curso c JOIN edicion e ON c.COD_C = e.COD_C
JOIN tema t ON e.COD_C = t.COD_C AND e.NUMERO = t.NUMERO
JOIN pertenece p ON t.ID_TEMA = p.ID_TEMA
JOIN men_foro m ON p.ID_MEN = m.id_men AND m.nss IS NULL
JOIN men_foro m2 ON m.ID_MEN = m2.RESPOSTA_DE AND m2.nss IS NOT NULL
GROUP BY c.nome, c.COD_C , e.numero

 /*33. Para cada tema cantos mensaxes de alumnos foron respondidos polos profesores e
cantos mensaxes de profesores foron respondidos polos alumnos. Mostrar o asunto
do tema.*/
SELECT t.asunto, c_alum, c_prof
FROM tema t JOIN (SELECT id_tema, COUNT(*) c_alum FROM men_foro m JOIN men_foro m2 ON m2.RESPOSTA_DE = m.id_men
JOIN pertenece p ON p.ID_MEN = m.ID_MEN
WHERE m.nss IS NULL AND m2.nss IS NOT NULL GROUP BY id_tema) m_alumno_resp_profesor
ON m_alumno_resp_profesor.id_tema = t.id_tema
JOIN (SELECT id_tema, COUNT(*) c_prof
FROM men_foro m JOIN MEN_FORO m2 ON m2.RESPOSTA_DE = m.ID_MEN
JOIN PERTENECE p ON p.ID_MEN = m.ID_MEN WHERE m.nss IS NOT NULL AND m2.nss IS NULL
GROUP BY id_tema) m_prof_resp_alumno
ON m_prof_resp_alumno.id_tema = t.id_tema

/*34. Amosa os alumnos (email) que estiveron rexistrados en polo menos tres edicións de
cursos que se celebraban solapadas no tempo (polo menos un día).*/

SELECT r.email, e.cod_c, e.numero, e.data_comezo, e.data_fin
 FROM rexistrase r JOIN edicion e ON r.cod_c=e.cod_c
 AND r.numero=e.numero
 WHERE 3 >=(SELECT COUNT(*) cuenta
 FROM rexistrase r1 JOIN edicion e1 ON r1.cod_c=e1.cod_c
 AND r1.numero=e1.numero
 WHERE r1.email=r.email
 AND ((e1.data_comezo <= e.data_fin)
 AND (e1.data_fin>=e.data_comezo)));

/*35. Para cada alumno indicar o importe medio das súas matrículas en cursos, e canto difire
este con respecto ao gasto medio por matrícula da súa organización.*/

SELECT a.email, a.organizacion, abs(media_a- media_b)
FROM (SELECT r.email, organizacion, avg(prezo) media_a
FROM rexistrase r JOIN edicion e ON r.cod_c = e.COD_C AND r.numero = e.numero
JOIN alumno a ON a.email = r.email
GROUP BY r.email, organizacion) a
JOIN
(SELECT organizacion, avg(prezo) media_b
FROM rexistrase r JOIN edicion e ON r.cod_c = e.COD_C AND r.numero = e.numero
JOIN alumno a ON a.email = r.email
GROUP BY organizacion) b
ON a.ORGANIZACION = b.organizacion

/*36. Cal é a diferenza do importe medio de todas as matrículas pagadas por transferencia e
as pagadas por tarxeta*/
SELECT transferencia-tarxeta
FROM (SELECT avg(prezo) transferencia FROM edicion e JOIN rexistrase r ON e.COD_C = r.COD_C AND e.NUMERO = r.NUMERO
WHERE metpago = 'transferencia')
CROSS JOIN (SELECT avg(prezo) tarxeta FROM edicion e JOIN rexistrase r ON e.COD_C = r.COD_C AND e.NUMERO = r.NUMERO
WHERE metpago = 'tarxeta' )

/*37. Para cada alumno amosa o importe medio das súas matrículas e a diferenza co
importe medio de todas as matrículas de todos alumnos.*/
SELECT r.email, avg(e.prezo)-

(SELECT avg(e1.prezo)
FROM rexistrase r1 JOIN edicion e1 ON r1.COD_C = e1.COD_C AND r1.NUMERO = e1.NUMERO)

FROM rexistrase r JOIN edicion e ON r.COD_C = e.COD_C AND r.NUMERO = e.NUMERO
GROUP BY r.email