[Solved] IT2351 Assignment4

For best success on the assignment read over the problems listed below, return to the textbook and make sure you have practiced these concepts and then, be sure to check out the Instructor Tutorials where I have posted additional notes, examples, and videos for the chapters.

Complete the following for 10 points each naming your png files Assignment4No1 (submit both an image and script file) etc.:

1. Write a SELECT statement that returns these columns from the Products table:
   1. The list_price column
   2. A column that uses the FORMAT function to return the list_price column with 1 digit to the right of the decimal point
   3. A column that uses the CONVERT function to return the list_price column as a signed integer
   4. 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.
   1. Email address
   2. The length of the email address with the alias Length
   3. The location of the @ sign with the alias @
   4. The email address username (before the @) with the alias Username
   5. 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
   1. discount_percent
   2. 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:
   1. The order_id column
   2. The order_date column
   3. A column named approx_ship_date thats calculated by adding 2 days to the order_date column
   4. The ship_date column if it doesnt contain a null value
   5. A column named days_to_ship that shows the number of days between the order date and the ship date
   6. 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:
   1. The category_name column from the Categories table
   2. The product_name column from the Products table
   3. A column named total_sales that shows the sum of the sales for each product with sales in the Order_Items table
   4. 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
   5. 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:
   1. This view should return these columns from the Orders table: order_id, order_date, tax_amount, and ship_date.
   2. Next, add the product_name column from the Products table.
   3. 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).
   4. Submit the script file of your view and, an image of the view output.