MySQL查询案例题_MYSQL子查询例题以及答案

More Subqueries Quizzes

Above is the ERD for the database again - it might come in handy as you tackle the quizzes below. You should write your solution as a subquery or subqueries, not by finding one solution and copying the output. The importance of this is that it allows your query to be dynamic in answering the question - even if the data changes, you still arrive at the right answer.

Provide the name of the sales_rep in each region with the largest amount of total_amt_usd sales.

SELECT * FROM(SELECT rname rname,MAX(totalsum) totalsumFROM(SELECT r.name rname,s.name sname,SUM(total_amt_usd) totalsumFROMaccounts aJOINorders oON o.account_id =a.idJOINsales_reps sON a.sales_rep_id =s.idJOINregion rON r.id =s.region_idGROUP BYr.name,s.nameORDER BY 1,3 desc) subGROUP BYrname ) t1JOIN(SELECT r.name rname,s.name sname,SUM(total_amt_usd) totalsumFROMaccounts aJOINorders oON o.account_id =a.idJOINsales_reps sON a.sales_rep_id =s.idJOINregion rON r.id =s.region_idGROUP BYr.name,s.nameORDER BY 1,3 desc) t2ON t1.rname =t2.rnameWHERE t1.totalsum = t2.totalsum

METHOD2

WITH t1 AS (SELECT rname rname,MAX(totalsum) totalsum

FROM

(SELECT r.name rname,s.name sname,SUM(total_amt_usd) totalsum

FROM accounts a

JOIN orders o

ON o.account_id = a.id

JOIN sales_reps s

ON a.sales_rep_id = s.id

JOIN region r

ON r.id = s.region_id

GROUP BY r.name,s.name

ORDER BY 1,3 desc ) sub

GROUP BY rname ),

t2 AS (SELECT r.name rname,s.name sname,SUM(total_amt_usd) totalsum

FROM accounts a

JOIN orders o

ON o.account_id = a.id

JOIN sales_reps s

ON a.sales_rep_id = s.id

JOIN region r

ON r.id = s.region_id

GROUP BY r.name,s.name

ORDER BY 1,3 desc )

SELECT *

FROM t1

JOIN t2

ON t1.rname = t2.rname

WHERE t1.totalsum = t2.totalsum

For the region with the largest (sum) of sales total_amt_usd, how many total (count) orders were placed?

For the name of the account that purchased the most (in total over their lifetime as a customer) standard_qty paper, how many accounts still had more in total purchases?

For the customer that spent the most (in total over their lifetime as a customer) total_amt_usd, how many web_events did they have for each channel?

What is the lifetime average amount spent in terms of total_amt_usd for the top 10 total spending accounts?

What is the lifetime average amount spent in terms of total_amt_usd for only the companies that spent more than the average of all orders.