Question

1. Write a query to:

a. select data from INVOICES table as follows:

• Invoice date in MM/DD/YYYY format
• Invoice Date in DD-Mon-YYYY format
• Invoice Total rounded to the nearest dollar

Note: you can alias columns as you sit fit

b. select data from VENDORS table as follows:

• Vendor Name
• Concatenate Vendor Name with the string ‘s Address
• Concatenate Vendor City, Vendor State and Vendor Zip Code (alias this)

Your output should look like this (this is just an example of it). Note how after each vendor name there is ‘s

vendor_name	Vendor	Address
Register of Copyrights	Register of Copyright's Address:	Washington, DC 20076
Newbrige Book Clubs	Newbrige Book's Address:	Los Angeles, CA 90045

Answer 1

1.a)

Select str_to_date(invoicedate, "%m/%d/%Y") AS "Invoices Date in MM/DD/YYYY format" , str_to_date(invoiceDate, "%d.%m.%Y") AS "Invoices Date in DD-MM-YYYY format" , ROUND( InvoiceTotal, 2) AS "Rounded Invoice Total" from INVOICES;

Here we use date function to convert the format of date from standard format to the given format:

string to date(str_to_date) function is used in order to convert invoice date into month, date, and then year (MM/DD/YYYY) format.

Similarly to convert the date into date month and year(DD-MM-YYYY) format, we use the same string to date (str_to_date) function.

Although there is another function that can be used but (str_to_date) function is quite easy.

If you want any specific date column in any other particular format, then you have to use DATE_FORMAT() function in which you have to mention it's original format in another condition.

For example:

DATE_FORMAT( STR_TO_DATE( nameofdatecolumn , "%d/%m/%Y" ) , "%Y/%m/%d" )

For rounding to the nearest dollar, we use ROUND function which can Round up the invoice total upto 2 decimal positions from INVOICES TABLE and we alias all these fields in their respective names written in the command.

1.b)

Select vendor_name, CONCAT(Vendor_name, " 's Address: ") AS Vendor, CONCAT(vendorCity, vendorState, vendorzipcode) AS Address from VENDORS;

In this query, we use CONCAT (concatenation) function in order to concatenate vendors name with " 's Address " and for concatenating vendor City, vendor state, vendor zip code, we use CONCAT function to concat all the field records and alias them in the respective name written in the expected output the question.