Case 2 - Extreme Mountain Bikes Data Retrieval using SQL

Introduction

Please read the "Introduction" in Case 1 - Extreme Mountain Bikes Cost Benefit Analysis.

Case 1 - Extreme Mountain Bikes Cost Benefit Analysis

Introduction

During his junior year in college, Bob Cooper was invited by his friend Tom Newton to go on a mountain biking trip. Bob owned a bike but primarily used it for short trips around campus. He'd never been mountain biking before and did not know what to expect. Tom told him it was a lot of fun and since Tom had been doing it for a couple of years, he could help Bob out. Bob agreed to give it a try and one cool Friday afternoon in the fall, Bob and Tom drove a short distance to a nearby off-road mountain bike trail. Bob was immediately surprised by the extreme ruggedness of the trail. The narrow trail twisted and turned through trees, travelled up and down steep ravines, and contained numerous "rock gardens" through which he was supposed to ride. The challenging terrain made it difficult for Bob to keep his balance and required tremendous focus and concentration. As they continued to ride that afternoon, Bob's comfort level on the bike and trail improved and he began to enjoy the challenges of maneuvering a bike skillfully over rough terrain while being outdoors with friends. It was a fantastic experience and the start of a lifelong passion for mountain biking.

Bob graduated from college a short time later and found a job selling life insurance. The pay was good, and the job was stable and secure. His early interest in mountain biking had grown and he rode his bike every morning before going to work. When he wasn't riding, he was spending his spare time maintaining and repairing his collection of three mountain bikes. Bob had purchased three different bikes because he was unable to find a bike to perfectly meet his needs. Each bike he owned had its own strengths and weaknesses. One bike was highly maneuverable, but had problems with overcoming large obstacles. Another bike was good with large obstacles, but was heavy and difficult to maneuver. His third bike excelled at going down large, extremely steep hills but lacked the maneuverability and clearance of his first two bikes. One day Bob was talking to a friend who he had recently sold a life policy to about mountain biking and the friend realized how passionate and knowledgeable Bob was about the activity and asked Bob if had every thought of starting his own business. The client knew an investor who might be willing to back a new company focused on mountain bikes. Bob met with the investor and decided to open a business to design, manufacture, and sell mountain bikes. They decided to name the company Extreme Mountain Bikes (EMB) and the company would focus on selling high performing all around mountain bikes at a reasonable cost.

EMB hired employees to help with product design, engineering, accounting, fulfillment, and sales. They quickly developed a prototype bike and approached several local stores about selling the bike. The stores were willing to give it a try and the bikes started to sell. In the first year of business, EMB was able to sell nearly a hundred bikes. Bob realized that his first year success meant there was a market for his product, so he decided to expand aggressively. He had a superior product at a reasonable price.

Based on your thorough analysis of the cost and benefits of implementing an ERP system, Bob and his team decided to move forward with a full-scale implementation of SAP. Implementation lasted approximately 12 months and went smoothly for a project of such size, complexity, and scope.

The Problem

EMB has been using the new ERP system for approximately 12 months and they are thrilled with the initial gains in productivity. Business processes have been formally defined and are routinely being followed, communication between departments has improved, delivery times have decreased, sales have increased, and employee morale has improved.

Now that the system configuration has stabilized, Bob would like to evaluate the performance of his organization using the transaction data being collected by SAP.

Your Task

EMB would like for you to perform an analysis of data contained in SAP using Microsoft Access. The SAP database administrator (DBA) has created a Microsoft Access data mart for you to use. The data mart is a small subset of the overall data contained in SAP and is available as a separate download file. The data mart contains sales orders for an entire year along with employee, customer and product information. To analyze the data, you will need to first understand relational databases and create queries to find and extract the data of interest.

Creating Queries using SQL

EMB would like for you to generate several queries using the SQL "Query Design" feature in MS Access. The queries are designed to provide an overview of the data stored in the data mart and techniques for answering specific questions about customers, products, and employees.

Query 1 Employee List

Design a query to list all employees in the organization. The query should display the following information: employee ID, employee first name, employee last name, employee address, employee salary, department name, and region name. Execute the query and verify the output is correct. Once you are satisfied with the query, save the query as "Query1".

Query 2 Sales Representative List

Design a query to list all sales people in the organization. The query should display the following information for the sales people in the organization: employee ID, sales rep first name, sales rep last name, sales rep address, sales rep salary, sales rep department name, sales rep region name. Execute the query and verify the output is correct. Once you are satisfied with the query, save the query as "Query2".

Query 3 Customer List

Design query to list all customers in alphabetical order by customer last name. The query should display the following information: customer ID, customer first name, customer last name, and customer birth date. Execute the query and verify the output is correct. Once you are satisfied with the query, save the query as "Query3".

Query 4 - Sales Orders by Quarter

Design a query to list all sales orders or the quarter you have been assigned to analyze sorted in ascending order by purchase order date. The query should display the following information: PO date, order ID, product ID, product description, quantity sold, and unit price. Execute the query and verify the output is correct. Once you are satisfied with the query, save the query as "Ouery4".

Query 5 - Finding a Sales Orders

Construct a query to find the sales order with the ID 40000136856. The query should display the following information: PO Date, order ID, customer ID, customer first name, customer last name, product ID, product description, quantity sold, and unit price. Execute the query and verify the output is correct. Once you are satisfied with the query, save the query as "Query5".

Query 6 - Sales Orders for Export to Excel

Create a query to list all sales orders in ascending order by PO Date. The query should contain the following information: PO date, order ID, customer first name, customer last name, product ID, product description, quantity sold, unit price, sales rep first name, sales rep last name, and sales rep region name. Execute the query and verify the output is correct. Once you are satisfied with the query, save the query as "Query6".

I NEED HELP SOLVING AND HOW TO DO IT STEP BY STEP