Assignment on Script to Create Database

Two tasks have been completed. Task 4 and Task 6
Task 4
-- SCRIPT TO CREATE DATABASE, TABLES, ROWS
/****** Object: Table [dbo].[Agent] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Agent]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Agent]
GO
/****** Object: Table [dbo].[Booking] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Booking]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Booking]
GO
/****** Object: Table [dbo].[Checkout] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Checkout]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Checkout]
GO
/****** Object: Table [dbo].[Customer] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Customer]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Customer]
GO
/****** Object: Table [dbo].[Hotel] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Hotel]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Hotel]
GO
/****** Object: Table [dbo].[Room] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Room]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Room]
GO
/****** Object: Table [dbo].[Time] Script Date: 04/09/2018 22:39:00 ******/
IF EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Time]')
AND type IN (N'U')
)
DROP TABLE [dbo].[Time]
GO
/****** Object: Table [dbo].[Time] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Time]')
AND type IN (N'U')
)
BEGIN
CREATE TABLE [dbo].[Time] (
[time_id] [int] NOT NULL
,[day] [int] NULL
,[month] [int] NULL
,[year] [int] NULL
,CONSTRAINT [PK_Time] PRIMARY KEY CLUSTERED ([time_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
1
,1
,2
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
2
,1
,2
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
3
,1
,2
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
4
,1
,2
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]

)
VALUES (
5
,1
,1
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
6
,1
,1
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
7
,1
,1
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
8
,1
,1
,2018
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
9
,1
,1
,2017
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
10
,1
,1
,2017
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
11

,1
,1
,2017
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
12
,1
,2
,2017
)
INSERT [dbo].[Time] (
[time_id]
,[day]
,[month]
,[year]
)
VALUES (
13
,1
,1
,2017
)
/****** Object: Table [dbo].[Room] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Room]')
AND type IN (N'U')
)
BEGIN
CREATE TABLE [dbo].[Room] (
[room_id] [int] NOT NULL
,[hotel_id] [int] NOT NULL
,[no_of_beds] [int] NULL
,[tv] [bit] NULL
,[whirlpool_bath] [bit] NULL
,[promotion] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,[rate] [decimal](18, 2) NULL
,CONSTRAINT [PK_Room] PRIMARY KEY CLUSTERED ([room_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
1
,5
,NULL
,NULL
,NULL
,NULL
,NULL

)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
2
,5
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
3
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
4
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
5
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]

,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
6
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
7
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
8
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
9
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]

)
VALUES (
10
,1
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
11
,5
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
12
,5
,NULL
,NULL
,NULL
,NULL
,NULL
)
INSERT [dbo].[Room] (
[room_id]
,[hotel_id]
,[no_of_beds]
,[tv]
,[whirlpool_bath]
,[promotion]
,[rate]
)
VALUES (
13
,5
,NULL
,NULL
,NULL
,NULL
,NULL
)
/****** Object: Table [dbo].[Hotel] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Hotel]')
AND type IN (N'U')
)

BEGIN
CREATE TABLE [dbo].[Hotel] (
[hotel_id] [int] NOT NULL
,[city] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,[province] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,[country] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,[category] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,CONSTRAINT [PK_Hotel] PRIMARY KEY CLUSTERED ([hotel_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
1
,N'calcutta '
,NULL
,N'india '
,N'4 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
2
,N'calcutta '
,NULL
,N'india '
,N'2 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
3
,N'delhi '
,NULL
,N'india '
,N'4 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
4
,N'mumbai '
,NULL
,N'india '
,N'4 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]

,[province]
,[country]
,[category]
)
VALUES (
5
,NULL
,NULL
,N'brazil '
,N'1 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
6
,NULL
,NULL
,N'brazil '
,N'2 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
7
,NULL
,NULL
,N'brazil '
,N'3 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
8
,NULL
,NULL
,N'brazil '
,N'4 '
)
INSERT [dbo].[Hotel] (
[hotel_id]
,[city]
,[province]
,[country]
,[category]
)
VALUES (
9
,NULL
,NULL
,N'albania '
,N'1 '
)
/****** Object: Table [dbo].[Customer] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *

FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Customer]')
AND type IN (N'U')
)
BEGIN
CREATE TABLE [dbo].[Customer] (
[customer_id] [int] NOT NULL
,[name] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED ([customer_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
1
,N'a '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
2
,N'b '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
3
,N'c '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
4
,N'd '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
5
,N'e '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
6
,N'f '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
7
,N'g '
)

INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
8
,N'h '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
9
,N'i '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
10
,N'j '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
11
,N'k '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
12
,N'l '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
13
,N'm '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
14
,N'n '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
15
,N'o '
)
INSERT [dbo].[Customer] (
[customer_id]
,[name]
)
VALUES (
16
,N'p '
)

/****** Object: Table [dbo].[Checkout] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Checkout]')
AND type IN (N'U')
)
BEGIN
CREATE TABLE [dbo].[Checkout] (
[checkout_id] [int] NOT NULL
,[room_id] [int] NULL
,[hotel_id] [int] NULL
,[customer_id] [int] NULL
,[time_id] [int] NULL
,[revenue] [decimal](18, 2) NULL
,CONSTRAINT [PK_Checkout] PRIMARY KEY CLUSTERED ([checkout_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
1
,1
,1
,1
,1
,CAST(500.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
2
,2
,1
,2
,2
,CAST(600.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
3
,3
,1
,3
,3

,CAST(700.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
4
,4
,1
,4
,4
,CAST(800.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
5
,5
,1
,5
,5
,CAST(900.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
6
,6
,1
,6
,6
,CAST(400.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
7
,7
,1
,7
,7
,CAST(500.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
8

,8
,1
,8
,8
,CAST(800.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
9
,9
,5
,9
,9
,CAST(900.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Checkout] (
[checkout_id]
,[room_id]
,[hotel_id]
,[customer_id]
,[time_id]
,[revenue]
)
VALUES (
10
,10
,5
,10
,10
,CAST(900.00 AS DECIMAL(18, 2))
)
/****** Object: Table [dbo].[Booking] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Booking]')
AND type IN (N'U')
)
BEGIN
CREATE TABLE [dbo].[Booking] (
[booking_id] [int] NOT NULL
,[customer_id] [int] NULL
,[room_id] [int] NULL
,[hotel_id] [int] NULL
,[agent_id] [int] NULL
,[time_id] [int] NULL
,[revenue] [decimal](18, 2) NULL
,CONSTRAINT [PK_Booking] PRIMARY KEY CLUSTERED ([booking_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]

,[revenue]
)
VALUES (
1
,1
,1
,1
,NULL
,1
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
2
,2
,2
,1
,NULL
,2
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
3
,3
,3
,1
,NULL
,3
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
4
,4
,4
,1
,NULL
,4
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
5
,5

,5
,1
,NULL
,5
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
6
,6
,6
,1
,NULL
,6
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
7
,7
,7
,1
,NULL
,7
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
8
,8
,8
,1
,NULL
,8
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
9
,9
,9
,5
,NULL
,9
,CAST(100.00 AS DECIMAL(18, 2))

)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
10
,10
,10
,5
,NULL
,10
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
11
,11
,11
,5
,NULL
,11
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
12
,12
,12
,5
,NULL
,12
,CAST(100.00 AS DECIMAL(18, 2))
)
INSERT [dbo].[Booking] (
[booking_id]
,[customer_id]
,[room_id]
,[hotel_id]
,[agent_id]
,[time_id]
,[revenue]
)
VALUES (
13
,13
,13
,5
,NULL
,13
,CAST(100.00 AS DECIMAL(18, 2))
)
/****** Object: Table [dbo].[Agent] Script Date: 04/09/2018 22:39:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (
SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[Agent]')
AND type IN (N'U')
)
BEGIN
CREATE TABLE [dbo].[Agent] (
[agent_id] [int] NOT NULL
,[name] [nchar](10) COLLATE Latin1_General_CI_AI NULL
,CONSTRAINT [PK_Agent] PRIMARY KEY CLUSTERED ([agent_id] ASC) WITH (
PAD_INDEX = OFF
,STATISTICS_NORECOMPUTE = OFF
,IGNORE_DUP_KEY = OFF
,ALLOW_ROW_LOCKS = ON
,ALLOW_PAGE_LOCKS = ON
)
)
END
GO
TASK 6
--Query1
SELECT T1.country
,T1.year
,T1.month
,T1.booked
,T2.checkedout
,(
(
SELECT count(*)
FROM room
LEFT JOIN booking ON room.room_id = booking.room_id
LEFT JOIN hotel ON room.hotel_id = hotel.hotel_id
LEFT JOIN TIME ON booking.time_id = TIME.time_id
GROUP BY hotel.country
HAVING t1.country = hotel.country
) - (T1.booked + T2.checkedout)
) AS 'Free'
FROM (
SELECT hotel.country
,TIME.year
,TIME.month
,COUNT(booking.room_id) AS booked
FROM booking
LEFT JOIN room ON room.room_id = booking.room_id
LEFT JOIN hotel ON room.hotel_id = hotel.hotel_id
LEFT JOIN TIME ON booking.time_id = TIME.time_id
GROUP BY hotel.country
,TIME.year
,TIME.month
) AS T1
INNER JOIN (
SELECT hotel.country
,TIME.year
,TIME.month
,COUNT(checkout.room_id) AS checkedout
FROM checkout
LEFT JOIN room ON room.room_id = checkout.room_id
LEFT JOIN hotel ON room.hotel_id = hotel.hotel_id
LEFT JOIN TIME ON checkout.time_id = TIME.time_id
GROUP BY hotel.country
,TIME.year
,TIME.month
) AS T2 ON T1.country = T2.country
AND T1.year = T2.year
AND T1.month = T2.month
--Query 2
SELECT T1.country
,T1.year
,
--T1.month ,

T1.booked
,T2.checkedout
,(
(
SELECT count(*)
FROM room
LEFT JOIN booking ON room.room_id = booking.room_id
LEFT JOIN hotel ON room.hotel_id = hotel.hotel_id
LEFT JOIN TIME ON booking.time_id = TIME.time_id
GROUP BY hotel.country
HAVING t1.country = hotel.country
) - (T1.booked + T2.checkedout)
) AS 'Free'
,booking_revenue
,checkedout_revenue
,booking_revenue + checkedout_revenue AS 'cumulative_revenue'
,T2.checkedout_revenue / T1.booking_revenue AS CheckedOutRatio
,DENSE_RANK() OVER (
ORDER BY T2.checkedout_revenue / T1.booking_revenue
) AS RankForTheYear
FROM (
SELECT hotel.country
,
--time.month,
TIME.year
,COUNT(booking.room_id) AS booked
,SUM(booking.revenue) AS booking_revenue
FROM booking
LEFT JOIN room ON room.room_id = booking.room_id
LEFT JOIN hotel ON room.hotel_id = hotel.hotel_id
LEFT JOIN TIME ON booking.time_id = TIME.time_id
GROUP BY hotel.country
,
--time.month,
TIME.year
) AS T1
INNER JOIN (
SELECT hotel.country
,TIME.year
,
--time.month,
COUNT(checkout.room_id) AS checkedout
,SUM(checkout.revenue) AS checkedout_revenue
FROM checkout
LEFT JOIN room ON room.room_id = checkout.room_id
LEFT JOIN hotel ON room.hotel_id = hotel.hotel_id
LEFT JOIN TIME ON checkout.time_id = TIME.time_id
GROUP BY hotel.country
,
--time.month,
TIME.year
) AS T2 ON T1.country = T2.country
AND T1.year = T2.year
--AND T1.month=T2.month
ORDER BY year DESC
,CheckedOutRatio DESC
,RankForTheYear ASC
--Query3
SELECT T1.country
,T1.year
,T1.month
,T1.booked
,T2.checkedout
,(
(
SELECT count(*)
FROM room
LEFT JOIN booking ON room.room_id = booking.room_id
LEFT JOIN hotel ON room.hotel_id = hotel.hotel_id
LEFT JOIN TIME ON booking.time_id = TIME.time_id
GROUP BY hotel.country
HAVING t1.country = hotel.country
) - (T1.booked + T2.checkedout)
) AS 'Free'
,booking_revenue
,checkedout_revenue
,booking_revenue + checkedout_revenue AS 'cumulative_revenue'
FROM (
SELECT hotel.country
,TIME.year
,TIME.month

,COUNT(booking.room_id) AS booked
,SUM(booking.revenue) AS booking_revenue
FROM booking
LEFT JOIN room ON room.room_id = booking.room_id
LEFT JOIN hotel ON room.hotel_id = hotel.hotel_id
LEFT JOIN TIME ON booking.time_id = TIME.time_id
WHERE hotel.category = 4
GROUP BY hotel.country
,TIME.year
,TIME.month
) AS T1
INNER JOIN (
SELECT hotel.country
,TIME.year
,TIME.month
,COUNT(checkout.room_id) AS checkedout
,SUM(checkout.revenue) AS checkedout_revenue
FROM checkout
LEFT JOIN room ON room.room_id = checkout.room_id
LEFT JOIN hotel ON room.hotel_id = hotel.hotel_id
LEFT JOIN TIME ON checkout.time_id = TIME.time_id
WHERE hotel.category = 4
GROUP BY hotel.country
,TIME.year
,TIME.month
) AS T2 ON T1.country = T2.country
AND T1.year = T2.year
AND T1.month = T2.month