How to use functions Exercises 1. Write a SELECT statement that returns these co

Question

How to use functions

Exercises

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

The ListPrice column

The DiscountPercent column

A column named DiscountAmount that uses the previous two columns to calculate the discount amount and uses the ROUND function to round the result to 2 decimal places

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

The OrderDate column

A column that returns the four-digit year that’s stored in the OrderDate column A column that returns only the day of the month that’s stored in the OrderDate column

A column that returns the result from adding thirty days to the OrderDate column.

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

The CardNumber column

The length of the CardNumber column

The last four digits of the CardNumber column

When you get that working right, add the column that follows to the result set. This is more difficult because the column requires the use of functions within functions.

A column that displays the last four digits of the CardNumber column in this format: XXXX-XXXX-XXXX-1234. In other words, use Xs for the first 12 digits of the card number and actual numbers for the last four digits of the number.

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

The OrderID column

The OrderDate column

A column named ApproxShipDate that’s calculated by adding 2 days to the OrderDate column

The ShipDate column

A column named DaysToShip that shows the number of days between the order date and the ship date

When you have this working, add a WHERE clause that retrieves just the orders for March 2012.

Explanation / Answer

1)

SELECT ListPrice,DiscountPecentage//Returns listpice and discount percentage from products table

FROM Products

SELECT Round(DiscountAmount,2)//Returns the Rounded discount amount

FROM Products

WHERE DiscountAmount=((ListPrice*DiscountPercentage)/100)

2)

SELECT OrderDate//Returns the date from Orders table

FROM Orders

SELECT YEAR(OrderDate) //Returns only year from the date retrieved.

FROM Orders

SELECT DATEOFMONTH(OrderDate )//Returns only Date from Date retrieved.

FROM Orders

SELECT DATEOFMONTH(OrderDate )+30 //Adds 30 to days retrieved from Date

FROM Orders

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

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