This is for SQL using Oracle 11g or 12c. I\'ll copy the text from the first proj

Question

This is for SQL using Oracle 11g or 12c. I'll copy the text from the first project that created all the tables that are needed below everything.

Step 1: Start by running the attached script from system/Oracle11. This will create the schema DDI, build several tables, and insert the current rows from the spreadsheets. Once you have the new user installed (DDI), begin to explore the data. (I'll attach this text at the bottom. It just needs to be copied into SQL plus to create the needed tables.)

Step 2: Project 2 is about printing a formatted list of room registrations from DDI.LEDGER_VIEW

SPOOL your output to c:cs422aproject2spool.txt

Format COLUMN Headings and Formats for ROOMNUM, REGDATE, LASTNAME, ADULTCNT, CHILDCNT, and ROOMRATE

BREAK on ROOMNUM and COMPUTE a total of ROOMRATE for each ROOMNUM

SELECT the fields indicated above from DDI.LEDGER_VIEW for registrations between '01-JUN-15' and '07-JUN-15'
(don't forget to order by ROOMNUM and REGDATE)

The coded needed to create the tables for the project:

Explanation / Answer

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;

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.