Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- select hotel_id , count(*) as NumberOfRooms
- from room
- group by hotel_id;
- # | hotel_id | NumberOfRooms
- -----------------------------
- 1 | 1111 | 1
- 2 | 2222 | 2
- 3 | 3333 | 2
- 4 | 4444 | 1
- select count(distinct client_id) as NumberOfClients
- from payment;
- # | NumberOfClients
- --------------------
- 1 | 4
- 2 | 2
- 3 | 3
- 4 | 4
- # | hotel_id | NumberOfRooms |NumberOfClients |
- -----------------------------------------------
- 1 | 1111 | 1 | 4 |
- 2 | 2222 | 2 | 2 |
- 3 | 3333 | 2 | 3 |
- 4 | 4444 | 1 | 4 |
- CREATE TABLE "RIADORA"."PAYMENT"
- ( "CLIENT_ID" NUMBER(20,6) NOT NULL ENABLE,
- "PAYMENT_ID" NUMBER(20,6) NOT NULL ENABLE,
- "ROOM_ID" NUMBER(20,6) NOT NULL ENABLE,
- "PAYMENT_DATE" DATE NOT NULL ENABLE,
- "PERIOD" NUMBER(20,6) NOT NULL ENABLE,
- "SUMM" NUMBER(20,6) NOT NULL ENABLE,
- "PERSONAL_ID" NUMBER(20,6) NOT NULL ENABLE,
- CONSTRAINT "PAYMENT_PK" PRIMARY KEY ("PAYMENT_ID")
- USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "USERS" ENABLE,
- CONSTRAINT "CLIENTP" FOREIGN KEY ("CLIENT_ID")
- REFERENCES "RIADORA"."CLIENT" ("CLIENT_ID") ENABLE,
- CONSTRAINT "ROOMP" FOREIGN KEY ("ROOM_ID")
- REFERENCES "RIADORA"."ROOM" ("ROOM_ID") ENABLE,
- CONSTRAINT "PERSONALP" FOREIGN KEY ("PERSONAL_ID")
- REFERENCES "RIADORA"."PERSONAL" ("PERSONAL_ID") ENABLE
- ) SEGMENT CREATION IMMEDIATE
- PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "USERS" ;
- COMMENT ON COLUMN "RIADORA"."PAYMENT"."CLIENT_ID" IS 'ID number of client';
- COMMENT ON COLUMN "RIADORA"."PAYMENT"."PAYMENT_ID" IS 'ID number of payment';
- COMMENT ON COLUMN "RIADORA"."PAYMENT"."ROOM_ID" IS 'ID number of room';
- COMMENT ON COLUMN "RIADORA"."PAYMENT"."PAYMENT_DATE" IS 'Date when payment occured';
- COMMENT ON COLUMN "RIADORA"."PAYMENT"."PERIOD" IS 'Period of days, during which client stays at hotel';
- COMMENT ON COLUMN "RIADORA"."PAYMENT"."SUMM" IS 'Amount of money paid';
- COMMENT ON COLUMN "RIADORA"."PAYMENT"."PERSONAL_ID" IS 'ID number of personal';
- COMMENT ON TABLE "RIADORA"."PAYMENT" IS 'Payment data';
- CREATE TABLE "RIADORA"."ROOM"
- ( "PRICE" NUMBER(20,6) NOT NULL ENABLE,
- "ROOM_SIZE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
- "ROOM_ID" NUMBER(20,6) NOT NULL ENABLE,
- "HOTEL_ID" NUMBER(20,6) NOT NULL ENABLE,
- CONSTRAINT "ROOM_PK" PRIMARY KEY ("ROOM_ID")
- USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "USERS" ENABLE,
- CONSTRAINT "HOTEL_ID" FOREIGN KEY ("HOTEL_ID")
- REFERENCES "RIADORA"."HOTEL" ("HOTEL_ID") ENABLE
- ) SEGMENT CREATION IMMEDIATE
- PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
- STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
- PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
- TABLESPACE "USERS" ;
- COMMENT ON COLUMN "RIADORA"."ROOM"."PRICE" IS 'Rooms price';
- COMMENT ON COLUMN "RIADORA"."ROOM"."ROOM_SIZE" IS 'Size of room';
- COMMENT ON COLUMN "RIADORA"."ROOM"."ROOM_ID" IS 'ID number of room';
- COMMENT ON COLUMN "RIADORA"."ROOM"."HOTEL_ID" IS 'ID number of hotel';
- COMMENT ON TABLE "RIADORA"."ROOM" IS 'Rooms data';
Add Comment
Please, Sign In to add comment