SQL PROJECT FOR A DUMMY COMPANY LIMITED
INTRODUCTION
In this project, we delve into a fictional scenario where we explore the dummy dataset of a DUMMY COMPANY LTD a fictitious company, and its sales transactions using the DUMMY SALES DATASET and answer the business questions.
COMPANY LTD is a make-believe corporation that serves as the basis for our data analysis. The DUMMY SALES DATASET has been crafted for the sole purpose of this project and simulates various aspects of the company's operations, including its staff, customers, and sales records.
Though the COMPANY LTD and DUMMY SALES DATASET are entirely fictitious, the insights and analyses derived from this dataset can be applied as a template for real-world business scenarios. This project aims to demonstrate how SQL data analysis can be used to extract valuable insights and drive informed decision-making within a corporate environment.
**_Disclaimer_** : _All datasets and reports do not represent any company, institution or country, but a dummy dataset for practice to demonstrate SQL skillls and capabilities._
ANALYSIS
1.) How many staff do we have in the company?
Explanation: This query counts the number of distinct staff members in the company and labels the result as "Total_Staff."
2.) How many staff are younger than 30 years?
Explanation: This query counts the number of distinct staff members who are younger than 30 years and labels the result as "Staff_Younger_Than_30."
3.) How many staff are between 25 and 30 years?
Explanation: This query counts the number of distinct staff members whose ages fall between 25 and 30 years (inclusive) and labels the result as "Staff_between_30_and_25_years."
4.) Select all the female staff and sort their age in descending order (from the oldest to the youngest)
Explanation: This query selects the distinct names and ages of female staff members and arranges them in descending order of age, showcasing the oldest to the youngest female staff.
5.) What is the Average age of Peter and Nina?
Explanation: This query calculates the average age of staff members named "Peter" and "Nina" and labels the result as "Average_age."
6.) How many customers do we have?
Explanation: This query counts the number of distinct customers in the company and labels the result as "Total_Customers."
7.) How many customers are from Cameroon?
Explanation: This query counts the number of distinct customers who are from Cameroon and labels the result as "Cameroon_Customers."
8.) How many customers are from Cameroon and are Males?
Explanation: This query counts the number of distinct customers who are from Cameroon and have a gender of "Male," labeling the result as "Cameroon_Male_Customers."
9.) What are the First names and Last names of customers who come from Togo and USA?
Explanation: This query retrieves the first names and last names of customers who are from either Togo or the USA.
10.) Show the first 5 oldest customers and arrange the list in decreasing order of Age (Oldest to youngest)
Explanation: This query selects the first names and last names of the five oldest customers, arranging them in descending order of age.
11.) What is the average age of customers per country?
Explanation: This query calculates the average age of customers for each unique country they belong to, excluding records with missing ages.
12.) What is the total profit we made?
Explanation: This query calculates the total profit made by summing the "Profit" column in the "salestable" and labels the result as "Total_Profit."
13.) What is the total cost we incurred for these 03 countries "Cameroon," "USA," "Togo"?
Explanation: This query calculates the total cost incurred for customers from the countries "Cameroon," "USA," and "Togo" by joining the "salestable" and "customertable."
14.) What is the total profit we made per country?
Explanation: This query calculates the total profit made per country by joining the "salestable" and "customertable" and grouping the results by country.
15.) What is the average profit we made per country?
Explanation: This query calculates the average profit made per country by joining the "salestable" and "customertable," grouping the results by country, and ordering them in descending order of average profit.
16.) What is the total revenue per Staff?
Explanation: This query calculates the total revenue generated by each staff member by joining the "stafftable" and "salestable" and grouping the results by staff name.
17.) Which countries made more than 100 sales transactions? Sort them in decreasing order (from biggest to smallest)
Explanation: This query identifies countries that made more than 100 sales transactions and lists them in descending order based on the number of transactions.
18.) What is the total profit made per country by the following staff "Emelda," "Anita," "Cynthia"?
Explanation: This query calculates the total profit made per country by staff members "Emelda," "Anita," and "Cynthia" by joining the "salestable," "customertable," and "stafftable." It then groups the results by country and orders them in descending order of total profit.
CONCLUSION AND RECOMMENDATION
In conclusion, the SQL queries presented provide valuable insights into the company’s staff, customers, and sales data. Here are some key takeaways:
1. Staff Information:
- The company has a total of "Total_Staff" staff members.
- "Staff_Younger_Than_30" and "Staff_between_30_and_25_years" queries help identify staff age demographics, which could be useful for HR planning.
2. Customer Information:
- The company has "Total_Customers" customers, with a breakdown of "Cameroon_Customers" and "Cameroon_Male_Customers" for specific demographics.
- "Average_Age" by country offers insights into the age distribution of customers per country.
3. Profit and Revenue:
- The company's total profit and cost can be found using "Total_Profit" and "Total_cost" queries, which are essential for financial analysis.
- "Total_Profit" and "Total_cost" per country provide insights into the profitability and cost management of different regions.
- "Average_Profit" per country highlights regions with strong average profitability.
4. Staff Performance:
- "Total_Revenue" per staff member identifies top revenue-generating employees.
- "Sales_Transactions" per country helps analyze countries with a high volume of sales transactions.
Based on these insights, the following recommendations can be made:
1. HR Planning:
- Use age demographic data to plan for recruitment, training, and succession planning among staff members.
- Recognize and reward high-performing staff members identified in "Total_Revenue" per staff query.
2. Customer Analysis:
- Focus marketing efforts on countries with high sales transactions, as indicated by "Sales_Transactions" per country.
- Tailor products or services to meet the specific needs of customers in high-profit countries.
3. Financial Management:
- Continuously monitor total profit and cost to ensure the company's financial health.
- Analyze the average profit per country to identify regions with potential for growth or improvement.
4. Sales Team Performance:
- Identify top revenue-generating staff members using the "Total_Revenue" query and consider rewarding their performance.
5. Operational Efficiency:
- Evaluate the total cost incurred for specific countries ("Total_cost" query) and identify areas for cost-saving or optimization.
By leveraging these SQL queries and recommendations, the company can make data-driven decisions to enhance staff management, customer engagement, financial performance, and operational efficiency, ultimately contributing to its overall success and growth.