针对Select Zoo题库平台,这次做的是select专题的练习SELECT within SELECT Tutorial和测试Nested SELECT Quiz。
一、SELECT within SELECT Tutorial:
SELECT within SELECT Tutorial
题目描述:
1、Bigger than Russia:
2、Richer than UK:
3、Neighbours of Argentina and Australia:
4、Between Canada and Poland:
5、Percentages of Germany:
6、Bigger than every country in Europe:
7、Largest in each continent:
8、First country of each continent:
9、Difficult Questions1:
10、Difficult Questions2:
上面是select子查询部分所涉及的练习题,题目代码均能正常运行,获得预期成功的笑脸结果。后面是对应的测试题代码,也是能正常运行的代码,可供大家参考。
二、Nested SELECT Quiz:
Nested SELECT Quiz
上面是练习题所涉及的表bbc
1. Select the code that shows the name, region and population of the smallest country in each region:
2.Select the code that shows the countries belonging to regions with all populations over 50000:
3.Select the code that shows the countries with a less than a third of the population of the countries around it:
4.Select the result that would be obtained from the following code:
5.Select the code that would show the countries with a greater GDP than any country in Africa (some countries may have NULL gdp values):
6.Select the code that shows the countries with population smaller than Russia but bigger than Denmark:
7.Select the result that would be obtained from the following code:
子查询相关知识点:
子查询就是在一个select查询语句中嵌套select查询语句。
一般可以在from子句中直接下定义视图的sql查询语句,外面会用一个括号括起来。这是普通的多行子查询,一般返回多行,也可以返回单行。
也可以结合使用in、any、all在where子句中直接书写子查询的sql查询语句,这种子查询是标量子查询。这种子查询是单一数值,可以结合比较运算符一起使用。
关联子查询时要使用两个表名,第二个表名的ID数据要和第一个表名的ID数据进行匹配。使用关联条件进行在每个组内比较。
sql里还有5个重要的聚合函数:
count(列名);求某列的行数,如count(*)
sum(列名);对某列数据求和。它只能对数据类型的列计算。
avg(列名);求某列数据的平均值。它只能对数值类型的列计算。
max(列名);求某列数据的最大值
min(列名);求某列数据的最小值