Write a SELECT statement that retyurns these columns from the vendors table: the

ID: 3824408 • Letter: W

Question

Write a SELECT statement that retyurns these columns from the vendors table:

the vendor_name column

the vendor_name column in all capital letters

the vendor_phone column

A column that displays the last four digits of each phone number

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

the vendor_phone column with the parts of the number seperated by dots as ub 555.555.5555

A column that displays the second word in each vendor name if there is one and blanks if there isn't.

Explanation / Answer

Query

SELECT vendor_name,
UPPER(vendor_name),
vendor_phone,
SUBSTR(vendor_phone, 11) AS last_digits,
SUBSTR(vendor_name, (INSTR(vendor_name, ' ') + 1),
(INSTR(vendor_name, ' ', (INSTR(vendor_name, ' ') + 1)) - (INSTR(vendor_name, ' '))))
AS second_word,
REPLACE((REPLACE((REPLACE(vendor_phone, '(', '')), ') ', '-')), '-', '.') AS phone_with_dots
FROM vendors;

Explanation

* UPPER --> Function used to convert the string to upper case (Capital letters)
* SUBSTR --> Function that returns a portion of string and calculates length using characters based on input character string ( Positions are 0,+ve,-ve for 1,begining,ending respectively)

* INSTR --> Function that searches a string for a substring and returns the position in the string (first character of substring)

* Here,SUBSTR is used for displaying last four digits of each phone number as " SUBSTR(vendor_phone, 11) AS last_digits ".

* For a column that displays the second word in each vendor name if there is one and blanks if there isn't, I used the following SUBSTR function with INSTR function in it as
" SUBSTR(vendor_name, (INSTR(vendor_name, ' ') + 1),
(INSTR(vendor_name, ' ', (INSTR(vendor_name, ' ') + 1)) - (INSTR(vendor_name, ' ')))) AS second_word "

* To display a Vendor_phone column with the parts of the number seperated by dots, I used the following part

" REPLACE((REPLACE((REPLACE(vendor_phone, '(', '')), ') ', '-')), '-', '.') AS phone_with_dots ".

Navigate

Write a SELECT statement that returns these columns from the Products table: The

Write a SELECT statement that uses the SYSDATE function to create a row with the

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

Write a SELECT statement that retyurns these columns from the vendors table: the

Question

Explanation / Answer

Related Questions

Navigate