10-1 查询年纪为2001的所有学生的名称按编号升序排列
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `students` (
`sid` bigint(20) NOT NULL,
`sname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
`grade` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`sid`)
)
表样例
输入样例
简单题目按要求写就能过,先看输入样例要什么,select就怎么写
select sname
from students
where grade = '2001'
10-2 查询学生的选课成绩合格的课程成绩,并把成绩换算为积点(60 分对应积点为 1,每增加 1 分,积点增加 0.1)。
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`salary` smallint(6) NULL DEFAULT NULL,
PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students` (
`sid` bigint(20) NOT NULL,
`sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`sid` bigint(20) NULL DEFAULT NULL,
`tid` bigint(20) NULL DEFAULT NULL,
`cid` bigint(20) NULL DEFAULT NULL,
`sorce` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`no`) USING BTREE,
INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);
表样例
请在这里给出上述表结构对应的表样例。例如
students
表:
teachers
表:
courses
表:
choices
表:
输出样例:
写这一题之前先要了解case
case的标准形式为:
case 列名
when 条件值1 then 选项1
when 条件值2 then 选项2
…………
else 默认值
end
现在这题目就很明了了
case when(sorce < 60)这个直接选出没有超过60分的人,绩点为0
else (sorce - 60)* 0.1 + 1 这个语句则用来计算绩点
注意一点是case也在select当中所以要加上,还要注意题干中选出合格的成绩
select sorce,
case
when (sorce < 60)
then 0
else (sorce- 60) * 0.1 + 1
end as '绩点'
from choices
where sorce >= 60
因为要及格,所以可简略一下不要判断未合格的人
select sorce,
case
when (sorce >= 60)
then (sorce- 60) * 0.1 + 1
end as '绩点'
from choices
where sorce >= 60
10-3 查询课时是 48 或 64 的课程的名称。
提示:请使用SELECT语句作答。
表结构:
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`cid`) USING BTREE
)
表样例
请在这里给出上述表结构对应的表样例。例如
courses
表:
输出样例:
请在这里给出输出样例。例如:
select cname
from courses
where hour = 64
or hour = 48
10-4 查询所有课程名称中含有“data”的课程编号
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`cid`) USING BTREE
);
表样例
请在这里给出上述表结构对应的表样例。例如
courses
表:
输出样例:
请在这里给出输出样例。
这题我不知道测试样例有没有写,但是应该要在data前后都加上%,即使视图中的表data前面是没有字符的,但是为了保证无错还是建议写成%data%的形式
%表示无限字符
select cid as '课程编号'
from courses
where cname like '%data%'
10-5 查询所有选课记录的课程号(不重复显示)。
提示:请使用SELECT语句作答。
表结构:
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`sid` bigint(20) NULL DEFAULT NULL,
`tid` bigint(20) NULL DEFAULT NULL,
`cid` bigint(20) NULL DEFAULT NULL,
`sorce` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`no`) USING BTREE,
INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
)
表样例
请在这里给出上述表结构对应的表样例。例如
choices
表:
输出样例:
请在这里给出输出样例。例如:
简单题,注意不要重复即可(加上distinct)
select distinct cid
from choices
10-6 统计所有老师的平均工资。
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`salary` smallint(6) NULL DEFAULT NULL,
PRIMARY KEY (`tid`) USING BTREE
)
表样例
请在这里给出上述表结构对应的表样例。例如
teachers
表:
输出样例:
select avg(salary) as 'avg(salary)'
from teachers
10-7 查询所有学生的编号,姓名和平均成绩,按总平均成绩降序排列。
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`salary` smallint(6) NULL DEFAULT NULL,
PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students` (
`sid` bigint(20) NOT NULL,
`sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`sid` bigint(20) NULL DEFAULT NULL,
`tid` bigint(20) NULL DEFAULT NULL,
`cid` bigint(20) NULL DEFAULT NULL,
`sorce` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`no`) USING BTREE,
INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);
表样例
请在这里给出上述表结构对应的表样例。例如
students
表:
teachers
表:
courses
表:
choices
表:
输出样例:
这题的话要联立表,我一般都是用的join on去联立两张表,也就是关系代数中的连接
on后面写连接条件(相同的列),然后再投影出自己需要的列即可,注意这题因为是要计算每个人的平均分,所以要用group by对学号进行分组,然后按照要求降序输出即可
select s.sid,sname,
avg(sorce) as 'AVG(sorce)'
from students s
join choices cs on cs.sid = s.sid
group by s.sid
order by AVG(sorce) desc
10-8 统计各个课程的选课人数和平均成绩
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`salary` smallint(6) NULL DEFAULT NULL,
PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students` (
`sid` bigint(20) NOT NULL,
`sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`sid` bigint(20) NULL DEFAULT NULL,
`tid` bigint(20) NULL DEFAULT NULL,
`cid` bigint(20) NULL DEFAULT NULL,
`sorce` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`no`) USING BTREE,
INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);
表样例
请在这里给出上述表结构对应的表样例。例如
students
表:
teachers
表:
courses
表:
choices
表:
输出样例:
请在这里给出输出样例。例如:
这题还是很简单的,但是我一直报错,发现是少了逗号,所以要切记select语句除了最后一个查询不需要逗号,其他的查询都需要逗号
然后看输出样例,再思考要从什么表中选择数据,发现在choices一张表中就可以解决,首先肯定要分组,不然无法算平均分数,分组后就好解决了,只需要选出cid和计算sid的个数即可
select cid,
count(sid) as 'count(sid)',
avg(sorce) as 'AVG(sorce)'
from choices
group by cid
10-9 查询至少选修了三门课程的学生编号。
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`salary` smallint(6) NULL DEFAULT NULL,
PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students` (
`sid` bigint(20) NOT NULL,
`sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`sid` bigint(20) NULL DEFAULT NULL,
`tid` bigint(20) NULL DEFAULT NULL,
`cid` bigint(20) NULL DEFAULT NULL,
`sorce` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`no`) USING BTREE,
INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);
表样例
请在这里给出上述表结构对应的表样例。例如
students表
:
teachers
:
courses
:
choices
:
输出样例:
请在这里给出输出样例。例如:
这题主要的思路还是看在几个表,发现是两张表,则将两张表都联立起来,连起来后就好,注意这里的条件表达式要用having去解决,因为要对sid分组(使用了group by)
select s.sid
from students s
join choices cs on cs.sid = s.sid
group by cs.sid
having count(cid) >= 3
这里写一下第二种方法,因为在数据大的时候使用join是非常慢的,所以第二种方法使用了子查询去写,这也是老师说的数据库代码优化
select s.sid
from students s
where s.sid in (
select cs.sid
from choices cs
group by cs.sid
having count(cid) >= 3
)
使用子查询的思路是先找cs表中的sid,计算出选课数超过三门的同学,然后再从s表中找到对应的sid,如果有就输出,没有继续找。
10-10 查询编号 800009026 的学生所选的全部课程的课程名和成绩
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`salary` smallint(6) NULL DEFAULT NULL,
PRIMARY KEY (`tid`) USING BTREE
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`cid`) USING BTREE
);
CREATE TABLE `students` (
`sid` bigint(20) NOT NULL,
`sname` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
`grade` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`sid`) USING BTREE
) ;
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
`sid` bigint(20) NULL DEFAULT NULL,
`tid` bigint(20) NULL DEFAULT NULL,
`cid` bigint(20) NULL DEFAULT NULL,
`sorce` int(11) NULL DEFAULT NULL,
PRIMARY KEY (`no`) USING BTREE,
INDEX `FK_CHOICES_STUDENTS_idx`(`sid`) USING BTREE,
INDEX `FK_CHOICES_TEACHERS_idx`(`tid`) USING BTREE,
INDEX `FK_CHOICES_COURESE_idx`(`cid`) USING BTREE,
CONSTRAINT `FK_CHOICES_COURESE` FOREIGN KEY (`cid`) REFERENCES `courses` (`cid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_STUDENTS` FOREIGN KEY (`sid`) REFERENCES `students` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION,
CONSTRAINT `FK_CHOICES_TEACHERS` FOREIGN KEY (`tid`) REFERENCES `teachers` (`tid`) ON DELETE NO ACTION ON UPDATE NO ACTION
);
表样例
请在这里给出上述表结构对应的表样例。例如
students
表:
teachers
表:
courses
表:
choices
表:
输出样例:
简单写法是把两张表连起来再去select自己需要的列,别忘了想清楚自己的条件表达式
select cr.cname,cs.sorce
from courses cr
join choices cs on cs.cid = cr.cid
where cs.sid = ' 800009026'
10-11 请编写 SQL 语句,返回订单数量总和不小于100的所有订单号,最后结果按照订单号升序排序。
表结构:
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
quantity INT(255) NOT NULL COMMENT '商品数量'
);
表样例
输出样例:
请编写 SQL 语句,返回订单数量总和不小于100的所有订单号,最后结果按照订单号升序排序。
表结构:
DROP TABLE IF EXISTS `OrderItems`;
CREATE TABLE IF NOT EXISTS `OrderItems`(
order_num VARCHAR(255) NOT NULL COMMENT '商品订单号',
quantity INT(255) NOT NULL COMMENT '商品数量'
);
表样例
输出样例:
又是水题,但是这题要注意,虽然不注意好像也能过,他的订单号有反复的,所以要注意分组,然后计算总和使用sum而不是count
select order_num
from OrderItems
group by order_num
having sum(quantity) >= 100
order by order_num asc
10-12 牛客每次考试完,都会有一个成绩表(grade),如下:
提示:请使用SELECT语句作答。
表结构:
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分
请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性,并且以id的升序排序,如下:
(注意: sqlite 1/2得到的不是0.5,得到的是0,只有1*1.0/2才会得到0.5,sqlite四舍五入的函数为round)
请在这里写定义表结构的SQL语句。例如:
drop table if exists grade;
CREATE TABLE grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));
表样例
请在这里给出上述表结构对应的表样例。例如
grade
表:
id | job | score |
---|---|---|
1 | C++ | 11001 |
2 | C++ | 10000 |
3 | C++ | 9000 |
4 | Java | 12000 |
5 | Java | 13000 |
6 | JS | 12000 |
7 | JS | 11000 |
8 | JS | 9999 |
9 | Java | 12500 |
输出样例:
请在这里给出输出样例。例如:
id | job | score |
---|---|---|
1 | C++ | 11001 |
2 | Java | 13000 |
6 | JS | 12000 |
7 | JS | 11000 |
这题有一点点小小的复杂,首先我们得找出平均的成绩,所以得按工作分组,便于计算平均成绩,这样很容易陷入一个误区就是一开始就对工作分组,然后找平均分数,这样就会发现一个问题,就是没办法用分数与平均分数去比较,所以这里应该使用的是子查询,通过子查询找出平均分数,然后这里有个小坑,子查询中不要使用group by job,因为是自联,所以两张表应该有不同的名字,而且为了保证子查询和父查询是一致的,所以where后面应该是子.job = 父.job
select g1.id,g1.job,g1.score
from grade g1
where g1.score > (
select avg(g2.score)
from grade g2
where g2.job = g1.job
)
10-12 检索每个顾客的名称和所有的订单号(二)
【问题】检索每个顾客的名称(Customers表中的 cust_name)和所有的订单号(Orders 表中的 order_num),列出所有的顾客,即使他们没有下过订单。最后根据顾客姓名cust_name升序返回。
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE IF NOT EXISTS `Customers`( cust_id VARCHAR(255) NOT NULL COMMENT '客户id', cust_name VARCHAR(255) NOT NULL COMMENT '客户姓名' );
CREATE TABLE IF NOT EXISTS `Orders`( order_num VARCHAR(255) NOT NULL COMMENT '商品订单号', cust_id VARCHAR(255) NOT NULL COMMENT '顾客id' );
表样例
输出样例:
这题要使用关系代数中的右外关联,right join,右关联是右表中的东西全部投影出来,而左表中只显示符合搜索条件的记录,左表记录不足的地方都使用null
select cs.cust_name,os.order_num
from Orders os
right join Customers cs on cs.cust_id = os.cust_id
order by cs.cust_name asc
10-14 现在运营想要查看除复旦大学以外的所有用户明细,请你取出相应数据
表结构:
drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`province` varchar(32) NOT NULL);
表样例
用户信息表user_profile
:
id | device_id | gender | age | university | province |
---|---|---|---|---|---|
1 | 2138 | male | 21 | 北京大学 | BeiJing |
2 | 3214 | male | 复旦大学 | Shanghai | |
3 | 6543 | female | 20 | 北京大学 | BeiJing |
4 | 2315 | female | 23 | 浙江大学 | ZheJiang |
5 | 5432 | male | 25 | 山东大学 | Shandong |
输出样例:
根据输入,你的查询应返回以下结果:
device_id | gender | age | university |
---|---|---|---|
2138 | male | 21 | 北京大学 |
6543 | female | 20 | 北京大学 |
2315 | female | 23 | 浙江大学 |
5432 | male | 25 | 山东大学 |
又是水题一道
select device_id,gender,age,university
from user_profile
where university not like '复旦大学'
现在运营想要查看不同大学的用户平均发帖情况,并期望结果按照平均发帖情况进行升序排列,请你取出相应数据。
表结构:
drop table if exists user_profile;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int
);
表样例
user_profile
表:
id | device_id | gender | age | university | gpa | active_days_within_30 | question_cnt | answer_cnt |
---|---|---|---|---|---|---|---|---|
1 | 2138 | male | 21 | 北京大学 | 3.4 | 7 | 2 | 12 |
2 | 3214 | male | 复旦大学 | 4.0 | 15 | 5 | 25 | |
3 | 6543 | female | 20 | 北京大学 | 3.2 | 12 | 3 | 30 |
4 | 2315 | female | 23 | 浙江大学 | 3.6 | 5 | 1 | 2 |
5 | 5432 | male | 25 | 山东大学 | 3.8 | 20 | 15 | 70 |
6 | 2131 | male | 28 | 山东大学 | 3.3 | 15 | 7 | 13 |
7 | 4321 | male | 28 | 复旦大学 | 3.6 | 9 | 6 | 52 |
输出样例:
根据示例,你的查询应返回以下结果:
university | avg_question_cnt |
---|---|
浙江大学 | 1.0000 |
北京大学 | 2.5000 |
复旦大学 | 5.5000 |
山东大学 | 11.0000 |
水题水题,按要求写就可以
select university,
avg(question_cnt) as 'avg_question_cnt'
from user_profile
group by university
order by avg_question_cnt