USE SPRINGFIELD;

/* ¿QUÉ PROFESIONES HAY EN LA SERIE DE LOS SIMPSONS? */

SELECT * FROM PROFESIONES;

/* O MEJOR*/

SELECT DESC_PROFESION FROM PROFESIONES;

/* PARA ASEGURARNOS QUE NO HAY REPETIDOS */

SELECT DISTINCT DESC_PROFESION FROM PROFESIONES;

/* SON TODAS DIFERENTES */

/* ¿CUÁNTAS PROFESIONES HAY? */

SELECT COUNT(*) TOTAL_PROFESIONES FROM PROFESIONES;

/* LISTA LOS PERSONAJES JUNTO A SU SEXO */

SELECT DESC_PERSONAJE, DESC_SEXO
FROM PERSONAJES PER
, SEXO SEXO
WHERE PER.ID_SEXO=SEXO.ID_SEXO; 

/* O MEJOR*/

SELECT PER.DESC_PERSONAJE, SEXO.DESC_SEXO
FROM PERSONAJES PER
, SEXO SEXO
WHERE PER.ID_SEXO=SEXO.ID_SEXO; 

/*TAMBIÉN*/
SELECT PER.DESC_PERSONAJE, SEXO.DESC_SEXO
FROM PERSONAJES PER
INNER JOIN SEXO SEXO ON PER.ID_SEXO=SEXO.ID_SEXO; 

/* ¿CUÁNTOS HOMBRES Y CUÁNTAS MUJERES HAY EN SPRINGFIELD? */

SELECT SEXO.DESC_SEXO, COUNT(*) TOTAL
FROM PERSONAJES PER
INNER JOIN SEXO SEXO ON PER.ID_SEXO=SEXO.ID_SEXO
GROUP BY SEXO.DESC_SEXO; 

/* ¿DÓNDE VIVE CADA PERSONAJE DE LOS SIMPSONS? */

SELECT DESC_PERSONAJE
, LUG.DESC_LUGAR DESC_LUGAR_RESIDENCIA
FROM PERSONAJES PER
LEFT OUTER JOIN LUGARES LUG ON PER.ID_LUGAR_RESIDENCIA=LUG.ID_LUGAR
;

/* ¿QUÉ PERSONAJES VIVEN Y TRABAJAN EN SU CASA? */

SELECT DESC_PERSONAJE
FROM PERSONAJES
WHERE ID_LUGAR_RESIDENCIA=ID_LUGAR_PROFESION;

/* ¿CUANTOS PERSONAJES HAY QUE NO SEAN DE LA FAMILIA SIMPSON NI DE LA FAMILIA FLANDERS?*/

SELECT PER.DESC_PERSONAJE, FAM.DESC_FAMILIA
FROM PERSONAJES PER
LEFT OUTER JOIN FAMILIAS FAM ON PER.ID_FAMILIA=FAM.ID_FAMILIA
WHERE FAM.DESC_FAMILIA NOT IN ('SIMPSONS','FLANDERS');

/*¿Y AGRUPADOS POR FAMILIA, DE MAYOR A MENOR NÚMERO DE MIEMBROS?*/

SELECT FAM.DESC_FAMILIA, COUNT(*) TOTAL_MIEMBROS
FROM PERSONAJES PER
LEFT OUTER JOIN FAMILIAS FAM ON PER.ID_FAMILIA=FAM.ID_FAMILIA
WHERE FAM.DESC_FAMILIA NOT IN ('SIMPSONS','FLANDERS')
GROUP BY FAM.DESC_FAMILIA
ORDER BY TOTAL_MIEMBROS DESC;

/* ¿CUÁNTAS FAMILIAS HAY DE TRES MIEMBROS?*/

SELECT FAM.DESC_FAMILIA, COUNT(*) TOTAL_MIEMBROS
FROM PERSONAJES PER
LEFT OUTER JOIN FAMILIAS FAM ON PER.ID_FAMILIA=FAM.ID_FAMILIA
GROUP BY FAM.DESC_FAMILIA
HAVING TOTAL_MIEMBROS=3;

/* ¿CUÁNTAS FAMILIAS HAY DE MÁS DE TRES MIEMBROS?*/

SELECT FAM.DESC_FAMILIA, COUNT(*) TOTAL_MIEMBROS
FROM PERSONAJES PER
LEFT OUTER JOIN FAMILIAS FAM ON PER.ID_FAMILIA=FAM.ID_FAMILIA
GROUP BY FAM.DESC_FAMILIA
HAVING TOTAL_MIEMBROS>=3;

/* ¿CUÁNTAS FAMILIAS HAY QUE TENGAN ENTRE TRES Y CINCO MIEMBROS?*/

SELECT FAM.DESC_FAMILIA, COUNT(*) TOTAL_MIEMBROS
FROM PERSONAJES PER
INNER JOIN FAMILIAS FAM ON PER.ID_FAMILIA=FAM.ID_FAMILIA
GROUP BY FAM.DESC_FAMILIA
HAVING TOTAL_MIEMBROS BETWEEN 3 AND 5;

/* ¿DE QUÉ PERSONAJES NO SABEMOS DÓNDE VIVEN? */

SELECT DESC_PERSONAJE
FROM PERSONAJES
WHERE ID_LUGAR_RESIDENCIA IS NULL;

/*¿QUÉ PERSONAJER VIVEN EN EVERGREEN TERRACE?*/

SELECT PER.DESC_PERSONAJE, LUG.DESC_LUGAR LUGAR_RESIDENCIA
FROM PERSONAJES PER
INNER JOIN LUGARES LUG ON PER.ID_LUGAR_RESIDENCIA=LUG.ID_LUGAR
AND LUG.DESC_LUGAR LIKE '%EVERGREEN TERRACE%';

/*¿EN QUÉ DIRECCIONES EXACTAMENTE*/

SELECT DISTINCT LUG.DESC_LUGAR LUGAR_RESIDENCIA
FROM PERSONAJES PER
INNER JOIN LUGARES LUG ON PER.ID_LUGAR_RESIDENCIA=LUG.ID_LUGAR
AND LUG.DESC_LUGAR LIKE '%EVERGREEN TERRACE%'
ORDER BY 1;

/* ¿POR QUÉ EL 82 DE EVERGREEN TERRACE APARECE EN ÚLTIMO LUGAR?*/

/* ¿QUÉ PERSONAJES VIVEN EN EVERGREEN TERRACE Y ADEMÁS TRABAJAN EN LA CENTRAL NUCLEAR?*/

SELECT PER.DESC_PERSONAJE, LUG1.DESC_LUGAR LUGAR_RESIDENCIA
FROM PERSONAJES PER
INNER JOIN LUGARES LUG1 ON PER.ID_LUGAR_RESIDENCIA=LUG1.ID_LUGAR
INNER JOIN LUGARES LUG2 ON PER.ID_LUGAR_PROFESION=LUG2.ID_LUGAR
AND LUG1.DESC_LUGAR LIKE '%EVERGREEN TERRACE%'
AND LUG2.DESC_LUGAR LIKE '%CENTRAL%NUCLEAR%'
;

SELECT PER.DESC_PERSONAJE, LUG1.DESC_LUGAR LUGAR_RESIDENCIA, LUG2.DESC_LUGAR LUGAR_PROFESION
FROM PERSONAJES PER
INNER JOIN LUGARES LUG1 ON PER.ID_LUGAR_RESIDENCIA=LUG1.ID_LUGAR
INNER JOIN LUGARES LUG2 ON PER.ID_LUGAR_PROFESION=LUG2.ID_LUGAR
AND LUG1.DESC_LUGAR LIKE '%EVERGREEN TERRACE%'
AND LUG2.DESC_LUGAR LIKE '%NUCLEAR%'
;

/* ¿QUÉ PERSONAJES VIVEN EN EVERGREEN TERRACE Y ADEMÁS TRABAJAN EN LA CENTRAL NUCLEAR O SON MUJERES?*/

SELECT PER.DESC_PERSONAJE, LUG1.DESC_LUGAR LUGAR_RESIDENCIA, SEXO.DESC_SEXO
FROM PERSONAJES PER
INNER JOIN LUGARES LUG1 ON PER.ID_LUGAR_RESIDENCIA=LUG1.ID_LUGAR
INNER JOIN LUGARES LUG2 ON PER.ID_LUGAR_PROFESION=LUG2.ID_LUGAR
INNER JOIN SEXO SEXO ON PER.ID_SEXO=SEXO.ID_SEXO
WHERE (LUG1.DESC_LUGAR LIKE '%EVERGREEN TERRACE%' AND LUG2.DESC_LUGAR LIKE '%NUCLEAR%') OR SEXO.DESC_SEXO='MUJER';

/* ¿cÓMO?*/

SELECT PER.DESC_PERSONAJE, LUG1.DESC_LUGAR LUGAR_RESIDENCIA, SEXO.DESC_SEXO
FROM PERSONAJES PER
INNER JOIN LUGARES LUG1 ON PER.ID_LUGAR_RESIDENCIA=LUG1.ID_LUGAR
LEFT OUTER JOIN LUGARES LUG2 ON PER.ID_LUGAR_PROFESION=LUG2.ID_LUGAR
INNER JOIN SEXO SEXO ON PER.ID_SEXO=SEXO.ID_SEXO
WHERE (LUG1.DESC_LUGAR LIKE '%EVERGREEN TERRACE%' AND LUG2.DESC_LUGAR LIKE '%NUCLEAR%') OR SEXO.DESC_SEXO='MUJER';

/* QUIERO ORDENARLOS DE MANERA ASCENDENTE EL LUGAR DE RESIDENCIA Y DE MANERA DESCENDIENTE EL SEXO*/

SELECT PER.DESC_PERSONAJE, LUG1.DESC_LUGAR LUGAR_RESIDENCIA, SEXO.DESC_SEXO
FROM PERSONAJES PER
INNER JOIN LUGARES LUG1 ON PER.ID_LUGAR_RESIDENCIA=LUG1.ID_LUGAR
LEFT OUTER JOIN LUGARES LUG2 ON PER.ID_LUGAR_PROFESION=LUG2.ID_LUGAR
INNER JOIN SEXO SEXO ON PER.ID_SEXO=SEXO.ID_SEXO
WHERE (LUG1.DESC_LUGAR LIKE '%EVERGREEN TERRACE%' AND LUG2.DESC_LUGAR LIKE '%NUCLEAR%') OR SEXO.DESC_SEXO='MUJER'
ORDER BY 2 ASC, 3 DESC;


/* LISTA SÓLO 10 LUGARES DE RESIDENCIA*/

SELECT DISTINCT LUG1.DESC_LUGAR LUGAR_RESIDENCIA
FROM PERSONAJES PER
INNER JOIN LUGARES LUG1 ON PER.ID_LUGAR_RESIDENCIA=LUG1.ID_LUGAR
LIMIT 10;

/* ¿QUÉ FAMILIAS TIENEN MASCOTAS?*/

SELECT DISTINCT FAM.DESC_FAMILIA
FROM FAMILIAS FAM
INNER JOIN MASCOTAS MAS ON FAM.ID_FAMILIA=MAS.ID_FAMILIA;

/* QUÉ PERSONAJES TIENEN MASCOTAS?*/

SELECT PER.DESC_PERSONAJE, FAM.DESC_FAMILIA, MAS.DESC_MASCOTA
FROM FAMILIAS FAM
INNER JOIN MASCOTAS MAS ON FAM.ID_FAMILIA=MAS.ID_FAMILIA
INNER JOIN PERSONAJES PER ON PER.ID_FAMILIA=FAM.ID_FAMILIA;

/* ¿QUÉ PERSONAJES TIENEN FRASES FAMOSAS? */

SELECT PER.DESC_PERSONAJE, FRA.DESC_FRASE
FROM PERSONAJES PER
INNER JOIN FRASES FRA WHERE PER.ID_PERSONAJE=FRA.ID_PERSONAJE;

/* ¿cUÁNTOS PERSONAJES DISTINTOS Y CUÁNTAS FRASES FAMOSAS TENGO? */

SELECT COUNT(DISTINCT PER.DESC_PERSONAJE) TOTAL_PERSONAJES, COUNT(DISTINCT FRA.DESC_FRASE) TOTAL_FRASES
FROM PERSONAJES PER
INNER JOIN FRASES FRA WHERE PER.ID_PERSONAJE=FRA.ID_PERSONAJE;