Question: Here is the coding that goes with these SQL> SQL> CREATE TABLE DDI.ROOMS ( 2 RoomNum NUMBER NOT NULL, 3 RoomSize VARCHAR2(6) NOT NULL, 4

Here is the coding that goes with these

SQL>

SQL> CREATE TABLE DDI.ROOMS (

2 RoomNum NUMBER NOT NULL,

3 RoomSize VARCHAR2(6) NOT NULL,

4 RoomBedCnt NUMBER NOT NULL,

5 RoomRate NUMBER(18,2) NOT NULL,

6 PRIMARY KEY (RoomNum))

7 TABLESPACE USERS;

Table created.

SQL> REM INSERTING into DDI.ROOMS

SQL> Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (101,'Double',1,44);

1 row created.

SQL> Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (102,'Double',2,49);

1 row created.

SQL> Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (103,'Queen ',2,61);

1 row created.

SQL> Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (104,'Queen ',2,61);

1 row created.

SQL> Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (105,'Queen ',2,61);

1 row created.

SQL> Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (106,'King ',1,61);

1 row created.

SQL> Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (107,'King ',1,61);

1 row created.

SQL> Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (108,'King ',1,61);

1 row created.

SQL>

SQL> CREATE TABLE DDI.PATRONS (

2 PatronID NUMBER NOT NULL,

3 FirstName VARCHAR2(16) NOT NULL,

4 LastName VARCHAR2(16) NOT NULL,

5 PhoneNum VARCHAR2(12) Null,

6 eMail VARCHAR2(100) Null,

7 PRIMARY KEY (PatronID))

8 TABLESPACE USERS;

Table created.

SQL> REM INSERTING into DDI.PATRONS

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (1,'Bill','Tulsa','555-485-8356','Bill.Tulsa@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (2,'Chet','Travis','555-972-3076','Chet.Travis@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (3,'Chip','Marino','555-933-2815','Chip.Marino@ourcampus.edu');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (4,'Dag','Renborn','555-595-6240','Dag.Renborn@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (5,'Darryl','Princeton','555-150-3607','Darryl.Princeton@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (6,'Donna','Smith','555-647-4949','Donna.Smith@ourcampus.edu');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (7,'Donna','Smith','555-708-3498','Donna.Smith@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (8,'Douglas','Fischer','555-664-2731','Douglas.Fischer@ourcampus.edu');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (9,'Elizabeth','Stanley','555-187-9958','Elizabeth.Stanley@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (10,'Eric','Jones','555-780-1222','Eric.Jones@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (11,'Eric','Parks','555-752-6266','Eric.Parks@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (12,'Fred','Price','555-176-4902','Fred.Price@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (13,'George','Berkely','555-662-3565','George.Berkely@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (14,'Hilary','Evans','555-860-5908','Hilary.Evans@somewhere.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (15,'James','Thompson','555-243-8344','James.Thompson@ourcampus.edu');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (16,'Jason','Tully','555-172-8639','Jason.Tully@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (17,'John','Watson','555-931-6794','John.Watson@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (18,'Kelly','Oxford','555-208-5106','Kelly.Oxford@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (19,'Linda','Becky','555-761-8562','Linda.Becky@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (20,'Lisa','Brisbon','555-115-5534','Lisa.Brisbon@ourcampus.edu');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (21,'Liz','Frier','555-141-4440','Liz.Frier@somewhere.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (22,'Marsha','Downs','555-248-7905','Marsha.Downs@somewhere.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (23,'Marsha','Downs','555-346-5630','Marsha.Downs@somewhere.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (24,'Mary','Harvard','555-558-2864','Mary.Harvard@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (25,'Matthew','Andrews','555-957-378','Matthew.Andrews@ourcampus.edu');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (26,'Matthew','Andrews','555-957-3780','Matthew.Andrews@ourcampus.edu');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (27,'Merle','Atkins','555-666-1794','Merle.Atkins@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (28,'Mike','Sailors','555-434-2463','Mike.Sailors@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (29,'Miles','Trent','555-971-2236','Miles.Trent@somewhere.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (30,'Pamela','Birch','555-139-1378','Pamela.Birch@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (31,'Richard','James','555-455-5163','Richard.James@somewhere.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (32,'Richard','James','555-815-4831','Richard.James@somewhere.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (33,'Richard','James','555-906-6780','Richard.James@somewhere.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (34,'Richardo','Romez','555-875-7101','Richardo.Romez@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (35,'Sally','Smith','555-329-4189','Sally.Smith@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (36,'Samantha','Jackson','555-632-7417','Samantha.Jackson@mymail.com');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (37,'Terry','Hwang','555-119-9217','Terry.Hwang@ourcampus.edu');

1 row created.

SQL> Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (38,'Tzu','Lai','555-986-2353','Tzu.Lai@ourcampus.edu');

1 row created.

SQL>

SQL> CREATE TABLE DDI.REGISTRATIONS (

2 RegID NUMBER NOT NULL,

3 RegDate DATE NOT NULL,

4 PatronID NUMBER NOT NULL,

5 AdultCnt NUMBER Null,

6 ChildCnt NUMBER Null,

7 RoomNum NUMBER NOT NULL,

8 RegNote VARCHAR2(100) Null,

9 PRIMARY KEY (RegID),

10 FOREIGN KEY (PatronID)

11 REFERENCES DDI.PATRONS,

12 FOREIGN KEY (RoomNum)

13 REFERENCES DDI.ROOMS)

14 TABLESPACE USERS;

Table created.

SQL> REM INSERTING into DDI.REGISTRATIONS

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (1,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,2,0,104,'On vacation');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (2,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,0,2,103,'On vacation');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (3,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),2,2,0,107,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (4,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),3,2,0,108,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (5,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (6,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),5,1,0,106,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (7,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),6,2,1,105,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (8,to_timestamp('01-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),7,2,0,102,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (9,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,2,0,104,'On vacation');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (10,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,0,2,103,'On vacation');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (11,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),8,1,1,105,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (12,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),9,2,0,106,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (13,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),10,2,0,107,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (14,to_timestamp('02-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),11,2,1,108,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (15,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,2,0,104,'On vacation');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (16,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),1,0,2,103,'On vacation');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (17,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),12,2,0,102,'Early arrival');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (18,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),13,2,0,105,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (19,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),14,1,0,106,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (20,to_timestamp('03-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (21,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),15,2,1,103,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (22,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),16,2,0,104,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (23,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),17,2,1,106,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (24,to_timestamp('04-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),18,1,0,107,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (25,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (26,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),19,2,0,106,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (27,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),20,1,0,107,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (28,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),21,2,0,103,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (29,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),22,2,1,104,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (30,to_timestamp('05-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),23,2,0,108,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (31,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),24,2,0,105,'Early arrival');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (32,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),25,2,0,106,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (33,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),26,1,0,101,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (34,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),27,2,0,107,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (35,to_timestamp('06-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),28,1,0,108,'Different Donna Smith');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (36,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),4,1,0,101,'Regular guest');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (37,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),29,2,0,103,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (38,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),30,2,0,104,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (39,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),31,1,0,106,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (40,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),32,2,1,105,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (41,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),33,2,0,107,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (42,to_timestamp('07-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),34,1,0,108,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (43,to_timestamp('08-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),32,2,0,102,'Early arrival');

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (44,to_timestamp('08-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),30,2,0,103,null);

1 row created.

SQL> Insert into DDI.REGISTRATIONS (REGID,REGDATE,PATRONID,ADULTCNT,CHILDCNT,ROOMNUM,REGNOTE) values (45,to_timestamp('08-JUN-15','DD-MON-RR HH.MI.SSXFF AM'),23,2,0,106,null);

1 row created.

SQL>

SQL> CREATE VIEW DDI.LEDGER_VIEW

2 AS

3 SELECT REG.RegID, REG.RegDate, P.LastName, P.FirstName,

4 REG.AdultCnt, REG.ChildCnt, REG.RoomNum,

5 RM.RoomSize, RM.RoomBedCnt, RM.RoomRate

6 FROM DDI.ROOMS RM, DDI.PATRONS P, DDI.REGISTRATIONS REG

7 WHERE REG.PatronID = P.PatronID

8 AND REG.RoomNum = RM.RoomNum

9 ORDER BY REG.RegDate, REG.RoomNum;

Help please I really need help please:

Update your report to produce a report grouped by date, ordered by last name with a subtotal for either the number of people per day or the rate, or both.

How can I show either the number of people per day or the rate or both please help me please

Here is my coding

SQL> SET SERVEROUT ON FORMAT TRUNCATED SQL> DECLARE 2 Room_rec DDI.LEDGER_VIEW%ROWTYPE; 3 Last_room DDI.LEDGER_VIEW.ROOMNUM%TYPE := 0; 4 Sub_Total number := 0; 5 Grand_Total number := 0; 6 7 PROCEDURE HEAD_OF_FORM 8 IS 9 BEGIN 10 DBMS_OUTPUT.NEW_LINE; 11 DBMS_OUTPUT.PUT_LINE(' Room Rates from DDI.LEDGER_VIEW by Room Number '); 12 DBMS_OUTPUT.PUT_LINE('ID RegDate Last First Adults Children Size Beds Rate'); 13 DBMS_OUTPUT.PUT_LINE('--- --------- ------------ ------------ -------- ----------- ----- ------ -----'); 14 END; 15 16 PROCEDURE FORM_BREAK(v_subtot IN OUT NUMBER) 17 IS 18 BEGIN 19 DBMS_OUTPUT.PUT_LINE(' --------'); 20 DBMS_OUTPUT.PUT_LINE('Subtotal ' || 21 LPAD(TO_CHAR(Sub_Total, '$99,999'),8)); 22 DBMS_OUTPUT.NEW_LINE; 23 v_subtot := 0; 24 END; 25 26 PROCEDURE END_OF_FORM (v_gtot IN NUMBER) 27 IS 28 BEGIN 29 DBMS_OUTPUT.PUT_LINE(' --------'); 30 DBMS_OUTPUT.PUT_LINE('Grand Total ' || 31 LPAD(TO_CHAR(v_gtot, '$99,999'),8)); 32 END; 33 34 BEGIN 35 FOR Room_rec IN 36 (SELECT * 37 FROM DDI.LEDGER_VIEW 38 ORDER BY ROOMNUM, REGDATE) 39 LOOP 40 IF (Last_room = 0) THEN 41 HEAD_OF_FORM; 42 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM,'999'), 4)); 43 Last_room := Room_rec.ROOMNUM; 44 Sub_Total := Sub_Total + Room_rec.ROOMRATE; 45 Grand_Total := Grand_Total + Room_rec.ROOMRATE; 46 ELSIF (Room_rec.Roomnum <> Last_room) THEN 47 FORM_BREAK(Sub_Total); 48 HEAD_OF_FORM; 49 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM,'999'), 4)); 50 Last_room := Room_rec.ROOMNUM; 51 Sub_Total := Sub_Total + Room_rec.ROOMRATE; 52 Grand_Total := Grand_Total + Room_rec.ROOMRATE; 53 ELSE 54 DBMS_OUTPUT.PUT(' '); 55 Sub_Total := Sub_Total + Room_rec.ROOMRATE; 56 Grand_Total := Grand_Total + Room_rec.ROOMRATE; 57 END IF; 58 DBMS_OUTPUT.PUT(' '); 59 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMNUM, '999'), 4)); 60 DBMS_OUTPUT.PUT(' '); 61 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.REGID, '999'), 4)); 62 63 DBMS_OUTPUT.PUT(' '); 64 DBMS_OUTPUT.PUT(TO_CHAR(Room_rec.REGDATE, 'MM/DD/YY')); 65 DBMS_OUTPUT.PUT(' '); 66 DBMS_OUTPUT.PUT(RPAD(Room_rec.LASTNAME,12)); 67 DBMS_OUTPUT.PUT(' '); 68 DBMS_OUTPUT.PUT(RPAD(Room_rec.FIRSTNAME,12)); 69 DBMS_OUTPUT.PUT(' '); 70 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ADULTCNT, '99'), 4)); 71 DBMS_OUTPUT.PUT(' '); 72 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.CHILDCNT, '99'), 4)); 73 DBMS_OUTPUT.PUT(' '); 74 DBMS_OUTPUT.PUT(RPAD(Room_rec.ROOMSIZE,8)); 75 DBMS_OUTPUT.PUT(' '); 76 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMBEDCNT, '99'), 4)); 77 DBMS_OUTPUT.PUT(' '); 78 DBMS_OUTPUT.PUT(LPAD(TO_CHAR(Room_rec.ROOMRATE, '$9,999'),6)); 79 DBMS_OUTPUT.NEW_LINE; 80 END LOOP; 81 FORM_BREAK(Sub_Total); 82 END_OF_FORM(Grand_Total); 83 EXCEPTION 84 WHEN NO_DATA_FOUND THEN 85 DBMS_OUTPUT.PUT_LINE('No data found.'); 86 87 END; 88 /

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

What's wrong with my SQL CODE: clear columns; SET WRAP OFF SET LINESIZE 3000 COLUMN ROOMNUM HEADING ROOMNUM FORMAT 999999999 COLUMN REGDATE HEADING 'ARRIVAl DATE' FORMAT DATE COLUMN LASTNAME HEADING...

This is the question to Project 3 1. Project 3 is about printing multiple records from DDI.LEDGER_VIEW using a PL/SQL LOOP 2. SPOOL your output to c:\CS4210\project3spool.txt 3. Set up a record...

I am getting an error when I work this program and I don't know why. Here are the DDI.LEDGER_VIEW codes: CREATE TABLE DDI.ROOMS ( RoomNum NUMBER NOT NULL, RoomSize VARCHAR2(6) NOT NULL, RoomBedCnt...

Project 3: Printing multiple records 1. Project 3 is about printing multiple records from DDI.LEDGER_VIEW using a PL/SQL LOOP 2. SPOOL your output to c:\CS\project3spool.txt 3. Set up a record...

Script for DDI: DROP USER DDI CASCADE; CREATE USER DDI PROFILE DEFAULT IDENTIFIED BY oracle DEFAULT TABLESPACE USERS ACCOUNT UNLOCK; GRANT CONNECT TO DDI; GRANT RESOURCE TO DDI; CREATE TABLE...

Project 2 is about printing variables for a single record from DDI.LEDGER_VIEW using PL/SQL 1. SPOOL your output 2. Set up variables in the DECLARE section. Use either %TYPE or %ROWTYPE 3. SELECT all...

The Dew Drop Inn was built during the age of mom and pop motels. It started out as four rustic log cabins in the style popular during the sixties. During the ensuing years it was rebuilt, and now...

these are 4 sql tables item (iid, name, description, qty) location (lid, name, arrival) order (oid, status, lid, cname, odate) order_items (oid, iid, qty, cost) with the code. I need help on coding...

The Sun Valley Foundry regularly uses 10,000 axles per year. It can purchase axles for $100 each. Ordering costs are $10 per order, and the holding costs of items in inventory are 20 percent of cost...

A train is traveling south at 24.0 m/s when the brakes are applied. It slows down with a constant acceleration to a speed of 6.00 m/s in a time of 9.00 s. (a) Draw a graph of vx versus t for a 12-s...

1 , ( i ( i 1 ) Section B Why is credit risk analysis an important component of financial institution risk management? What recent activities by financial institutions have made the task of credit...

5. If the risk-free rate =2%, market return =12%, and the firm's stock beta is 1.5 , the weight of debt in total capital is 65%, the yield to maturity of the firm's bond =8%, tax rate =21%, what is...

1. What types of companies are most likely to adopt cloud-based CRM software services? Why? What companies might not be well-suited for this type of software? Salesforce.com is the most successful...

8-13 What is a botnet? The IT sector is one of the key drivers of the European economy. It has been estimated that 60 percent of Europeans use the Internet regularly. Additionally, 87 percent own or...

8-15 Explain how a cyber attack can be carried out. The IT sector is one of the key drivers of the European economy. It has been estimated that 60 percent of Europeans use the Internet regularly....