数据库中有两张表,users表中有三个字段(user_id,user_name,create_at),orders表中有三个字段(order_id,user_id,amount)
【难度】一般
(1)查询users表中存在而orders表中不存在的user_id
A.select users.user_id from users left joinorders on users.user_id=orders.user_id;
B.select users.user_id from users rightjoin orders on users.user_id=orders.user_id;
C.select users.user_id from users left joinorders on users.user_id=orders.user_id where orders.user_id is null;
D.select users.user_id from users rightjoin orders on users.user_id=orders.user_id where users.user_id is null;
【答案】C
【难度】一般
【分数】2.0
【解析】A以users为主表左连接,返回users表中所有的user_id。B以orders为主表右连接,返回orders表中所有的user_id。D以orders为主表右连接并筛选users表中user_id为空的记录,返回的是orders表中存在而users表中不存在的user_id。而C以users为主表左连接并筛选orders表中user_id为空的记录,返回即是users表中存在而orders表中不存在的user_id。
题目来源于cda数据分析认证考试模拟题库及考试资料(2021最新版)
https://edu.cda.cn/tag/datacode
(2)运行以下SQL查询语句,哪句不会报错
A.select user_id,avg(amount) from orderswhere avg(amount)>2000;
B.select user_id,avg(amount) from ordersgroup by user_id where avg(amount)>2000;
C.select user_id,avg(amount) from orderswhere avg(amount)>2000 group by user_id;
D.select user_id,avg(amount) from ordersgroup by user_id having avg(amount)>2000;
【答案】D
【难度】一般
【分数】2.0
【解析】查询语句的书写顺序中,where子句应该在group by子句之前,且where子句中不能用聚合函数。