Question

Create a new SQL Developer SQL worksheet and create/run the following TWO (2) queries and save your file as Comp2138LabTest1_JohnSmith100123456.sql (replace JohnSmith 100123456 with your name and student ID). Please place comment that includes your name and your student ID at the top of your script and number your queries using comments sections. Each query carries equal weight. A selection of the expected result set has been shown below for your convenience. Your output should match with this sample output. PAY SPECIAL ATTENTION TO COLUMN NAMES.

1. Use the Dual table to create a row with these columns: Starting Principal New Principal Interest Principal + Interest Starting principle which should be equal to $51,000 Starting principal plus a 10% increase 6.5% of the new principal The new principal plus the interest (add the expression you used for the new principal calculation to the expression you used for the interest calculation) Now, add a column named “System Date” that uses the TO_CHAR function to show the results of the SYSDATE function when it’s displayed with this format: 'dd-mon-yyyy hh24:mi:ss' This format will display the day, month, year, hours, minutes, and seconds of the system date, and this will show that the system date also includes a time. The query would return only one row as shown here

2. Write a SELECT statement that returns one row for each general ledger account number that contains three columns: The account_description column from the General_Ledger_Accounts table The count of the entries in the Invoice_Line_Items table that have the same account_number The sum of the line item amounts in the Invoice_Line_Items table that have the same account-number Filter for invoices dated in the second quarter of 2014 (April 1, 2014 to June 30, 2014). Include only those rows with a count greater than 1; group the result set by account description; and sort the result set in descending sequence by the sum of the line item amounts. [schema: ap] The query would return 13 rows as shown here

Answer 1

1)

Given:

Use the Dual table to create a row with these columns:

Starting Principal New Principal Interest Principal + Interest Starting principle which should be equal to $51,000

New principal àStarting principal + 10% increase

So interest à 6.5% of the new principal

new principal + interest à add the expression you used for the new principal calculation to the expression you used for the interest calculation

Now, add a column named “System Date” that uses the TO_CHAR function to show the results of the SYSDATE function with format: 'dd-mon-yyyy hh24:mi:ss'

SELECT 51000 AS "Starting Principal"

              (51000 + (51000 * 0.10)) AS "New Principal"

               (.065 * (51000 + (51000 * 0.10))) AS "Interest"

                (51000 + (51000 * 0.10)) + (.065 * (51000 + (51000 * 0.10))) AS "Principal + Interest TO_CHAR(SYSDATE'dd-mon-yyyy hh24:mi:ss') AS "System Date"

FROM Dual

2)

SELECT g.account_description, count(*), sum(invoice.line_item_amount)

FROM   General_Ledger_Accounts g

INNER JOIN Invoice_Line_Items invoice ON invoice.account_number = g.account_number

GROUP BY g.account_description

HAVING count(*) > 1

ORDER BY SUM(invoice.line_item_amount) DESC