db_hotel, Sql Server

Language: Layout:

--Sql Server 2014 Express Edition
--Batches are separated by 'go'

select @@version as 'sql server version'
DECLARE @MyDatetime datetime

CREATE TABLE[dbo].[room](
  [Id_room] [INT] NOT NULL PRIMARY KEY,
  [caract] [VARCHAR] (20) NOT NULL,
  [tipoR] [VARCHAR] (20) NOT NULL,
  [precio] [FLOAT] NOT NULL,
  [npiso] [INT] NOT NULL
);

CREATE TABLE [dbo].[cliente](
  [Id_cliente] [INT]  NOT NULL,
  [nombre]  [VARCHAR] (20) NOT NULL,
  [apellido] [VARCHAR] (20) NOT NULL,
  [telefono] [INT] NOT NULL,
  PRIMARY KEY ([Id_cliente] )
 );
 
CREATE TABLE [dbo].[empleado] (
  [Id_empleado] [INT]  NOT NULL ,
  [nombre]   [VARCHAR] (20) NOT NULL,
  [apellido] [VARCHAR] (20) NOT NULL,
  [telefono] [INT] NOT NULL,
  [direccion] [VARCHAR] (20) NOT NULL,
  [puesto]   [VARCHAR] (20) NOT NULL
  PRIMARY KEY ([Id_empleado] )
 );
  
CREATE TABLE [dbo].[reserva](
  [Id_rsv] [INT]  NOT NULL IDENTITY(1,1) ,
  [Id_cliente] [INT]  NOT NULL,
  [Id_empleado] [INT]  NOT NULL,
  [Id_room] [INT] NOT NULL, 
  [fechaE]  [DATETIME] NOT NULL,
  [fechaS]  [DATETIME] NULL,
  [cantP]   [INT] NOT NULL,
  PRIMARY KEY (Id_rsv),  
  FOREIGN KEY (Id_cliente) REFERENCES cliente(Id_cliente),
  FOREIGN KEY (Id_empleado) REFERENCES empleado(Id_empleado),
  FOREIGN KEY (Id_room ) REFERENCES room(Id_room)
 );
 
 CREATE TABLE [dbo].[pago](
  [Id_pago] [INT] NOT NULL IDENTITY(1,1) ,
  [Id_rsv ][INT] NOT NULL ,
  [fecha ] [DATETIME] NOT NULL,
  [total]  [FLOAT] NOT NULL ,
  PRIMARY KEY ([Id_pago]),  
  FOREIGN KEY ([Id_rsv]) REFERENCES reserva([Id_rsv]) 
 );

go
INSERT INTO [dbo].[room](Id_room, caract, tipoR, precio,npiso) VALUES ('1','3 o max. 5','Dúplex','5000','10'),
                                                                      ('2','3 o max. 5','Dúplex','5000','9'),
                                                                      ('3','3 o max. 5','Dúplex','5000','8'),
                                                                      ('4','3 o max. 5','Dúplex','5000','7'),
                                                                      ('5','3 o max. 5','Dúplex','5000','6'),
                                                                      ('6','2 o max. 3','hab. doble','3000','5'),
                                                                      ('7','2 o max. 3','hab. doble','3000','5'),
                                                                      ('8','2 o max. 3','hab. doble','3000','4'),
                                                                      ('9','2 o max. 3','hab. doble','3000','4'),
                                                                      ('10','2 o max. 3','hab. doble','3000','3'),
                                                                      ('11','1 o max. 2','hab. sencilla','1000','3'),
                                                                      ('12','1 o max. 2','hab. sencilla','1000','3'),
                                                                      ('13','1 o max. 2','hab. sencilla','1000','2'),
                                                                      ('14','1 o max. 2','hab. sencilla','1000','2'),
                                                                      ('15','1 o max. 2','hab. sencilla','1000','2'),
                                                                      ('16','1 o max. 2','hab. sencilla','1000','2');                                                        ;
                                                              
INSERT INTO [dbo].[cliente](Id_cliente,nombre, apellido, telefono) VALUES ('1','kenia','hernandez','1234567890'),
                                                                          ('56','pablo','fernandez','1584567890');
                                                                  
                                                                  
INSERT INTO [dbo].[empleado] ( Id_empleado,nombre, apellido,telefono, direccion, puesto) VALUES('1','karen','hernandez','12365478','calle mexico','Recepcionista'),
                                                                                               ('2','fernando' ,'torres','12365478','calle mexico sur','Recepcionista');  
                                                                                       
                                                                                       
                                                                              
INSERT INTO [dbo].[reserva](Id_cliente, Id_empleado,Id_room, fechaE, fechaS, cantP) VALUES ('1','2','1','2008-11-11 13:23:44','2008-11-15 14:30:44','5'),
                                                                                           ('1','2','2','2008-11-11 13:23:44','2008-11-15 14:30:44','3'),
                                                                                           ('56','1','16','2009-11-11 13:23:44','2009-11-15 14:30:44','1');
                                                                                        
INSERT INTO [dbo].[pago]( Id_rsv,fecha, total ) VALUES ('1','2008-11-15 14:30:44','5000'),
                                                       ('2','2008-11-15 14:30:44','5000'),
                                                       ('3','2008-11-15 14:30:44','1000');
go

--SELECT * FROM [cliente];
--SELECT * FROM [empleado];
--SELECT * FROM [reserva];
--SELECT * FROM [pago];
--SELECT * FROM room;
go

create view room_view as select id_room as NumRegistro, caract as [Caracteristicas],tipoR [Tipo habitación], precio[Costo habitación] from room;
GO

create view reserva_view as select id_rsv as Reservación, id_cliente as [Clave del cliente], id_empleado as [Clave del empleado], id_room as Habitación, fechaE as [Fecha Ingreso], fechaS as [Fecha Salida],cantP as [Cantidad de Personas] from reserva;
go
--INNER JOIN
CREATE VIEW PagoReserva_View WITH SCHEMABINDING 
    AS
        SELECT fecha, total
        FROM [dbo].pago
            INNER 
                JOIN [dbo].reserva
                ON pago.Id_rsv=reserva.Id_rsv;
go
CREATE VIEW TotalView WITH SCHEMABINDING 
    AS
        SELECT SUM(total) as [Total]
        FROM [dbo].[pago] 
        GROUP BY total;
go
-- LEFT JOIN
CREATE VIEW clientRsv_View WITH SCHEMABINDING 
    AS
   SELECT nombre, apellido, telefono
   FROM [dbo].cliente
   LEFT JOIN [dbo].reserva
   ON cliente.Id_cliente = reserva.Id_cliente;

go
--RIGHT JOIN
CREATE VIEW roomClient_View WITH SCHEMABINDING 
    AS
        SELECT Id_cliente,caract, tipoR,precio, npiso
        FROM [dbo].room
        RIGHT JOIN [dbo].reserva
        ON room.Id_room = reserva.Id_room;
go
--OUTER JOIN
CREATE VIEW EmpleadoRsv_View WITH SCHEMABINDING 
    AS
      SELECT nombre,apellido,telefono,direccion,puesto
      FROM [dbo].empleado
      FULL JOIN [dbo].reserva
      ON empleado.Id_empleado = reserva.Id_empleado;
      
go

select * from room_view
select * from reserva_view
select * from clientRsv_View
select * from PagoReserva_View
select * from TotalView
select * from roomClient_View
select * from EmpleadoRsv_View

View schema
Execution time: 0,09 sec, rows selected: 34, rows affected: 26, absolute service time: 0,28 sec, absolute service time: 0,3 sec