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 variable in the DECLARE section using %ROWTYPE

4. SELECT all the fields in DDI.LEDGER_VIEW where ROOMNUM is 107

5. Use DBMS__OUTPUT.PUT_LINE to print the values for each field for each record.

6. Compile and run the procedure.

Here is an example of printing variables for a single record from DDI.LEDGER_VIEW using PL/SQL:

DECLARE

REGID DDI.LEDGER_VIEW.regid%type;

REGDATE DDI.LEDGER_VIEW.regdate%type;

PATRONID DDI.LEDGER_VIEW.patronid%type;

ADULTCNT DDI.LEDGER_VIEW.adultcnt%type;

CHILDCNT DDI.LEDGER_VIEW.childcnt%type;

ROOMNUM DDI.LEDGER_VIEW.roomnum%type;

BEGIN

SELECT REGID, REGDATE, PATRONID, ADULTCNT, CHILDCNT, ROOMNUM FROM DDI.LEDGER_VIEW WHERE REGID = REGID;

dbms_output.put_line('REGID' || REGID);

dbms_output.put_line('REGDATE' || REGDATE);

dbms_output.put_line('PATRONID' || PATRONID);

dbms_output.put_line('ADULTCNT' || ADULTCNT);

dbms_output.put_line('CHILDCNT' || CHILDCNT);

dbms_output.put_line('ROOMNUM' || ROOMNUM);

EXCEPTION

WHEN no_data_found THEN dbms_output.put_line('Data not found');

WHEN others THEN dbms_output.put_line('Error');

END;

.

SELECT REGID, REGDATE, PATRONID, ADULTCNT, CHILDCNT, ROOMNUM FROM REGISTRATIONS WHERE REGID = 17;

here is the script for database tables script:

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 DDI.ROOMS ( RoomNum NUMBER NOT NULL, RoomSize VARCHAR2(6) NOT NULL, RoomBedCnt NUMBER NOT NULL, RoomRate NUMBER(18,2) NOT NULL, PRIMARY KEY (RoomNum)) TABLESPACE USERS; REM INSERTING into DDI.ROOMS Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (101,'Double',1,44); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (102,'Double',2,49); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (103,'Queen ',2,61); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (104,'Queen ',2,61); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (105,'Queen ',2,61); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (106,'King ',1,61); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (107,'King ',1,61); Insert into DDI.ROOMS (ROOMNUM,ROOMSIZE,ROOMBEDCNT,ROOMRATE) values (108,'King ',1,61); CREATE TABLE DDI.PATRONS ( PatronID NUMBER NOT NULL, FirstName VARCHAR2(16) NOT NULL, LastName VARCHAR2(16) NOT NULL, PhoneNum VARCHAR2(12) Null, eMail VARCHAR2(100) Null, PRIMARY KEY (PatronID)) TABLESPACE USERS; REM INSERTING into DDI.PATRONS Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (1,'Bill','Tulsa','555-485-8356','Bill.Tulsa@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (2,'Chet','Travis','555-972-3076','Chet.Travis@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (3,'Chip','Marino','555-933-2815','Chip.Marino@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (4,'Dag','Renborn','555-595-6240','Dag.Renborn@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (5,'Darryl','Princeton','555-150-3607','Darryl.Princeton@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (6,'Donna','Smith','555-647-4949','Donna.Smith@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (7,'Donna','Smith','555-708-3498','Donna.Smith@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (8,'Douglas','Fischer','555-664-2731','Douglas.Fischer@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (9,'Elizabeth','Stanley','555-187-9958','Elizabeth.Stanley@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (10,'Eric','Jones','555-780-1222','Eric.Jones@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (11,'Eric','Parks','555-752-6266','Eric.Parks@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (12,'Fred','Price','555-176-4902','Fred.Price@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (13,'George','Berkely','555-662-3565','George.Berkely@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (14,'Hilary','Evans','555-860-5908','Hilary.Evans@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (15,'James','Thompson','555-243-8344','James.Thompson@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (16,'Jason','Tully','555-172-8639','Jason.Tully@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (17,'John','Watson','555-931-6794','John.Watson@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (18,'Kelly','Oxford','555-208-5106','Kelly.Oxford@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (19,'Linda','Becky','555-761-8562','Linda.Becky@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (20,'Lisa','Brisbon','555-115-5534','Lisa.Brisbon@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (21,'Liz','Frier','555-141-4440','Liz.Frier@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (22,'Marsha','Downs','555-248-7905','Marsha.Downs@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (23,'Marsha','Downs','555-346-5630','Marsha.Downs@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (24,'Mary','Harvard','555-558-2864','Mary.Harvard@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (25,'Matthew','Andrews','555-957-378','Matthew.Andrews@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (26,'Matthew','Andrews','555-957-3780','Matthew.Andrews@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (27,'Merle','Atkins','555-666-1794','Merle.Atkins@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (28,'Mike','Sailors','555-434-2463','Mike.Sailors@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (29,'Miles','Trent','555-971-2236','Miles.Trent@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (30,'Pamela','Birch','555-139-1378','Pamela.Birch@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (31,'Richard','James','555-455-5163','Richard.James@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (32,'Richard','James','555-815-4831','Richard.James@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (33,'Richard','James','555-906-6780','Richard.James@somewhere.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (34,'Richardo','Romez','555-875-7101','Richardo.Romez@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (35,'Sally','Smith','555-329-4189','Sally.Smith@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (36,'Samantha','Jackson','555-632-7417','Samantha.Jackson@mymail.com'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (37,'Terry','Hwang','555-119-9217','Terry.Hwang@ourcampus.edu'); Insert into DDI.PATRONS (PATRONID,FIRSTNAME,LASTNAME,PHONENUM,EMAIL) values (38,'Tzu','Lai','555-986-2353','Tzu.Lai@ourcampus.edu'); CREATE TABLE DDI.REGISTRATIONS ( RegID NUMBER NOT NULL, RegDate DATE NOT NULL, PatronID NUMBER NOT NULL, AdultCnt NUMBER Null, ChildCnt NUMBER Null, RoomNum NUMBER NOT NULL, RegNote VARCHAR2(100) Null, PRIMARY KEY (RegID), FOREIGN KEY (PatronID) REFERENCES DDI.PATRONS, FOREIGN KEY (RoomNum) REFERENCES DDI.ROOMS) TABLESPACE USERS; REM INSERTING into DDI.REGISTRATIONS 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'); 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'); 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); 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); 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'); 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); 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); 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); 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'); 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'); 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); 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); 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); 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); 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'); 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'); 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'); 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); 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); 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'); 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); 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); 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); 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); 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'); 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); 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); 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); 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); 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); 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'); 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); 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); 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); 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'); 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'); 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); 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); 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); 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); 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); 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); 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'); 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); 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); CREATE VIEW DDI.LEDGER_VIEW AS SELECT REG.RegID, REG.RegDate, P.LastName, P.FirstName, REG.AdultCnt, REG.ChildCnt, REG.RoomNum, RM.RoomSize, RM.RoomBedCnt, RM.RoomRate FROM DDI.ROOMS RM, DDI.PATRONS P, DDI.REGISTRATIONS REG WHERE REG.PatronID = P.PatronID AND REG.RoomNum = RM.RoomNum ORDER BY REG.RegDate, REG.RoomNum;