5年+算法工程师，分享人工智能前沿技术，与自己的工程经验，欢迎订阅关注。

Table:-- Select clients that have an invoiceSELECT *FROM clients cWHERE EXISTS( SELECT client_id FROM invoices WHERE client_id = c.client_id)output:

--Select employees whose salary is above the average in their office-- GET invoices that are larger than the client's average invoice amountSELECT *FROM invoices iWHERE invoice_total > ( SELECT AVG(invoice_total) FROM invoices WHERE clien

--Select client with at least two invoices__________________________________________count___________________________________________________看一下COUNT的用法：SELECT client_id, COUNT(*)FROM invoicesGROUP BY client_idOutput：_________________________

-- Select invoices larger than all invoices of client 3USE sql_invoicing;SELECT *FROM invoicesWHERE invoice_total >( SELECT MAX(invoice_total) FROM invoices WHERE invoice_id = 3)Other solution(with ALL 关键字)：USE sql_invoicing;SELEC

-- Find customers who have ordered lettuce(id = 3)-- Select customer_id, first_name, last_nameSELECT *FROM customers-- 在下面的子查询中， 返回订购了生菜的顾客的IDWHERE customer_id IN ( SELECT o.customer_id FROM order_items oi JOIN orders o USING(orde...

-- Find the products that have never been orderedUSE sql_store;SELECT *FROM productsWHERE product_id NOT IN( SELECT DISTINCT product_id FROM order_items)Output：-------------------------------------------------------------------作业---.

-- FIND products that are more-- expensive than Lettuce(id = 3)SELECT *FROM productsWHERE unit_price > ( SELECT unit_price FROM products WHERE product_id = 3)-- (这里面是lettuce的价格)Output：-------------------------------------------.

SELECT pm.name AS payment_method, sum(amount) AS totalFROM payments p JOIN payment_methods pm ON p.payment_method = pm.payment_method_idGROUP BY pm.name WITH ROLLUPOutput:

SELECT client_id, SUM(invoice_total) AS total_salesFROM invoicesGROUP BY client_id -- 分组HAVING total_sales > 500-- 使用having子句：为了在分组之后进行筛选Output:-------------------------------------------------作业-----------------------------------..

SELECT client_id, SUM(invoice_total) AS total_salesFROM invoicesWHERE invoice_date >= '2019-07-01'GROUP BY client_id -- 看每一个顾客花的钱ORDER BY total_sales DESC -- 按照total_sales 的降序来显示 看看谁花的多-- 注意顺序 永远是 FROM WHERE GROUP_BY ORDER_BY 这个顺序哈Ou.