CONSTRUCTORA DE HOTELES

La empresa constructora “MARK CORPORATION S.A.C.”, ejecuta un proyecto de construcción de un consorcio de hoteles para lo cual cuenta con varios trabajadores, y para el control del personal y la labor que realizan se tiene el siguiente modelo entidad relación: Dónde: la tabla “asignación” guarda una fila por cada vez que un empleado es asignado a laborar en un hotel a construir y en la tabla “empleado ” cada empleado tiene un empleado que es su supervisor.

Creamos el modelo de la base de datos en una base de datos en blanco de preferencia:

CREATE TABLE Empleado ( CodEmpleado Int NOT NULL, Nombre Varchar ( 60), PagoXHora Numeric ( 18, 2), Profesion Varchar ( 60), CodEmpleadoSupervisor Int , CONSTRAINT PK_Empleado PRIMARY KEY ( CodEmpleado ) ); ALTER TABLE Empleado ADD CONSTRAINT FK_Empleado_EmpleadoSup FOREIGN KEY ( CodEmpleadoSupervisor ) REFERENCES Empleado ( CodEmpleado ) ; CREATE TABLE Hotel ( CodHotel Int NOT NULL, Direccion Varchar ( 60), Tipo Varchar ( 60), NivelCalidad Int , Categoria Int , CONSTRAINT PK_Hotel PRIMARY KEY ( CodHotel ) ); CREATE TABLE Asignacion ( CodEmpleado Int NOT NULL, CodHotel Int NOT NULL, FechaInicio DateTime NOT NULL, NumDias Int , CONSTRAINT PK_Asignacion PRIMARY KEY ( CodEmpleado , CodHotel , FechaInicio ), CONSTRAINT FK_Empleado_Asignacion FOREIGN KEY ( CodEmpleado ) REFERENCES Empleado ( CodEmpleado ), CONSTRAINT FK_Hotel_Asignacion FOREIGN KEY ( CodHotel ) REFERENCES Hotel ( CodHotel ) );

Ahora insertamos los datos con los que vamos a trabajar

INSERT INTO Hotel ( CodHotel , Direccion , Tipo , NivelCalidad , Categoria ) VALUES ( 111 , 'JR. LIMA # 120' , 'TURISTAS' , 4 , 1 ); INSERT INTO Hotel ( CodHotel , Direccion , Tipo , NivelCalidad , Categoria ) VALUES ( 210 , 'JR. JIMENEZ PIMENTEL # 340' , 'TURISTAS' , 3 , 1 );

INSERT INTO Hotel ( CodHotel , Direccion , Tipo , NivelCalidad , Categoria ) VALUES ( 312 , 'JR. AUGUSTO B. LEGUIA # 780' , 'TURISTAS' , 2 , 2 ); INSERT INTO Hotel ( CodHotel , Direccion , Tipo , NivelCalidad , Categoria ) VALUES ( 435 , 'JR. MAYNAS # 560' , 'RESIDENCIAL' , 1 , 1 ); INSERT INTO Hotel ( CodHotel , Direccion , Tipo , NivelCalidad , Categoria ) VALUES ( 460 , 'JR. ORELLANA # 450' , 'ECOLOGICO' , 3 , 3 ); INSERT INTO Hotel ( CodHotel , Direccion , Tipo , NivelCalidad , Categoria ) VALUES ( 515 , 'JR. AUGUSTO B. LEGUIA # 386' , 'FAMILIAR' , 3 , 2 ); INSERT INTO Empleado ( CodEmpleado , Nombre, PagoXHora , Profesion , CodEmpleadoSupervisor ) VALUES ( 3231 , 'O.ANGULO' , 17.40 , 'CARPINTERO' , 3231 ); INSERT INTO Empleado ( CodEmpleado , Nombre, PagoXHora , Profesion , CodEmpleadoSupervisor ) VALUES ( 1311 , 'E.PEREZ' , 15.50 , 'ELECTRONICO' , 1311 ); INSERT INTO Empleado ( CodEmpleado , Nombre, PagoXHora , Profesion , CodEmpleadoSupervisor ) VALUES ( 1520 , 'D.BARRIA' , 11.75 , 'FONTANERO', 1520 ); INSERT INTO Empleado ( CodEmpleado , Nombre, PagoXHora , Profesion , CodEmpleadoSupervisor ) VALUES ( 2920 , 'S.SABOYA' , 10.00 , 'ALBAÑIL' , 2920 ); INSERT INTO Empleado ( CodEmpleado , Nombre, PagoXHora , Profesion , CodEmpleadoSupervisor ) VALUES ( 1235 , 'A.CELIS' , 12.50 , 'ELECTRONICO' , 1311 ); INSERT INTO Empleado ( CodEmpleado , Nombre, PagoXHora , Profesion , CodEmpleadoSupervisor ) VALUES ( 1412 , 'C.ROJAS' , 13.75 , 'FONTANERO', 1520 ); INSERT INTO Empleado ( CodEmpleado , Nombre, PagoXHora , Profesion , CodEmpleadoSupervisor ) VALUES ( 3001 , 'J.RAMIREZ' , 8.20 , 'CARPINTERO' , 3231 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1235 , 312 , '2013-10-10' , 5 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1235 , 515 , '2013-10-17' , 22 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1311 , 435 , '2013-10-08' , 12 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1311 , 460 , '2013-10-23' , 24 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1412 , 111 , '2013-12-01' , 4 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1412 , 210 , '2013-11-15' , 12 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1412 , 312 , '2013-10-10' , 10 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1412 , 435 , '2013-10-15' , 15 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1412 , 460 , '2013-10-08' , 18 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1412 , 515 , '2013-11-05' , 8 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1520 , 312 , '2013-10-30' , 17 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 1520 , 515 , '2013-10-09' , 14 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 2920 , 210 , '2013-11-10' , 15 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 2920 , 435 , '2013-10-28' , 10 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 2920 , 460 , '2013-10-05' , 18 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 3001 , 111 , '2013-10-08' , 14 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 3001 , 210 , '2013-10-27' , 14 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 3231 , 111 , '2013-10-10' , 8 ); INSERT INTO Asignacion ( CodEmpleado , CodHotel , FechaInicio , NumDias) VALUES ( 3231 , 312 , '2013-10-24' , 20 );

Ejercicio N°01 Crear una Consulta SQL que muestre los nombres de los empleados cuya Pago x Hora este entre 10 y 12 nuevos soles. Ejercicio N°02 Crear una Consulta SQL que muestre cuales son las profesiones de los empleados asignados al hotel número 435. Ejercicio N°03 Crear una Consulta SQL que muestre por cada empleado el nombre y el de su supervisor. Ejercicio N°04 Crear una Consulta SQL que muestre los nombre de los empleados asignados a hoteles tipo “TURISTAS”. Ejercicio N°05 Crear una Consulta SQL que muestre, ¿Qué empleados reciben un pago por hora mayor que la de su supervisor. Ejercicio N°06 Crear una Consulta SQL que muestre, ¿Cuál es el número total de días que se han dedicado a fontanería en el hotel 312? Ejercicio N°07 Crear una Consulta SQL que muestre, ¿Cuántos tipos de profesiones diferentes hay? Ejercicio N°08 Crear una Consulta SQL que muestre, por cada supervisor, ¿Cuál es el pago por hora más alta que se paga a un empleado que informa a ese supervisor? Ejercicio N°09 Crear una Consulta SQL que muestre, por cada supervisor que supervisa a más de un empleado, ¿cuál es el pago por hora más alta que se paga a un empleado que informa a ese supervisor? Ejercicio N°10 Crear una Consulta SQL que muestre, por cada tipo de hotel, ¿Cuál es el nivel de calidad medio de los hoteles con categoría 1? Considérense sólo aquellos tipos de hoteles que tienen un nivel de calidad máximo no mayor que 3 Ejercicio N°11 Crear una Consulta SQL que muestre, ¿Qué empleados reciben un pago por hora menor que la del promedio de todos los empleados? Ejercicio N°12 Crear una Consulta SQL que muestre, ¿Qué empleados reciben un pago por hora menor que la del promedio de los empleados que tienen su mismo oficio?

Ejercicio N°13 Crear una Consulta SQL que muestre, ¿Qué empleados reciben un pago por hora menor que la del promedio de los empleados que dependen del mismo supervisor que él? Ejercicio N°14 Crear una Consulta SQL que muestre, el nombre de los ELECTRONICOS asignados al hotel 435 y la fecha en la que empezaron a trabajar en él Ejercicio N°15 Crear una Consulta SQL que muestre, ¿Qué supervisores tienen empleados que tienen un pago por hora por encima de los 12 soles? Ejercicio N°16 Crear una Consulta SQL que muestre, una lista de empleados Incrementando en un 5 por ciento el pago por hora de todos los empleados a los que supervisa el supervisor “E.PEREZ”.

SOLUCION Ejercicio N°01 Crear una Consulta SQL que muestre los nombres de los empleados cuya Pago x Hora este entre 10 y 12 nuevos soles. Consulta Resultado

SELECT Nombre FROM Empleado WHERE PagoXHora BETWEEN 10 AND 12;

Nombre ------------- D.BARRIA S.SABOYA

Ejercicio N°02 Crear una Consulta SQL que muestre cuales son las profesiones de los empleados asignados al hotel número 435. Consulta Resultado

SELECT Empleado . Profesion FROM Empleado INNER JOIN Asignacion ON Empleado . CodEmpleado = Asignacion . CodEmpleado WHERE Asignacion . CodHotel =435 GROUP BY Empleado . Profesion;

Profesion ------------- ALBAÑIL ELECTRONICO FONTANERO

Ejercicio N°03 Crear una Consulta SQL que muestre por cada empleado el nombre y el de su supervisor. Consulta Resultado

SELECT E. Nombre AS Empleado , S. Nombre AS Supervisor FROM Empleado E INNER JOIN Empleado S ON S. CodEmpleado = E. CodEmpleadoSupervisor;

Empleado Supervisor -------------------------- A.CELIS E.PEREZ E.PEREZ E.PEREZ C.ROJAS D.BARRIA D.BARRIA D.BARRIA S.SABOYA S.SABOYA J.RAMIREZ O.ANGULO O.ANGULO O.ANGULO

Ejercicio N°04 Crear una Consulta SQL que muestre los nombre de los empleados asignados a hoteles tipo “TURISTAS”. Consulta Resultado

SELECT Empleado . Nombre FROM Empleado INNER JOIN Asignacion ON Empleado . CodEmpleado = Asignacion . CodEmpleado INNER JOIN Hotel ON Asignacion . CodHotel = Hotel . CodHotel WHERE Hotel . Tipo ='TURISTAS' GROUP BY Empleado . Nombre;

Nombre ------------- A.CELIS C.ROJAS D.BARRIA J.RAMIREZ O.ANGULO S.SABOYA

Ejercicio N°05 Crear una Consulta SQL que muestre, ¿Qué empleados reciben un pago por hora mayor que la de su supervisor. Consulta Resultado

SELECT T. Nombre

Nombre

FROM Empleado T INNER JOIN Empleado S ON S. CodEmpleado = T. CodEmpleadoSupervisor WHERE T. PagoXHora > S. PagoXHora;

------------- C.ROJAS

Ejercicio N°06 Crear una Consulta SQL que muestre, ¿Cuál es el número total de días que se han dedicado a fontanería en el hotel 312? Consulta Resultado

SELECT SUM( A. NumDias) AS TotalDias FROM Empleado E INNER JOIN Asignacion A ON E. CodEmpleado = A. CodEmpleado WHERE E. Profesion ='FONTANERO' AND A. CodHotel =312 ;

TotalDias ------------- 27

Ejercicio N°07 Crear una Consulta SQL que muestre, ¿Cuántos tipos de profesiones diferentes hay? Consulta Resultado

SELECT COUNT( DISTINCT Profesion ) AS Cantidad FROM Empleado ;

Cantidad ------------- 4

Ejercicio N°08 Crear una Consulta SQL que muestre, por cada supervisor, ¿Cuál es el pago por hora más alta que se paga a un empleado que informa a ese supervisor? Consulta Resultado

SELECT S. Nombre, MAX( E. PagoXHora ) AS PagoXHora FROM Empleado E INNER JOIN Empleado S ON S. CodEmpleado = E. CodEmpleadoSupervisor GROUP BY S. Nombre;

Nombre PagoXHora -------------------------- D.BARRIA 13.75 E.PEREZ 15.50 O.ANGULO 17.40 S.SABOYA 10.00

Ejercicio N°09 Crear una Consulta SQL que muestre, por cada supervisor que supervisa a más de un empleado, ¿cuál es el pago por hora más alta que se paga a un empleado que informa a ese supervisor? Consulta Resultado

SELECT S. Nombre, MAX( T. PagoXHora ) AS PagoXHora FROM Empleado T INNER JOIN Empleado S ON S. CodEmpleado = T. CodEmpleadoSupervisor GROUP BY S. Nombre HAVING COUNT( T. CodEmpleado )> 1;

Nombre PagoXHora -------------------------- D.BARRIA 13.75 E.PEREZ 15.50 O.ANGULO 17.40

Ejercicio N°10 Crear una Consulta SQL que muestre, por cada tipo de hotel, ¿Cuál es el nivel de calidad medio de los hoteles con categoría 1? Considérense sólo aquellos tipos de hoteles que tienen un nivel de calidad máximo no mayor que 3 Consulta Resultado

SELECT Hotel . Tipo , AVG( Hotel . NivelCalidad ) AS Calidad

Tipo Calidad --------------------------

FROM Hotel WHERE Hotel . Categoria =1 AND Hotel . NivelCalidad <3 GROUP BY Hotel . Tipo ;

RESIDENCIAL 1

Ejercicio N°11 Crear una Consulta SQL que muestre, ¿Qué empleados reciben un pago por hora menor que la del promedio de todos los empleados? Consulta Resultado

SELECT E. Nombre FROM Empleado E WHERE E. PagoXHora < ( SELECT AVG( Empleado . PagoXHora ) FROM Empleado ) OTRA SOLUCION SELECT E.Nombre FROM Empleado E, ( SELECT AVG( Empleado . PagoXHora ) AS Promedio FROM Empleado ) P WHERE E. PagoXHora < P. Promedio ;

Nombre ------------- A.CELIS D.BARRIA S.SABOYA J.RAMIREZ

Ejercicio N°12 Crear una Consulta SQL que muestre, ¿Qué empleados reciben un pago por hora menor que la del promedio de los empleados que tienen su mismo oficio? Consulta Resultado

SELECT E. Nombre FROM Empleado E INNER JOIN ( SELECT Empleado . Profesion , AVG( Empleado . PagoXHora ) AS Promedio FROM Empleado GROUP BY Empleado . Profesion ) P ON E. Profesion = P. Profesion WHERE E. PagoXHora < P. Promedio ;

Nombre ------------- J.RAMIREZ A.CELIS D.BARRIA

Ejercicio N°13 Crear una Consulta SQL que muestre, ¿Qué empleados reciben un pago por hora menor que la del promedio de los empleados que dependen del mismo supervisor que él? Consulta Resultado

SELECT E. Nombre FROM Empleado E INNER JOIN ( SELECT CodEmpleadoSupervisor , AVG( Empleado . PagoXHora ) AS Promedio FROM Empleado GROUP BY Empleado . CodEmpleadoSupervisor ) P ON E. CodEmpleadoSupervisor = P. CodEmpleadoSupervisor WHERE E. PagoXHora < P. Promedio ;

Nombre ------------- A.CELIS D.BARRIA J.RAMIREZ

Ejercicio N°14 Crear una Consulta SQL que muestre, el nombre de los ELECTRONICOS asignados al hotel 435 y la fecha en la que empezaron a trabajar en él Consulta Resultado

SELECT E. Nombre, A. FechaInicio FROM Empleado E INNER JOIN

Nombre FechaInicio --------------------------

Asignacion A ON E. CodEmpleado = A. CodEmpleado WHERE E. Profesion ='ELECTRONICO' AND A. CodHotel =435 ;

E.PEREZ 2013-10-08

Ejercicio N°15 Crear una Consulta SQL que muestre, ¿Qué supervisores tienen empleados que tienen un pago por hora por encima de los 12 soles?

Consulta Resultado

SELECT Nombre FROM Empleado WHERE CodEmpleado = CodEmpleadoSupervisor AND CodEmpleadoSupervisor IN ( SELECT CodEmpleadoSupervisor FROM Empleado WHERE PagoXHora >12 AND CodEmpleado <> CodEmpleadoSupervisor );

Nombre ------------- E.PEREZ D.BARRIA

Nota: en la sub consulta agregamos en el Where “CodEmpleado <> CodEmpleadoSupervisor”, debido a que deben ser solamente empleados Ejercicio N°16 Crear una Consulta SQL que muestre, una lista de empleados Incrementando en un 5 por ciento el pago por hora de todos los empleados a los que supervisa el supervisor “E.PEREZ”. Consulta Resultado

SELECT T. Nombre, T. PagoXHora , ( T. PagoXHora + ( T. PagoXHora * 5 )/ 100) AS PagoXHora_Al_5P FROM Empleado T INNER JOIN Empleado S ON S. CodEmpleado = T. CodEmpleadoSupervisor WHERE S. Nombre='E.PEREZ' ;

Nombre PagoXHora PagoXHora_Al_5P ---------------------------------- A.CELIS 12.50 13.125000 E.PEREZ 15.50 16.275000