Untitled
unknown
mysql
a year ago
3.8 kB
5
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.amount * (oi.price - oi.discount)) 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.name ORDER BY total_purchase DESC LIMIT 5; -- 19. SELECT customers.name AS CustomerName, SUM(order_items.price) AS TotalQuantity FROM customers INNER JOIN orders ON customers.id = orders.customer_id INNER JOIN order_items ON orders.id = order_items.order_id GROUP BY customers.id, customers.name HAVING TotalQuantity > 10000; -- 20. SELECT e.name as sales_name, sum(oi.price * oi.amount * (1-oi.discount)) 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.name HAVING total_sales > (SELECT AVG(total_sales) FROM (SELECT e.id, sum(oi.price * oi.amount * (1-oi.discount)) 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) AS avg_sales); -- 21. SELECT e.name as employee_name FROM employees e LEFT JOIN orders o ON e.id = o.employee_id LEFT JOIN order_items oi ON o.id = oi.order_id WHERE oi.order_id IS NULL UNION SELECT c.name as customer_name FROM customers c LEFT JOIN orders o ON c.id = o.customer_id LEFT JOIN order_items oi ON o.id = oi.order_id WHERE oi.order_id IS NULL; -- 22. SELECT customer_name, MAX(total_purchase) AS max_purchase FROM ( SELECT c.id, c.name AS customer_name, SUM(oi.amount * (oi.price - oi.discount)) 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, c.name HAVING total_purchase < ( SELECT AVG(total_purchase) FROM ( SELECT c.id, SUM(oi.amount * (oi.price - oi.discount)) 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 ) AS avg_purchase ) ) AS customer_purchases GROUP BY customer_name LIMIT 0, 1000; -- 23. SELECT c.name as customer_name, COUNT(o.id) as order_count FROM customers c JOIN orders o ON c.id = o.customer_id LEFT JOIN employees e ON e.id = o.employee_id WHERE e.id IS NULL GROUP BY c.id, c.name ORDER BY order_count DESC LIMIT 1; -- 24. SELECT DISTINCT c.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 NOT LIKE 'ไท เอฟเวอเรสต์'; -- 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.name; -- 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 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 = 'ไท เอฟเวอเรสต์' ) GROUP BY c.id, c.name; -- Nattapon Nupao SNRU
Editor is loading...
Leave a Comment