Assignment: DB Fundamentals SQL Using Oracle Application Express, complete the questions.

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

Using the attached data file and Word document, submit your assignment here. Your submission should be a single Word document or PDF containing a screen shot that shows: your email address, the SQL query and at least the start of the table that was created; for each question. 

 

Assignment:  DB Fundamentals SQL (100 points)

 

Read the brief case below.  Using Oracle Application Express, complete the questions.  

 

Downtown Office Supply 

 

Downtown Office supply is a small family owned store in a small suburb.  In order to maintain and grow the business the owners want to do some analysis to see how their sales are doing.  Management wants to see how the company did last year and make some decisions about what products to keep along with staffing for busy times of the year.  

 

Instructions:  

1. Visit Oracle Live SQL - https://livesql.oracle.com

2. Create an account and login.  The account should be created using your UTD email address.  

3. Click on “My Scripts” on the left menu.  

4. Upload the “Orders_FA19.sql” script by clicking the upload Script button  in the upper right corner.  

 

5. Fill in the required fields and upload the script.  

6. Once the script is uploaded click Run Script in the upper righ corner.  

 

7. Click on the “Run” on the top right corner

8. To verify if your script ran fine – Click on “Schema” from the menu on the left. You ll find the tables listed as described below.  Take a Screen shot and paste it into the document. 

 

 

 

15 points for setup.  

 

Use the ER Diagram below to understand the Data Model

 

Respond to the questions below. Paste the query and a screenshot of the result for each of the questions. Take the Screenshot in a way that the Username is captured. For example:

 

 

 

 

17 points each  - You must include a screen shot AND the sql query in your answer.  The screen shot must clearly display your user name.  5 pts for the screen shot, 5 pts for the query.  

 

1. List all the different product categories and sub categories in alphabetical order, list only the product category and product subcategory.  Sort by the product subcategory.

 

2. How many ORDERS had a CRITICAL order_priority?  [Hint:  you will use the function COUNT(*)  ]

 

3. Display the order date and the ship date for all orders that were made April 1 through April 15.  List the order date, ship date, order priority, and ship mode.  Order the results by order_date. Do you notice anything unusual about the data? [Hint: You will need to join the orders and shipping together and use a join statement.  You will need to limit the result set by the date field order_date <= to_date('04/15/2018', 'mm/dd/yyyy'). ]  

 

4. How much sales profit was made from the sales of COMPUTER PERIPHERALS (Prod_8)?  [Hint:  you will use the SUM function you will need a WHERE clause to filter the rows].

 

5. List the sales, order_quantity, and profit for all products in the TECHNOLOGY category.  Display the product sub category name, the sales, order quantity, and profit.  Order by the product sub category name.  [Hint:  you will need to join the market and product tables together and filter the rows by product category.  You will also need an ORDER BY clause].

 

 

 

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of