(First 4 queries needs to be run only and donot require any script)
5- Write a SELECT statement that returns three columns from the Vendors table:
vendor_name, vendor_contact_last_name, vendor_contact_first_name. Then, run this statement.
Next, add code to this statement so it sorts the result set by last name and then first name. Then, run this statement again. This is a good way to build and test a statement, one clause at a time.
6- Write a SELECT statement that returns one column from the Vendors table named
full_name. Create this column from the vendor_contact_first_name and
vendor_contact_last_name columns, and format it like this: last name, comma, space, first name
(for example, “Doe, John”). Next, sort the result set by last name and then first name. Then, filter the result set for contacts whose last name begins with the letter A,B,C, or E.
7- Write a SELECT statement that returns four columns from the Invoices table named Due Date, Invoice Total, 10%, and Plus 10%. These columns should contain this data:
Due Date The invoice_due_date column
Invoice Total The invoice_total column
10% 10% of the value of invoice_total
Plus 10% The value of invoice_total plus 10%
(For example, if invoice_total is 100, 10% is 10, and Plus 10% is 110.) Next, filter the result set so it returns only those rows with an invoice total that’s greater than or equal to 500 and less than or equal to 1000. Then, sort the result set in descending sequence by invoice_due_date.
8- Write and run a SELECT statement that returns four columns from the Invoices table named
Number, Total, Credits, and Balance Due. These columns should include this data:
Number The invoice_number column
Total The invoice_total column
Credits Sum of the Paympayment_total and credit_total columns
Balance Due Invoice_total minus the sum of payment_total and creadit_total
Next, filter for invoices with a balance due that’s greater than or equal to $500. Then, sort the result set by balance due in descending sequence. Last, use the ROWNUM pseudo column so the result set contains only the rows with the 10 largest balance dues.
9- Write a SELECT statement that returns the balance due and payment date from the Invoices table, but only when the payment_date column contains a null value. Then, modify the WHERE clause so it returns any invalid rows (rows in which the balance due is zero and the payment date is null).
10 – Use the Dual table to create a row with these columns:
Starting Principal Starting principle which should be equal to $51,000
New Principal Starting principal plus a 10% increase
Interest 6.5% of the new principal
Principal + Interest 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:
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. (You should be able to figure out how to use the TO_CHAR and SYSDATE functions by studying figure 3-7.)
* If you have any issues with the payment method, please let us know at email@example.com and that can be discussed and considered.
* Your paypal has to be pre-loaded in order to complete the purchase or otherwise please discuss it with us at firstname.lastname@example.org.
* As soon as the payment is received through PayPal, download link of the solution will automatically be sent to the address used in Paypal.
* Please check your junk mails as the download link email might go there.
* All the contents are compressed in one zip folder.
* In case if you get stuck at any point during the payment process, please immediately contact us at email@example.com and we will fix it with you.
* We try our best to reach back to you on immediate basis. However, please expect to get a response within 8 hours from our side.
* Comments/Feedbacks are truely welcomed and there might be some incentives for you for the next lab/quiz/assignment.
* In case of any query, please donot hesitate to contact us at firstname.lastname@example.org.
******************************************** Good Luck ***************************************************