---

USE Hotel;

-- 1.1
SELECT RoomNumber, FloorNumber, IsAvailable
FROM dbo.Room
ORDER BY FloorNumber, RoomNumber;

-- 1.2
SELECT *
FROM dbo.Room
WHERE FloorNumber IN (2, 3);

-- 1.3
SELECT FullName, Country
FROM dbo.Guest
WHERE Country = N'Россия';

-- 1.4
SELECT *
FROM dbo.Booking
WHERE Status = N'Завершено'
ORDER BY CheckIn;

-- 1.5
SELECT TOP 3 *
FROM dbo.RoomType
ORDER BY BasePrice DESC;

-- 1.6
SELECT DISTINCT Status
FROM dbo.Booking;

-- 1.7
SELECT *
FROM dbo.HotelService
WHERE Price BETWEEN 500 AND 1500;

-- 1.8
SELECT *
FROM dbo.Guest
WHERE Phone IS NULL;


-- 2.1
SELECT r.RoomNumber, rt.TypeName, rt.Capacity, rt.BasePrice
FROM dbo.Room r
JOIN dbo.RoomType rt ON r.RoomTypeID = rt.RoomTypeID;

-- 2.2
SELECT b.BookingID, g.FullName, r.RoomNumber,
       b.CheckIn, b.CheckOut, b.Status
FROM dbo.Booking b
JOIN dbo.Guest g ON b.GuestID = g.GuestID
JOIN dbo.Room r ON b.RoomID = r.RoomID;

-- 2.3
SELECT rt.TypeName, r.RoomNumber
FROM dbo.RoomType rt
LEFT JOIN dbo.Room r ON rt.RoomTypeID = r.RoomTypeID;

-- 2.4
SELECT g.FullName, b.BookingID, b.CheckIn, b.CheckOut, b.Status
FROM dbo.Guest g
LEFT JOIN dbo.Booking b ON g.GuestID = b.GuestID;

-- 2.5
SELECT s.ServiceName, su.UsageID, su.BookingID, su.Quantity
FROM dbo.HotelService s
LEFT JOIN dbo.ServiceUsage su ON s.ServiceID = su.ServiceID;

-- 2.6
SELECT b.BookingID, s.ServiceName, su.Quantity, s.Price
FROM dbo.Booking b
JOIN dbo.ServiceUsage su ON b.BookingID = su.BookingID
JOIN dbo.HotelService s ON su.ServiceID = s.ServiceID;


-- 3.1
SELECT
    (SELECT COUNT(*) FROM dbo.Guest)    AS GuestsCount,
    (SELECT COUNT(*) FROM dbo.Room)     AS RoomsCount,
    (SELECT COUNT(*) FROM dbo.Booking)  AS BookingsCount;

-- 3.2
SELECT MIN(BasePrice) AS MinPrice,
       MAX(BasePrice) AS MaxPrice,
       AVG(BasePrice) AS AvgPrice
FROM dbo.RoomType;

-- 3.3
SELECT rt.TypeName, COUNT(r.RoomID) AS RoomsCount
FROM dbo.RoomType rt
LEFT JOIN dbo.Room r ON rt.RoomTypeID = r.RoomTypeID
GROUP BY rt.TypeName;

-- 3.4
SELECT Status, COUNT(*) AS BookingsCount
FROM dbo.Booking
GROUP BY Status;

-- 3.5
SELECT TypeName, AVG(BasePrice) AS AvgBasePrice
FROM dbo.RoomType
GROUP BY TypeName
HAVING AVG(BasePrice) > 5000;


-- 6.1
SELECT FullName, COALESCE(Country, N'Не указана') AS Country
FROM dbo.Guest;

-- 6.2
SELECT UPPER(FullName) AS FullNameUpper, LEN(FullName) AS FullNameLength
FROM dbo.Guest;

-- 6.3
SELECT BookingID, CheckIn, CheckOut,
       DATEDIFF(DAY, CheckIn, CheckOut) AS StayDays
FROM dbo.Booking;

-- 6.4
SELECT BookingID,
       YEAR(CheckIn)  AS CheckInYear,
       MONTH(CheckIn) AS CheckInMonth
FROM dbo.Booking;

-- 6.5
SELECT TypeName, ROUND(BasePrice, -2) AS RoundedBasePrice
FROM dbo.RoomType;


-- 7.1
INSERT INTO dbo.RoomType (TypeName, Capacity, BasePrice)
VALUES (N'Новый тип', 2, 1000);

-- 7.2
INSERT INTO dbo.Room (RoomNumber, RoomTypeID, FloorNumber, IsAvailable)
SELECT N'999', RoomTypeID, 1, 1
FROM dbo.RoomType
WHERE TypeName = N'Новый тип';

-- 7.3
INSERT INTO dbo.HotelService (ServiceName, Price, IsAvailable)
VALUES (N'Новая услуга', 0, 1);

-- 7.4
SELECT *
FROM dbo.HotelService
WHERE ServiceName = N'Завтрак';

UPDATE dbo.HotelService
SET Price = Price * 1.10
WHERE ServiceName = N'Завтрак';

-- 7.5
SELECT *
FROM dbo.ServiceUsage su
JOIN dbo.HotelService s ON su.ServiceID = s.ServiceID
WHERE s.ServiceName = N'Новая услуга';

DELETE s
FROM dbo.HotelService s
WHERE s.ServiceName = N'Новая услуга'
  AND NOT EXISTS (
      SELECT 1
      FROM dbo.ServiceUsage su
      WHERE su.ServiceID = s.ServiceID
  );