Hello, Need some help with the following Oracle SQL questions: Write a SELECT st

Question

Hello, Need some help with the following Oracle SQL questions:

Write a SELECT statement to return one column from the vendors table named “Full Name”. Create this column from the first name and the last name columns and format it like this: last name, dash, space, first name (for example, “Smith - Joe”). You don’t have to worry about the case of the strings. Next, sort the results by vendor_name. Then, filter result set for contacts whose last name (vendor_contact_last_name) start with the letter A, X, Y or Z.

Write a SELECT statement that returns invoice_id, vendor_id and payment_total and payment_total + 100 from the invoices table. Name these columns Invoice, Vendor, Payment and new payment. New payment is the payment_total + $100. Filter the results set to only the data for payment_toatl between $600 and $900. Sort the results in descending order of payment_total (high to low).  

Write a SELECT statement to return the balance due and the payment date from the invoices table, but only when the payment_date column does not contain a null value.

Write the statement to use the DUAL table (don’t use any of the existing tables that has data in it) to create a row with the following columns: Current date using the format dd-mon-yyyy, hh24:mi:ss and the current user

Write the statement to use the DUAL table to display the result of the expression 2 * 5 + 9 – 16/3.

Explanation / Answer

1. Select firstname ||'- '|| lastName as FullName, from vendors where vendor_contact_last_name like 'A' or vendor_contact_last_name like 'X' or vendor_contact_last_name like 'Y' or vendor_contact_last_name like 'Z' order by vendor_name;

2. Select invoice_id as invoice, vendor_id as vendor, payment_total as Payment, sum(payment_total + 100) as newLayment from invoices where payment_total between 600 and 900 order by payment_total desc;

3. Select payment_due, balance_due from invoices where payment_date is not null;

4. Select USERNAME, to_char(SYSDATE, dd-mon-yyyy, hh24:mi:ss) from dual;

5. Select (2*5+9-16/3) as result from dual;

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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