This is in SQL 1. Write a SELECT statement that returns four columns based on th

Question

This is in SQL

1. Write a SELECT statement that returns four columns based on the InvoiceTotal column of the Invoices table:

Use the CAST function to return the first column as data type decimal with 2 digits to the right of the decimal point.

Use the CONVERT function to return the second column as the same data type as the first column.

Use CAST to return the third column as a varchar.

Use CONVERT to return the fourth column as a varchar, using style 1.

2. Write a SELECT statement that returns three columns based on the InvoiceDate column of the Invoices table:

Use the CAST function to return the first column as data type varchar.

Use the CONVERT function to return the second and third columns as a varchar, using style 1 and style 10, respectively.

3. Write a SELECT statement that returns two columns based on the Vendors table. The first column, Contact, is the vendor contact name in this format: first name followed by last initial (for example, “John S.”) The second column, Phone, is the VendorPhone column without the area code. Only return rows for those vendors with an 800 area code. Sort the result set by first name, then last name.

4. Write a SELECT statement that returns the InvoiceNumber and balance due for every invoice with a non-zero balance and an InvoiceDueDate that’s less than one month from today.

5. Add a column to the query in question 4 that uses the RANK() function to return a column name BalanceRank that ranks the balance due in descending order.

Bonus: Write a SELECT statement that returns two columns from the GLAccounts table: AccountNo and AccountDescription. The result set should have one row for each account number that has not been used in the InvoiceLineItems table. Order the results by AccountNo. We want to find general ledger account numbers that have not been used, yet.
Hint: Start by writing a query that lists all of the distinct AccountNo values in the InvoiceLineItems table and use that as a subquery in the operand of the NOT IN operator.

Explanation / Answer

Solution:

Note: The first four subparts have been answered as per Chegg guidelines, please repost others.

1)

SELECT * FROM Invoice GROUP BY InvoiceTotal

Explanation:

In the above query, we have extracted all the columns from the invoice table based on InvoiceTotal

SELECT CAST(first_column AS decimal(2));

SELECT CONVERT(first_column, second_column);

SELECT CAST(third_column AS varchar);

SELECT CONVERT(varchar, second_column, third_column);

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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