Complete the SQL queries to find business insights. Prepare a short report containing succinct answers to...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
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? 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?
Expert Answer:
Answer rating: 100% (QA)
Answer Section 1 Exploratory Data Analysis Query 1 Total Number of Customers SELECT COUNT AS TotalCu... View the full answer
Related Book For
Income Tax Fundamentals 2013
ISBN: 9781285586618
31st Edition
Authors: Gerald E. Whittenburg, Martha Altus Buller, Steven L Gill
Posted Date:
Students also viewed these databases questions
-
Discuss the functional significance of parallel versus serially arranged sarcomeres in terms of force output and velocity of shortening?
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
The Crazy Eddie fraud may appear smaller and gentler than the massive billion-dollar frauds exposed in recent times, such as Bernie Madoffs Ponzi scheme, frauds in the subprime mortgage market, the...
-
A red laser ( lambda r = 6 3 3 nm ) is shone through a narrow slit located 2 m from a screen. When the light source is replaced by a blue laser ( b = 4 8 8 nm ) the position of the first minimum of...
-
A bank has a portfolio of options on an asset. The delta of the options is -30 and the gamma is -5. Explain how these numbers can be interpreted. The asset price is 20 and its volatility per day is...
-
Suppose housing prices across the world double. a. Is society any richer for the change? b. Are homeowners wealthier? c. Can you reconcile your answers to ( a ) and ( b )? Is anyone worse off as a...
-
Determine the illuminances (sun, sky, and ground-reflected) on a vertical, south-facing window at solar noon at \(36^{\circ} \mathrm{N}\) latitude on June 21 and December 21 for (a) a clear day (b)...
-
Harper acquires 40 percent of the outstanding voting stock of Kinman Company on January 1, 2014, for $210,000 in cash. The book value of Kinmans net assets on that date was $400,000, although one of...
-
Papraika Limited ( also known as Papraika Stores ) is the largest non - food retailer in South Africa and is listed on the Johannesburg Stock Exchange ( JSE ) . The company is the leading retailer of...
-
The bookkeeper of Butterfly Co. gathered the following data from individual employee earnings records and daily time cards. Your task is to complete a payroll register on August 8. Assume the...
-
What are some reasons why companies engage in stock buybacks? Are they gimmicks or tricks to fool investors, or might there be good reasons fordoing this? Here is a recent article about stock...
-
In 2018, India was the worlds seventh largest economy, with a $2.69 trillion GDP (as measured in U.S. dollars). India was also one of the worlds fastest-growing economies, with an annual growth rate...
-
Which of the following are signs of inflation? a. The price of a house in a high-demand market increased by 6% last year. b. CPI in the European Union was 101 in 2017 and 104 in 2018. c. The price of...
-
Both CPI and the GDP deflator measure the change in the price of goods and services and tend to change in similar ways over time. In the third quarter of 2018, CPI rose by 1.5% from the previous...
-
In the debate over the Tax Cuts and Job Act of 2018, Republicans argued that businesses needed an incentive to invest more in physical capital in order for the United States to see much faster...
-
The average household income in the United States in 1975 was $13,800 and CPI was 53.8. Convert the average income in 1975 to 2018 dollars if CPI was 251.1 in 2018.
-
First identify and label any variable(s) in this problem. (Diagram not drawn to scale.) Then write an equation(s) and show how to solve it algebraically. Be prepared to show your work. Cheryl fenced...
-
Which one of the following anhydrous chloride is not obtained on direct heating of its hydrated chloride? (A) BaCl2 (B) CaClz (C) MgCl2 (D) SrCl2
-
Ken (age 31) and Amy (age 28) Booth have brought you the following information regarding their income, expenses, and withholding for the year. They are unsure which of these items must be used to...
-
Skyler is covered by his company's health insurance plan. The health insurance costs his company $3,500 a year. During the year, Skyler is diagnosed with a serious illness and the health insurance...
-
Lisa Sizemore, a taxpayer in the 10-15 percent tax bracket, purchased stock as an investment on July 11, 2011. She sold the stock on July 9, 2012, 2 days before qualifying for the long-term holding...
-
What is the direction of the magnetic field lines inside the bar magnet of Figure 27.13? Figure 27.13 Magnetic field line pattern surrounding a bar magnet. N S
-
(a) Consider a single elementary magnet inside a closed surface. Given that elementary magnets are particles without spatial extent, is the magnetic field line flux through the closed surface...
-
Sketch the magnetic field line pattern in the horizontal plane around the rod in Figure 27.16. Figure 27.16 A flow of charge carriers through a conducting rod causes a circular alignment of compass...
Study smarter with the SolutionInn App