db_hotelLEFT, 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,
  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],
  [direccion] [VARCHAR] (20) 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 ,
  [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','grande','suit','5000','10'),
                                                              ('25','economica','sencilla','500','3');
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) VALUES('1','karen','hernandez','12365478','calle mexico'),
                                                                              ('2','fernando' ,'torres','12365478','calle mexico');   
INSERT INTO [dbo].[reserva](Id_cliente, Id_empleado,Id_room, fechaE, fechaS, direccion ) VALUES ('1','2','1','2008-11-11 13:23:44','2008-11-15 14:30:44','Lazaro cradenas'),
                                                                                        ('56','1','25','2009-11-11 13:23:44','2009-11-15 14:30:44','Mexica');
                                                                                        
INSERT INTO [dbo].[pago]( Id_pago,Id_rsv,fecha, total ) VALUES ('1','1','2008-11-15 14:30:44','2550.50');
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 Suite, fechaE as [Fecha Ingreso], fechaS as [Fecha Salida], direccion from reserva;
go

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

select * from room_view
select * from reserva_view
select * from clientRsv_View
go

View schema
Execution time: 0,05 sec, rows selected: 7, rows affected: 9, absolute service time: 0,27 sec, absolute service time: 0,27 sec