1 | P a g e
Assignment 1 (Descriptive Report + SQL)
The SQL file "BUSI 650-Assignment 1" is an 8-table database for a retailer representing the operations
of a business selling classic models of cars, trains, etc. This includes specific business data such as
customers, products, sales orders, line items, etc. Use the database to answer the following questions.
Questions:
1. By using SQL, select and export the "customerNumber" and "amounts" from the payments table of
the given database and write a descriptive analytics report. (The report must contain a summary of
statistic calculations (MUST be done with Excel as described in class) and discuss the meaning of
each value in a business context. The report must have at least two charts that should be used for a
etter data presentation. You may use any information from the database to plot the charts. (11
points)
2. By using SQL, count how many employees work in the company. (4 points)
3. By using SQL, select the "customerNumber" associated with the amounts lower than the average.
(4 points)
4. By using SQL, select the "customerNumber" associated with the amounts between 5000 and 10000.
(4 points)
5. By using SQL, find the average percentage markup of the MSRP on buyPrice. (The markup of the
MSRP (Manufacturer's Suggested Retail Price) on the buy price refers to the difference between the
price at which a manufacturer suggests a product should be sold and the price at which a retailer
actually purchases the product from the manufacturer). (4 points)
6. By using SQL, report the name and city of customers who do not have sales representatives. (4
points)
7. By using SQL, report how many orders were cancelled and what the cancelled "orderNumber" was.
(4 points)
8. By using SQL, find the name of 1625's supervisor. (4 points)
9. By using SQL, find products containing the name 'Ford'. (4 points)
10. By using SQL identify the "customerNumber" with the median payment amount. Provide his/her
"customerName", "contactLastName" and "phone". (4 points)
11. By using SQL, report the "customerName" of the orders "On Hold". (4 points)
2 | P a g e
12. By using SQL, report the number of customers in Denmark, Norway, and Sweden. (4 points)
Grading ru
ic:
Key points Grade allocation (%)
Format (font type, size, tables, and charts) 25
Analysis (grade
eakdowns for each question are provided above) 55
Creativity in presenting the solutions 10
N.B. Failure to comply with the above would result in low grades.
Word limit:
Maximum 2000 words (excluding tables and graphs).
Deliverables:
A PDF file that is written professionally and follows the specific rules below:
• For each question, please write down the SQL statements you used and the results you obtained.
• In the appendix, please include a screenshot of your entire screen showing the SQL statements
and the results you obtained. Make sure the screenshots are readable.
Naming convention:
Use the following format to name your files:
Your name_Student ID_Individual assignment1 (e.g., Alireza Samea_112233_Individual assignment1)
Deadline:
Deadlines are specified in your course shell.
3 | P a g e
How to write a professional business report:
A professional business report should be well-structured and easy to read. Here are some general guidelines
for formatting a business report:
1. Title page: The title page should include the report title, the name of the author or authors, the name
of the organization or company the report is for, and the date the report was completed.
2. Table of contents: Include a table of contents that lists the sections of the report and their page
numbers.
3. Executive summary: This section
iefly overviews the report's main findings and recommendations.
4. Introduction: In this section, provide some background information about the topic and explain why
the report was written.
5. Body: The body of the report should be divided into sections that cover the main points of the report
(In this assignment, the questions). Use subheadings and bullet points to make the information easy
to read and follow.
6. Conclusion: Summarize the main findings and conclusions of the report.
7. Recommendations: Provide specific recommendations based on the findings of the report.
8. Appendices: Include any additional information (In this assignment, a screenshot of your entire
screen).
• When formatting the report, use a professional font such as Times New Roman or Arial, and keep
the font size between 10 and 12 points. Use 1.5 or double spacing. Number the pages.
• When presenting charts in a report, it is important to choose the right type of chart and label it
clearly. The chart should be placed in a logical location in the report, and the design should be
simple and easy to read. When selecting colours and fonts, it is important to consider the overall
style of the report and the audience for whom it is intended.
Finally, proofread the report carefully to ensure it is free of e
ors and typos.