Modelo 2017 A
Dada la siguiente base de datos:
HOTEL(id, nombre, estrellas, idCiudad)
ESTADIA(id, fechaIngreso, fechaEgreso, nroHab, montoAbonado, idPersona, idHotel)
PAIS(id, nombre)
CIUDAD(id, nombre, país)
Persona(ndoc, nombre, apellido, idpais)
Habitacion(idHotel, nro)
*La fecha de egreso se carga al hacer el check-out.
A. Dada las siguientes consultas, indique el enunciado que considera le dio origen:
CREATE VIEW Auxiliar1 AS
SELECT SUM(monto) as Monto, idPersona
FROM Estadia e INNER JOIN hotel h on h.id = e.idHotel
INNER JOIN Ciudad c on c.id = h.idCiudad INNER JOIN Pais P on c.idPais = p.id
WHERE p.nombre = “EEUU”
GROUP BY idPersona;
CREATE VIEW Auxiliar2 AS
SELECT idPersona
FROM Estadia e INNER JOIN hotel h on h.id = e.idHotel
INNER JOIN Ciudad c ON c.id = h.idCiudad INNER JOIN Pais p on c.idPais = p.id
WHERE p.nombre <> “EEUU”
GROUP BY idPersona
HAVING COUNT (distinct pais) >= 2;
SELECT a1.idPersona
FROM Auxiliar a1 INNER JOIN Auxiliar a2 ON a2.idPersona = a1.idPersona
WHERE a1.monto >= ( SELECT MAX(a3.monto) FROM Auxiliar1 a3)
La vista Auxiliar1 hace la suma de los montos que tuvo una misma persona durante sus diferentes
estadías en hoteles que pertenezcan a ciudades de EEUU.
La vista Auxiliar2 muestra las personas que residieron en hoteles de dos o más diferentes países que no
sean EEUU.
La última consulta de todas muestra aquellas personas que residieron en hoteles de EEUU y en hoteles
de dos o más diferentes países a EEUU, cuyo monto abonado en hoteles de EEUU sea mayor o igual al
máximo monto que haya pagado una persona en un hotel de EEUU durante su estadía.
B. Liste el documento y nombre de aquellas personas que en sus estadías solo estuvieron en todos los
hoteles 5 estrellas de México y Costa Rica en los últimos 5 años.
--Hoteles de mexico y costa rica que son 5 estrellas
CREATE VIEW v_HotelesMC AS
SELECT h.id
FROM Hotel h JOIN Ciudad c ON h.idCiudad = c.id JOIN Pais p ON c.pais = p.id
WHERE h.estrellas = 5 AND (p.nombre = ‘Mexico’ OR p.nombre = ‘Costa Rica’)
GO
--Personas que fueron a todos los Hot. de Mex y Costa, pero que pudieron ir a otros de otros paises
SELECT per.ndoc, per.nombre
FROM Estadia e JOIN Hotel h ON e.idHotel = h.id JOIN Ciudad c ON c.id = h.idCiudad JOIN Pais p on c.pais
= p.id JOIN Persona per ON e.idPersona = per.ndoc
WHERE e.fechaIngreso >= ‘2015-01-01’ AND (p.nombre = ‘Mexico OR p.nombre = ‘Costa Rica’)
GROUP BY per.ndoc, per.nombre
HAVING COUNT(e.idHotel) = ( SELECT COUNT(*) FROM v_HotelesMC)
EXCEPT
--Personas que fueron a hoteles de otros países en los últimos 5 años
SELECT per.ndoc, per.nombre
FROM Estadia e JOIN Hotel h ON e.idHotel = h.id JOIN Ciudad c ON c.id = h.idCiudad JOIN Pais p on c.pais
= p.id JOIN Persona per ON e.idPersona = per.ndoc
WHERE (p.nombre <> ‘Mexico’ OR p.nombre <> ‘Costa Rica’) AND e.fechaIngreso >= ‘2015-01-01’
GROUP BY per.ndoc, per.nombre
Modelo 2017 B
Dada la base de datos:
CHOFER(doc, nombre, apellido)
MANEJA(mat, chofer, fechaInicio, fechaFin, montoRecaudado, cantViajes)
AUTO(matricula, añoPat, modelo, dueño)
DUEÑO(doc, nombre, apellido)
A. Indique los choferes que manejaron todos los autos de Juan Perez y ninguno de Andrea Bonetto.
CREATE VIEW v_AutosJuan AS
SELECT a.matricula
FROM Auto a JOIN Dueño d ON a.dueño = d.doc
WHERE d.nombre = 'Juan' AND d.apellido = 'Perez'
GO
SELECT c.doc
FROM Chofer c JOIN Maneja m ON c.doc = m.chofer JOIN Auto a ON a.matricula = m.mat JOIN
Dueño d ON a.dueño = d.doc
WHERE d.nombre = 'Juan' AND d.apellido = 'Perez' AND NOT EXISTS (
SELECT 1
FROM Chofer c1 JOIN Maneja m1 ON c1.doc = m1.chofer JOIN Auto a1 ON a1.matricula =
m1.mat JOIN Dueño d1 ON a1.dueño = d1.doc
WHERE c.doc = c1.doc AND d1.nombre = 'Andrea' AND d1.apellido = 'Bonetto' )
GROUP BY c.doc
HAVING COUNT(*) = ( SELECT COUNT(*) FROM v_AutosJuan )
B. Realice las sentencias DDL requeridas para crear la base del enunciado con las tablas y restricciones
indicadas en el MR utilizado y las requeridas para insertar un registro en cada una de las tablas.
CREATE TABLE Chofer (
doc int PRIMARY KEY,
nombre varchar(50),
apellido varchar(50) )
CREATE TABLE Dueño (
doc int PRIMARY KEY,
nombre varchar(50),
apellido varchar(50) )
CREATE TABLE Auto (
matricula int PRIMARY KEY,
añoPat datetime,
modelo varchar(50),
dueño int,
CONSTRAINT Fk_Dueño FOREIGN KEY (dueño)
REFERENCES Dueño (doc) )
CREATE TABLE Maneja (
mat int,
chofer int,
fechaInicio datetime,
fechaFin datetime,
montoRecaudado money,
cantViajes int,
CONSTRAINT Pk_maneja PRIMARY KEY (mat, chofer, fechaInicio),
CONSTRAINT Fk_manejaChofer FOREIGN KEY (chofer)
REFERENCES Chofer (doc),
CONSTRAINT Fk_manejaMat FOREIGN KEY (mat)
REFERENCES Auto (matricula) )
INSERT INTO Chofer VALUES
(10, 'Alesio', 'Sinopoli'),
(20, 'AAA', 'BBB'),
(30, 'BBB', 'AAA')
INSERT INTO Dueño VALUES
(1, 'Juan', 'Perez'),
(2, 'Andrea', 'Bonetto'),
(3, 'Cos', 'Cas')
INSERT INTO Auto VALUES
(1, '2020-01-01', 'Corsa', 1),
(2, '2020-01-01', 'Corsa', 1),
(3, '2020-01-01', 'Corsa', 1),
(4, '2020-01-01', 'Hilux', 2),
(5, '2020-01-01', 'Gold', 3)
INSERT INTO Maneja VALUES
(1, 10, '2020-01-01', '2020-01-02', 0, 0),
(2, 10, '2020-01-01', '2020-01-02', 0, 0),
(3, 10, '2020-01-01', '2020-01-02', 0, 0),
(1, 20, '2020-01-01', '2020-01-02', 0, 0),
(2, 20, '2020-01-01', '2020-01-02', 0, 0),
(3, 20, '2020-01-01', '2020-01-02', 0, 0),
(4, 20, '2020-01-01', '2020-01-02', 0, 0)
(1, 30, '2020-01-01', '2020-01-02', 0, 0),
(2, 30, '2020-01-01', '2020-01-02', 0, 0),
(5, 30, '2020-01-01', '2020-01-02', 0, 0)
Modelo C 2017
Dada la siguiente BD:
Auto(matricula, idModelo, año)
Modelo(id, desc)
Cliente(id, dni, nombre, apellido, genero)
Alquiler(id, fhInicio, fhFinPlanificado, fhFinReal, idCli, matricula)
A. Indique los clientes que han alquilado todos los modelos menos 1 el año pasado y nunca se atrasaron
en la devolución.
CREATE VIEW v_TodosMod AS
SELECT id
FROM Modelo
GO
SELECT c.id
FROM Alquiler a JOIN Cliente c ON a.idCli = c.id JOIN Auto au ON au.matricula =
a.matricula
JOIN Modelo m ON au.idModelo = m.id
WHERE a.fhFinPlanificado = a.fhFinReal AND fhInicio BETWEEN '2019-01-01' AND '2020-01-01'
GROUP BY c.id
HAVING COUNT(m.id) = ( SELECT COUNT(*)-1 From v_TodosMod )
B. Genere un procedimiento que permita crear un alquiler pasando como parámetro la fecha de inicio y
fin (planificado), el DNI del cliente y la matrícula del auto. Asignando como ID un número consecutivo.
CREATE PROCEDURE p_NuevoAlq
@fechaInicio datetime,
@fechaFinPlanif datetime,
@dni int,
@matricula int
AS
DECLARE @id int
DECLARE @idCli int
SET @id = (SELECT MAX(id)+1 FROM Alquiler)
IF ( NOT EXISTS ( SELECT 1 FROM Cliente WHERE dni = @dni ) )
BEGIN
RETURN
END
SET @idCli = SELECT id FROM Cliente WHERE dni = @dni
INSERT INTO Alquiler VALUES
(@id, @fechaInicio, @fechaFinPlanif, NULL, @idCli, @matricula)
GO
Modelo D 2017
Dada la siguiente BD:
Auto(matricula, idModelo, año)
Modelo(id, desc)
Cliente(id, dni, nombre, apellido, genero)
Alquiler(id, fhInicio, fhFinPlanificado, fhFinReal, idCli, matricula)
A. Indique el modelo con menores alquileres hechos (fecha inicio) por mujeres en el año 2019.
CREATE VIEW v_Alquileres AS
SELECT m.id, COUNT(*) cantAlquileres
FROM Alquiler al JOIN Cliente c ON al.idCli = c.id JOIN Auto a ON a.matricula =
al.matricula
JOIN Modelo m ON m.id = a.idModelo
GROUP BY m.id
GO
SELECT m.id, m.descripcion
FROM Alquiler al JOIN Cliente c ON al.idCli = c.id JOIN Auto a ON a.matricula =
al.matricula
JOIN Modelo m ON m.id = a.idModelo
WHERE c.genero = 'F' AND al.fhInicio BETWEEN '2019-01-01' AND '2019-31-12'
GROUP BY m.id, m.descripcion
HAVING COUNT(*) = ( SELECT MIN(cantAlquileres) FROM v_Alquileres)
B. Genere un Procedure para archivar los alquileres realizados previos a una fecha pasada como
parámetro (fhFinReal). Esto implica moverlos a la tabla “AlquileresHistoricos”
CREATE PROCEDURE sp_AlquileresHist
@fhFinReal datetime
AS
INSERT INTO AlquileresHistoricos VALUES
(SELECT * FROM Alquiler WHERE fhFinReal < @fhFinReal)
DELETE FROM Alquiler
WHERE fhFinReal < @fhFinReal
Modelo R2 2017
Dada la siguiente BD:
Auto(matricula, idModelo, año)
Modelo(id, desc)
Cliente(id, dni, nombre, apellido, genero)
Alquiler(id, fhInicio, fhFinPlanificado, fhFinReal, idCli, matricula)
A. Realice todas las sentencias necesarias para crear la base de datos y tablas indicadas en el enunciado
con las restricciones que ahí aparezcan. La sentencia debe poder ejecutarse de una sola vez y en caso de
eliminar un auto o un cliente deben eliminarse los alquileres correspondientes.
Qué paja hacer esto. Es lo mismo de siempre, solo que con un ON DELETE CASCADE en la FOREIGN KEYS
mencionadas.
B. Liste en orden decreciente los 10 modelos más alquilados del 2016 (fhInicio) indicando la cantidad de
alquileres y cuántos autos tenemos disponibles de c/u.
SELECT m.descripcion, COUNT(*) as cantAlquileres, COUNT(DISTINCT a.matricula) as
cantAutos
FROM Alquiler al JOIN Cliente c ON al.idCli = c.id JOIN Auto a ON a.matricula =
al.matricula
JOIN Modelo m ON m.id = a.idModelo
WHERE al.fhInicio BETWEEN '2019-01-01' AND '2019-31-12'
GROUP BY m.descripcion, a.matricula
ORDER BY cantAlquileres DESC
Modelo 2013 A
Dada la siguiente BD:
Cliente (id, nombre, apellido, fnac, tel, email, localidad)
Factura (nro, fecha, medioPag, tipoIVA, idSucursal, codVendedor, codCliente)
DetalleFactura(nroFac, codArt, cant, descuento)
Articulo(cod, descripción, punitario)
Sucursal(id, nombre, direccion, localidad)
Vendedor(id, nombre, apellido, fnac, tel, email, idSucural)
Nota: el descuento es un nro. decimal indicando el %.
1. Dada la siguiente consulta, indique cual es el enunciado que le dio origen:
SELECT v.id, v.nombre, v.apellido
FROM Vendedor V
WHERE NOT EXISTS ( SELECT 1
FROM Articulo a
WHERE NOT EXISTS ( SELECT 1
FROM Factura f JOIN DetalleFactura df ON f.nro = df.nroFac JOIN Cliente c ON
f.codCliente = c.id
WHERE a.cod = df.codArt AND v.id = f.codVendedor
AND c.localidad = ‘Capital Federal’ ))
Muestre los vendedores (indicando ID, nombre, y apellido) que vendieron artículos únicamente a todos
los clientes que son de Capital Federal. (Es decir, si un vendedor le vendió a un cliente que no es de
Capital Federal, no será seleccionado.)
2. Indique la cantidad de ventas, monto total de las ventas y monto total de los descuentos de los
vendedores que dieron mayores descuentos (en total monetario) en los últimos 2 meses calendario
completos (todos los cálculos tienen en cuenta ese período).
(en total monetario = aquel que en base a sus descuentos por articulo descontó mas plata)
--Vendedores y sus descuentos monetarios
CREATE VIEW v_VendMonet AS
SELECT codVendedor, SUM( (df.cant * a.prUnitario * df.descuento)/100 ) AS descuentoMonet
FROM Factura f JOIN DetalleFactura df ON f.nro = df.nroFac JOIN Articulo a ON a.cod = df.codArt
WHERE f.fecha >= ‘2019-12-25’
GROUP BY f.codVendedor
--Vendedores que hayan dado el mayor descuento monetario
CREATE VIEW v_DescMonetMax AS
SELECT codVendedor
FROM v_VendMonet
WHERE descuentoMonet = ( SELECT MAX(descuentoMonet) FROM v_VendMonet )
--Vendedores y el monto total de sus ventas
CREATE VIEW v_MontoTotal AS
SELECT vd.codVendedor, SUM( a.prUnitario*df.cantidad – (df.cant * a.prUnitario * df.descuento)/100 )
AS montoTotal
FROM v_DescMonetMax vd JOIN Factura f ON vd.codVendedor = f.codVendedor JOIN DetalleFactura df
ON f.nro = df.nroFac JOIN Articulo a ON a.cod = df.codArt
WHERE f.fecha >= ‘2019-12-25’
GROUP BY vd.codVendedor
--Vendedores y la cantidad de ventas
CREATE VIEW v_CantVentas AS
SELECT codVendedor, COUNT(*) as cantVentas
FROM Factura
GROUP BY codVendedor
SELECT cv.codVendedor, cv.cantVentas, mt.montoTotal, vm.descuentoMonet
FROM v_CantVentas cv JOIN v_MontoTotal mt ON cv.codVendedor = mt.codVendedor JOIN
v_DescMonetMax dmm ON cv.codVendedor = dmm.codVendedor JOIN v_VendMonet vm ON
vm.codVendedor = cv.codVendedor
No sé si esta solución esté bien. Si está bien, es un poco ineficiente porque son todas vistas… y el
resultado final es un conjunto gigantesco de queries. Se podría hacer más eficiente si se usase SELECT …
INTO #temptable
Modelo 2013 B
Dada la siguiente BD:
Cliente (id, nombre, apellido, fnac, tel, email, localidad)
Factura (nro, fecha, medioPag, tipoIVA, idSucursal, codVendedor, codCliente)
DetalleFactura(nroFac, codArt, cant, descuento)
Articulo(cod, descripción, punitario)
Sucursal(id, nombre, direccion, localidad)
Vendedor(id, nombre, apellido, fnac, tel, email, idSucural)
A. Dada la siguiente consulta indique cuál es el enunciado que le dio origen
SELECT c.cliente
FROM Cliente c
WHERE NOT EXISTS ( SELECT 1
FROM Articulo a
WHERE NOT EXISTS ( SELECT 1
FROM Factura f JOIN DetalleFactura df ON f.nro = df.nroFac JOIN Cliente c ON
f.codCliente = c.id
WHERE a.cod = df.codArt AND c.id = f.codCliente))
AND c.id IN ( SELECT c2.id
FROM Cliente c2 JOIN Factura f2 ON f2.codCliente = c2.id
GROUP BY c2.id
HAVING COUNT(distinct f2.idSucursal) < 3 )
Muestre los clientes que compraron todos los artículos y que hayan realizado sus compras en menos de
3 sucursales distintas.
B. Indique para el último trimestre (a mes vencido) la cantidad de artículos vendidos, el monto total, y la
cantidad de clientes de las sucursales con más vendedores.
CREATE VIEW v_SucVend AS
SELECT idSucursal, COUNT(idVendedor) as cantVend
FROM Vendedor
GROUP BY idSucursal
CREATE VIEW v_SucMasVend AS
SELECT idSucursal
FROM v_SucVend
WHERE cantVend = ( SELECT MAX(cantVend) FROM v_SucVend )
SELECT SUM(a.cant) as cantArt, SUM(a.cant*a.PrUnitario – (df.descuento*a.cant*PrUnitario)/100) as
montoTotal, COUNT(DISTINCT f.codCliente) as cantCli
FROM v_SucMasVend scm JOIN Factura f ON scm.idSucursal = f.idSucursal JOIN DetalleFactura df ON
f.nro = df.nroFac JOIN Articulo a ON a.cod = df.codArt
WHERE f.fecha BETWEEN ‘2019-10-01’ AND ‘2019-12-31’
Modelo 2012
Dada la siguiente BD:
Empresa(emp_id, emp_nombre, privada)
Sucursal(suc_id, emp_id, suc_direccion, suc_provincia)
Trabaja(dni, suc_id, emp_id, fecha_inicio, fecha_fin)
Persona(dni, nombre, fecha_nacimiento)
A. Crear un Stored Procedure que permite dar de baja con fecha de ayer a todas las personas que
trabajan actualmente, desde hace más de un año, en una sucursal que no es la Casa Matriz (suc_id = 1)
de la empresa. Asociar estas personas con fecha de hoy a la Casa Matriz. Se sabe que en el día de ayer no
se realizó ninguna baja.
CREATE PROCEDURE p_ActEmp AS
UPDATE Trabaja
SET fecha_fin = DATEADD(day, -1, getdate())
WHERE suc_id <> 1 AND fecha_inicio < DATEADD(year, -1, getdate())
INSERT INTO Trabaja
SELECT dni, 1, emp_id, getdate(), NULL
FROM Trabaja
WHERE suc_id <> 1 AND fecha_fin = DATEADD(day, -1, getdate())
B. Listar las personas que trabajaron en todas las empresas con solo sucursales en Mendoza y
actualmente se encuentran trabajando en Tucumán para una empresa privada.
CREATE VIEW v_empMendoza AS
SELECT e.emp_id
FROM Empresa e JOIN Sucursal s ON e.emp_id = s.emp_id
WHERE s.suc_provincia = ‘Mendoza’ AND NOT EXISTS (
SELECT 1
FROM Empresa e2 JOIN Sucursal s2 ON e2.emp_id = s2.emp_id
WHERE s2.suc_provincia <> ‘Mendoza’ AND e2.emp_id = e.emp_id )
CREATE VIEW v_TodosMend AS
SELECT t.dni
FROM Trabaja t JOIN v_ empMendoza empM ON t.emp_id = empM.emp_id
GROUP BY t.dni
HAVING COUNT(DISTINCT emp_id) = ( SELECT COUNT(*) FROM v_empMendoza)
SELECT tm.dni
FROM v_TodosMend tm JOIN Trabaja t ON tm.dni = t.dni JOIN Sucursal s ON t.suc_id = s.suc_id JOIN
Empresa e ON e.emp_id = t.emp_id
WHERE s.suc_provincia = ‘Tucumán’ AND e.privada = 1 AND t.fecha_fin IS NULL
