版权声明:本文为博主原创文章,未经博主允许不得转载。
文章目录
一、单选题
1.1 高级查询
1.1.1 聚合函数
1.关于聚合函数,下列描述不正确的是( D)
- A .AVG(列名):用于获得非空值的平均值
- B .SUM(列名):用于求和,只能用于数字类型
- C .MAX(列名):用于获得非空值的最大值
- D .COUNT(列名):用于获得包含空值的记录数 【COUNT(*)】
31.已有 student 表如下图所示,统计已缴纳宿舍费的人数,下列选项正确的是( B )
- A .select SUM(dorm_money) from student;
- B .select COUNT(dorm_money) from student;
- C .select COUNT(tuition) from student;
- D .select SUM(tuition) from student;
2.已有student表如下图所示,查询入学超过2年的学生缴纳费用最高为多少,下列选项正确的是( D )
A.select MAX(tuition+IFNULL(dorm_money,0))from student WHERE
DATEDIFF(NOW(),enrollment_date)>2;
B.select MIN(tuition+IFNULL(dorm_money,0))from student WHERE
DATEDIFF(NOW(),enrollment_date)/365>2;
C.select MAX(tuition+dorm_money)from student WHERE
DATEDIFF(NOW(),enrollment_date)/365>2;
D.select MAX(tuition+IFNULL(dorm_money,0))from student WHERE
DATEDIFF(NOW(),enrollment_date)/365>2;
1.1.2 分组查询
3.关于分组,下列描述错误的是(D )
- A .数据库支持多列分组条件,执行的时候逐级分组
- B .group by 子句的作用是通过一定的规则将表中数据划分为若干个小的区域
- C .group by 子句划分后,可以针对每个小区域进行数据汇总处理
- D .如果含有group by 子句时,对select子句中的内容无任何限制 【查询语句中如果含有GROUP BY子句,那么 SELECT 子句中的内容就必须要遵守规定:SELECT 子句中可以包含聚合函数,或者GROUP BY子句的分组列,其余内容均不可以出现在SELECT子句中】
4.已有student表,如下图所示,查询各个系,每种职位的人员数量和最高奖学金金额,并按系别排序,下列选项实现该功能的是( B )
A. SELECT department,job,COUNT(*),min(scholarship) from student GROUP
BY department,job ORDER BY department;
B. SELECT department,job,COUNT(*),max(scholarship) from student GROUP
BY department,job ORDER BY department;
C. SELECT department,job,COUNT(*),avg(scholarship) from student GROUP BY
department,job ORDER BY department;
D. SELECT department,job,COUNT(*),max(scholarship) from student GROUP
BY department ORDER BY department;
1.1.3 HAVING 子句
5.已有 student 表,如下图所示,查询各个系2017年以后入学的且超过2个人(包含2个人)的系别,下列选项实现该功能的是( A )
A. select department from student where enrollment_date >'2017-1-1' group by
department HAVING COUNT(*)>=2;
B. select department from student group by department HAVING COUNT(*)>=2;
C. select department from student where enrollment_date >'2017-1-1' group by
department HAVING COUNT(*)>2;
D. select department from students where enrollment_date >'2017-1-1' group by
department HAVING COUNT(*)>=2;
1.1.4 表连接查询
6.关于内连接的格式,下列格式错误的是( A )
A. SELECT ...FROM 表1 ,表2 ON 连接条件;
B. SELECT ...FROM 表1 JOIN 表2 ON 连接条件;
C. SELECT ...FROM 表1 ,表2 WHERE 连接条件;
D. SELECT ...FROM 表1 JOIN 表2 WHERE 连接条件;
7.已有 student 表,如下图所示,查询奖学金超过学校平均奖学金的学生姓名,电话,奖学金金额,下列选项实现该功能的是( B )
A. select stu1.name,stu1.tel,stu1.scholarship from student stu1 join (select
avg(scholarship) avg from student stu2) on stu1.scholarship>stu2.avg
B. select stu1.name,stu1.tel,stu1.scholarship from student stu1 join (select
avg(scholarship)