Oracle SQL Test 2

Oracle SQL Test 2 SQL code www.2cto.com cid Region (Region) Saler (salesman) Money (contract amount) 1 Beijing Yang Jian 100 2 Shanghai President 200 3 Hangzhou deputy regiment 500 4 Shanghai President 200 5 Shanghai Yang Jian 400 6 Beijing President 300 7 Beijing Yang Jian 200 8 Hangzhou deputy regiment 100 1. query the number of sales personnel in each region and sort SELECT t in reverse order. region, COUNT (t. saler) FROM (SELECT region, saler, COUNT (saler) FROM test group by region, saler order by region DESC) t group by t. region 2. query SELECT region, MIN (money) money FROM test group by region 3. query the contract id SELECT t whose contract amount is less than the average contract amount in the region. cid FROM test t, (SELECT region, AVG (money) money FROM test group by region) t2 WHERE t. region = t2.region AND t. money <t2.money or SELECT t. cid FROM test t WHERE t. money <(select avg (money) FROM test t2 WHERE t. region = t2.region group by t2.region)