Answered step by step
Verified Expert Solution
Question
1 Approved Answer
Complete the SQL queries to find business insights. Prepare a short report containing succinct answers to the questions in Sections #1 and #2. ACME
Complete the SQL queries to find business insights. Prepare a short report containing succinct answers to the questions in Sections #1 and #2. ACME Traders is a Small and Medium-sized Enterprise (SME) that imports and exports specialty foods from around the world. ACME has engaged your Analytics Consultancy to help them discover insights and provide advice crucial to their plans for supply chain optimisation and business expansion. As a business analyst, you are to analyse data stored in ACME's database. The dataset consists of the following: Customers: sole traders or businesses that purchase products from ACME. Products: Product information. Orders and Order Details: records of Sales Orders between ACME and their Customers. Shippers: details of shippers who ship products from ACME to the Customers. Employees: ACME Traders employee information. Section #1: Database Connection & Data Exploration Part 1: Distributed SQL Database Connection 1. Given the connection details, e.g., username, hostname and password, create a connection to the database from a database client e.g., DBeaver. Part 2: Identify Tables in Database 1. Using the database navigator, note down the tables stored in this database. 2. Draw a simple diagram of the ACME database schema Part 3: SQL Select Data 1. For each of the following tables, Customers Orders Run an SQL SELECT statement, selecting all COLUMNS (also called attributes or features) of data but LIMIT the results of the queries to 50 rows. Section #2: Develop Business Insights Part 4: Customer Insights Part 4 are SQL statements that queries customers, products, and product order details to find possible patterns in customer purchase behaviours. Execute the SQL queries, interpret the results of the queries, and answer the following questions: 1. Who are the top customers in terms of the total dollar value of sales orders? 2. What were the top 5 product purchases? Part 5: The Supply Chain Part 5 are SQL statements that queries shipment and product supplier details to find insights into the supply chain. Execute the SQL queries, interpret the results of the queries, and answer the following questions: 1. Who are the top Suppliers in terms of total value of products? 2. Who are the top Shippers in terms of the number of orders? Part 6: Geographical Insights Part 6 are SQL statements that queries customers, products, and region details to find possible patterns in the geographical dispersion of customers. Execute the SQL queries, interpret the results of the queries, and answer the following questions: 1. Which countries are the top exporters in terms of the total value of goods? 2. In terms of the total value of goods, what are the top 5 regions for imports? Part 7: Employee Performances Part 7 are SQL statements that queries employees, product sales and customer order details to find possible patterns between employees and product sales. Execute the SQL queries, interpret the results of the queries, and answer the following questions: 1. With respect to the total values of the products sold, who are the top performing employees? 2. What are the top sales items sold by the top performing employees?
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Answer Section 1 Exploratory Data Analysis Query 1 Total Number of Customers SELECT COUNT AS TotalCu...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started