db_hotel29102020, Sql Server

db_hotel29102020

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

select @@version as 'sql server version'

DECLARE @MyDatetime datetime

CREATE TABLE room(
  Id_room INT NOT NULL,
  caract VARCHAR (20)NOT NULL,
  tipoR VARCHAR (20)NOT NULL,
  precio INT NOT NULL,
  npiso INT NOT NULL,
  PRIMARY KEY(Id_room)
);

CREATE TABLE 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 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 reserva(
  Id_rsv INT NOT NULL ,
  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 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) 
 );

INSERT INTO room(Id_room, caract, tipoR, precio,npiso) VALUES ('1','grande','suit','5000','10'),
                                                              ('5','economica','sencilla','500','3');
INSERT INTO cliente(Id_cliente,nombre, apellido, telefono) VALUES ('1','kenia','hernandez','1234567890'),
                                                                  ('56','pablo','fernandez','1584567890');
INSERT INTO empleado( Id_empleado,nombre, apellido,telefono, direccion) VALUES('1','karen','hernandez','12365478','calle mexico'),
                                                                              ('2','fernando' ,'torres','12365478','calle mexico');   
INSERT INTO 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','5','2009-11-11 13:23:44','2009-11-15 14:30:44','Mexica');
                                                                                        
INSERT INTO pago( Id_pago,Id_rsv,fecha, total ) VALUES ('1','1','2008-11-15 14:30:44','2550.50');

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

create view room_view as select caract, tipoR, precio 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

select * from room_view
select * from reserva_view
go


run \| edit \| history \| help	0