SQLize | PHPize | SQLtest

-- Table des clients CREATE TABLE customers ( customer_id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(100) NOT NULL UNIQUE, name VARCHAR(100) NOT NULL, address VARCHAR(200), loyalty_level ENUM('New', 'Bronze', 'Silver', 'Gold') DEFAULT 'New', date_created DATETIME DEFAULT CURRENT_TIMESTAMP, date_modified DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_visit DATETIME, active BOOLEAN DEFAULT TRUE ); -- Table des fournisseurs CREATE TABLE suppliers ( supplier_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, email VARCHAR(100), phone VARCHAR(20) ); -- Table des produits CREATE TABLE products ( product_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(100) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL, stock INT DEFAULT 0, category VARCHAR(50), supplier_id INT, status VARCHAR(20) DEFAULT 'Active', last_sale_date DATETIME, date_added DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id) ); -- Table du catalogue produits CREATE TABLE product_catalog ( catalog_id INT PRIMARY KEY AUTO_INCREMENT, product_id INT, featured BOOLEAN DEFAULT FALSE, display_order INT, FOREIGN KEY (product_id) REFERENCES products(product_id) ); -- Table des commandes CREATE TABLE orders ( order_id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT, order_date DATETIME DEFAULT CURRENT_TIMESTAMP, status VARCHAR(20) DEFAULT 'Pending', total_amount DECIMAL(10,2), delivery_address VARCHAR(200), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); -- Table des éléments de commande CREATE TABLE order_items ( order_id INT, product_id INT, quantity INT NOT NULL, price_at_time DECIMAL(10,2) NOT NULL, PRIMARY KEY (order_id, product_id), FOREIGN KEY (order_id) REFERENCES orders(order_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); -- Table des avis produits CREATE TABLE product_reviews ( review_id INT PRIMARY KEY AUTO_INCREMENT, product_id INT, customer_id INT, rating INT CHECK (rating BETWEEN 1 AND 5), comment TEXT, review_date DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (product_id) REFERENCES products(product_id), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); -- Table des adresses de livraison CREATE TABLE delivery_addresses ( address_id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT, address VARCHAR(200) NOT NULL, is_default BOOLEAN DEFAULT FALSE, date_added DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); -- Table de l'historique des prix CREATE TABLE price_history ( history_id INT PRIMARY KEY AUTO_INCREMENT, product_id INT, price DECIMAL(10,2) NOT NULL, change_date DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (product_id) REFERENCES products(product_id) ); -- Table des listes de souhaits CREATE TABLE wishlist_items ( wishlist_id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT, product_id INT, date_added DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(customer_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); -- Table du panier CREATE TABLE cart_items ( cart_id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT, product_id INT, quantity INT DEFAULT 1, date_added DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(customer_id), FOREIGN KEY (product_id) REFERENCES products(product_id) ); -- Table des factures CREATE TABLE invoices ( invoice_id INT PRIMARY KEY AUTO_INCREMENT, order_id INT, customer_email VARCHAR(100), amount DECIMAL(10,2), date_created DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(order_id) ); -- Table des tickets support CREATE TABLE support_tickets ( ticket_id INT PRIMARY KEY AUTO_INCREMENT, customer_id INT, customer_email VARCHAR(100), subject VARCHAR(200), status VARCHAR(20) DEFAULT 'Open', date_created DATETIME DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ); -- Table d'audit CREATE TABLE audit_log ( log_id INT PRIMARY KEY AUTO_INCREMENT, table_name VARCHAR(50), record_id INT, action VARCHAR(20), old_value TEXT, new_value TEXT, change_date DATETIME DEFAULT CURRENT_TIMESTAMP, changed_by VARCHAR(50) ); -- Insert data into customers table INSERT INTO customers (email, name, address, loyalty_level, last_visit, active) VALUES ('john.doe@example.com', 'John Doe', '123 Elm Street, Cityville', 'Silver', '2023-09-15 12:45:00', TRUE), ('jane.smith@example.com', 'Jane Smith', '456 Oak Avenue, Townsville', 'Gold', '2023-10-01 14:10:00', TRUE), ('mike.jones@example.com', 'Mike Jones', '789 Pine Road, Villageton', 'Bronze', '2023-08-22 09:00:00', FALSE), ('lisa.brown@example.com', 'Lisa Brown', '321 Maple Drive, Lakeview', 'New', '2023-07-19 16:25:00', TRUE), ('paul.green@example.com', 'Paul Green', '654 Cedar Avenue, Hometown', 'Silver', '2023-06-25 11:40:00', TRUE), ('amy.white@example.com', 'Amy White', '987 Birch Blvd, Hilltown', 'Bronze', '2023-05-13 17:55:00', TRUE), ('mark.davis@example.com', 'Mark Davis', '456 Spruce Street, Valley City', 'Gold', '2023-04-20 18:05:00', FALSE), ('sara.wilson@example.com', 'Sara Wilson', '321 Aspen Way, Riverside', 'New', '2023-09-10 15:00:00', TRUE), ('tom.harris@example.com', 'Tom Harris', '789 Willow Lane, Greenfield', 'Silver', '2023-10-11 13:30:00', TRUE), ('linda.thomas@example.com', 'Linda Thomas', '456 Fir Avenue, Beachtown', 'Gold', '2023-10-15 12:10:00', TRUE), ('linda.x@example.com', 'Linda X', '456 Fir Avenue, X', 'Gold', NULL, TRUE); -- Insert data into suppliers table INSERT INTO suppliers (name, email, phone) VALUES ('TechCorp', 'contact@techcorp.com', '123-456-7890'), ('GadgetHouse', 'info@gadgethouse.com', '987-654-3210'), ('RetroFun', 'support@retrofun.com', '555-123-4567'), ('ElectroWorld', 'sales@electroworld.com', '444-222-3333'), ('PowerUp', 'info@powerup.com', '111-222-3333'), ('ComputeHub', 'service@computehub.com', '222-333-4444'), ('GizmoDepot', 'support@gizmodepot.com', '333-444-5555'), ('DeviceCenter', 'contact@devicecenter.com', '555-666-7777'), ('Techies', 'hello@techies.com', '666-777-8888'), ('BestDevices', 'info@bestdevices.com', '777-888-9999'); -- Insert data into products table INSERT INTO products (name, description, price, stock, category, supplier_id, status, last_sale_date) VALUES ('Gaming Laptop', 'High-performance gaming laptop', 1429.99, 50, 'Electronics', 1, 'Active', '2023-09-12 10:00:00'), ('Smartphone Pro', 'Latest model smartphone', 989.99, 100, 'Electronics', 2, 'Active', '2023-09-14 11:20:00'), ('Retro Console', 'Classic retro gaming console', 219.99, 75, 'Gaming', 3, 'Active', '2023-10-01 13:30:00'), ('Wireless Earbuds', 'Noise-cancelling earbuds', 149.99, 200, 'Audio', 4, 'Active', '2023-10-10 16:45:00'), ('Smartwatch', 'Feature-packed smartwatch', 249.99, 120, 'Wearables', 5, 'Active', '2023-10-12 14:55:00'), ('Tablet Pro', 'High-resolution tablet', 499.99, 90, 'Electronics', 6, 'Active', '2023-09-20 09:10:00'), ('4K TV', 'Ultra HD 4K television', 699.99, 30, 'Home Electronics', 7, 'Active', '2023-10-15 12:05:00'), ('Gaming Keyboard', 'Mechanical gaming keyboard', 79.99, 150, 'Peripherals', 8, 'Active', '2023-09-25 17:30:00'), ('Bluetooth Speaker', 'Portable Bluetooth speaker', 89.99, 180, 'Audio', 9, 'Active', '2023-10-05 18:00:00'), ('Smart Home Hub', 'Smart home device controller', 129.99, 80, 'Home Automation', 10, 'Active', '2023-09-18 20:15:00'); -- Insert data into product_catalog table INSERT INTO product_catalog (product_id, featured, display_order) VALUES (1, TRUE, 1), (2, TRUE, 2), (3, TRUE, 3), (4, FALSE, 4), (5, FALSE, 5), (6, TRUE, 6), (7, FALSE, 7), (8, TRUE, 8), (9, FALSE, 9), (10, TRUE, 10); -- Insert data into orders table INSERT INTO orders (customer_id, order_date, status, total_amount, delivery_address) VALUES (1, '2023-10-15 14:30:00', 'UNKNOWN', 1429.99, '123 Elm Street, Cityville'), (2, '2023-10-14 09:45:00', 'Delivered', 989.99, '456 Oak Avenue, Townsville'), (3, '2023-09-10 16:15:00', 'Shipped', 219.99, '789 Pine Road, Villageton'), (4, '2023-10-20 11:10:00', 'Pending', 149.99, '321 Maple Drive, Lakeview'), (5, '2023-10-11 13:25:00', 'Shipped', 249.99, '654 Cedar Avenue, Hometown'), (6, '2023-09-12 12:00:00', 'Delivered', 499.99, '987 Birch Blvd, Hilltown'), (7, '2023-08-22 09:55:00', 'Pending', 699.99, '456 Spruce Street, Valley City'), (8, '2023-07-10 10:45:00', 'Shipped', 79.99, '321 Aspen Way, Riverside'), (9, '2023-10-21 12:35:00', 'Pending', 89.99, '789 Willow Lane, Greenfield'), (10, '2023-09-10 15:15:00', 'Delivered', 129.99, '456 Fir Avenue, Beachtown'); -- Insert data into order_items table INSERT INTO order_items (order_id, product_id, quantity, price_at_time) VALUES (1, 1, 1, 1429.99), (2, 2, 1, 989.99), (3, 3, 1, 219.99), (4, 4, 1, 149.99), (5, 5, 1, 249.99), (6, 6, 1, 499.99), (7, 7, 1, 699.99), (8, 8, 1, 79.99), (9, 9, 1, 89.99), (10, 10, 1, 129.99); -- Insert data into product_reviews table INSERT INTO product_reviews (product_id, customer_id, rating, comment) VALUES (1, 1, 5, 'Amazing product, highly recommended!'), (2, 2, 4, 'Good value for money.'), (3, 3, 5, 'Classic experience! Love it.'), (4, 4, 3, 'Decent sound, could be better.'), (5, 5, 4, 'Great features, battery life could be improved.'), (6, 6, 5, 'Perfect for entertainment.'), (7, 7, 4, 'Impressive clarity and colors.'), (8, 8, 3, 'Keys are a bit stiff, but overall good.'), (9, 9, 4, 'Great for outdoor use.'), (10, 10, 5, 'Works flawlessly with all my devices.'); -- Insert data into delivery_addresses table INSERT INTO delivery_addresses (customer_id, address, is_default) VALUES (1, '123 Elm Street, Cityville', TRUE), (2, '456 Oak Avenue, Townsville', TRUE), (3, '789 Pine Road, Villageton', FALSE), (4, '321 Maple Drive, Lakeview', TRUE), (5, '654 Cedar Avenue, Hometown', TRUE), (6, '987 Birch Blvd, Hilltown', FALSE), (7, '456 Spruce Street, Valley City', TRUE), (8, '321 Aspen Way, Riverside', TRUE), (9, '789 Willow Lane, Greenfield', TRUE), (10, '456 Fir Avenue, Beachtown', TRUE); INSERT INTO delivery_addresses (customer_id, address, is_default, date_added) VALUES (1, '123 Elm Street, Cityville', TRUE, '2024-10-01'), (1, '123 Elm Street, Cityville', FALSE, '2023-10-01'); -- Insert data into price_history table INSERT INTO price_history (product_id, price, change_date) VALUES (1, 1429.99, '2023-09-10 10:30:00'), (2, 989.99, '2023-08-20 09:45:00'), (3, 219.99, '2023-09-01 14:00:00'), (4, 149.99, '2023-10-01 11:30:00'), (5, 249.99, '2023-09-25 15:10:00'), (6, 499.99, '2023-09-20 16:45:00'), (7, 699.99, '2023-08-10 12:10:00'), (8, 79.99, '2023-09-05 13:35:00'), (9, 89.99, '2023-09-30 14:20:00'), (10, 129.99, '2023-10-15 15:55:00'); -- Insert data into wishlist_items table INSERT INTO wishlist_items (customer_id, product_id) VALUES (1, 1), (2, 2), (3, 3), (4, 4), (5, 5), (6, 6), (7, 7), (8, 8), (9, 9), (10, 10); -- Insert data into cart_items table INSERT INTO cart_items (customer_id, product_id, quantity) VALUES (1, 2, 1), (2, 1, 1), (3, 5, 2), (4, 3, 1), (5, 4, 2), (6, 6, 1), (7, 8, 1), (8, 9, 2), (9, 7, 1), (10, 10, 1); -- Insert data into invoices table INSERT INTO invoices (order_id, customer_email, amount) VALUES (1, 'john.doe@example.com', 1429.99), (2, 'jane.smith@example.com', 989.99), (3, 'mike.jones@example.com', 219.99), (4, 'lisa.brown@example.com', 149.99), (5, 'paul.green@example.com', 249.99), (6, 'amy.white@example.com', 499.99), (7, 'mark.davis@example.com', 699.99), (8, 'sara.wilson@example.com', 79.99), (9, 'tom.harris@example.com', 89.99), (10, 'linda.thomas@example.com', 129.99); -- Insert data into support_tickets table INSERT INTO support_tickets (customer_id, customer_email, subject, status) VALUES (1, 'john.doe@example.com', 'Issue with Gaming Laptop', 'Open'), (2, 'jane.smith@example.com', 'Smartphone Pro not charging', 'Closed'), (3, 'mike.jones@example.com', 'Retro Console defect', 'Pending'), (4, 'lisa.brown@example.com', 'Missing accessory', 'Resolved'), (5, 'paul.green@example.com', 'Delivery delayed', 'Open'), (6, 'amy.white@example.com', 'Tablet Pro screen issue', 'Closed'), (7, 'mark.davis@example.com', 'Keyboard keys malfunctioning', 'Pending'), (8, 'sara.wilson@example.com', 'Bluetooth speaker sound issue', 'Open'), (9, 'tom.harris@example.com', 'Invoice discrepancy', 'Closed'), (10, 'linda.thomas@example.com', 'Warranty question', 'Open'); -- Insert data into audit_log table INSERT INTO audit_log (table_name, record_id, action, old_value, new_value, changed_by) VALUES ('customers', 1, 'Update', '{"loyalty_level": "Silver"}', '{"loyalty_level": "Gold"}', 'admin'), ('products', 2, 'Update', '{"stock": 100}', '{"stock": 90}', 'admin'), ('orders', 3, 'Insert', NULL, '{"order_id": 3, "status": "Shipped"}', 'system'), ('customers', 4, 'Delete', '{"active": "TRUE"}', '{"active": "FALSE"}', 'user'), ('support_tickets', 5, 'Update', '{"status": "Open"}', '{"status": "Resolved"}', 'support'), ('product_reviews', 6, 'Update', '{"rating": "4"}', '{"rating": "5"}', 'moderator'), ('products', 7, 'Insert', NULL, '{"product_id": 7, "status": "Active"}', 'admin'), ('invoices', 8, 'Update', '{"amount": "79.99"}', '{"amount": "89.99"}', 'accounting'), ('cart_items', 9, 'Delete', '{"quantity": "1"}', '{"quantity": "0"}', 'system'), ('wishlist_items', 10, 'Insert', NULL, '{"customer_id": 10, "product_id": 10}', 'user'); -- Insertion des produits avec stock=0 INSERT INTO products (name, price, stock, category, supplier_id, status) VALUES ('Casque Gaming Pro X', 149.99, 0, 'Audio', 1, 'Active'), ('Souris Optique 4000dpi', 49.99, 0, 'Peripherals', 2, 'Active'), ('Webcam HD Pro', 89.99, 0, 'Peripherals', 3, 'Active'); -- Ces produits ne sont pas dans order_items donc jamais commandés INSERT INTO products (name, price, stock, category, supplier_id, status) VALUES ('Microphone Studio USB', 129.99, 5, 'Audio', 4, 'Active'), ('Clavier Rétroéclairé', 79.99, 8, 'Peripherals', 5, 'Active'); -- Ces produits n'ont pas d'avis dans product_reviews INSERT INTO products (name, price, stock, category, supplier_id, status) VALUES ('Tapis de Souris XXL', 29.99, 20, 'Peripherals', 6, 'Active'), ('Hub USB Type-C', 39.99, 15, 'Peripherals', 7, 'Active'); INSERT INTO delivery_addresses (address) VALUES ('789 Test Street, New City'); -- D'abord, ajoutons la colonne et quelques managers ALTER TABLE customers ADD COLUMN manager_id INT; UPDATE customers SET manager_id = 2 WHERE customer_id IN (1, 3, 4); --- adresse de livraison non utilisée INSERT INTO delivery_addresses (address) VALUES ('789 Test Street, New City'); SELECT products.name, price FROM products WHERE price > (SELECT AVG(price) FROM products); SELECT customers.name, SUM(orders.total_amount) AS montant_tot FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.name, customers.customer_id HAVING SUM(orders.total_amount) > ( SELECT AVG(client_total) FROM ( SELECT SUM(orders.total_amount) AS client_total FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id GROUP BY customers.customer_id ) AS subquery ); SELECT products.name, products.category, products.price, CASE WHEN products.price > ( SELECT AVG(p2.price) FROM products p2 WHERE p2.category = products.category ) THEN 'Au dessus moyenne' WHEN products.price = ( SELECT AVG(p2.price) FROM products p2 WHERE p2.category = products.category ) THEN 'prix moyen' ELSE 'En dessous moyenne' END AS category FROM products; SELECT products.name, products.price from products WHERE products.price=(SELECT MAX(products.price) FROM products INNER JOIN product_reviews on products.product_id=product_reviews.product_id WHERE rating =5 ); SELECT products.name FROM products WHERE products.product_id NOT IN (SELECT order_items.product_id FROM order_items ); SELECT products.name FROM products WHERE price > ANY ( SELECT price FROM products WHERE category ='Audio' ); SELECT name, price FROM products WHERE price > ANY ( SELECT price FROM products WHERE category = 'Audio' ); SELECT sales_by_category.category, COUNT(sales_by_category.product_id) AS nombre_produits_vendus, SUM(sales_by_category.total_vente) AS montant_total_ventes FROM ( SELECT p.category, oi.product_id, oi.quantity * oi.price_at_time AS total_vente FROM order_items oi INNER JOIN products p ON oi.product_id = p.product_id ) AS sales_by_category GROUP BY sales_by_category.category; SELECT p.category, COUNT(oi.product_id) AS nombre_produits_vendus, SUM(oi.quantity * oi.price_at_time) AS montant_total_ventes FROM order_items oi JOIN products p ON oi.product_id = p.product_id GROUP BY p.category; SELECT p1.name, p1.category, p1.price - ( SELECT AVG(p2.price) FROM products p2 WHERE p2.category = p1.category ) AS price_difference FROM products p1; SELECT p1.name FROM products p1 WHERE p1.product_id NOT IN ( SELECT order_items.product_id FROM order_items ); SELECT products.name FROM products WHERE products.product_id NOT IN ( SELECT product_reviews.product_id from product_reviews ) AND stock >0; -- D'abbord... -- Insérer un nouveau client INSERT INTO customers (email, name, address, loyalty_level, last_visit, active) VALUES ('robert.johnson@example.com', 'Robert Johnson', '123 New Street, Techville', 'New', NOW(), TRUE); -- Récupérer l'ID du client nouvellement créé SET @new_customer_id = LAST_INSERT_ID(); -- Créer une commande pour ce client INSERT INTO orders (customer_id, order_date, status, total_amount, delivery_address) VALUES (@new_customer_id, NOW(), 'Pending', 159.99, '123 New Street, Techville'); -- Récupérer l'ID de la commande nouvellement créée SET @new_order_id = LAST_INSERT_ID(); -- Ajouter un élément à cette commande INSERT INTO order_items (order_id, product_id, quantity, price_at_time) VALUES (@new_order_id, 5, 1, 159.99); -- Supprimer tous les avis du client avec l'ID 5 (ou un autre client de votre choix) DELETE FROM product_reviews WHERE customer_id = 5; SELECT customers.name FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id WHERE customers.customer_id NOT IN ( SELECT product_reviews.customer_id FROM product_reviews ); SELECT products.name, products.price, MAX(price_history.change_date) AS date_modif FROM products LEFT JOIN price_history ON products.product_id = price_history.product_id GROUP BY products.product_id, products.name, products.price; SELECT products.name, products.price, price < SOMME ( SELECT products.price FROM products products.category='Electronics') AND price>100;