Prácticas GD 2013 1,2,3,4,5,7

/*
 * Práctica SQL
 * Autores de la práctica: Ing. Vilma Martin, Ing. Adrián Meca, Ing. María Inés Seguenzia
 * Versión: 9
 * Entorno donde se realizaron los ejercicios:
 * MariaDB versión 10.0.4
 * phpMyAdmin 3.4.11.1
 */

--  Práctica Número I: Sentencia SELECT
USE agencia_personal;

-- Práctica I » Ejercicio I
SHOW FULL COLUMNS FROM empresas;
SELECT * FROM empresas;

-- Práctica I » Ejercicio II
SHOW FULL COLUMNS FROM personas;
SELECT apellido, nombre, fecha_registro_agencia FROM personas;

-- Práctica I » Ejercicio III
SELECT * FROM titulos ORDER BY tipo_titulo ASC;

-- Práctica I » Ejercicio IV
SELECT * FROM personas WHERE dni = 28675888;

-- Práctica I » Ejercicio V
SELECT * FROM personas WHERE dni = 27890765 or dni = 29345777 or dni = 31345778;

-- Práctica I » Ejercicio VI
SELECT * FROM personas WHERE apellido LIKE 'G%';

-- Práctica I » Ejercicio VII
SELECT sueldo, dni, fecha_nacimiento FROM personas, contratos WHERE personas.dni = contratos.dni AND fecha_nacimiento BETWEEN '1980-01-01' AND '2000-01-01' AND fecha_finalizacion_contrato IS NULL;
/*
 * Nota: la consulta funciona perfectamente pero en la base de datos no hay ningún registro que cumpla con estas condiciones.
 * Podemos sacar la condición de que el contrato no esté caducado para ver algunos resultados:
 */
SELECT sueldo, dni, fecha_nacimiento FROM personas, contratos WHERE personas.dni = contratos.dni AND fecha_nacimiento BETWEEN '1980-01-01' AND '2000-01-01';

-- Práctica I » Ejercicio VIII
SELECT * FROM solicitudes_empresas ORDER BY fecha_solicitud ASC;

-- Práctica I » Ejercicio IX
SELECT * FROM antecedentes WHERE fecha_hasta IS NULL ORDER BY fecha_desde ASC;

-- Práctica I » Ejercicio X
SELECT * FROM antecedentes WHERE fecha_hasta IS NOT NULL AND fecha_hasta BETWEEN '2006-06-01' AND '2006-12-31';

-- Práctica I » Ejercicio XI
SELECT nro_contrato AS 'Nro Contrato', dni AS 'DNI', sueldo AS 'Salario', cuit AS 'CUIT' FROM contratos WHERE sueldo > 2000 AND (cuit = '30-10504876-5' OR cuit = '30-21098732-4');

-- Práctica I » Ejercicio XII
SELECT * FROM titulos WHERE desc_titulo LIKE 'Tecnico%';

-- Práctica I » Ejercicio XIII
SELECT * FROM solicitudes_empresas WHERE cod_cargo = 6 OR fecha_solicitud > '2007-09-21' OR sexo = 'Femenino';

-- Práctica I » Ejercicio XIV
SELECT * FROM contratos WHERE sueldo > 2000 AND fecha_finalizacion_contrato IS NULL;

-- Práctica I » Ejercicio XV
SELECT * FROM solicitudes_empresas WHERE cod_cargo = 6 OR sexo IS NULL OR fecha_solicitud > '2007-02-21';

--  Práctica Número II: INNER JOIN

-- Práctica II » Ejercicio I
SELECT t1.nombre, t1.apellido, t2.sueldo, t1.dni FROM personas AS t1 INNER JOIN contratos AS t2 ON t1.dni = t2.dni;

-- Práctica II » Ejercicio II
SELECT t1.fecha_registro_agencia, t1.dni, t2.nro_contrato, t2.fecha_incorporacion, IFNULL(t2.fecha_caducidad,"Sin Fecha") FROM personas AS t1 INNER JOIN contratos AS t2 ON t2.dni = t1.dni INNER JOIN empresas AS t3 ON t3.cuit = t2.cuit AND (t3.razon_social = 'Viejos Amigos' OR t3.razon_social = 'Traigame eso');

-- Práctica II » Ejercicio III
SELECT t1.razon_social, t1.direccion, t1.e_mail, t3.desc_cargo, t2.anios_experiencia FROM empresas AS t1 INNER JOIN solicitudes_empresas AS t2 ON t1.cuit = t2.cuit INNER JOIN cargos AS t3 ON t3.cod_cargo = t2.cod_cargo ORDER BY t2.fecha_solicitud ASC, t3.desc_cargo ASC;

-- Práctica II » Ejercicio IV
SELECT t1.dni, t1.nombre, t1.apellido, t3.desc_titulo FROM personas AS t1 INNER JOIN personas_titulos AS t2 ON t1.dni = t2.dni INNER JOIN titulos AS t3 ON t3.cod_titulo = t2.cod_titulo WHERE (t3.tipo_titulo = 'Educacion no formal') OR (t3.desc_titulo = 'Bachiller');

-- Práctica II » Ejercicio V
SELECT t1.nombre, t1.apellido, t3.desc_titulo FROM personas AS t1 INNER JOIN personas_titulos AS t2 ON t1.dni = t2.dni INNER JOIN titulos AS t3 ON t3.cod_titulo = t2.cod_titulo;

-- Práctica II » Ejercicio VI
SELECT CONCAT(t1.apellido,' ',t1.nombre,' tiene como referencia a ',IFNULL(t2.persona_contacto,"No tiene contacto"),' cuando trabajo en ',t3.razon_social) FROM personas AS t1 INNER JOIN antecedentes AS t2 ON t2.dni = t1.dni INNER JOIN empresas AS t3 ON t3.cuit = t2.cuit;

-- Práctica II » Ejercicio VII
SELECT t1.razon_social, t2.fecha_solicitud, t3.desc_cargo, IFNULL(t2.edad_minima,'Sin Especificar'),IFNULL(t2.edad_maxima,'Sin Especificar'),t4.resultado_final FROM empresas AS t1 INNER JOIN solicitudes_empresas AS t2 ON t1.cuit = t2.cuit INNER JOIN cargos AS t3 ON t2.cod_cargo = t3.cod_cargo INNER JOIN entrevistas AS t4 ON t1.cuit = t4.cuit;

-- Práctica II » Ejercicio VIII
SELECT CONCAT(t1.nombre,' ',t1.apellido) AS Postulante, t3.desc_cargo AS Cargo FROM personas AS t1 INNER JOIN antecedentes AS t2 ON t1.dni = t2.dni INNER JOIN cargos as t3 ON t2.cod_cargo = t3.cod_cargo;

-- Práctica II » Ejercicio IX
SELECT t2.razon_social AS 'Razón Social', t3.desc_cargo AS 'Cargo', t5.desc_evaluacion as 'Descripción de Evaluación', t4.resultado AS 'resultado' FROM entrevistas AS t1 INNER JOIN empresas AS t2 ON t1.cuit = t2.cuit INNER JOIN cargos AS t3 ON t1.cod_cargo = t3.cod_cargo INNER JOIN entrevistas_evaluaciones AS t4 ON t1.nro_entrevista = t4.nro_entrevista INNER JOIN evaluaciones AS t5 ON t4.cod_evaluacion = t5.cod_evaluacion;

-- Práctica II » Ejercicio X
SELECT t1.cuit, t1.razon_social, IFNULL(t2.fecha_solicitud,'Sin Solicitud'), IFNULL(t3.desc_cargo,'Sin Solicitud') FROM empresas AS t1 LEFT JOIN solicitudes_empresas AS t2 ON t1.cuit = t2.cuit LEFT JOIN cargos AS t3 ON t2.cod_cargo = t3.cod_cargo;

-- Práctica II » Ejercicio XI
SELECT t1.cuit, t1.razon_social, t3.desc_cargo, IFNULL(t5.dni, 'Sin Contrato') AS DNI, IFNULL(t5.apellido, 'Sin Contrato') AS Apellido, IFNULL(t5.nombre, 'Sin Contrato') AS Nombre FROM empresas AS t1 INNER JOIN solicitudes_empresas AS t2 ON t1.cuit = t2.cuit INNER JOIN cargos AS t3 ON t2.cod_cargo = t3.cod_cargo LEFT JOIN contratos AS t4 ON t1.cuit = t4.cuit AND t2.fecha_solicitud = t4.fecha_solicitud LEFT JOIN personas AS t5 ON t4.dni = t5.dni;

-- Práctica II » Ejercicio XII
SELECT t1.cuit, t3.razon_social, t4.desc_cargo FROM solicitudes_empresas AS t1 LEFT JOIN contratos AS t2 ON t1.cuit = t2.cuit AND t1.fecha_solicitud = t2.fecha_solicitud INNER JOIN empresas AS t3 ON t1.cuit = t3.cuit INNER JOIN cargos AS t4 ON t1.cod_cargo = t4.cod_cargo WHERE nro_contrato IS NULL;

-- Práctica II » Ejercicio XIII
SELECT t1.desc_cargo, IFNULL(t2.dni,'Sin Antecedente'), IFNULL(t3.apellido,'Sin Antecedente'), t4.razon_social FROM cargos AS t1 LEFT JOIN antecedentes AS t2 ON t1.cod_cargo = t2.cod_cargo LEFT JOIN personas AS t3 ON t2.dni = t3.dni LEFT JOIN empresas AS t4 ON t2.cuit = t4.cuit;

--  Práctica Número II.II: SELF JOIN
USE afatse;

-- Práctica II.II » Ejercicio XIV
SELECT t1.cuil AS 'Cuil Instructor', t1.nombre, t1.apellido, t1.cuil_supervisor AS 'Cuil Supervisor', t2.nombre, t2.apellido FROM instructores AS t1 INNER JOIN instructores AS t2 ON t1.cuil_supervisor = t2.cuil;

-- Práctica II.II » Ejercicio XV
SELECT t1.cuil AS 'Cuil Instructor', t1.nombre, t1.apellido, t1.cuil_supervisor AS 'Cuil Supervisor', t2.nombre, t2.apellido FROM instructores AS t1 LEFT JOIN instructores AS t2 ON t1.cuil_supervisor = t2.cuil;

-- Práctica II.II » Ejercicio XVI
/*
 * Nota: creo que está bien, en la práctica el resultado de prueba está mal, como fecha de corrección figuran algunas del 2007
 */
SELECT t6.cuil, t6.nombre, t6.apellido, t5.nombre, t5.apellido, t1.nom_plan, t1.nro_curso, t2.nombre, t2.apellido, t1.nro_examen, t1.fecha_evaluacion, t1.nota FROM evaluaciones AS t1 INNER JOIN alumnos AS t2 ON t1.dni = t2.dni AND t1.fecha_evaluacion BETWEEN '2008-01-01' AND '2008-12-31' INNER JOIN cursos_instructores AS t3 ON t1.nro_curso = t3.nro_curso AND t1.cuil = t3.cuil INNER JOIN instructores AS t5 ON t4.cuil = t5.cuil LEFT JOIN instructores AS t6 ON t5.cuil = t6.cuil_supervisor ORDER BY t5.cuil ASC;

--  Práctica Número III: Funciones de presentación de datos
USE agencia_personal;

-- Práctica III » Ejercicio I
/*
 * Nota: va a tirar una consulta vacía porque los registros son del año 2008
 */
SELECT nro_contrato, fecha_incorporacion, fecha_finalizacion_contrato, ADDDATE(fecha_incorporacion, INTERVAL 30 DAY) FROM contratos WHERE fecha_finalizacion_contrato > NOW();

-- Práctica III » Ejercicio II
SELECT t1.nro_contrato, t2.razon_social, t3.apellido, t3.nombre, t1.fecha_incorporacion, IFNULL(t1.fecha_caducidad,'Contrato Vigente') FROM contratos AS t1 INNER JOIN empresas AS t2 ON t1.cuit = t2.cuit INNER JOIN personas AS t3 ON t1.dni = t3.dni;

-- Práctica III » Ejercicio III
SELECT *,DATE_DIFF(fecha_finalizacion_contrato,fecha_caducidad) AS diferencia FROM contratos WHERE fecha_caducidad < fecha_finalizacion_contrato;

-- Práctica III » Ejercicio IV
SELECT t3.cuit, t3.razon_social, t3.direccion, t1.anio_contrato, t1.mes_contrato, t1.importe_comision, ADDDATE(DATE(NOW()), INTERVAL 2 MONTH) as Vencimiento FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato AND t1.fecha_pago IS NULL INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit;

-- Práctica III » Ejercicio V
SELECT CONCAT(nombre,' ',apellido) AS 'Nombre y Apellido', fecha_nacimiento, DAY(fecha_nacimiento) AS dia, MONTH(fecha_nacimiento) AS mes, YEAR(fecha_nacimiento) AS 'año' FROM personas;

--  Práctica Número IV: GROUP BY - HAVING

-- Práctica IV » Ejercicio I
SELECT t3.razon_social, SUM(t1.importe_comision) FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t3.cuit = t2.cuit GROUP BY t3.razon_social HAVING t3.razon_social = 'Traigame eso';

-- Práctica IV » Ejercicio II
SELECT t3.razon_social, SUM(t1.importe_comision) FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t3.cuit = t2.cuit GROUP BY t3.razon_social;

-- Práctica IV » Ejercicio III
/*
 * NO FUNCA, PREGUNTAR
 */
SELECT t2.nombre_entrevistador, t1.cod_evaluacion, AVG(t1.resultado) AS 'Promedio', STDDEV(t1.resultado) AS 'Desviación Estándar', VARIANCE(t1.resultado) AS 'Varianza' FROM entrevistas_evaluaciones AS t1 INNER JOIN entrevistas AS t2 ON t1.nro_entrevista = t2.nro_entrevista GROUP BY t1.cod_evaluacion ORDER BY AVG(t1.resultado) DESC, STDDEV(t1.resultado) ASC;

-- Práctica IV » Ejercicio IV
SELECT t2.nombre_entrevistador, t1.cod_evaluacion, AVG(t1.resultado) AS Promedio, STDDEV(t1.resultado) AS 'Desviación Estándar', VARIANCE(t1.resultado) AS 'Varianza' FROM entrevistas_evaluaciones AS t1 INNER JOIN entrevistas AS t2 ON t1.nro_entrevista = t2.nro_entrevista AND t2.nombre_entrevistador = 'Angelica Doria' GROUP BY t1.cod_evaluacion HAVING Promedio > 71 ORDER BY AVG(t1.resultado) DESC, STDDEV(t1.resultado) ASC;

-- Práctica IV » Ejercicio V
SELECT nombre_entrevistador, COUNT(*) FROM entrevistas WHERE nombre_entrevistador = 'Angelica Doria' AND fecha_entrevista BETWEEN '2007-10-01' AND '2007-10-31' GROUP BY nombre_entrevistador;

-- Práctica IV » Ejercicio VI
SELECT nombre_entrevistador, COUNT(*) AS cantidad FROM entrevistas GROUP BY nombre_entrevistador ORDER BY cantidad DESC;

-- Práctica IV » Ejercicio VII
SELECT nombre_entrevistador, COUNT(*) AS cantidad FROM entrevistas GROUP BY nombre_entrevistador HAVING cantidad > 1 ORDER BY cantidad DESC;

-- Práctica IV » Ejercicio VIII
SELECT nro_contrato, COUNT(*) AS total, COUNT(fecha_pago) AS cant_pagadas, COUNT(*)-COUNT(fecha_pago) AS cant_a_pagar FROM comisiones GROUP BY nro_contrato;

-- Práctica IV » Ejercicio IX
SELECT nro_contrato, COUNT(nro_contrato), COUNT(fecha_pago)*100/COUNT(*) AS porcentaje_pagadas, (COUNT(*)-COUNT(fecha_pago))*(100/COUNT(*)) AS porcentaje_a_pagar FROM comisiones GROUP BY nro_contrato;

-- Práctica IV » Ejercicio X
SELECT COUNT(t1.mes_contrato) AS meses_pendientes, t3.razon_social FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit WHERE t1.fecha_pago IS NULL GROUP BY t2.cuit;

-- Práctica IV » Ejercicio XI
SELECT t2.cuit, t2.razon_social, COUNT(t1.cuit) AS cant_solicitudes FROM solicitudes_empresas AS t1 INNER JOIN empresas AS t2 ON t1.cuit = t2.cuit GROUP BY t1.cuit;

-- Práctica IV » Ejercicio XII
/*
 * Revisar
 */
SELECT t3.cuit, t3.razon_social, t1.cod_cargo, COUNT(*) FROM solicitudes_empresas AS t1 INNER JOIN contratos AS t2 ON t1.cod_cargo = t2.cod_cargo AND t1.fecha_solicitud = t2.fecha_solicitud INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit GROUP BY t1.cod_cargo

-- Práctica IV » Ejercicio XIII
SELECT t1.cuit, t1.razon_social, IFNULL(COUNT(t2.cuit),'0') AS 'Cant de Personas' FROM empresas AS t1 LEFT JOIN antecedentes AS t2 ON t1.cuit = t2.cuit GROUP BY t2.cuit

-- Práctica IV » Ejercicio XIV
SELECT t1.cod_cargo, t1.desc_cargo, COUNT(t2.cod_cargo) AS 'Cant. de Solicitudes' FROM cargos AS t1 LEFT JOIN solicitudes_empresas AS t2 ON t1.cod_cargo = t2.cod_cargo GROUP BY t1.cod_cargo ORDER BY COUNT(t2.cod_cargo) DESC

-- Práctica IV » Ejercicio XV
SELECT t1.cod_cargo, t1.desc_cargo, COUNT(t2.cod_cargo) AS 'Cant. de Solicitudes' FROM cargos AS t1 LEFT JOIN solicitudes_empresas AS t2 ON t1.cod_cargo = t2.cod_cargo GROUP BY t1.cod_cargo HAVING COUNT(t2.cod_cargo) < 2 ORDER BY COUNT(t2.cod_cargo) DESC

--  Práctica Número V: Subconsultas y Tablas Temporales

-- Práctica V » Ejercicio I
CREATE TEMPORARY TABLE tmp_empresas (
    SELECT t1.* FROM empresas AS t1 INNER JOIN contratos AS t2 ON t1.cuit = t2.cuit INNER JOIN personas AS t3 ON t2.dni = t3.dni WHERE t3.nombre = 'Stefanía' AND t3.apellido = 'Lopez'
);

SELECT t1.dni, t1.apellido, t1.nombre FROM personas AS t1 INNER JOIN contratos AS t2 ON t1.dni = t2.dni INNER JOIN tmp_empresas AS t3 ON t2.cuit = t3.cuit;

DROP TEMPORARY TABLE IF EXISTS tmp_empresas;

-- Práctica V » Ejercicio II
SELECT * FROM contratos AS t1 INNER JOIN personas AS t2 ON t1.dni = t2.dni WHERE t1.sueldo > (SELECT MAX(t3.sueldo) FROM contratos AS t3 INNER JOIN empresas AS t4 ON t3.cuit = t4.cuit WHERE t4.razon_social = 'Viejos Amigos');

-- Práctica V » Ejercicio III
SELECT t2.cuit, t1.dni, t2.sueldo FROM personas AS t1 INNER JOIN contratos AS t2 ON t1.dni = t2.dni WHERE t2.sueldo > (SELECT AVG(t3.sueldo) AS promedio FROM contratos AS t3 WHERE t3.cuit = t2.cuit GROUP BY t3.cuit)

-- Práctica V » Ejercicio IV
SELECT t1.apellido, t1.nombre FROM personas AS t1 INNER JOIN personas_titulos AS t2 ON t1.dni = t2.dni INNER JOIN titulos AS t3 ON t2.cod_titulo = t3.cod_titulo WHERE t1.dni NOT IN (SELECT t4.dni FROM personas AS t4 INNER JOIN personas_titulos AS t5 ON t4.dni = t5.dni INNER JOIN titulos AS t6 ON t5.cod_titulo = t6.cod_titulo WHERE t6.tipo_titulo = 'Educacion no formal' OR t6.tipo_titulo = 'Terciario') GROUP BY t1.dni;

-- Práctica V » Ejercicio V
SELECT t3.razon_social, SUM(t1.importe_comision),AVG(t1.importe_comision) FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit GROUP BY t3.cuit HAVING AVG(t1.importe_comision) > (SELECT AVG(t4.importe_comision) FROM comisiones AS t4 INNER JOIN contratos AS t5 ON t4.nro_contrato = t5.nro_contrato INNER JOIN empresas AS t6 ON t5.cuit = t6.cuit WHERE t6.razon_social = 'Traigame eso' GROUP BY t6.cuit);

-- Práctica V » Ejercicio VI
SELECT t3.razon_social, t4.nombre, t4.apellido, t2.nro_contrato, t1.mes_contrato, t1.anio_contrato, t1.importe_comision FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit INNER JOIN personas AS t4 ON t2.dni = t4.dni WHERE t1.importe_comision < (SELECT AVG(t5.importe_comision) FROM comisiones AS t5)

-- Práctica V » Ejercicio VII
/*
 * Este ejercicio se puede resolver sin usar tablas temporales ni subconsultas
 */
SELECT AVG(t1.importe_comision) AS promedio_comisiones, t3.razon_social,t3.cuit FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit GROUP BY t2.cuit HAVING promedio_comisiones = MAX(promedio_comisiones) OR promedio_comisiones = MIN(promedio_comisiones);

-- Práctica V » Ejercicio VIII
SELECT t3.razon_social, AVG(t1.importe_comision) FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit GROUP BY t3.cuit HAVING AVG(t1.importe_comision) > (SELECT AVG(t4.importe_comision) FROM comisiones AS t4);

-- Práctica V.II » Ejercicio IX
USE DATABASE afatse;

/*
 * Este ejercicio se puede resolver sin usar tablas temporales ni subconsultas
 */
SELECT t1.cuil,t2.fecha_ini FROM cursos_instructores AS t1 INNER JOIN cursos AS t2 ON t1.nro_curso = t2.nro_curso AND t1.nom_plan = t2.nom_plan  WHERE t2.nom_plan = 'Marketing 1' GROUP BY t1.nro_curso HAVING YEAR(t2.fecha_ini) = 2007 AND YEAR(t2.fecha_ini) != 2009;

-- Práctica V.II » Ejercicio X
/*
 * Este ejercicio se puede resolver sin usar tablas temporales ni subconsultas
 */
SELECT t1.nro_curso, t1.fecha_ini, t2.nom_plan, COUNT(t2.dni), t1.cupo FROM cursos AS t1 INNER JOIN inscripciones AS t2 ON t1.nro_curso = t2.nro_curso AND t1.nom_plan = t2.nom_plan WHERE YEAR(t1.fecha_ini) > 2008 GROUP BY t2.nom_plan HAVING ((COUNT(t2.dni)/t1.cupo)*100) < 80;

-- Práctica V.II » Ejercicio XI
CREATE TEMPORARY TABLE inscripciones_tmp (
    SELECT COUNT(*) AS total FROM inscripciones
);
SELECT t1.nom_plan, COUNT(*), ((COUNT(*)/total)*100) AS '% total' FROM inscripciones_tmp AS t3, cursos AS t1 INNER JOIN inscripciones AS t2 ON t1.nro_curso = t2.nro_curso AND t1.nom_plan = t2.nom_plan GROUP BY t2.nom_plan;

DROP TEMPORARY TABLE IF EXISTS inscripciones_tmp;

-- Práctica V.II » Ejercicio XII
CREATE TEMPORARY TABLE promedios_tmp (
    SELECT AVG(nota) AS promedio_total,nro_curso FROM evaluaciones GROUP BY nro_curso
);

SELECT t2.nombre, AVG(t1.nota), t3.promedio_total FROM evaluaciones AS t1 INNER JOIN alumnos AS t2 ON t1.dni = t2.dni INNER JOIN promedios_tmp AS t3 ON t1.nro_curso = t3.nro_curso GROUP BY t1.dni;

DROP TEMPORARY TABLE IF EXISTS promedios_tmp;

-- Práctica V.II » Ejercicio XIII
/*
 * Esta consulta devuelve un valor nulo, pero se puede cambiar el año tanto en la tabla temporal como en la consulta para ver que funciona
 */
CREATE TEMPORARY TABLE valores_planes_tmp (
    SELECT AVG(valor_plan) as promedio_actual FROM valores_plan WHERE YEAR(fecha_desde_plan) = '2009'
);

SELECT t1.nom_plan, AVG(t2.valor_plan), t3.promedio_actual FROM valores_planes_tmp AS t3, evaluaciones AS t1 INNER JOIN valores_plan AS t2 ON t1.nom_plan = t2.nom_plan AND YEAR(t2.fecha_desde_plan) = YEAR(t1.fecha_evaluacion) WHERE YEAR(t1.fecha_evaluacion) = '2009' AND AVG(t2.valor_plan) > promedio_actual GROUP BY t1.nom_plan;

DROP TEMPORARY TABLE IF EXISTS valores_planes_tmp;

-- Práctica V.II » Ejercicio XIV
CREATE TEMPORARY TABLE principito_tmp(
    SELECT COUNT(*) AS veces_principito FROM inscripciones AS t1 INNER JOIN alumnos AS t2 ON t1.dni = t2.dni WHERE t2.nombre = 'Antoine de' AND t2.apellido = 'Saint-Exupery' GROUP BY t1.dni
);

SELECT (COUNT(*)-t3.veces_principito), t3.veces_principito, t2.nombre, t2.apellido FROM principito_tmp AS t3, inscripciones AS t1 INNER JOIN alumnos AS t2 ON t1.dni = t2.dni GROUP BY t1.dni HAVING COUNT(*) > t3.veces_principito;

-- Práctica V.II » Ejercicio XV
SELECT t1.nombre, t1.apellido, t1.dni FROM alumnos AS t1 WHERE dni NOT IN (SELECT dni FROM cuotas WHERE fecha_pago IS NULL GROUP BY dni);

-- Práctica V.II » Ejercicio XVI
/*
 * REVISAR, ESTÁ MAL
 */
CREATE TEMPORARY TABLE alumnos_al_dia_tmp(
    SELECT t1.dni, t1.nombre, t1.apellido FROM alumnos AS t1 WHERE t1.dni NOT IN (SELECT dni FROM cuotas WHERE fecha_pago IS NULL GROUP BY dni)
);

SELECT t2.nombre, t2.apellido, t1.nom_plan FROM evaluaciones AS t1 INNER JOIN alumnos_al_dia_tmp AS t2 ON t1.dni = t2.dni WHERE t1.nota = MAX(t1.nota) GROUP BY t2.dni;

-- Práctica V.II » Ejercicio XVII
/*
 * WTF?
 */

--  Práctica Número VII: Variables

-- Práctica VII » Ejercicio 1
SELECT AVG(importe_comision) FROM comisiones WHERE fecha_pago IS NOT NULL INTO @promedio_pagos_comisiones;
SELECT @promedio_pagos_comisiones;

-- Práctica VII » Ejercicio II
SELECT AVG(importe_comision), COUNT(importe_comision), SUM(importe_comision) FROM comisiones WHERE fecha_pago IS NOT NULL INTO @promedio_pagos_comisiones,@cant_pagos_comisiones,@suma_pagos_comisiones;

SELECT @promedio_pagos_comisiones, @cant_pagos_comisiones, @suma_pagos_comisiones;

-- Práctica VII » Ejercicio III
SELECT AVG(importe_comision) FROM comisiones WHERE fecha_pago IS NOT NULL INTO @prom;

SELECT t3.razon_social, MONTH(t2.fecha_incorporacion), YEAR(t2.fecha_incorporacion), t2.nro_contrato, t4.nombre, t4.apellido, t1.importe_comision FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit INNER JOIN personas AS t4 ON t2.dni = t4.dni WHERE t1.importe_comision < @prom;

-- Práctica IV » Ejercicio IV
SELECT AVG(importe_comision) FROM comisiones WHERE fecha_pago IS NOT NULL INTO @promedio_pagos_comisiones;

SELECT IFNULL(SUM(t1.importe_comision),'0'), t3.razon_social, t3.cuit FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit GROUP BY t2.cuit HAVING SUM(t1.importe_comision) < @promedio_pagos_comisiones;

-- Práctica VII » Ejercicio V
SELECT SUM(t1.importe_comision) FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit WHERE t3.razon_social = 'Traigame eso' GROUP BY t3.cuit INTO @comisiones_traigame_eso;

SELECT t3.razon_social, SUM(t1.importe_comision) FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit GROUP BY t3.cuit HAVING SUM(t1.importe_comision) > @comisiones_traigame_eso;

-- Práctica VII » Ejercicio VI
/*
 * Animalada provisoria forever
 */
SELECT SUM(t1.importe_comision) AS max_total_comisiones FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit GROUP BY t2.cuit ORDER BY max_total_comisiones DESC LIMIT 1 INTO @max_importe_comisiones;

SELECT SUM(t1.importe_comision) AS max_total_comisiones FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit GROUP BY t2.cuit ORDER BY max_total_comisiones ASC LIMIT 1 INTO @min_importe_comisiones;

SELECT t3.razon_social, SUM(t1.importe_comision) FROM comisiones AS t1 INNER JOIN contratos AS t2 ON t1.nro_contrato = t2.nro_contrato INNER JOIN empresas AS t3 ON t2.cuit = t3.cuit GROUP BY t3.cuit HAVING SUM(t1.importe_comision) IN (@max_importe_comisiones,@min_importe_comisiones);

-- Práctica VII » Ejercicio VII
SELECT MAX(sueldo) FROM contratos INTO @max_sueldo;

SELECT t1.sueldo, t2.cuit, t2.razon_social, t3.dni, t3.nombre, t3.apellido FROM contratos AS t1 INNER JOIN empresas AS t2 ON t1.cuit = t2.cuit INNER JOIN personas AS t3 ON t1.dni = t3.dni WHERE t1.sueldo = @max_sueldo;

-- Práctica VII » Ejercicio VIII
SELECT t1.nro_contrato AS cantidad FROM contratos AS t1 INNER JOIN comisiones AS t2 ON t1.nro_contrato = t2.nro_contrato GROUP BY t2.nro_contrato ORDER BY SUM(t1.sueldo) DESC LIMIT 1 INTO @mayor_contrato;

SELECT t3.nombre, t3.apellido, t2.razon_social, t1.nro_contrato, t1.sueldo, t1.porcentaje_comision, @mayor_contrato, (t1.sueldo/t1.porcentaje_comision) AS pago FROM contratos AS t1 INNER JOIN empresas AS t2 ON t1.cuit = t2.cuit INNER JOIN personas AS t3 ON t1.dni = t3.dni WHERE t1.nro_contrato = @mayor_contrato GROUP BY t1.nro_contrato;

-- Práctica VII.II » Ejercicio IX
USE DATABASE afatse;
SELECT AVG(valor_plan) FROM valores_plan WHERE YEAR(fecha_desde_plan) = '2009' INTO @promedio_valor_plan;

SELECT t2.nom_plan, t2.detalle, t1.valor_plan FROM valores_plan AS t1 INNER JOIN plan_temas AS t2 ON t1.nom_plan = t2.nom_plan WHERE YEAR(fecha_desde_plan) = '2009' AND t1.valor_plan > @promedio_valor_plan;

-- Práctica VII.II » Ejercicio X
SELECT COUNT(*) AS veces_principito FROM inscripciones AS t1 INNER JOIN alumnos AS t2 ON t1.dni = t2.dni WHERE t2.nombre = 'Antoine de' AND t2.apellido = 'Saint-Exupery' GROUP BY t1.dni INTO @veces_principito;

SELECT *,COUNT(t1.dni) AS total_inscripciones, (COUNT(t1.dni)-@veces_principito) AS diferencia FROM inscripciones AS t1 GROUP BY t1.dni HAVING total_inscripciones > @veces_principito;

-- Práctica VII.II » Ejercicio XI
SELECT nom_plan FROM valores_plan WHERE YEAR(fecha_desde_plan) = '2009' ORDER BY valor_plan ASC LIMIT 1 INTO @mas_barato;

SELECT * FROM plan_capacitacion WHERE nom_plan = @mas_barato;