Facebook
From ABC, 3 Years ago, written in Plain Text.
Embed
Download Paste or View Raw
Hits: 230
  1. CREATE table "GUEST" (
  2.     "GUEST_NO"   NUMBER NOT NULL,
  3.     "NAME"       VARCHAR2(20),
  4.     "ADDRESS"    VARCHAR2(20),
  5.     constraint  "GUEST_PK" primary key ("GUEST_NO")
  6. );
  7.  
  8. CREATE table "ROOM" (
  9.     "ROOM_NO"    NUMBER,
  10.     "HOTEL_NO"   NUMBER,
  11.     "TYPE"       VARCHAR2(10),
  12.     "PRICE"      NUMBER,
  13.     constraint  "ROOM_PK" primary key ("ROOM_NO")
  14. );
  15.  
  16. CREATE TABLE BOOKING (
  17.      HOTEL_NO NUMBER,
  18.      GUEST_NO NUMBER,
  19.      DATE_FROM DATE,
  20.      DATE_TO   DATE,
  21.      ROOM_NO   NUMBER
  22. );
  23.  
  24. CREATE TABLE HOTEL(
  25.         HOTEL_NO NUMBER PRIMARY KEY,
  26.         NAME  VARCHAR(20),
  27.         CITY  VARCHAR(30)
  28. );
  29. SELECT NAME,ADDRESS FROM GUEST WHERE ADDRESS='LONDON' ORDER BY NAME ASC;
  30.  
  31. SELECT HOTEL.NAME, COUNT(ROOM.ROOM_NO) FROM HOTEL INNER JOIN ROOM ON ROOM.HOTEL_NO=HOTEL.HOTEL_NO GROUP BY HOTEL.NAME;