162 lines
8.5 KiB
Vala
162 lines
8.5 KiB
Vala
/*
|
||
* Copyright 2018-2019 Chris Cromer
|
||
*
|
||
* Redistribution and use in source and binary forms, with or without modification, are permitted provided that the following conditions are met:
|
||
*
|
||
* 1. Redistributions of source code must retain the above copyright notice, this list of conditions and the following disclaimer.
|
||
*
|
||
* 2. Redistributions in binary form must reproduce the above copyright notice, this list of conditions and the following disclaimer in the documentation and/or other materials provided with the distribution.
|
||
*
|
||
* 3. Neither the name of the copyright holder nor the names of its contributors may be used to endorse or promote products derived from this software without specific prior written permission.
|
||
*
|
||
* THIS SOFTWARE IS PROVIDED BY THE COPYRIGHT HOLDERS AND CONTRIBUTORS "AS IS" AND ANY EXPRESS OR IMPLIED WARRANTIES, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE DISCLAIMED. IN NO EVENT SHALL THE COPYRIGHT HOLDER OR CONTRIBUTORS BE LIABLE FOR ANY DIRECT, INDIRECT, INCIDENTAL, SPECIAL, EXEMPLARY, OR CONSEQUENTIAL DAMAGES (INCLUDING, BUT NOT LIMITED TO, PROCUREMENT OF SUBSTITUTE GOODS OR SERVICES; LOSS OF USE, DATA, OR PROFITS; OR BUSINESS INTERRUPTION) HOWEVER CAUSED AND ON ANY THEORY OF LIABILITY, WHETHER IN CONTRACT, STRICT LIABILITY, OR TORT (INCLUDING NEGLIGENCE OR OTHERWISE) ARISING IN ANY WAY OUT OF THE USE OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE.
|
||
*/
|
||
|
||
namespace Sernatur {
|
||
namespace Constants {
|
||
/**
|
||
* The Q1 statement
|
||
*/
|
||
public const string Q1_STATEMENT = "Genere la vista REGIONES_SINDESCUENTO(nombreRegion, cantidad) que contiene el nombre de cada región y la cantidad de personas a las que no se les ha aplicado descuento y que han visitado algún lugar de la región en algún tour, durante el mes de noviembre del presente año. Luego utilizando la vista, muestre las regiones que han recibido la mayor cantidad de turistas sin descuento.";
|
||
/**
|
||
* The Q2 statement
|
||
*/
|
||
public const string Q2_STATEMENT = "Genere la vista VALORES_TOURS(idT, nombreT, TotalVentas) que contiene el identificador y nombre de cada tour, además del total de ventas de cada tour. Par ello debe considerar todas las ventas realizadas del tour. Genere la vista TOUR_DESCUENTOS(idT, nombreT, TotalDescuentos) que contiene el identificador y nombre de cada tour, además del total de descuentos aplicados en todas las ventas de cada tour. Luego utilizando las vistas, muestre el nombre de los tours y valor total recibido, considerando el dinero recibido menos los descuentos.";
|
||
/**
|
||
* The Q3 statement
|
||
*/
|
||
public const string Q3_STATEMENT = "Genere la vista TOTAL_COORDINADORES(idT, nombreT, TotalCoordinadores) que almacena el identificador y nombre de los tour que poseen guı́as coordinadores con más de tres especialidades y que han participado en tours que visitan lugares con nivel de esfuerzo medio. Luego, utilizando la vista, muestre los tours que poseen la mayor cantidad de coordinadores con las caracterı́sticas mencionadas.";
|
||
/**
|
||
* The Q4 statement
|
||
*/
|
||
public const string Q4_STATEMENT = "Genere la vista TOTAL_TURISTAS(idT, nombreT, TotalTuristas) que contiene el identificador y nombre de los tours, y la cantidad de turistas que no tienen enfermedades cardı́acas ni respiratorias y que han visitado lugares cuyo nivel de exigencia es experto durante el mes de octubre del presente año. Luego, utilizando la vista, muestre los tours que poseen la mayor cantidad de turistas que han visitado lugares con exigencia experto y que cumplan el requisito de no tener las enfermedades mencionadas.";
|
||
/**
|
||
* The Q5 statement
|
||
*/
|
||
public const string Q5_STATEMENT = "Genere la vista TOTAL_ARRIENDOS(TotalArriendo) que almacena el total de arriendos de vehı́culos (no precio) realizados por SERNATUR durante los meses de Enero y Febrero del presente año y la vista TOTAL_VEHICULOS(TotalVeh) que contiene el total de vehı́culos que posee SERNATUR. Luego, utilizando las vistas muestre el porcentaje de arriendo de vehı́culos realizados por SERNATUR (número de vehı́culos arrendados/número total de vehı́culos).";
|
||
/**
|
||
* The Q1 SQL
|
||
*/
|
||
public const string Q1_SQL = "CREATE VIEW REGIONES_SINDESCUENTO(nombreRegion, cantidad)
|
||
AS (
|
||
SELECT nombre_region, COUNT(R2.rut_turista) FROM region R
|
||
JOIN ciudad C ON (C.id_region = R.id_region) JOIN tour T ON (T.id_ciudad = C.id_ciudad)
|
||
JOIN realiza R2 ON (R2.id_tour = T.id_tour)
|
||
WHERE (
|
||
EXISTS(SELECT fecha_llegada
|
||
FROM asociado
|
||
WHERE (id_tour = T.id_tour AND fecha_llegada BETWEEN '2018-11-01' AND '2018-11-30')
|
||
) AND
|
||
R2.id_descuento = 2
|
||
)
|
||
GROUP BY (R.nombre_region)
|
||
);
|
||
SELECT nombreRegion, cantidad FROM REGIONES_SINDESCUENTO WHERE (cantidad = (SELECT MAX(cantidad) FROM REGIONES_SINDESCUENTO));";
|
||
/**
|
||
* The Q2 SQL
|
||
*/
|
||
public const string Q2_SQL = "CREATE VIEW VALORES_TOURS(idT, nombreT, TotalVentas)
|
||
AS (
|
||
SELECT T.id_tour, T.nombre_tour,
|
||
((CASE
|
||
WHEN T.minima_personas <= (SELECT COUNT(rut_turista) FROM realiza WHERE (id_tour = T.id_tour))
|
||
THEN (SELECT costo_grupal FROM tour WHERE (id_tour = T.id_tour))
|
||
ELSE (SELECT costo_indiv FROM tour WHERE (id_tour = T.id_tour))
|
||
END) * COUNT(rut_turista)) AS cantidad
|
||
FROM tour T
|
||
JOIN realiza R ON (T.id_tour = R.id_tour)
|
||
GROUP BY (T.id_tour)
|
||
);
|
||
|
||
CREATE VIEW TOUR_DESCUENTOS(idT, nombreT, TotalDescuentos)
|
||
AS (
|
||
SELECT T.id_tour, T.nombre_tour,
|
||
SUM(DISTINCT (SELECT porcentaje FROM descuento WHERE (id_descuento = R.id_descuento)) *
|
||
(SELECT COUNT(rut_turista) FROM realiza WHERE (id_tour = R.id_tour AND id_descuento = R.id_descuento)) *
|
||
(CASE
|
||
WHEN T.minima_personas <= (SELECT COUNT(rut_turista) FROM realiza WHERE (id_tour = T.id_tour))
|
||
THEN (SELECT costo_grupal FROM tour WHERE (id_tour = T.id_tour))
|
||
ELSE (SELECT costo_indiv FROM tour WHERE (id_tour = T.id_tour))
|
||
END)) AS test
|
||
FROM tour T
|
||
JOIN realiza R ON (T.id_tour = R.id_tour)
|
||
WHERE (R.id_descuento != 2)
|
||
GROUP BY (T.id_tour)
|
||
);
|
||
|
||
SELECT V.nombreT, (V.TotalVentas - COALESCE(MAX(T.TotalDescuentos), 0)) AS ValorTotalRecibido
|
||
FROM VALORES_TOURS V FULL JOIN TOUR_DESCUENTOS T ON (T.idT = V.idT) GROUP BY (V.nombreT, V.TotalVentas);";
|
||
/**
|
||
* The Q3 SQL
|
||
*/
|
||
public const string Q3_SQL = "CREATE VIEW TOTAL_COORDINADORES(idT, nombreT, TotalCoordinadores)
|
||
AS (
|
||
SELECT T.id_tour, T.nombre_tour, COUNT(DISTINCT P.rut_guia)
|
||
FROM participa P
|
||
JOIN tour T ON (T.id_tour = P.id_tour)
|
||
JOIN guia G ON (G.rut_guia = P.rut_guia)
|
||
JOIN categoria C ON (C.id_categoria = P.id_categoria)
|
||
JOIN posee P2 ON (G.rut_guia = P2.rut_guia)
|
||
JOIN asociado A ON (A.id_tour = T.id_tour)
|
||
JOIN lugar L ON (L.id_lugar = A.id_lugar)
|
||
WHERE (
|
||
C.descripcion_categoria = 'Coordinador' AND
|
||
EXISTS(SELECT FROM lugar WHERE (id_lugar = L.id_lugar AND L.nivel = 3)) AND
|
||
(SELECT COUNT(rut_guia) FROM posee WHERE (rut_guia = G.rut_guia)) > 3
|
||
)
|
||
GROUP BY (T.id_tour)
|
||
);
|
||
|
||
SELECT nombreT, TotalCoordinadores FROM TOTAL_COORDINADORES WHERE (TotalCoordinadores = (SELECT MAX(TotalCoordinadores) FROM TOTAL_COORDINADORES));";
|
||
/**
|
||
* The Q4 SQL
|
||
*/
|
||
public const string Q4_SQL = "CREATE VIEW TOTAL_TURISTAS(idT, nombreT, TotalTuristas)
|
||
AS (
|
||
SELECT T.id_tour, T.nombre_tour, COUNT(R.rut_turista)
|
||
FROM tour T
|
||
JOIN realiza R ON (T.id_tour = R.id_tour)
|
||
JOIN asociado A ON (T.id_tour = A.id_tour)
|
||
JOIN lugar L ON (A.id_lugar = L.id_lugar)
|
||
WHERE (
|
||
EXISTS (SELECT id_lugar FROM lugar WHERE (id_lugar = L.id_lugar AND nivel >= 5)) AND
|
||
(A.fecha_llegada BETWEEN '2018-10-01' AND '2018-10-31') AND
|
||
NOT EXISTS (
|
||
SELECT id_enfermedad
|
||
FROM tiene_enfermedad
|
||
WHERE (rut_turista = R.rut_turista AND
|
||
(id_enfermedad = 1 OR id_enfermedad = 3)
|
||
)
|
||
)
|
||
)
|
||
GROUP BY (T.id_tour)
|
||
);
|
||
|
||
SELECT nombreT, TotalTuristas FROM TOTAL_TURISTAS WHERE (TotalTuristas = (SELECT MAX(TotalTuristas) FROM TOTAL_TURISTAS));";
|
||
/**
|
||
* The Q5 SQL
|
||
*/
|
||
public const string Q5_SQL = "CREATE VIEW TOTAL_ARRIENDOS(TotalArriendo)
|
||
AS (SELECT COUNT(A.patente)
|
||
FROM arrienda A
|
||
JOIN requerir_auto R ON (R.patente = A.patente)
|
||
JOIN tour T ON (T.id_tour = R.id_tour)
|
||
JOIN asociado A2 ON (T.id_tour = A2.id_tour)
|
||
WHERE (A2.fecha_llegada BETWEEN '2018-01-01' AND '2018-02-28')
|
||
);
|
||
|
||
CREATE VIEW TOTAL_VEHICULOS(TotalVeh)
|
||
AS (SELECT COUNT(V.patente)
|
||
FROM vehiculo V
|
||
JOIN requerir_auto R ON (R.patente = V.patente)
|
||
JOIN tour T ON (T.id_tour = R.id_tour)
|
||
JOIN asociado A ON (T.id_tour = A.id_tour)
|
||
WHERE (A.fecha_llegada BETWEEN '2018-01-01' AND '2018-02-28')
|
||
);
|
||
|
||
SELECT
|
||
(cast(T1.totalarriendo AS DECIMAL(3,2)) / cast(T2.totalveh AS DECIMAL(3,2))) AS porcentaje
|
||
FROM total_arriendos AS T1, total_vehiculos AS T2;";
|
||
}
|
||
}
|