2022级数据库原理能力测试2

于 2023-11-18 12:48:18 发布
阅读量180 收藏 1
点赞数
文章标签： 数据库 sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/H0r1zon/article/details/134472656
版权

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`)
)

表样例

image.png

输入样例

J70V95CJLPB~TA94OOS1MCI.png

 简单题目按要求写就能过，先看输入样例要什么，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表：

student.png

teachers表：

teacher.png

courses表：

course.png

choices表：

choices.png

输出样例:

image.png

写这一题之前先要了解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表：

course.png

输出样例:

请在这里给出输出样例。例如：

QQ图片20200413030233.png

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表：

course.png

输出样例:

请在这里给出输出样例。

QQ图片20200413031308.png

 这题我不知道测试样例有没有写，但是应该要在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表：

choices.png

输出样例:

请在这里给出输出样例。例如：

QQ图片20200413135525.png

简单题，注意不要重复即可（加上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表：

teacher.png

输出样例:

QQ图片20200413141450.png

 

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表：

student.png

teachers表：

teacher.png


courses表：

course.png


choices表：

choices.png

输出样例:

QQ图片20200420160527.png

 这题的话要联立表，我一般都是用的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表：

student.png

teachers表：

teacher.png


courses表：

course.png


choices表：

choices.png

输出样例:

请在这里给出输出样例。例如：

QQ图片20200420153803.png

这题还是很简单的，但是我一直报错，发现是少了逗号，所以要切记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表：

student.png

teachers：

teacher.png

courses：

course.png

choices：

choices.png

输出样例:

请在这里给出输出样例。例如：

9.png

 这题主要的思路还是看在几个表，发现是两张表，则将两张表都联立起来，连起来后就好，注意这里的条件表达式要用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表：

student.png

teachers表：

teacher.png

courses表：

course.png

choices表：

choices.png

输出样例:

10.png

简单写法是把两张表连起来再去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 '商品数量'
);

表样例

image.png

输出样例:

image.png

请编写 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 '商品数量'
);

表样例

image.png

输出样例:

image.png

 又是水题，但是这题要注意，虽然不注意好像也能过，他的订单号有反复的，所以要注意分组，然后计算总和使用sum而不是count

select order_num
from OrderItems
group by order_num
having sum(quantity) >= 100
order by order_num asc

10-12 牛客每次考试完，都会有一个成绩表(grade)，如下:

提示：请使用SELECT语句作答。

表结构:

image.png


第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分

请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序，如下:
 

image.png


(注意: 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表：

idjobscore
1C++11001
2C++10000
3C++9000
4Java12000
5Java13000
6JS12000
7JS11000
8JS9999
9Java12500

输出样例:

请在这里给出输出样例。例如：

idjobscore
1C++11001
2Java13000
6JS12000
7JS11000

 这题有一点点小小的复杂，首先我们得找出平均的成绩，所以得按工作分组，便于计算平均成绩，这样很容易陷入一个误区就是一开始就对工作分组，然后找平均分数，这样就会发现一个问题，就是没办法用分数与平均分数去比较，所以这里应该使用的是子查询，通过子查询找出平均分数，然后这里有个小坑，子查询中不要使用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' );

表样例

image.png

image.png

输出样例:

image.png

这题要使用关系代数中的右外关联，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：

iddevice_idgenderageuniversityprovince
12138male21北京大学BeiJing
23214male复旦大学Shanghai
36543female20北京大学BeiJing
42315female23浙江大学ZheJiang
55432male25山东大学Shandong

输出样例:

根据输入，你的查询应返回以下结果：

device_idgenderageuniversity
2138male21北京大学
6543female20北京大学
2315female23浙江大学
5432male25山东大学

又是水题一道

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表：

iddevice_idgenderageuniversitygpaactive_days_within_30question_cntanswer_cnt
12138male21北京大学3.47212
23214male复旦大学4.015525
36543female20北京大学3.212330
42315female23浙江大学3.6512
55432male25山东大学3.8201570
62131male28山东大学3.315713
74321male28复旦大学3.69652

输出样例:

根据示例，你的查询应返回以下结果：

universityavg_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

H0r1zon.
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值