hw17-01-67
user_3598222
plain_text
a year ago
6.3 kB
16
Indexable
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 department_id = d.id order by d.name; -- select p.name, p.price ,s.phone_number from products p join suppliers s on s.id = p.suppliers_id; -- select * from orders o join customers c on c.id = o.customer_id; select o.id, c.name custName, e.name Sales from orders o join customers c on c.id = o.customer_id join employees e on e.id = o.employee_id; -- select c.id, c.name, count(o.id) as order_amount from customers c left join orders o on c.id = o.customer_id group by c.id; -- 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; -- select s.id, s.name, count(*) from suppliers s left join products p on s.id = p.supplier_id group by s.id; -- select s.id, s.name, p.name, p.price from products p join suppliers s on s.id = p.supplier_id where p.price >800; -- select e.name from employees e join departments d on d.id = e.department_id where d.name = 'ฝ่ายขาย'; -- 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; -- select format(sum(price * amount * (1-discount)),2) as SalesAll from order_items; -- 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; -- 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; -- 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 o.id = oi.order_id group by e.id; -- select p.id, p.name,format(sum(oi.price * amount * (1-discount)),2) as Total from products p join order_items oi on p.id = oi.product_id group by p.id having `total` <20000; -- select p.id, p.name,format(sum(oi.price * amount * (1-discount)),2) 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; -- 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; -- 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; -- select c.name as Customer_Name, p.name as Product_Name, oi.amount as Amount, p.price as Price_Products from order_items oi join orders o on oi.order_id = o.id join products p on oi.product_id = p.id join customers c on o.customer_id = c.id where p.name like '%ไฮยี%' order by oi.amount DESC limit 5; -- 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; -- 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); -- SELECT e.name AS employee_name FROM employees e WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE e.id = o.employee_id ); SELECT c.name AS customer_name FROM customers c WHERE NOT EXISTS ( SELECT 1 FROM orders o WHERE c.id = o.customer_id ); -- 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; -- 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; -- 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; -- 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; -- 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