Untitled
user_4930908
mysql
a year ago
3.5 kB
12
Indexable
-- 17. SELECT c.name AS customer_name, p.name AS product_name, oi.amount, oi.price FROM orders o JOIN order_items oi ON o.id = oi.order_id JOIN customers c ON o.customer_id = c.id JOIN products p ON oi.product_id = p.id; -- 18. SELECT c.name AS customer_name, SUM(oi.price) AS total_purchase FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id GROUP BY c.id ORDER BY total_purchase DESC LIMIT 5; -- 19. SELECT c.name AS customer_name, SUM(oi.price) AS total_purchase FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id GROUP BY c.id HAVING total_purchase > 10000; -- 20. SELECT e.name AS employee_name, SUM(oi.price) AS total_sales FROM employees e JOIN orders o ON e.id = o.employee_id JOIN order_items oi ON o.id = oi.order_id GROUP BY e.id HAVING total_sales > (SELECT AVG(sales) FROM (SELECT e.id, SUM(oi.price) AS sales FROM employees e LEFT JOIN orders o ON e.id = o.employee_id LEFT JOIN order_items oi ON o.id = oi.order_id GROUP BY e.id) AS employee_sales); -- 21. SELECT e.name AS employee_name FROM employees e LEFT JOIN orders o ON e.id = o.employee_id WHERE o.id IS NULL; -- แสดงลูกค้าที่ไม่มียอดซื้อ SELECT c.name AS customer_name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id WHERE o.id IS NULL; -- 22. SELECT c.name AS customer_name, MAX(oi.price) AS max_purchase_amount FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id GROUP BY c.id HAVING max_purchase_amount > (SELECT AVG(purchase_amount) FROM (SELECT c.id, MAX(oi.price) AS purchase_amount FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id GROUP BY c.id) AS customer_purchases); -- 23. SELECT c.name AS customer_name FROM customers c JOIN orders o ON c.id = o.customer_id WHERE o.employee_id IS NULL ORDER BY COUNT(o.id) DESC LIMIT 1; -- 24. SELECT c.name AS customer_name FROM customers c WHERE c.id NOT IN (SELECT DISTINCT o.customer_id FROM orders o JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id JOIN suppliers s ON p.supplier_id = s.id WHERE s.name = 'ไท เอฟเวอเรสต์'); -- 25. SELECT c.name AS customer_name FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id WHERE p.name IN ('น้ำดื่มตรา คชสาร', 'น้ำดื่มตรา แมมมอธ') GROUP BY c.id HAVING COUNT(DISTINCT p.id) = 2; -- 26. SELECT c.name AS customer_name FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id JOIN suppliers s ON p.supplier_id = s.id WHERE s.name = 'ไท อาเซียน แอตแลนติก' AND c.id NOT IN (SELECT DISTINCT c.id FROM customers c JOIN orders o ON c.id = o.customer_id JOIN order_items oi ON o.id = oi.order_id JOIN products p ON oi.product_id = p.id JOIN suppliers s ON p.supplier_id = s.id WHERE s.name = 'ไท เอฟเวอเรสต์');
Editor is loading...
Leave a Comment