从这篇题解开始一般的简单题目都不会开始写解题思路,看代码即可
10-1 现在运营想要针对20岁及以上且23岁及以下的用户开展分析,请你取出满足条件的用户明细数据。
表结构:
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 |
---|---|---|
2138 | male | 21 |
6543 | female | 20 |
2315 | female | 23 |
水题
select device_id,gender,age
from user_profile
where age between 20 and 23
10-2 现在运营想要找到男性且GPA在3.5以上的用户进行调研,请你取出相关数据。
表结构:
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,
`gpa` float);
表样例
user_profile
表:
id | device_id | gender | age | university | gpa |
---|---|---|---|---|---|
1 | 2138 | male | 21 | 北京大学 | 3.4 |
2 | 3214 | male | 复旦大学 | 4.0 | |
3 | 6543 | female | 20 | 北京大学 | 3.2 |
4 | 2315 | female | 23 | 浙江大学 | 3.6 |
5 | 5432 | male | 25 | 山东大学 | 3.8 |
输出样例:
根据输入,你的查询应返回以下结果:
device_id | gender | age | university | gpa |
---|---|---|---|---|
3214 | male | None | 复旦大学 | 4.0 |
5432 | male | 25 | 山东大学 | 3.8 |
select device_id,gender,age,university,gpa
from user_profile
where gender = 'male' and gpa > 3.5
10-3 现在运营想要找到gpa在3.5以上的山东大学用户 或 gpa在3.8以上的复旦大学同学进行用户调研,请你取出相应数据
表结构:
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,
`gpa` float);
表样例
user_profile
表:
id | device_id | gender | age | university | gpa |
---|---|---|---|---|---|
1 | 2138 | male | 21 | 北京大学 | 3.4 |
2 | 3214 | male | 复旦大学 | 4.0 | |
3 | 6543 | female | 20 | 北京大学 | 3.2 |
4 | 2315 | female | 23 | 浙江大学 | 3.6 |
5 | 5432 | male | 25 | 山东大学 | 3.8 |
输出样例:
根据输入,你的查询应返回以下结果:
device_id | gender | age | university | gpa |
---|---|---|---|---|
5432 | male | 25 | 山东大学 | 3.8 |
3214 | male | None | 复旦大学 | 4.0 |
select device_id,gender,age,university,gpa
from user_profile
where (gpa > 3.5 and university = '山东大学')
or(gpa > 3.8 and university = '复旦大学')
10-4 现在运营想查看所有大学中带有北京的用户的信息,请你取出相应数据。
表结构:
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);
表样例
user_profile
表:
id | device_id | gender | age | university | gpa |
---|---|---|---|---|---|
1 | 2138 | male | 21 | 北京大学 | 3.4 |
2 | 3214 | male | 复旦大学 | 4.0 | |
3 | 6543 | female | 20 | 北京大学 | 3.2 |
4 | 2315 | female | 23 | 浙江大学 | 3.6 |
5 | 5432 | male | 25 | 山东大学 | 3.8 |
6 | 2131 | male | 28 | 北京师范大学 | 3.3 |
输出样例:
根据示例,你的查询应返回如下结果:
device_id | age | university |
---|---|---|
2138 | 21 | 北京大学 |
6543 | 20 | 北京大学 |
2131 | 28 | 北京师范大学 |
一般要查名称,使用%xx%的形式就能模糊匹配
select device_id,age,university
from user_profile
where university like '%北京%'
10-5 运营想要知道复旦大学学生gpa最高值是多少,请你取出相应数据
表结构:
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);
表样例
user_profile
表:
id | device_id | gender | age | university | gpa |
---|---|---|---|---|---|
1 | 2138 | male | 21 | 北京大学 | 3.4 |
2 | 3214 | male | 复旦大学 | 4.0 | |
3 | 6543 | female | 20 | 北京大学 | 3.2 |
4 | 2315 | female | 23 | 浙江大学 | 3.6 |
5 | 5432 | male | 25 | 山东大学 | 3.8 |
6 | 2131 | male | 28 | 北京师范大学 | 3.3 |
输出样例:
根据输入,你的查询应返回以下结果:
gpa |
---|
4.0 |
使用max()函数就能解决问题
select max(gpa) as gpa
from user_profile
where university = '复旦大学'
10-6 现在运营想要对每个学校不同性别的用户活跃情况和发帖数量进行分析,请分别计算出每个学校每种性别的用户数、30天内平均活跃天数和平均发帖数量。
表结构:
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` float,
`question_cnt` float,
`answer_cnt` float
);
表样例
user_profile
表
30天内活跃天数字段(active_days_within_30)
发帖数量字段(question_cnt)
回答数量字段(answer_cnt)
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 |
输出样例:
你的查询返回结果需要对性别和学校分组,示例如下:
gender | university | user_num | avg_active_day | avg_question_cnt |
---|---|---|---|---|
male | 北京大学 | 1 | 7.0 | 2.0 |
male | 复旦大学 | 2 | 12.0 | 5.5 |
female | 北京大学 | 1 | 12.0 | 3.0 |
female | 浙江大学 | 1 | 5.0 | 1.0 |
male | 山东大学 | 2 | 17.5 | 11.0 |
这个题目只是看上去很长,其实捋清楚题目意思然后按要求输出就好
select gender,university,
count(device_id) as user_num,
avg(active_days_within_30) as avg_active_day,
avg(question_cnt) as avg_question_cnt
from user_profile
group by university,gender
10-7 现在运营想查看每个学校用户的平均发贴和回帖情况,寻找低活跃度学校进行重点运营,请取出平均发贴数低于5的学校或平均回帖数小于20的学校。
表结构:
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` float,
`answer_cnt` float
);
表样例
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 | avg_answer_cnt |
---|---|---|
北京大学 | 2.5 | 21.0 |
浙江大学 | 1.0 | 2.0 |
简单题,按要求输出:
select university,
avg(question_cnt) as avg_question_cnt,
avg(answer_cnt) as avg_answer_cnt
from user_profile
group by university
having avg_answer_cnt < 20
or avg_question_cnt < 5
10-8 现在运营想要查看不同大学的用户平均发帖情况,并期望结果按照平均发帖情况进行升序排列,请你取出相应数据。
表结构:
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 asc
10-9 现在运营想要查看所有来自浙江大学的用户题目回答明细情况,请你取出相应数据
表结构:
drop table if exists `user_profile`;
drop table if exists `question_practice_detail`;
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
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
表样例
question_practice_detail
表:
id | device_id | question_id | result |
---|---|---|---|
1 | 2138 | 111 | wrong |
2 | 3214 | 112 | wrong |
3 | 3214 | 113 | wrong |
4 | 6543 | 111 | right |
5 | 2315 | 115 | right |
6 | 2315 | 116 | right |
7 | 2315 | 117 | wrong |
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 |
输出样例:
根据示例,你的查询应返回以下结果:
device_id | question_id | result |
---|---|---|
2315 | 115 | right |
2315 | 116 | right |
2315 | 117 | wrong |
select qpd.device_id,question_id,result
from question_practice_detail qpd
join user_profile up on up.device_id = qpd.device_id
where university = '浙江大学'
10-10 运营想要了解每个学校答过题的用户平均答题数量情况,请你取出数据。
表结构:
drop table if exists `user_profile`;
drop table if exists `question_practice_detail`;
CREATE TABLE `user_profile` (
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int
);
CREATE TABLE `question_practice_detail` (
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL
);
表样例
用户信息表 user_profile,其中device_id指终端编号(认为每个用户有唯一的一个终端),gender指性别,age指年龄,university指用户所在的学校,gpa是该用户平均学分绩点,active_days_within_30是30天内的活跃天数。
答题情况明细表 question_practice_detail,其中question_id是题目编号,result是答题结果。
输出样例:
说明:某学校用户平均答题数量计算方式为该学校用户答题总次数除以不同用户的个数,根据示例,你的查询应返回以下结果(结果保留4位小数):
university | avg_answer_cnt |
---|---|
北京大学 | 1.0000 |
复旦大学 | 2.0000 |
山东大学 | 2.0000 |
浙江大学 | 3.0000 |
select university,
count(question_id) / count(distinct up.device_id) as avg_answer_cnt
from user_profile up
join question_practice_detail qpd on qpd.device_id = up.device_id
group by university
order by avg_answer_cnt asc
10-11 求每门课程的学生人数
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `sc` (
`SId` varchar(10) DEFAULT NULL,
`CId` varchar(10) DEFAULT NULL,
`score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
);
输出样例:
请在这里给出输出样例。例如:
CId | 学生人数 |
---|---|
51 | 1 |
52 | 1 |
select CId,
count(*) as '学生人数'
from sc
group by CId
10-12 查询各个课程及相应的选修人数
提示:请使用SELECT语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `sc` (
`SId` varchar(10) DEFAULT NULL,
`CId` varchar(10) DEFAULT NULL,
`score` decimal(18,1) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
输出样例:
请在这里给出输出样例。例如:
CId | cot |
---|---|
01 | 6 |
select CId,
count(*) as cot
from sc
group by CId
10-13 本题目要求编写SQL语句,
查询选修了张老师所讲授的所有课程的学生。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `stu` (
`sno` char(4) NOT NULL,
`sname` char(8) NOT NULL,
`sex` tinyint(1) DEFAULT NULL,
`mno` char(2) DEFAULT NULL,
`birdate` datetime DEFAULT NULL,
`memo` text,
PRIMARY KEY (`sno`)
);
CREATE TABLE `cou` (
`cno` char(4) NOT NULL,
`cname` varchar(30) NOT NULL,
`credit` smallint(6) DEFAULT NULL,
`ptime` char(5) DEFAULT NULL,
`teacher` char(10) DEFAULT NULL,
PRIMARY KEY (`cno`)
);
CREATE TABLE `sc` (
`sno` char(4) NOT NULL,
`cno` char(4) NOT NULL,
`grade` decimal(6,1) DEFAULT NULL,
PRIMARY KEY (`sno`,`cno`),
CONSTRAINT `fk_sc_cno` FOREIGN KEY (`cno`) REFERENCES `cou` (`CNO`),
CONSTRAINT `fk_sc_sno` FOREIGN KEY (`sno`) REFERENCES `stu` (`sno`)
);
表样例
请在这里给出上述表结构对应的表样例。例如
stu
表:
cou
表:
sc
表:
输出样例:
请在这里给出输出样例。例如:
这题不写题解纯粹是我暂时还没想出来,用的网上的答案,到时候再修改这篇文章
select sname
from stu
where sno in (
select sno -- ,count(cno) -- 这里用了count(cno)的解释为了更直观的看出group by后的结果
from sc
where cno in (
select cno
from cou
where teacher='张老师'
)
group by sno
-- 基本就是分组统计了选了多少张老师的课
having count(cno)= -- 用having去过滤筛选(如果不清楚having的用法可以去查查)
(select count(cno)
from cou
where teacher='张老师')
)
10-14 本题目要求编写SQL语句,
检索出``teachers、
teaching、
sc```表中“谭浩强”教师任课的课程号,选修其课程的学生的学号和成绩。
提示:请使用join连接作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE teachers (
tno char(3) ,
tname char(8),
ps char(10),
tbirthday date ,
tdept char(16) ,
tsex char(2),
PRIMARY KEY (tno)
) ;
CREATE TABLE teaching (
sid int ,
cterm int,
class char(10) DEFAULT NULL,
cno char(7) NOT NULL,
tno char(3) DEFAULT NULL,
period int DEFAULT NULL,
PRIMARY KEY (sid)
) ;
CREATE TABLE sc (
sno char(7) ,
cno char(7) ,
score decimal(4,1),
point decimal(2,1),
PRIMARY KEY (sno,cno)
) ;
表样例
请在这里给出上述表结构对应的表样例。例如
teachers
表:
tno | tname | ps | tbirthday | tdept | tsex |
---|---|---|---|---|---|
001 | 谭浩强 | 教授 | 1958-01-01 | 计科 | 男 |
002 | 王珊 | 教授 | 1962-02-13 | 计科 | 女 |
003 | 萨师煊 | 教授 | 1953-05-01 | 计科 | 男 |
004 | 严蔚敏 | 副教授 | 1968-07-02 | 软工 | 女 |
005 | 李琳 | 讲师 | 1988-11-15 | 软工 | 女 |
006 | 韩万江 | 助教 | 1992-10-17 | 信管 | 男 |
teaching
表:
sid | cterm | class | cno | tno | period |
---|---|---|---|---|---|
1 | 1 | 17物流1 | 0000011 | 001 | 36 |
2 | 1 | 17物流1 | 0000034 | 002 | 72 |
3 | 3 | 17物流1 | 0000052 | 003 | 60 |
4 | 1 | 17物流1 | 0000027 | 004 | 108 |
5 | 2 | 17物流1 | 0000039 | 005 | 36 |
6 | 6 | 17物流1 | 0000005 | 006 | 72 |
sc
表:
sno | cno | score | point |
---|---|---|---|
1311104 | 0000011 | 73.0 | 0.0 |
1311104 | 0000027 | 80.0 | 1.0 |
1311105 | 0000027 | 84.0 | 1.0 |
1711101 | 0000052 | 71.0 | 2.0 |
输出样例:
请在这里给出输出样例。例如:
cno | sno | score |
---|---|---|
001 | 0000011 | 73.0 |
select tc.cno,sc.sno,sc.score
from teaching tc
join sc on sc.cno = tc.cno
join teachers ts on ts.tno = tc.tno
where ts.tname = '谭浩强'
10-15 本题目要求编写SQL语句,
检索出students
表中出生日期大于所有女同学出生日期的男同学的姓名及系别。
提示:请使用嵌套查询语句作答。
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE students (
sno char(7) ,
sname char(8) NOT NULL,
class char(10),
ssex char(2),
bday date ,
bplace char(10) ,
IDNum char(18) ,
sdept char(16),
phone char(11),
PRIMARY KEY (sno)
) ;
表样例
请在这里给出上述表结构对应的表样例。例如
students
表:
sno | sname | class | ssex | bday | bplace | IDNum | sdept | phone |
---|---|---|---|---|---|---|---|---|
1311104 | 李嘉欣 | 13英语1 | 女 | 1995-05-28 | 山西太原 | 330204199405281056 | 人文学院 | 15900002211 |
1311105 | 苏有明 | 13英语1 | 男 | 1994-04-16 | 内蒙古包头 | 330204199504162036 | 人文学院 | 15900002222 |
1711101 | 赵薇 | 17物流1 | 女 | 1999-02-11 | 安徽合肥 | 330203199902110925 | 经管学院 | 15900001177 |
1711102 | 陆毅 | 17物流1 | 女 | 1999-02-17 | 上海 | 330203199902170017 | 经管学院 | 15900001188 |
输出样例:
请在这里给出输出样例。例如:
sname | sdept |
---|---|
苏有明 | 人文学院 |
这一题有个简单方法是其实只需要找出女生生日的最大值即可
select sname,sdept
from students
where bday > (
select max(bday)
from students
where ssex = '女'
)
and ssex = '男'
10-16 找出选修课程成绩最差的选课记录
表结构:
请在这里写定义表结构的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`)
);
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
`salary` smallint(6) DEFAULT NULL,
PRIMARY KEY (`tid`)
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`cid`)
);
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 NOT NULL,
`sid` bigint(20) DEFAULT NULL,
`tid` bigint(20) DEFAULT NULL,
`cid` bigint(20) DEFAULT NULL,
`sorce` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `FK_CHOICES_STUDENTS_idx` (`sid`),
KEY `FK_CHOICES_TEACHERS_idx` (`tid`),
KEY `FK_CHOICES_COURESE_idx` (`cid`),
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
表:
输出样例:
这一题使用子查询找出最小的课程成绩即可
select no,sid,tid,cid,sorce
from choices
where sorce = (
select min(sorce)
from choices
)
10-17 查询了选修所有课程的学生姓名
表结构:
请在这里写定义表结构的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`)
);
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
`salary` smallint(6) DEFAULT NULL,
PRIMARY KEY (`tid`)
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`cid`)
);
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 NOT NULL,
`sid` bigint(20) DEFAULT NULL,
`tid` bigint(20) DEFAULT NULL,
`cid` bigint(20) DEFAULT NULL,
`sorce` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `FK_CHOICES_STUDENTS_idx` (`sid`),
KEY `FK_CHOICES_TEACHERS_idx` (`tid`),
KEY `FK_CHOICES_COURESE_idx` (`cid`),
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
表:
choices
表:
输出样例:
请在这里给出输出样例。例如:
这一题也比较好写,就是写子查询麻烦了一点,最里面的内查询查询所有选课的数量,然后第二层查询用来判断该学生选的课程数,最外面的查询用来查找学生学号。
select sname
from students
where sid = (
select sid
from choices
group by sid
having count(cid) = (
select count(distinct cid)
from choices
)
)
10-18 找出和课程uml或课程c++的课时一样课程名称
表结构:
请在这里写定义表结构的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`)
);
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
`salary` smallint(6) DEFAULT NULL,
PRIMARY KEY (`tid`)
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`cid`)
);
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 NOT NULL,
`sid` bigint(20) DEFAULT NULL,
`tid` bigint(20) DEFAULT NULL,
`cid` bigint(20) DEFAULT NULL,
`sorce` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `FK_CHOICES_STUDENTS_idx` (`sid`),
KEY `FK_CHOICES_TEACHERS_idx` (`tid`),
KEY `FK_CHOICES_COURESE_idx` (`cid`),
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
);
表样例
请在这里给出上述表结构对应的表样例。例如
courses
表:
输出样例:
这一题的话也是用内查询查找选修c++或者uml的课时,但是要注意的是因为返回的不止一个值,所以要用同表不同的缩写名表示是同一节课程
select cid,cname,hour
from courses c1
where hour = (
select hour
from courses c2
where (cname = 'c++'
or cname = 'uml')
and c1.cid = c2.cid
)
10-19 查询所有选修编号1001的课程的学生的姓名
表结构:
请在这里写定义表结构的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`)
);
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
`salary` smallint(6) DEFAULT NULL,
PRIMARY KEY (`tid`)
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`cid`)
);
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 NOT NULL,
`sid` bigint(20) DEFAULT NULL,
`tid` bigint(20) DEFAULT NULL,
`cid` bigint(20) DEFAULT NULL,
`sorce` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `FK_CHOICES_STUDENTS_idx` (`sid`),
KEY `FK_CHOICES_TEACHERS_idx` (`tid`),
KEY `FK_CHOICES_COURESE_idx` (`cid`),
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
表:
choices
表:
输出样例:
select sname
from students s
join choices cs on cs.sid = s.sid
where cs.cid = '1001'
10-20 实现集合减运算,查询选修课程c++而没有选修课程java 的学生的编号
表结构:
请在这里写定义表结构的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`)
);
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
`salary` smallint(6) DEFAULT NULL,
PRIMARY KEY (`tid`)
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`cid`)
);
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 NOT NULL,
`sid` bigint(20) DEFAULT NULL,
`tid` bigint(20) DEFAULT NULL,
`cid` bigint(20) DEFAULT NULL,
`sorce` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `FK_CHOICES_STUDENTS_idx` (`sid`),
KEY `FK_CHOICES_TEACHERS_idx` (`tid`),
KEY `FK_CHOICES_COURESE_idx` (`cid`),
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
表:
courses
表:
输出样例:
请在这里给出输出样例。例如:
这题说使用集合减运算,所以第一反应想到的应该是intersect,但是不知道出于什么原因判题机无法判断该关键字,于是使用了内查询查询非选择Java课程的学生再进行比较
select sid
from choices cs
join courses cr on cr.cid = cs.cid
where cr.cname = 'c++'
and cs.sid not in(
select sid
from choices cs
join courses cr on cr.cid = cs.cid
where cname = 'java'
)
10-21 利用集合交运算,查询既选修课程c++又选修课程java的学生的编号
表结构:
请在这里写定义表结构的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`)
);
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
`salary` smallint(6) DEFAULT NULL,
PRIMARY KEY (`tid`)
);
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`cid`)
);
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 NOT NULL,
`sid` bigint(20) DEFAULT NULL,
`tid` bigint(20) DEFAULT NULL,
`cid` bigint(20) DEFAULT NULL,
`sorce` int(11) DEFAULT NULL,
PRIMARY KEY (`no`),
KEY `FK_CHOICES_STUDENTS_idx` (`sid`),
KEY `FK_CHOICES_TEACHERS_idx` (`tid`),
KEY `FK_CHOICES_COURESE_idx` (`cid`),
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
);
表样例
请在这里给出上述表结构对应的表样例。例如
courses
表:
choices
表:
输出样例:
请在这里给出输出样例。例如:
select sid
from choices cs
join courses cr on cr.cid = cs.cid
where cs.sid in (
select sid
from choices cs
join courses cr on cr.cid = cs.cid
where cr.cname = 'java'
)and cr.cname = 'c++'
group by cs.sid
10-22 查询选修成绩合格的课程超过2门的学生编号。
表结构:
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 NOT NULL,
`sid` bigint(20) DEFAULT NULL,
`tid` bigint(20) DEFAULT NULL,
`cid` bigint(20) DEFAULT NULL,
`sorce` int(11) DEFAULT NULL,
PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表样例
choices
表:(此处只显示部分)
输出样例:
这题很简单,只是count里面的条件写好一点就行
select sid
from choices
group by sid
having count(sorce >= 60) > 2
10-23 查询没有选修java学生的姓名
提示:请使用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
);create table Student (
id int,
name varchar(32)
);
表样例
请在这里给出上述表结构对应的表样例。例如
输出样例:
请在这里给出输出样例。例如:
select sname
from students
where sid not in
(select sid
from choices
where cid in
(select cid
from courses
where cname='java'))
10-24 找出课时最少的课程的详细信息。
表结构:
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表样例
courses
表:
输出样例:
select cid,cname,hour
from courses
where hour = (
select min(hour)
from courses
)
10-25 查询工资最高的教师的编号和开设的课程号
提示:请使用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
);
表样例
请在这里给出上述表结构对应的表样例。例如
输出样例:
请在这里给出输出样例。例如:
这题取巧了,直接从高到低排序工资然后只输出一个就OK
select ts.tid,cs.cid
from teachers ts
join choices cs on cs.tid = ts.tid
join courses cr on cr.cid = cs.cid
order by salary desc
limit 1
10-26 找出选修课程c++成绩最高的学生编号
提示:请使用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
);
表样例
请在这里给出上述表结构对应的表样例。例如
输出样例:
请在这里给出输出样例。例如:
这题一样的排个序输出一下
select sid
from choices
order by sorce desc
limit 1
10-27 查询没有学生选的课程名称
提示:请使用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
);
表样例
请在这里给出上述表结构对应的表样例。例如
输出样例:
请在这里给出输出样例。例如:
这题我想的是直接选择出所有的被选择的课程,然后再用排除法看看没被选择的课程
select cname
from courses
where cid not in (
select distinct cid
from choices
)
10-28 在teachers表中使用SQL语句将所有教师的工资加500元
表结构:
CREATE TABLE `teachers` (
`tid` bigint(20) NOT NULL,
`tname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`email` char(20) CHARACTER SET utf8mb4 DEFAULT NULL,
`salary` smallint(6) DEFAULT NULL,
PRIMARY KEY (`tid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表样例
teachers
表:
输出样例:
update teachers
set salary = salary + 500
10-29 在students表中使用SQL语句删除姓名为"LiMing"的学生信息
表结构:
请在这里写定义表结构的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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表样例
Students
表:
输出样例:
delete from students
where sname = 'LiMing'
10-30 删除所有选修课程"java"的选课记录
表结构:
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 NOT NULL,
`sid` bigint(20) DEFAULT NULL,
`tid` bigint(20) DEFAULT NULL,
`cid` bigint(20) DEFAULT NULL,
`sorce` int(11) DEFAULT NULL,
PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
CREATE TABLE `courses` (
`cid` bigint(20) NOT NULL,
`cname` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
`hour` char(10) CHARACTER SET utf8mb4 DEFAULT NULL,
PRIMARY KEY (`cid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表样例
choices
表:
courses
表:
输出样例:
delete from choices
where cid = '1003'
10-31 删除年级高于1998的学生信息
表结构:
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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
表样例
输出样例:
delete from students
where grade > 1998
10-32 删除成绩不及格的选课记录
表结构:
请在这里写定义表结构的SQL语句。例如:
CREATE TABLE `choices` (
`no` char(10) CHARACTER SET utf8mb4 NOT NULL,
`sid` bigint(20) DEFAULT NULL,
`tid` bigint(20) DEFAULT NULL,
`cid` bigint(20) DEFAULT NULL,
`sorce` int(11) DEFAULT NULL,
PRIMARY KEY (`no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
表样例
请在这里给出上述表结构对应的表样例。例如
choices
表:
输出样例:
delete from choices
where sorce < 60