Chapter 8 How to work with data types and functions Exercises 1. Write a SELECT

Question

Chapter 8

How to work with data types and functions

Exercises

1.       Write a SELECT statement that returns these columns from the Invoices table:

The invoice_total column

Use the TO_CHAR function to return the invoice_total column with 2 digits to the right of the decimal point.

Use the TO_CHAR function to return the invoice_total column with no digits to the right of the decimal point and no decimal point

Use the CAST function to return the invoice_total column as an integer with seven digits

2.       Write a SELECT statement that returns these columns from the Invoices table:

The invoice_date column

Use the TO_CHAR function to return the invoice_date column with its full date and time including a four-digit year on a 24-hour clock

Use the TO_CHAR function to return the invoice_date column with its full date and time including a four-digit year on a 12-hour clock with an am/pm indicator

Use the CAST function to return the invoice_date column as VARCHAR2(10)

3.       Write a SELECT statement that returns these columns from the Vendors table:

The vendor_name column

The vendor_name column in all capital letters

The vendor_phone column

The last four digits of each phone number

When you get that working right, add the columns that follow to the result set. This is more difficult because these columns require the use of functions within functions.

The second word in each vendor name if there is one; otherwise, blanks

The vendor_phone column with the parts of the number separated by dots as in 555.555.5555

4.       Write a SELECT statement that returns these columns from the Invoices table:

The invoice_number column

The invoice_date column

The invoice_date column plus 30 days

The payment_date column

A column named days_to_pay that shows the number of days between the invoice date and the payment date

The number of the invoice_date’s month

The four-digit year of the invoice_date

The last day of the invoice date’s month

When you’ve got this working, add a WHERE clause that retrieves just the invoices for the month of May based on the invoice date, not the number of the invoice month.

5.       Write a SELECT statement that returns these columns from the Invoices table:

The invoice_number column

The balance due (invoice total minus payment total minus credit total) with commas, a decimal point, and two decimal positions

A column named “Balance Rank” that uses the RANK function to return a column that ranks the balance due in descending order.

Explanation / Answer

1.Write a SELECT statement that returns these columns from the Invoices table:
The invoice_total column
Use the TO_CHAR function to return the invoice_total column with 2 digits to the right of the decimal point.
Ans>>>> SELECT ROUND(TO_CHAR(invoice_total,2)) from Invoices;
Alternative:
COLUMN invoice_total FORMAT 99.99
SELECT invoice_total from Invoices;

Use the TO_CHAR function to return the invoice_total column with no digits to the right of the decimal point and no decimal point
Ans>>> SELECT ROUND(TO_CHAR(invoice_total,0)) from Invoices;

Use the CAST function to return the invoice_total column as an integer with seven digits
Ans>>>> SELECT SUBSTR('0000000' || CAST(invoice_total as varchar(7)), -7) from Invoices;

2. Write a SELECT statement that returns these columns from the Invoices table:
The invoice_date column
Use the TO_CHAR function to return the invoice_date column with its full date and time including a four-digit year on a 24-hour clock
Ans>>>> SELECT TO_CHAR(invoice_date, 'DD-MM-YYYY HH24:MI:SS') "Date 24Hr" from Invoices;

Use the TO_CHAR function to return the invoice_date column with its full date and time including a four-digit year on a 12-hour clock with an am/pm indicator
Ans>>>> SELECT TO_CHAR(invoice_date, 'DD-MM-YYYY FMHH:MI:SS AM') "Date 12Hr" from Invoices;

Use the CAST function to return the invoice_date column as VARCHAR2(10)
Ans>>>> SELECT CAST(invoice_date as VARCHAR2(10)) from Invoices;

3. Write a SELECT statement that returns these columns from the Vendors table:
The vendor_name column
The vendor_name column in all capital letters
The vendor_phone column
The last four digits of each phone number
Ans>>>>>> SELECT vendor_name, UPPER(vendor_name) AS upper_vendor_name, vendor_phone, SUBSTR(vendor_phone,(length(vendor_phone)-4),4) from Vendors;

When you get that working right, add the columns that follow to the result set. This is more difficult because these columns require the use of functions within functions.
The second word in each vendor name if there is one; otherwise, blanks
Ans>>>>> SELECT SUBSTR(vendor_name, (INSTR(vendor_name, ' ') + 1)) AS second_word_vendor_name from Vendors;

The vendor_phone column with the parts of the number separated by dots as in 555.555.5555
Ans>>>> SELECT replace(replace(replace(vendor_phone, ') ', '.'), '(', ''), '-','.') AS vendor_phone_dot from Vendors;

4. Write a SELECT statement that returns these columns from the Invoices table:
The invoice_number column
The invoice_date column
The invoice_date column plus 30 days
The payment_date column

Ans>>>>> SELECT invoice_number, invoice_date, dateadd(dd,30,invoice_date) as invoice_date_30, payment_date from Invoices;

A column named days_to_pay that shows the number of days between the invoice date and the payment date
Ans>>>>> SELECT DATEDIFF(dd, DATEADD(mm, DATEDIFF(mm, 0, invoice_date)+1, -1), payment_date) as days_to_pay from Invoices;

The number of the invoice_date’s month
Ans>>>>> SELECT MONTH(invoice_date) from Invoices;

The four-digit year of the invoice_date
Ans>>>>> SELECT YEAR(DATE(invoice_date,YYYYMMDD)) as invoice_date_by_year from Invoices;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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