IT2351-Assignment 4 Solved

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

a)          The list_price column  

b)          A column that uses the FORMAT function to return the list_price column with 1 digit to the right of the decimal point  

c)           A column that uses the CONVERT function to return the list_price column as a signed integer  

d)          A column that uses the CAST function to return the list_price column as a signed integer  

  

2.      Write a SELECT statement that returns the following information from the email_address column in the customers table.  

a)          Email address  

b)          The length of the email address with the alias “Length”  

c)           The location of the “@” sign with the alias “@”  

d)          The email address username (before the “@”) with the alias “Username”  

e)          The email address domain (after the “@”) with the alias “Domain”  

 

3.      Write a SELECT statement that returns these columns from the Products table: a)          list_price

b)          discount_percent

c)           A column named discount_amount that uses the previous two columns to calculate the discount amount and uses the ROUND function to round the result, so it has 2 decimal digits

 

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

a)           The order_id column

b)          The order_date column

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

d)          The ship_date column if it doesn’t contain a null value

e)          A column named days_to_ship that shows the number of days between the order date and the ship date

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

 

5.      Write a SELECT statement that uses the analytic functions to get the highest and lowest sales by product within each category. Return these columns:

a)             The category_name column from the Categories table

b)             The product_name column from the Products table

c)              A column named total_sales that shows the sum of the sales for each product with sales in the Order_Items table

d)             A column named highest_sales that uses the FIRST_VALUE function to show the name of the product with the highest sales within each category  

e)             A column named lowest_sales that uses the LAST_VALUE function to show the name of the product with the lowest sales within each category

 

6.      Create a view named order_item_products that returns columns from the Orders, Order_Items, and Products tables. Build your view in stages:

a)             This view should return these columns from the Orders table: order_id, order_date, tax_amount, and ship_date.

b)             Next, add the product_name column from the Products table.

c)              Next, add the following from the Order_Items table: item_price, discount_amount, final_price (the discount amount subtracted from the item price), quantity, and item_total (the calculated total for the item).

d)             Submit the script file of your view and, an image of the view output.