SQL homework. 1. Write a SELECT statement that returns these columns from the Pr

Question

SQL homework.

1.

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

The list_price column

A column that uses the TO_CHAR function to return the list_price column with currency formatting.

2.

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

The card_number column

The length of the card_number column

The last four digits of the card_number column

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.

A column that displays the last four digits of the card_number 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.

3.

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

The order_id column

The order_date column

A column named approx_ship_date that’s calculated by adding 2 days to the order_date column

The ship_date column

A column named days_to_ship 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

Answer 1:

SELECT product_Name, to_char(list_price, ’$345.67’) "Price" FROM Products;

Table structure would be like this:

CREATE TABLE Products(product_id varchar(10),category_id varchar(10),product_code varchar(10),product_Name varchar(30),description varchar(30),list_price decimal(7,2),date_added date);

Answer 2:

SELECT card_number, length(card_number) as card_length,substring(card_number,16,4) as four_digits FROM Orders

here, length(str)-> is used to return the length

substring(str,start,len)--> used to return the last four digits

Ansewer 3:

SELECT order_id, order_date, DATEADD(DAY,2, order_date) as approx_ship_Date, DATEDIFF(DAY, order_date, DATEADD(DAY, 2, order_date)) as days_To_Ship FROM Orders WHERE YEAR(order_date) = 2012 AND MONTH(order_date) = 3;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

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