CREATE table "GUEST" (
    "GUEST_NO"   NUMBER NOT NULL,
    "NAME"       VARCHAR2(20),
    "ADDRESS"    VARCHAR2(20),
    constraint  "GUEST_PK" primary key ("GUEST_NO")
);

CREATE table "ROOM" (
    "ROOM_NO"    NUMBER,
    "HOTEL_NO"   NUMBER,
    "TYPE"       VARCHAR2(10),
    "PRICE"      NUMBER,
    constraint  "ROOM_PK" primary key ("ROOM_NO")
);

CREATE TABLE BOOKING (
     HOTEL_NO NUMBER,
     GUEST_NO NUMBER,
     DATE_FROM DATE,
     DATE_TO   DATE,
     ROOM_NO   NUMBER
);

CREATE TABLE HOTEL(
	HOTEL_NO NUMBER PRIMARY KEY,
	NAME  VARCHAR(20),
	CITY  VARCHAR(30)
);
SELECT NAME,ADDRESS FROM GUEST WHERE ADDRESS='LONDON' ORDER BY NAME ASC;

SELECT HOTEL.NAME, COUNT(ROOM.ROOM_NO) FROM HOTEL INNER JOIN ROOM ON ROOM.HOTEL_NO=HOTEL.HOTEL_NO GROUP BY HOTEL.NAME;