mysql booking room

DROP TABLE IF EXISTS sesi;
CREATE TABLE sesi(
    idsesi int2,
    namasesi VARCHAR(50),
    PRIMARY KEY (idsesi)
);
INSERT INTO sesi(idsesi,namasesi)
VALUES
    (1,'08:00-09:00'),
    (2,'09:00-10:00'),
    (3,'10:00-11:00'),
    (4,'11:00-12:00'),
    (5,'13:00-14:00'),
    (6,'14:00-15:00'),
    (7,'15:00-16:00'),
    (8,'16:00-17:00')
;

DROP TABLE IF EXISTS ruangan;
CREATE TABLE ruangan(
    idruangan int2,
    namaruangan VARCHAR(50),
    PRIMARY KEY (idruangan)
);

INSERT INTO ruangan(idruangan,namaruangan)
VALUES
    (1,'101'),
    (2,'102'),
    (3,'201'),
    (4,'202'),
    (5,'301'),
    (6,'302')
;

DROP TABLE IF EXISTS USER;
CREATE TABLE USER(
    iduser int2,
    namauser VARCHAR(50),
    PRIMARY KEY (iduser)
);

INSERT INTO USER(iduser,namauser)
VALUES
    (1,'Andi'),
    (2,'Budi'),
    (3,'Carla'),
    (4,'Dewi'),
    (5,'Edo'),
    (6,'Farah')
;

DROP TABLE IF EXISTS seminar;
CREATE TABLE seminar(
    idseminar int2,
    idsesi int2,
    iduser int2,
    idruangan int2,
    tanggal DATE,
    PRIMARY KEY (idseminar)
);

INSERT INTO seminar(idseminar,idsesi,iduser,idruangan,tanggal)
VALUES
    (1,2,1,2,'2023-11-20'),
    (2,3,2,1,'2023-11-20'),
    (3,5,3,4,'2023-11-21'),
    (4,5,4,3,'2023-11-21'),
    (5,6,5,4,'2023-11-21'),
    (6,6,6,2,'2023-11-21')
;
-- status ruangan '202' pada tanggal '2023-11-21'
SELECT
    s.namasesi,
    s2.namauser,
    CASE
        WHEN s2.namauser IS NULL THEN
            'tersedia'
        ELSE
            'booked'
    END AS statusruangan
FROM
    (
    SELECT
        s.tanggal,
        r.namaruangan,
        u.namauser,
        s.idsesi
    FROM seminar s
        JOIN ruangan r
            ON r.idruangan=s.idruangan
                AND r.namaruangan='202'
        JOIN USER u
            ON s.iduser=u.iduser
    ) s2
    RIGHT JOIN sesi s
        ON s2.idsesi=s.idsesi
            AND s2.tanggal='2023-11-21'
ORDER BY
    s.namasesi
;

+-------------+----------+---------------+
| namasesi    | namauser | statusruangan |
+-------------+----------+---------------+
| 08:00-09:00 | NULL     | tersedia      |
| 09:00-10:00 | NULL     | tersedia      |
| 10:00-11:00 | NULL     | tersedia      |
| 11:00-12:00 | NULL     | tersedia      |
| 13:00-14:00 | Carla    | booked        |
| 14:00-15:00 | Edo      | booked        |
| 15:00-16:00 | NULL     | tersedia      |
| 16:00-17:00 | NULL     | tersedia      |
+-------------+----------+---------------+
8 ROWS IN SET (0.01 sec)

-- status ruangan '101' pada tanggal '2023-11-21'
SELECT
    s.namasesi,
    s2.namauser,
    CASE
        WHEN s2.namauser IS NULL THEN
            'tersedia'
        ELSE
            'booked'
    END AS statusruangan
FROM
    (
    SELECT
        s.tanggal,
        r.namaruangan,
        u.namauser,
        s.idsesi
    FROM seminar s
        JOIN ruangan r
            ON r.idruangan=s.idruangan
                AND r.namaruangan='101'
        JOIN USER u
            ON s.iduser=u.iduser
    ) s2
    RIGHT JOIN sesi s
        ON s2.idsesi=s.idsesi
            AND s2.tanggal='2023-11-21'
ORDER BY
    s.namasesi
;

+-------------+----------+---------------+
| namasesi    | namauser | statusruangan |
+-------------+----------+---------------+
| 08:00-09:00 | NULL     | tersedia      |
| 09:00-10:00 | NULL     | tersedia      |
| 10:00-11:00 | NULL     | tersedia      |
| 11:00-12:00 | NULL     | tersedia      |
| 13:00-14:00 | NULL     | tersedia      |
| 14:00-15:00 | NULL     | tersedia      |
| 15:00-16:00 | NULL     | tersedia      |
| 16:00-17:00 | NULL     | tersedia      |
+-------------+----------+---------------+
8 ROWS IN SET (0.63 sec)