Assessment Task 1– SQL Report – Individual Assessment

MIS202 –Managing Data and Information – Trimester 1 2025
Assessment Task 1– SQL Report – Individual Assessment
DUE DATE: Friday, 4 April, by 8:00pm (Melbourne time)
PERCENTAGE OF FINAL GRADE: 30%
WORD COUNT: Maximum 2000 words
Description
Purpose
This task provides you with opportunities to learn the knowledge (GLO1 & ULO1) and skills (GLO 3 & ULO2;
GLO4 & ULO3) required in the study and practice of managing data and information in organisations. By
completing this task, you will develop the skills required by data scientists and data analysts in formulating
SQL queries to extract specific information from a relational database.
Context/Scenario
For this task, you need to formulate a set of SQL queries based on specific requirements given to you.
Imagine you are a new graduate with SQL knowledge, and you apply for a data science position that
requires SQL skills. You task would be to showcase these skills by writing SQL statements to extract
information from a large relational database.
Specific Requirements
Your task is to develop and write SQL queries to extract data from the Pine Valey Furniture database.
Ensure that you can access the Deakin Oracle Teaching & Learning database (revisit the content of Week 2)
to access the database. As preparation for this assessment, make sure that you worked through and
completed all the W3Schools SQL commands and the first 5 weeks of MIS202 workshop materials. Use the
SQL Report template to complete this assessment item.
Each response to each SQL query must have the following components:
i. Your SQL query that you entered into the SQL Oracle database interaction interface
e.g. SELECT column FROM table;
ii. A screenshot of each SQL query’s result(s) after execution of the SQL command showing the
extracted information including the resulting table(s).
Make sure the SQL query and the resulting table(s) used are captured in full in the screenshot. All
output (including tables) should be well formatted with no lines wrapping around. Use the SET
LINESIZE SQL command (see Workshop Week 2) to sit the display line length if necessary.
2
iii. An explanation of the SQL queries in your own words (1 or 2 sentences max). This means you need
to clearly explain what the query requires as input, and following tis execution, what result each
query returns.
YOUR SQL TASKS FOR THIS ASSESSMENT
Imagine that you are applying for a new data scientist role in the Pine Valley Furniture Company located in
the USA. You have recently acquired your Bachelor’s degree at Deakin, and the company wants to first
assess your SQL skills. The Data Science manager has compiled the following set of queries that requires
you to write and run SQL commands using the company’s Pine Value Furniture database. You need to
compile your responses in the form of a final SQL Report for the Data Science manager.
Query 1: [3 mark]
1.1 Display the name, state, and city (in that order) for each customer.
1.2 For each customer, list the name, state, city and postal code. Order the results on postal code.
1.3 For each customer, list the name, city and state ordered by the city but in reverse alphabetical
order (from Z to A).
Query 2: [3 marks]
2.1. Which customers are in Florida and have a customer ID less than 20?
2.2 Which product has a product line ID of three or a “Walnut Finish”, but not both?
2.3 List the product description and the price of all products that are made out of “Natural Ash”
and costs less than $500.
Query 3: [3 marks]
3.1 How many products are in product line one?
3.2. What is the most expensive item in either product lines one or three.
3.3. What is the average price of products that have the word “Natural” in the product finish.
Query 4: [3 marks]
4.1. List the product description and quantities of the items for order 1001.
4.2. List all orders for coffee tables.
4.3 List all the product with product line name “Country Look”. You may need to use the
m_product_line table together with other tables.
3
Query 5: [4 marks]
5.1. List the total, average, and standard deviation of the prices of product line two.
5.2. What is the least expensive item in either product lines one, two or three?
5.3. List the order ID, order quantity, product description and the price of all orders
containing desks (the results should be alphabetically ordered by product description).
Query 6: [4 marks]
List the product description, quantity ordered and the price for all items included in order
number 1006. Your manager also wants to have a subtotal column added as the last column

• see below.

  Additional marks:
  i) Well-structured SQL code [5 marks]
  ii) Quality of your documentation [5 marks]

The post Assessment Task 1– SQL Report – Individual Assessment appeared first on Universal Assignment.