SET ECHO OFF
SET PAGESIZE 30
SET LINESIZE 120
SET FEEDBACK OFF
SET VERIFY OFF
SET UNDERLINE *

/*Setting up Formatting for report*/

TTITLE CENTER 'Movie Rental Details for Client' -
    RIGHT 'Page: ' FORMAT 9 SQL.PNO SKIP 2
COLUMN FName FORMAT A15 HEADING 'First Name'
COLUMN LName FORMAT A15 HEADING 'Last Name'
COLUMN Name  FORMAT A55 HEADING 'Movie Name'
COLUMN AgreementID HEADING 'Agreement'
COLUMN AgreementDate FORMAT A12 HEADING 'Date'
COLUMN RentalAmount FORMAT $90.00 HEADING 'Paid'
BTITLE LEFT 'Run by: ' SQL.USER SKIP
BREAK ON REPORT ON AgreementID ON FName ON LName
COMPUTE SUM LABEL 'Total:' OF RentalAmount ON REPORT

/*Setting up prompt for user*/

ACCEPT CustID NUMBER FORMAT '999' PROMPT 'Customer ID: '

/*Generating the report*/

SPOOL C:\DBMSDBII\A1\Reports\Specht_K.txt
SELECT R.AgreementID, SUBSTR(C.FName,1,14) AS "FName", C.LName, R.AgreementDate, M.Name, MR.RentalAmount
FROM Customer C, RentalAgreement R, MovieRented MR, Movie M
WHERE C.CustID = R.CustID
AND R.AgreementID = MR.AgreementID
AND M.MovieID = MR.MovieID
AND C.CustID = &CustID
ORDER BY R.AgreementID;
SPOOL OFF