Untitled
user_9217929
plain_text
a year ago
6.3 kB
17
Indexable
-- 1 SELECT e.name, e.address, d.name FROM employees e, departments d WHERE e.department_id = d.id ORDER BY d.name; SELECT e.name, e.address, d.name FROM employees e JOIN departments d on e.department_id ORDER BY d.name; -- 2 SELECT p.name, p.price, s.phone_number FROM products p JOIN suppliers s on s.id = p.supplier_id; -- 3 SELECT o.id, c.name custName, e.name FROM orders o JOIN customers c on c.id = o.customer_id JOIN employees e on e.id = o.employee_id; -- 4 SELECT c.id, c.name, count(*) AS order_amount FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id; -- 5 SELECT e.id, e.name, count(*) as order_amount FROM employees e LEFT JOIN orders o ON e.id = o.employee_id GROUP BY e.id; -- 6 SELECT s.id, s.name, count(*) FROM suppliers s LEFT JOIN products p ON s.id = p.supplier_id GROUP BY s.id; -- 7 SELECT s.id, s.name, count(*) as amount FROM products p JOIN suppliers s on s.id = p.supplier_id WHERE p.price > 800 GROUP BY s.id; -- 8 SELECT e.name FROM employees e JOIN departments d on d.id = e.department_id WHERE d.name = "ฝ่ายขาย"; -- 9 SELECT d.id, d.name, format (avg(e.salary), 2) AS AVG_salary FROM employees e RIGHT JOIN departments d ON d.id = e.department_id GROUP BY d.id Order by d.id; -- 10 SELECT format(SUM(price * amount * (1-discount)), 2) as SalasALL FROM order_items; -- 11 SELECT o.date, format(SUM(price * amount * (1-discount)), 2) as Sales FROM order_items od JOIN orders o ON o.id = od.order_id GROUP BY o.date; -- 12 SELECT c.id, c.name, format(SUM(price * amount * (1-discount)), 2) as Total 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; -- 13 SELECT e.id, e.name, format(SUM(price * amount * (1-discount)), 2) as Total FROM employees e JOIN orders o ON e.id = o.employee_id JOIN order_items oi ON e.id = oi.order_id GROUP BY e.id; -- 14 SELECT p.id, p.name, SUM(oi.price * oi.amount * (1-oi.discount)) as `Total` FROM products p JOIN order_items oi ON p.id = oi.product_id GROUP BY p.id HAVING `Total` < 20000; -- 15. lier ด้วย SELECT p.id, p.name, s.name, sum(oi.price * oi.amount * (1-oi.discount)) as `total` FROM products p JOIN order_items oi ON p.id = oi.product_id JOIN suppliers s ON s.id = p.supplier_id GROUP BY p.id ORDER BY total DESC Limit 5; -- 16. SELECT p.id, p.name, sum(oi.amount) 'amount' FROM products p JOIN order_items oi ON p.id = oi.product_id GROUP BY p.id ORDER BY amount desc LIMIT 1; -- 17. SELECT c.name as customer_name, p.name as product_name, sum(oi.price * oi.amount * (1-oi.discount)) as total_spent 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 p.id = oi.product_id GROUP BY c.name, p.name; -- 18. SELECT c.name as customer_name, format(SUM(oi.price * oi.amount * (1-oi.discount)), 2) as total_spent 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 ORDER BY total_spent DESC LIMIT 5; -- 19. SELECT c.name as customer_name, FORMAT(SUM(oi.price * oi.amount * (1-oi.discount)), 2) as total_spent 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 SUM(oi.price * oi.amount * (1-oi.discount)) > 10000; -- 20. SELECT employees.name AS SalespersonName,SUM(order_items.price) AS TotalSales FROM employees INNER JOIN orders ON employees.id = orders.employee_id INNER JOIN order_items ON orders.id = order_items.order_id GROUP BY employees.id , employees.name HAVING TotalSales > (SELECT AVG(sales) FROM (SELECT SUM(order_items.price) AS sales FROM orders INNER JOIN order_items ON orders.id = order_items.order_id INNER JOIN employees ON orders.employee_id = employees.id GROUP BY orders.employee_id) AS sales_avg); -- 21. -- Employees with no sales SELECT e.name AS employee_name FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE e.id = o.employee_id ); -- Customers with no purchases SELECT c.name AS customer_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE c.id = o.customer_id ); -- 22. SELECT cps.customer_name, FORMAT(cps.total_purchase, 2) AS total_purchase FROM ( SELECT c.id AS customer_id, c.name AS customer_name, SUM(oi.price * oi.amount * (1 - oi.discount)) AS total_purchase FROM customers c LEFT JOIN orders o ON c.id = o.customer_id LEFT JOIN order_items oi ON o.id = oi.order_id GROUP BY c.id, c.name ) AS cps ORDER BY cps.total_purchase DESC LIMIT 1; -- 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 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 LEFT JOIN products p ON oi.product_id = p.id LEFT JOIN suppliers s ON p.supplier_id = s.id WHERE s.name <> 'ไท เอฟเวอเรสต์' OR s.id IS NULL GROUP BY c.id, c.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, c.name HAVING COUNT(DISTINCT p.name) = 2 ORDER BY customer_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;
Editor is loading...
Leave a Comment