CREATE TABLE rooms (
room_id number(3) NOT NULL PRIMARY KEY,
floor number(2) NOT NULL,
light_is_on varchar2(1)
);

INSERT INTO rooms (room_id, floor, light_is_on) VALUES (101, 1, 0);
INSERT INTO rooms (room_id, floor, light_is_on) VALUES (102, 1, 0);
INSERT INTO rooms (room_id, floor, light_is_on) VALUES (103, 1, 1);
INSERT INTO rooms (room_id, floor, light_is_on) VALUES (201, 2, 0);
INSERT INTO rooms (room_id, floor, light_is_on) VALUES (202, 2, 0);
INSERT INTO rooms (room_id, floor, light_is_on) VALUES (301, 3, 1);
INSERT INTO rooms (room_id, floor, light_is_on) VALUES (302, 3, 1);


--Вывести список всех этажей;   
select floor from rooms group by floor;

--Вывести список этажей и кол-во комнат в них;
select floor, COUNT(room_id) "rooms" from rooms group by floor;

--Вывести список всех этажей, где выключен свет. 
--Выключенным светом на этаже считается отсутствие света во всех комнатах этажа;
select floor from rooms where floor not in 
(select floor from rooms where light_is_on = 1) group by floor;

--Вывести макс номер комнаты(по последним двум цифрам) для всего здания;
select max(substr(room_id, -2)) max_number_room  from rooms;

--Написать update который,  для каждой комнаты «нажмет на выключатель»;
update rooms set light_is_on = case when light_is_on = 1 then 0 else 1 end;

--Написать merge который, который для каждой нечетной комнаты «нажмет на выключатель»;
merge into rooms table1 using (SELECT room_id from rooms WHERE mod(room_id, 2) = 1) table2
on (table1.room_id = table2.room_id)
when matched then
update set light_is_on = case when light_is_on = 1 then 0 else 1 end;

--Вывести список этажей и 
--для каждого этажа признак наличия света в комнате с самым большим номером;
select table1.floor, table1.light_is_on from rooms table1
join(select floor, max(room_id) max_room_id from rooms group by floor) table2
on table1.floor = table2.floor and table1.room_id = table2.max_room_id;


--Вывести список этажей и для каждого этажа кол-во комнат 
--с включенным светом на этажах ниже текущего(включая текущий).
select table1.floor, count(table2.room_id) count_rooms from
(select floor from rooms group by floor) table1
join rooms table2 ON table1.floor >= table2.floor and table2.light_is_on = 1
GROUP BY table1.floor