Overview:

In this assignment, you are given a database and a set of information requests. Use SQL commands to extract the required data, export it$,$ and then apply data visualization techniques to build a dashboard.

Case Study on the Given Database$$ SQL file:

The RetailDB database is designed for a retail business that manages customer information, product details, orders, and reviews. It is structured to facilitate efficient querying and reporting for various aspects of the retail operation, including customer management, product tracking, order processing, and customer feedback. The database consists of four tables.$$

Customers Table: Stores information about customers who engage with the retail business.

CustomerID: A unique identifier for each customer $($Primary Key$).$

$-$ FirstName: The customer's first name.

$-$ LastName: The customer's last name.

$-$ Email: The customer's email address $($Unique$).$

$-$ JoinDate: The date when the customer joined the retail system.

Products Table: Contains details about the products available for sale.

$-$ ProductID: A unique identifier for each product $($Primary Key$).$

$-$ ProductName: The name of the product.

$-$ Category: The category to which the product belongs $($e$.$g$.,$ Electronics, Clothing$).$

$-$ Price: The price of the product.

Orders Table: records the orders placed by customers for various products.

$-$ OrderID: A unique identifier for each order $($Primary Key$).$

$-$ CustomerID: The identifier of the customer who placed the order $($Foreign Key$).$

$-$ ProductID: The identifier of the product ordered $($Foreign Key$).$

$-$ OrderDate: The date when the order was placed.

$-$ Quantity: The quantity of the product ordered.

Reviews Table: Stores customer reviews for products, including ratings and review dates.

$-$ ReviewID: A unique identifier for each review $($Primary Key$).$

$-$ ProductID: The identifier of the product being reviewed $($Foreign Key$).$

$-$ CustomerID: The identifier of the customer who wrote the review $($Foreign Key$).$

$-$ Rating: The rating given by the customer, which must be between $1$ and $5.$

$-$ ReviewDate: The date when the review was written.

Task Description:

$1.$ Import the provided SQL file: $$RetailDB$.$sql$($this link downloads a file$)$

$2.$ Write SQL queries based on the requests below and export the data to CSV files.

Request $1$: Retrieve all product names and their prices where the price is higher than $$50.$

Request $2$: Retrieve the name of each product and the total quantity of that product ordered$$

$($Hint: need return a list of products with the total quantity ordered for each one.$)$

Request $3$: Retrieve all orders, including the order ID$,$ product ID$,$ quantity, and the year of the order, for products that have been rated $4$ or higher by any customer.$$

$($Hint: Consider using a subquery to first find the ProductID of products with ratings of $4$ or higher, and then retrieve the relevant order details for those products.$)$

For each query above, after running the SQL queries, export the results to CSV files. Name the files appropriately, for example, Request$1\_$Products$\_$prices.csv$,$ Request$2\_$Products$\_$quantity.csv and Request$3\_$Orders$\_$details.csv$.$

$3.$ Import CSV Data into Splunk and Create Dashboard:

Create a dashboard with the following $3$ panels, each linked to one of the imported CSV files.

Panel $1$: Create a pie chart that visualises the top $5$ products based on their price.

Panel $2$:$$Create a column chart that presents a simplified view of product names alongside the total quantity ordered.

Panel $3$:$$Create a line chart that visualises the total quantity ordered per year $.$