Обьединение 3х таблиц и агрегация

Select  Sum(Rooms.Capacity) AS Rooms
From Rooms
WHERE Rooms.HostelCode=1

Можно так

SELECT SUM(Capacity) as AllC, c as Busy, (SUM(Capacity) - c) as Free from Rooms as Ro
INNER JOIN
(select Count(*) as c, r.RoomId as room
from Student as s
inner join Rooms as r On r.RoomId=s.RoomId && r.HostelCode=1
 ) as T On true 
 WHERE HostelCode=1

Ссылка на рабочий пример: http://sqlfiddle.com/#!9/16f2ab/3

Либо со вложенным запросом получается еще несколько короче:

SELECT SUM(Capacity) - (select Count(*)
from Student as s
inner join Rooms as r On r.RoomId=s.RoomId && r.HostelCode=1) 
from Rooms
WHERE HostelCode=1;

Ссылка на рабочий пример: http://sqlfiddle.com/#!9/16f2ab/7

Ответ на вопрос в заголовке: начиная с mySQL 8 появилась конструкция With, которая позволяет разбить текст SQL-запроса на логические части.

например,

with HostelCapacity (
    Select HostelCode, RoomId, sum(Rooms.Capacity) AS Capacity
    From Rooms
    group by HostelCode, RoomId
),
RoomsOccupied (
    Select HostelCode, Student.RoomId, count(*) as Occupied
    From Student
    Left join Rooms on (Student.roomid = rooms.roomId)
    Group by HostelCode, RoomId
),
RoomsRemain (
    select HostelCode, roomId, Capacity as Rooms from HostelCapacity 
    union
    select HostelCode, roomId, -Occupied as Rooms from RoomsOccupied
)
select HostelCode, roomId, sum(rooms) from RoomsRemain
where HostelCode = 1
group by HostelCode, roomId

При всей кажущейся неэффективности запроса, движок сделает нормальный план и вполне оптимально выполнит запрос как единое целое. Но при этом человеку гораздо легче воспринимать отдельные части запроса. а главное в таком запросе четко выделяются повторно-используемые части запроса, текст которых вы можете использовать в других запросах. А также очень четко выделяются части, которые движок может распаралеллить.

Довольно глупый но работающий пример.

declare @HostCode int = 1;
Select  Sum(Rooms.Capacity)-(Select COUNT(*)
    From Student inner join Rooms on Rooms.RoomId=Student.StudentId
    inner join Hostel on Hostel.HostelCode=Rooms.HostelCode
    Where Hostel.HostelCode Like @HostCode)
 AS Rooms
From Rooms
WHERE Rooms.HostelCode Like @HostCode