SQL-Project-Electronics-and-Appliances-Business

Project Overview

This is a comprehensive SQL script for managing an electronics and appliances business database. It includes the creation and population of three tables: Customers, Orders, and Products. Key SQL queries include: retrieve customers, price adjustment, revenue analysis and product information key skills include: data insertion and updating, joining tables, and data aggregation and analysis.

Technology Used

SQL

Methodology

1. Data Collection:

   The project simulates a real-life scenario for a business that sells electronics and appliances by creating and populating three different SQL tables: Customers, Orders, and Products. The data for these tables is generated randomly for demonstration purposes only.

2. Table Creation and Data Insertion:

   1. Customers Table: The Customers table was created to store customer information such as ID, name, email address, mobile number, age, and join date. Sample data was inserted to represent various customers.
   2. Orders Table: The Orders table was designed to record orders placed by customers, including details like order number, customer ID, order date, total amount, and order details. Sample data was inserted to represent orders placed within a specific timeframe.
   3. Products Table: The Products table holds information about available products, including product ID, name, category, price, and stock count. Sample data was inserted to represent the various products available for sale.

3. Queries for Data Retrieval and Analysis:

   Key SQL queries are performed to analyse business data, including selecting recent customers, updating customer information, counting total orders per customer, retrieving electronics category products, and determining total orders and revenue within a specific timeframe. These queries provide critical insights into customer behaviour, product inventory, and overall business performance.

Setup and Installation

1. Ensure that there is a SQL database setup (e.g., MySQL, PostgreSQL).
2. Download and execute the provided SQL script ('electronics_appliances.txt') in your database environment.

SQL Code Structure

The SQL code for this project is divided into several sections:

1. Creating Tables:
   1. Customers: Stores information about customers.
   2. Orders: Records orders placed by customers.
   3. Products: Contains information about available products.
2. Inserting Data: Populates the Customers, Orders, and Products tables with sample data.
3. SQL Queries:
   1. Retrieve customers who joined after January 1st, 2022.
   2. Update the email address of a specific customer.
   3. Find the total number of orders placed by each customer within a specified date range.
   4. Retrieve all products in the 'Electronics' category.
   5. Calculate the total order amount for each customer.
   6. Select the top 5 products with the most stock.
   7. Find the average age of customers who placed orders over 100 GBP.
   8. Increase prices of products in the 'Kitchen Appliances' category by 10%.
   9. Find the total number of orders and total revenue within a specified date range.

Conclusion

This project provides a practical example of using SQL to manage and analyse data for a business. By following the provided steps, the process can be executed and insights from the data can be retrieved.