USE master
GO

DECLARE @InBuildingID TINYINT;--заданного корпуса 
SELECT @InBuildingID=1;


--День недели(идентификатор дня недели|название дня недели)
WITH CALENDER AS
(
  SELECT 1 AS DAY_ID,'MONDAY'AS DAYYNAME
   UNION ALL
  SELECT 2,'TUESDAY'
   UNION ALL
  SELECT 3,'WEDNESDAY'
   UNION ALL
  SELECT 4,'THURSDAY'
   UNION ALL
  SELECT 5,'FRIDAY'
   UNION ALL
  SELECT 6,'SATURDAY'
   UNION ALL
  SELECT 7,'SUNDAY'      
),
--Корпус(название корпуса|адрес корпуса), 
BUILDING AS
(
  SELECT 1 AS BUILDING_ID,'BUILDING 1'AS BUILDINGDESCRIPTION
   UNION ALL
  SELECT 2,'BUILDING 2'
   UNION ALL
  SELECT 3,'BUILDING 3'  
),
--Аудитория(номер аудитории|кол-во мест в аудитории|название корпуса|идентификатор типа аудитории)
ROOM AS
(
  SELECT 1 AS ROOM_NUMBER,5 AS SEATSCOUNT,1 AS BUILDING_ID,'A'AS ROOMTYPE
   UNION ALL
  SELECT 2 AS ROOM_NUMBER,10 AS SEATSCOUNT,1 AS BUILDING_ID,'B'AS ROOMTYPE
   UNION ALL
  SELECT 3 AS ROOM_NUMBER,50 AS SEATSCOUNT,2 AS BUILDING_ID,'ZZ'AS ROOMTYPE  
),
--Занятие(идентификатор занятия|номер аудитории|номер пары|идентификатор дня недели)
LESSON AS
(
  SELECT 1 AS LESSON_ID,1 AS ROOM_NUMBER,1 AS PARANUMBER,1 AS DAY_ID
   UNION ALL
  SELECT 2 AS LESSON_ID,2 AS ROOM_NUMBER,2 AS PARANUMBER,2 AS DAY_ID
   UNION ALL
  SELECT 3 AS LESSON_ID,3 AS ROOM_NUMBER,5 AS PARANUMBER,7 AS DAY_ID  
)

/*
Нужно составить запрос: для каждой аудитории заданного корпуса 
и каждого дня недели вывести сведения о наличии 
занятий(если занятий нет, то полю столбца 'Наличие занятие' присваивается '-', если есть - '+').
Использовать соединение таблиц Занятие и Аудитория. 
*/
SELECT R.ROOM_NUMBER,R.ROOMTYPE,R.SEATSCOUNT,C.DAYYNAME,
CASE
 WHEN L.LESSON_ID IS NULL THEN '-'
 ELSE '+'
END AS IS_LESSON_PRESENT 
FROM ROOM AS R
CROSS JOIN CALENDER AS C
LEFT JOIN LESSON AS L ON R.ROOM_NUMBER=L.ROOM_NUMBER AND L.DAY_ID=C.DAY_ID
WHERE R.BUILDING_ID=@InBuildingID;