BDA初级分析——SQL语句应用基础练习题

 1、请检查OLYMPIC表中是否存在重复国家?

 

SELECT Team,COUNT(*) AS 重复次数
FROM Olympic
GROUP BY Team
HAVING COUNT(*) > 1;

SELECT COUNT(Team),COUNT(DISTINCT(Team))
FROM Olympic;

 2、将OLYMPIC表中Armenia(ARM)的奖牌总数更新为14

 

UPDATE Olympic
SET total_medals = 14
WHERE Team = ' Armenia (ARM)';

SELECT Team,total_medals AS 奖牌总数
FROM Olympic 
WHERE Team=' Armenia (ARM)';

 3、计算OLYMPIC表中所有国家的奖牌总数

 

SELECT SUM(total_medals) AS 金牌总数
FROM Olympic

4、查找OLYMPIC表中中国夏季奥运会获得的金牌总数

 

SELECT Team,SUM(s_gold) AS 金牌总数
FROM Olympic
WHERE Team=' China (CHN) [CHN]'; 

5、将金牌总数超过100的国家按照降序排列

 

SELECT Team,total_gold
FROM Olympic
WHERE total_gold > 100
ORDER BY CAST(total_gold AS SIGNED) DESC;