Tutorial SQL E-commerce - Bază de Date Magazin Online

📊 Schema Bazei de Date

Schema reprezintă structura unui magazin online cu 4 tabele principale interconectate:

💡 Tip: Relațiile dintre tabele sunt esențiale pentru integritatea datelor. Customer → Orders (1:N), Orders → Order_Details (1:N), Products → Order_Details (1:N)

🔨 Creare Tabele

Pentru a crea structura bazei de date, executăm următoarele comenzi SQL în ordine:

1. Tabelul CUSTOMERS

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(100) UNIQUE NOT NULL,
    password VARCHAR(100) NOT NULL,
    full_name VARCHAR(100) NOT NULL,
    billing_address VARCHAR(100),
    default_shipping_address VARCHAR(100),
    country VARCHAR(40),
    phone VARCHAR(40)
);

2. Tabelul PRODUCTS

CREATE TABLE products (
    id INT PRIMARY KEY AUTO_INCREMENT,
    sku INT UNIQUE NOT NULL,
    name VARCHAR(40) NOT NULL,
    price INT NOT NULL,
    weight INT,
    descriptions VARCHAR(200),
    thumbnail VARCHAR(100),
    image VARCHAR(100),
    category VARCHAR(40),
    create_date DATE DEFAULT CURRENT_DATE,
    stock INT DEFAULT 0
);

3. Tabelul ORDERS

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    ammount INT NOT NULL,
    shipping_address VARCHAR(100),
    order_address VARCHAR(100),
    order_email VARCHAR(100),
    order_date DATE DEFAULT CURRENT_DATE,
    order_status VARCHAR(40) DEFAULT 'pending',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

4. Tabelul ORDER_DETAILS

CREATE TABLE order_details (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    price INT NOT NULL,
    sku INT,
    quantity INT DEFAULT 1,
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

⚠️ Atenție: Ordinea creării tabelelor este importantă! Tabelele referențiate de chei străine trebuie create primele.

📝 Populare cu Date

După crearea tabelelor, le populăm cu date de test pentru a putea exersa query-uri:

Inserare Clienți (Exemple)

INSERT INTO customers (email, password, full_name, billing_address, country, phone) 
VALUES 
    ('ion.popescu@gmail.com', MD5('parola123'), 'Ion Popescu', 'Str. Victoriei 10, București', 'România', '0721234567'),
    ('maria.ionescu@yahoo.com', MD5('maria456'), 'Maria Ionescu', 'Bd. Eroilor 12, Cluj', 'România', '0734567890'),
    ('andrei.stan@outlook.com', MD5('andrei789'), 'Andrei Stan', 'Str. Libertății 45, Timișoara', 'România', '0745678901');

Inserare Produse (Exemple)

INSERT INTO products (sku, name, price, weight, descriptions, category, stock) 
VALUES 
    (1001, 'Laptop ASUS ROG', 4500, 2300, 'Gaming laptop RTX 3060', 'Laptopuri', 15),
    (2001, 'iPhone 15 Pro', 7300, 221, 'Flagship Apple 256GB', 'Telefoane', 20),
    (3001, 'iPad Pro 12.9', 6000, 682, 'Tableta M2 256GB', 'Tablete', 8);

💡 Tip: Folosim MD5() pentru a cripta parolele și nu le stocăm în text clar. În producție, folosiți metode mai sigure precum bcrypt.

🔍 30 SELECT-uri Utile

📌 Query-uri de Bază

1. Afișare toți clienții

Listează toți clienții din baza de date

SELECT * FROM customers;

2. Produse în stoc

Afișează doar produsele disponibile

SELECT name, price, stock 
FROM products 
WHERE stock > 0;

3. Comenzi recente

Ultimele 10 comenzi plasate

SELECT * FROM orders 
ORDER BY order_date DESC 
LIMIT 10;

4. Căutare client după email

Găsește un client specific

SELECT * FROM customers 
WHERE email = 'ion.popescu@gmail.com';

5. Produse dintr-o categorie

Toate laptopurile disponibile

SELECT * FROM products 
WHERE category = 'Laptopuri';

🔗 JOIN-uri și Relații

6. Comenzi cu detalii client

Afișează comenzile împreună cu numele clienților

SELECT o.id, c.full_name, o.ammount, o.order_date, o.order_status
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id;

7. Detalii complete comandă

Toate informațiile despre o comandă

SELECT o.id, c.full_name, p.name, od.quantity, od.price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_details od ON od.order_id = o.id
JOIN products p ON p.id = od.product_id
WHERE o.id = 1;

8. Istoric comenzi client

Toate comenzile unui client specific

SELECT o.*, p.name AS product_name
FROM orders o
JOIN order_details od ON o.id = od.order_id
JOIN products p ON p.id = od.product_id
WHERE o.customer_id = 1;

9. Produse comandate frecvent

Produsele care apar în comenzi

SELECT DISTINCT p.name, p.price
FROM products p
INNER JOIN order_details od ON p.id = od.product_id;

10. Clienți fără comenzi

Identifică clienții care nu au comandat

SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.id IS NULL;

📊 Agregări și Statistici

11. Total vânzări

Suma totală a tuturor comenzilor

SELECT SUM(ammount) AS total_sales
FROM orders
WHERE order_status = 'completed';

12. Număr comenzi per status

Distribuția comenzilor pe statusuri

SELECT order_status, COUNT(*) AS count
FROM orders
GROUP BY order_status;

13. Top 5 clienți

Clienții cu cele mai mari cheltuieli

SELECT c.full_name, SUM(o.ammount) AS total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
ORDER BY total_spent DESC
LIMIT 5;

14. Produs cel mai scump

Găsește produsul cu prețul maxim

SELECT * FROM products
WHERE price = (SELECT MAX(price) FROM products);

15. Media prețurilor per categorie

Prețul mediu pentru fiecare categorie

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

🔍 Căutări și Filtrări Avansate

16. Produse între prețuri

Produse cu preț între 1000 și 5000

SELECT * FROM products
WHERE price BETWEEN 1000 AND 5000
ORDER BY price;

17. Căutare în nume produs

Produse care conțin "Laptop" în nume

SELECT * FROM products
WHERE name LIKE '%Laptop%';

18. Comenzi din ultima lună

Comenzi plasate în ultimele 30 de zile

SELECT * FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY);

19. Produse cu stoc critic

Produse cu mai puțin de 5 bucăți

SELECT name, stock FROM products
WHERE stock < 5 AND stock > 0;

20. Clienți din România

Toți clienții din țara specificată

SELECT full_name, email, phone
FROM customers
WHERE country = 'România';

🚀 Query-uri Complexe

21. Raport vânzări lunare

Total vânzări grupate pe luni

SELECT 
    MONTH(order_date) AS month,
    YEAR(order_date) AS year,
    COUNT(*) AS total_orders,
    SUM(ammount) AS total_revenue
FROM orders
WHERE order_status = 'completed'
GROUP BY YEAR(order_date), MONTH(order_date);

22. Produse nevândute

Produse care nu au fost comandate niciodată

SELECT p.*
FROM products p
WHERE NOT EXISTS (
    SELECT 1 FROM order_details od
    WHERE od.product_id = p.id
);

23. Ranking produse după vânzări

Top produse după numărul de vânzări

SELECT 
    p.name,
    COUNT(od.id) AS times_sold,
    SUM(od.quantity) AS total_quantity
FROM products p
JOIN order_details od ON p.id = od.product_id
GROUP BY p.id
ORDER BY times_sold DESC;

24. Valoare medie comandă per client

Media cheltuielilor pentru fiecare client

SELECT 
    c.full_name,
    COUNT(o.id) AS total_orders,
    AVG(o.ammount) AS avg_order_value
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.id
HAVING COUNT(o.id) > 0;

25. Duplicate email check

Verifică emailuri duplicate

SELECT email, COUNT(*) AS count
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

🛠️ Utilitare și Mentenanță

26. Actualizare stoc după vânzare

Scade stocul unui produs

UPDATE products 
SET stock = stock - 1
WHERE id = 1 AND stock > 0;

27. Ștergere comenzi vechi anulate

Curăță comenzile anulate mai vechi de 1 an

DELETE FROM orders
WHERE order_status = 'cancelled'
AND order_date < DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

28. Backup clienți activi

Creează tabel backup pentru clienți cu comenzi

CREATE TABLE customers_backup AS
SELECT DISTINCT c.*
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id;

29. Index pentru performanță

Creează indexuri pentru optimizare

CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status ON orders(order_status);
CREATE INDEX idx_products_category ON products(category);

30. Informații despre tabele

Vezi structura unui tabel

DESCRIBE products;
-- sau
SHOW COLUMNS FROM products;

📚 Exerciții Practice

Testează-ți cunoștințele cu aceste exerciții progresive:

🟢 Nivel Începător

1. Afișează toate produsele care costă mai mult de 3000 lei

2. Găsește clientul cu ID-ul 5

3. Listează toate comenzile cu status "pending"

4. Afișează primele 3 produse din categoria "Telefoane"

5. Calculează numărul total de clienți

🟡 Nivel Intermediar

1. Găsește toate comenzile plasate de "Ion Popescu"

2. Calculează valoarea totală a stocului pentru fiecare categorie

3. Afișează produsele comandate de mai mult de 3 ori

4. Găsește clientul care a cheltuit cel mai mult

5. Listează produsele care nu sunt în stoc dar au fost comandate

🔴 Nivel Avansat

1. Creează un raport cu top 3 produse din fiecare categorie după vânzări

2. Găsește clienții care au comandat din toate categoriile

3. Calculează rata de conversie (clienți cu comenzi / total clienți)

4. Identifică produsele cu preț peste media categoriei lor

5. Creează un view cu comenzile și profitul estimat (20% din valoare)

🏆 Provocări Expert

1. Implementează un trigger pentru actualizare automată stoc

2. Creează o procedură stocată pentru raport lunar complet

3. Dezvoltă un sistem de recomandări bazat pe istoric comenzi

4. Optimizează toate query-urile pentru o bază cu 1M înregistrări

5. Creează un dashboard SQL cu KPI-uri principale

💡 Sfat pentru exerciții: Începe cu exercițiile simple și avansează gradual. Pentru fiecare exercițiu, gândește-te mai întâi la logica query-ului, apoi scrie codul SQL. Verifică rezultatele și optimizează dacă este necesar.

Soluții Exemple (Nivel Începător)

Exercițiul 1: Produse peste 3000 lei

SELECT name, price, category
FROM products
WHERE price > 3000
ORDER BY price DESC;

Exercițiul 2: Client cu ID 5

SELECT * FROM customers
WHERE id = 5;

Resurse Adiționale

📖 Pentru învățare continuă:

Practică zilnic cu date reale
Studiază planurile de execuție (EXPLAIN)
Învață despre normalizarea bazelor de date
Explorează funcții avansate: Window Functions, CTEs
Optimizează performanța cu indexuri corecți