db_hotelc , Sql Server

db_hotelc

--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','2009-11-15 14:30:44','1000');                                           
--UPDATE

UPDATE cliente
        SET nombre = 'juan',apellido = 'Peña'
        WHERE Id_cliente = '56'
        AND telefono='1584567890' ;
        
-- DELETE    
   DELETE FROM pago
   WHERE total='1000';

-- ALTER
    ALTER TABLE empleado
    ADD email varchar(255);
        
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
CREATE VIEW roomRsv_View WITH SCHEMABINDING 
    AS
 SELECT Id_cliente,Id_room,fechaE, fechaS
 FROM [dbo].reserva
 WHERE Id_room IS NOT NULL 
go
select * from room
select * from cliente
select * from empleado
select * from reserva
select * from pago

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
--select * from empleado
select * from roomRsv_View
go


run \| edit \| history \| help	0