SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE [dbo].[RoomTypes]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [Title] NVARCHAR(50) NOT NULL,
    [Description] NVARCHAR(MAX) NOT NULL, 
    [RoomPrice] MONEY NOT NULL, 
)

CREATE TABLE [dbo].[RoomDetails]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY,
    [RoomTypeID] INT NOT NULL, 
    [RoomNumber] NVARCHAR(10) NOT NULL, 
    [Available] BIT NOT NULL, 
    CONSTRAINT [FK_RoomDetails_RoomTypes] FOREIGN KEY ([RoomTypeID]) REFERENCES RoomTypes(Id)
)

CREATE TABLE [dbo].[GuestDetails]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [FristName] NVARCHAR(50) NOT NULL, 
    [LastName] NVARCHAR(50) NOT NULL, 
    [Phone] NVARCHAR(50) NULL, 
    [Email] NVARCHAR(50) NULL
)

CREATE TABLE [dbo].[BookingDetails]
(
    [Id] INT NOT NULL PRIMARY KEY IDENTITY, 
    [FromDate] DATETIME2 NOT NULL, 
    [ToDate] DATETIME2 NOT NULL,
    [CheckIn] BIT NOT NULL DEFAULT 0, 
    [TotalCost] MONEY NOT NULL, 
    [GuestId] INT NOT NULL, 
    [RoomId] INT NOT NULL
    CONSTRAINT [FK_BookingDetails_GuestDetails] FOREIGN KEY ([GuestId]) REFERENCES GuestDetails(Id), 
    CONSTRAINT [FK_BookingDetails_RoomDetails] FOREIGN KEY ([RoomId]) REFERENCES RoomDetails(Id)
)

/*
Post-Deployment Script Template                         
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.       
 Use SQLCMD syntax to include a file in the post-deployment script.         
 Example:      :r .\myfile.sql                              
 Use SQLCMD syntax to reference a variable in the post-deployment script.       
 Example:      :setvar TableName MyTable                            
               SELECT * FROM [$(TableName)]                 
--------------------------------------------------------------------------------------
*/

--if (Select COUNT(*) from dbo.RoomTypes) = 0 
if not exists (select 1 from dbo.RoomDetails)
begin 
DECLARE @a int;
SET @a =  1
 insert into dbo.RoomTypes (Title,Description,RoomPrice) values 
                           ('King Size Bed', 'A room with a king-size bed and a nice view',100),
                           ('Two Queen Size Bed', 'A room with two queen-size beds and a nice view',200),
                           ('Executive Suite', 'Two rooms, each with a King-size bed and a nice view',300);
end

if not exists (select 1 from dbo.RoomDetails)
begin
    declare @roomId1 int;
    declare @roomId2 int;
    declare @roomId3 int;

select @roomId1 = Id from dbo.RoomTypes  where Title = 'King Size Bed';
    select @roomId2 = Id from dbo.RoomTypes  where Title = 'Two Queen Size Bed';
    select @roomId3 = Id from dbo.RoomTypes  where Title = 'Executive Suite';

insert into dbo.RoomDetails (RoomNumber,RoomTypeID,Available) values 
    ('101',@roomId1,1),
    ('102',@roomId1,1),
    ('202',@roomId1,1),
    ('105',@roomId2,1),
    ('205',@roomId2,1),
    ('505',@roomId3,1);

end

9 rows affected