Description

SQL Data Types & Functions Assignment

 Drop all tables in your schema and then run the create_mgs_table script for your SQL problems.

Data Types & Functions (MIS 325)                                                 Oct 2019.v1

Instructions:

·         Do your own work: This is an individual assignment and you must do your own work and create your own SQL statements.  If you are caught cheating on this, you will receive a zero on this assignment and be reported to the Dean of Students.  Also this homework prepares you for the exam coming up so doing the work now will help you learn and do well on when it counts more.

·         What to turn in

o   Clearly separate your code for each question. Save your code into one SQL file with the naming format: LastName_FirstName_UTEid.  Please make sure the lastname and firstname you use matches what is in Canvas.

o   Submit your .sql file on Canvas before the deadline.  Late submissions receive 50% off.  No submissions will be accepted 24 hours after the deadline.

·         The SQL problems below will be based on the DDL script that is posted on the Canvas instructions.  Download that script and run it before you start.

Problems:

1.      Pull six columns from DUAL table.  The first column will just be the system date by itself without formatting and no alias.  The remaining five columns will be variations of the SYSDATE that use different formatting combinations for month, day, year, and time.  Include both abbreviated and full names in examples.  We’re not judging on quality but ability to use different elements.  Be sure to include alias for the five columns that format SYSDATE.  Also use a trim function to reduce unwanted white space.   

2.      Write a SELECT statement that a row for each order and contains the following columns in the format shown below:

The order_id column

The order_date but with a literal string of ‘Ordered on: ’ as a prefix.  Format date like Mar-28-2012

The day of the week the item shipped with a literal string like so: e.g. ‘Shipped on a Tuesday’. 

The tax_amount column formatted like so without decimals: e.g. $999

      Note: If the ship_date is null, we want it to return ‘Not shipped yet’

       Sort results by the order_id ascending

       Result should look something like this partial result:

3.  Pull a list of customer names formatted with the first initial and the last name in all caps like so:  A. SHERWOOD

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

The product_name column

The list_price column.  Format the price like the following example: e.g. $489.99

The discount_percent column.  Format the percent as a whole number with a % symbol like so: e.g. 30%

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

Results should look like this partial result:

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

The product_id column

The product_name column

A column named description_length that returns the length of the product description

A column named Days_on_shelf that shows the number of days between the date_added column and Jan 1 2012.  Make sure this is rounded to nearest whole day.

When you have this working, add a WHERE clause that retrieves just the orders after Jan 1, 2012. 

6.      Write a SELECT statement that returns these columns for each customer address:

The address_id column

A column named Num that returns just the street number from the line1 field

A column named Street_Name that returns just the text after the first space occurrence in the line1 field

The line2 column but make sure that it does not show null but instead a blank.

The last three columns are just city, state, and zip_code with not formatting.

Results should look like this partial result:

7.       Write a SELECT statement that returns these columns from the Customers and Addresses tables:

The first_name column

The last_name colum

A column called local_phone, which is the phone column with the area code removed.  e.g. 654-1291

A column called last_four, which is the phone column but masks all the numbers except the last 4 with a #.  e.g. ###-###-1291.  NOTE: We want you to utilize both the SUBSTR and LENGTH functions to dynamically pull the last four digits regardless of the phone’s length.

8.      Write a SELECT statement joins the necessary tables to return the following columns:

A column called the “Brand” column, which is first word of the product_name field

A column called the “Average_Price” column, which is the average item price of the brand.  

A column called the “Product_tier” column which is calculated based on the following rules.

Average_Price greater than or equal to 1000 is considered “High-End”

Average_Price greater than or equal to 500 but less than 1000 is considered “Mid-Range”

Everything else is considered “Low-End”

            Lastly, be sure to sort by average_price descending

9.      Write a SELECT statement that pulls the following columns

The product_name for each product

The list_price for each product

The “Price_Rank” column which assigns the ranking of that list price.  Output should look something like this:

            NOTE: Please format the list_price column to show in a currency format with decimals (e.g. $2,301.00)

10.  Update the previous query to return a row_number, product_name, and list_price.  Sort by list_price descending.  NOTE: Do not use the ROWNUM Pseudo column.  Instead use row_number() function.  After you complete this query, make it an in-line subquery and select * from it like a table but only return row_number 7