تمارين عملية لتحليل البيانات باستخدام SQL و Excel

سنبدأ بمشروع تحليل بيانات بسيط، وهو تحليل مبيعات متجر إلكتروني، وسنستخدم بيانات افتراضية لغرض التمرين.

لدينا جدولين أولهم جدول المنتجات Products:

اسم العمود	نوع البيانات	الوصف
product_id	INT		مُعرّف المنتج 
product_name	VARCHAR(255)	اسم المنتج
category	VARCHAR(255)	تصنيف المنتج
price		DECIMAL(10,2)	سعر المنتج

المفتاح الأساسي هو product_id.

الجدول الثاني جدول الطلبات Orders:

اسم العمود	نوع البيانات	الوصف
order_id	INT	مُعرّف الطلب (مفتاح أساسي)
product_id	INT	مُعرّف المنتج (مفتاح خارجي)
quantity	INT	كمية المنتج المطلوبة
order_date	DATE	تاريخ الطلب

عليك تنفيذ الاستعلامات التالية:

1. استعلام SQL لعرض جميع المنتجات من تصنيف "إلكترونيات".
2. استعلام لعرض إجمالي المبيعات (السعر * الكمية) لكل منتج.
3. استعلام لعرض أفضل 5 منتجات مبيعًا من حيث الكمية.
4. استعلام لعرض إجمالي المبيعات لكل شهر.
5. استعلام لعرض متوسط سعر المنتجات في كل تصنيف.

تمارين Excel:

1. قومي باستيراد البيانات من جدولي SQL (Products و Orders) إلى Excel.
2. أنشئي جدولًا محوريًا لعرض إجمالي المبيعات لكل تصنيف.
3. أنشئي مخططًا بيانيًا لعرض إجمالي المبيعات لكل شهر.
4. استخدمي الدوال في Excel لحساب هامش الربح لكل منتج (بافتراض أن تكلفة كل المنتجات ثابتة).
5. تنسيق البيانات في Excel لإنشاء تقرير مبيعات شهري جذاب بصريًا.

but, You didn't give me the data that I should insert them to the tables, or should I find them by myself but where

تستطيعي البحث عن قاعدة بيانات خاصة بذلك على المواقع التي ذكرتها لك من قبل، أو تستطيعي توليد تلك البيانات من خلال استعلامات SQL 

should you give me Exercise about thus files instead

archive.zip

لتسهيل الأمر إليك كود SQL لتوليد قاعدة البيانات مع بيانات وهمية:

-- إنشاء قاعدة البيانات
CREATE DATABASE IF NOT EXISTS ecommerce_db;

USE ecommerce_db;

-- إنشاء جدول المنتجات
CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(255),
    category VARCHAR(255),
    price DECIMAL(10,2)
);

-- إنشاء جدول الطلبات
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    product_id INT,
    quantity INT,
    order_date DATE,
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

-- إدراج بيانات وهمية في جدول المنتجات
INSERT INTO Products (product_id, product_name, category, price) VALUES
(1, 'هاتف ذكي', 'إلكترونيات', 500.00),
(2, 'حاسوب محمول', 'إلكترونيات', 1000.00),
(3, 'سماعة رأس', 'إلكترونيات', 50.00),
(4, 'تيشيرت', 'ملابس', 20.00),
(5, 'بنطلون جينز', 'ملابس', 50.00),
(6, 'حذاء رياضي', 'ملابس', 80.00),
(7, 'كتاب', 'كتب', 10.00),
(8, 'قلم', 'أدوات مكتبية', 2.00),
(9, 'دفتر ملاحظات', 'أدوات مكتبية', 5.00);

-- إدراج بيانات وهمية في جدول الطلبات
INSERT INTO Orders (order_id, product_id, quantity, order_date) VALUES
(1, 1, 2, '2023-03-01'),
(2, 3, 1, '2023-03-02'),
(3, 2, 1, '2023-03-03'),
(4, 4, 3, '2023-03-04'),
(5, 5, 2, '2023-03-05'),
(6, 1, 1, '2023-03-06'),
(7, 7, 5, '2023-03-07'),
(8, 8, 10, '2023-03-08'),
(9, 9, 2, '2023-03-09'),
(10, 2, 1, '2023-03-10'),
(11, 6, 2, '2023-03-11'),
(12, 3, 3, '2023-03-12');

تستطيعي زيادة عدد البيانات كما تريدين باستخدام دالة WHILE لإنشاء حلقة تقوم بإدراج سجلات جديدة في الجدول حتى تصل إلى عدد السجلات المطلوب.

أو من خلال Python مع مكتبات مثل random و faker لإنشاء بيانات وهمية عشوائية، ثم استخدام مكتبات مثل pyodbc أو mysql.connector لإدراجها في قاعدة بيانات SQL.

استعلام SQL لعرض جميع المنتجات من تصنيف "إلكترونيات".

استعلام لعرض إجمالي المبيعات (السعر * الكمية) لكل منتج.

استعلام لعرض أفضل 5 منتجات مبيعًا من حيث الكمية.

استعلام لعرض إجمالي المبيعات لكل شهر.

استعلام لعرض متوسط سعر المنتجات في كل تصنيف.

also I didn't know how to answear question 4 because I didn't know how to use month, also what is the use of "FOREIGN KEY (product_id) REFERENCES Products(product_id)" I didn't know how to use it 

New folder.zip

بتاريخ منذ ساعة مضت قال Rafika Cha:

استعلام SQL لعرض جميع المنتجات من تصنيف "إلكترونيات".

استعلام لعرض إجمالي المبيعات (السعر * الكمية) لكل منتج.

أهنئك على اجتهادك ومحاولاتك، وكلها صحيحة بفضل الله مع بعض الملاحظات البسيطة.

الاستعلام الأول والثاني سليم تمامًا لا ملاحظات عليه

بتاريخ 1 ساعة قال Rafika Cha:

استعلام لعرض أفضل 5 منتجات مبيعًا من حيث الكمية.

الاستعلام الثالث بالفعل يعرض المنتجات على حسب أفضلية المبيعات، إلا إنه يعرض جميع المنتجات في حين نحن نريد عرض أول خمس منتجات فقط لذلك هناك إضافة بسيطة جدًا وهي limit 5 بآخر الاستعلام

select product_name, quantity, price, (quantity*price) as total_price
from master..Products, orders
where Products.product_id = orders.product_id
order by quantity desc
limit 5

بتاريخ 1 ساعة قال Rafika Cha:

استعلام لعرض متوسط سعر المنتجات في كل تصنيف.

الاستعلام صحيح مائة بالمائة.

أما بخصوص الاستعلام الرابع

بتاريخ 2 ساعة قال Rafika Cha:

استعلام لعرض إجمالي المبيعات لكل شهر

نحن نريد استخراج السنة والشهر من التاريخ، لأنه سيتم التجميع بناء عليهم وليس على التاريخ كله

SELECT YEAR(order_date) AS SalesYear,
       MONTH(order_date) AS SalesMonth,
       SUM(Price) AS TotalSales
FROM Sales
GROUP BY YEAR(order_date), MONTH(order_date)
ORDER BY YEAR(order_date), MONTH(order_date)

تمارين Excel:

قومي باستيراد البيانات من جدولي SQL (Products و Orders) إلى Excel.

أنشئي جدولًا محوريًا لعرض إجمالي المبيعات لكل تصنيف.

أنشئي مخططًا بيانيًا لعرض إجمالي المبيعات لكل شهر.

استخدمي الدوال في Excel لحساب هامش الربح لكل منتج (بافتراض أن تكلفة كل المنتجات ثابتة).

تنسيق البيانات في Excel لإنشاء تقرير مبيعات شهري جذاب بصريًا.

could you send me more please

Attachments.zip

بتاريخ 21 ساعة قال Rafika Cha:

I will give you 4 files, each file will be used by unique program,

 you tell me what you desire from them and I will give you what you want 

books.csv sql and power BI

netflix_titles.csv Tableau

Attachments.zipexcelimdb_top_1000.csvj

Anime_rank.csv python