sql之mysql45题
- 建表+数据
- 45题
- 个人认为第11,12,13,18,20,26,38,42题比较有意思
- 1.查询“001”课程比“002”课程成绩高的所有学生的学号;
- 2.查询平均成绩大于60分的同学的学号和平均成绩;
- 3.查询所有同学的学号、姓名、选课数、总成绩;
- 4.查询姓“李”的老师的个数;
- 5.查询没学过“叶平”老师课的同学的学号、姓名;
- 6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
- 7.查询学过“叶平”老师所教的所有课的同学的学号、姓名;
- 8.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
- 9.查询所有课程成绩小于60分的同学的学号、姓名;
- 10.查询没有学全所有课的同学的学号、姓名;
- 11.****查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名; ****
- 12.****把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;****
- 13.****查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名; ****
- 13扩展、查询所有包含“1002”号的同学学习的所有课程的其他同学学号和姓名;
- 14.删除学习“叶平”老师课的SC表记录;
- 15.按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
- 16.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
- 17.按各科平均成绩从低到高和及格率的百分数从高到低排序
- 18.****查询如下课程平均成绩和及格率的百分数(用1行显示): 企业管理(001),马克思(002),UML (003),数据库(004) ****
- 19.查询不同老师所教不同课程平均分从高到低显示 要求显示:教师ID,教师姓名,课程ID,课程名称,平均成绩
- 20.****统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] (从本题开始没有使用美化sql)****
- 21.查询每门课程被选修的学生数
- 22.查询出只选修了一门课程的全部学生的学号和姓名
- 23.查询男生、女生人数
- 24.查询姓“张”的学生名单
- 25.查询同名同性学生名单,并统计同名人数
- 26.****1981年出生的学生名单(注:Student表中Sage列的类型是datetime)****
- 27.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
- 28.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
- 29.查询课程名称为“数据库”,且分数低于60的学生姓名和分数
- 30.查询所有学生的选课情况;
- 31.查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
- 32.查询不及格的课程,并按课程号从大到小排列
- 33.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
- 34.求选了课程的学生人数
- 35.查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
- 36.查询各个课程及相应的选修人数
- 37.查询不同课程成绩相同的学生的学号、课程号、学生成绩
- 38.****查询每门功课成绩最好的前两名****
- 39.统计每门课程的学生选修人数。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
- 40.检索至少选修两门课程的学生学号
- 41.查询全部学生都选修的课程的课程号和课程名
- 42.****查询没学过“叶平”老师讲授的任一门课程的学生姓名 ****
- 43.查询两门以上不及格课程的同学的学号及其平均成绩
- 44.检索“004”课程分数小于60,按分数降序排列的同学学号
- 45.删除“1002”同学的“001”课程的成绩
建表+数据
建表语句
/*Student(sid,Sname,Sage,Ssex) 学生表*/
CREATE TABLE student (
sid varchar(10) NOT NULL,
sName varchar(20) DEFAULT NULL,
sAge datetime DEFAULT '1980-10-12 23:12:36',
sSex varchar(10) DEFAULT NULL,
PRIMARY KEY (sid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Course(cid,Cname,tid) 课程表 */
CREATE TABLE course (
cid varchar(10) NOT NULL,
cName varchar(10) DEFAULT NULL,
tid int(20) DEFAULT NULL,
PRIMARY KEY (cid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*SC(sid,cid,score) 成绩表 */
CREATE TABLE sc (
sid varchar(10) DEFAULT NULL,
cid varchar(10) DEFAULT NULL,
score int(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*Teacher(tid,Tname) 教师表*/
CREATE TABLE teacher (
tid int(10) DEFAULT NULL,
tName varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
增加数据
/*增加数据*/
insert into teacher(tid,tName) values (1,'李老师'),(2,'何以琛'),(3,'叶平');
insert into student(sid,sName,sAge,sSex) values ('1001','张三丰','1980-10-12 23:12:36','男'),('1002','张无极','1995-10-12 23:12:36','男'),('1003','李奎','1992-10-12 23:12:36','女'),('1004','李元宝','1980-10-12 23:12:36','女'),('1005','李世明','1981-10-12 23:12:36','男'),('1006','赵六','1986-10-12 23:12:36','男'),('1007','田七','1981-10-12 23:12:36','女');
insert into sc(sid,cid,score) values ('1001','001',80),('1001','002',60),('1001','003',75),('1002','001',85),('1002','002',70),('1003','004',100),('1003','001',90),('1003','002',55),('1004','002',65),('1004','003',60);
insert into course(cid,cName,tid) values ('001','企业管理',3),('002','马克思',3),('003','UML',2),('004','数据库',1),('005','英语',1);
45题
个人认为第11,12,13,18,20,26,38,42题比较有意思
1.查询“001”课程比“002”课程成绩高的所有学生的学号;
SELECT
a.sid
FROM
(
SELECT
sid,
score
FROM
sc
WHERE
cid = '001'
) a
LEFT JOIN (
SELECT
sid,
score
FROM
sc
WHERE
cid = '002'
) b ON a.sid = b.sid
WHERE
a.score > b.score
2.查询平均成绩大于60分的同学的学号和平均成绩;
SELECT
sid,
AVG(score) score
FROM
sc
GROUP BY
sid
HAVING
AVG(score) > 60;
3.查询所有同学的学号、姓名、选课数、总成绩;
SELECT
student.sid,
MAX(student.sName),
COUNT(sc.cid) cidnum,
SUM(sc.score) score
FROM
student
LEFT JOIN sc ON student.sid = sc.sid
GROUP BY
student.sid
4.查询姓“李”的老师的个数;
SELECT
SUM(teacher.tid)
FROM
teacher
WHERE
teacher.tName LIKE '李%';
5.查询没学过“叶平”老师课的同学的学号、姓名;
select s.sid,s.sName from student s where s.sid
NOT IN (
select stu.sid from student stu
LEFT JOIN sc on sc.sid = stu.sid
LEFT JOIN course on course.cid = sc.cid
LEFT JOIN teacher on teacher.tid = course.tid
WHERE teacher.tName = '叶平'
)
6.查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
SELECT
a.sid
FROM
(
SELECT
sid,
score
FROM
sc
WHERE
cid = '001'
) a
LEFT JOIN (
SELECT
sid,
score
FROM
sc
WHERE
cid = '002'
) b ON a.sid = b.sid
7.查询学过“叶平”老师所教的所有课的同学的学号、姓名;
SELECT
s.sid,
s.sName
FROM
student s
WHERE
s.sid IN (
SELECT
stu.sid
FROM
student stu
LEFT JOIN sc ON sc.sid = stu.sid
LEFT JOIN course ON course.cid = sc.cid
LEFT JOIN teacher ON teacher.tid = course.tid
WHERE
teacher.tName = '叶平'
GROUP BY
stu.sid
HAVING
COUNT(stu.sid) = (
SELECT
COUNT(teacher.tid)
FROM
teacher
LEFT JOIN course ON teacher.tid = course.tid
WHERE
teacher.tName = '叶平'
)
)
8.查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
SELECT
student.sid,
student.sName
FROM
(
SELECT
sid,
score
FROM
sc
WHERE
cid = '001'
) a
LEFT JOIN (
SELECT
sid,
score
FROM
sc
WHERE
cid = '002'
) b ON a.sid = b.sid
LEFT JOIN student ON b.sid = student.sid
WHERE
a.score > b.score
9.查询所有课程成绩小于60分的同学的学号、姓名;
9.1、
SELECT
student.sid,
student.sName
FROM
(
SELECT
sid,
score
FROM
sc
WHERE
cid = '001'
) a
LEFT JOIN (
SELECT
sid,
score
FROM
sc
WHERE
cid = '002'
) b ON a.sid = b.sid
LEFT JOIN (
SELECT
sid,
score
FROM
sc
WHERE
cid = '002'
) c ON c.sid = b.sid
LEFT JOIN student ON c.sid = student.sid
WHERE
a.score < 60
AND b.score < 60
AND c.score < 60
9.2、一个课程都没有选择的也算是成绩在60以下
select sid,Sname
from Student
where sid not in (select Student.sid from Student,SC where Student.sid=SC.sid and score>60);
10.查询没有学全所有课的同学的学号、姓名;
select sid,Sname
from Student
where sid not in (select Student.sid from Student,SC where Student.sid=SC.sid);
11.****查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名; ****
SELECT
student.sid,
student.sName
FROM
student
LEFT JOIN sc ON sc.sid = student.sid
WHERE
sc.cid IN (
SELECT
sc.cid
FROM
sc
WHERE
sc.sid = '1001'
)
AND sc.sid != '1001'
GROUP BY
student.sid
12.把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;
UPDATE sc,
(
SELECT
c.cid,
AVG(score) avgs
FROM
sc,
course c,
teacher t
WHERE
sc.cid = c.cid
AND c.tid = t.tid
AND t.tName = '叶平'
GROUP BY
c.cid
) sc_2
SET sc.score = sc_2.avgs
WHERE
sc.cid = sc_2.cid
13.****查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名; ****
课程完全相同:两个人的课程一模一样,谁的都不能多,也不能少
其他同学:搜索的结果不能为1002号同学
方法1:
SELECT
SC.sid
FROM
SC
LEFT JOIN (
SELECT
sid,
count(1) a1
FROM
SC
GROUP BY
sid
) a ON SC.sid = a.sid
LEFT JOIN (
SELECT
count(1) b1
FROM
SC
WHERE
sid = '1002'
) b ON a.a1 = b.b1
WHERE
cid IN (
SELECT
cid
FROM
SC
WHERE
sid = '1002'
)
AND a.a1 = b.b1
AND SC.sid != '1002'
GROUP BY
sid
方法2:
SELECT
SC.sid
FROM
SC
LEFT JOIN (
SELECT
sid,
count(1) a1
FROM
SC
GROUP BY
sid
) a ON SC.sid = a.sid
WHERE
cid IN (
SELECT
cid
FROM
SC
WHERE
sid = '1002'
)
AND a.a1 = (
SELECT
count(1) b1
FROM
SC
WHERE
sid = '1002'
)
AND SC.sid != '1002'
GROUP BY
sid
13扩展、查询所有包含“1002”号的同学学习的所有课程的其他同学学号和姓名;
SELECT
sid,
count(1)
FROM
SC
WHERE
cid IN (
SELECT
cid
FROM
SC
WHERE
sid = '1002'
)
GROUP BY
sid
HAVING
count(1) = (
SELECT
count(1)
FROM
SC
WHERE
sid = '1002'
);
14.删除学习“叶平”老师课的SC表记录;
DELETE
FROM
sc
WHERE
cid IN (
SELECT
cid
FROM
course
LEFT JOIN teacher ON course.tid = teacher.tid
WHERE
teacher.tName = '叶平'
);
15.按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩,按如下形式显示: 学生ID,数据库,企业管理,英语,有效课程数,有效平均分
SELECT
sid AS 学生ID,
(
SELECT
score
FROM
SC
WHERE
SC.sid = t.sid
AND cid = '004'
) AS 数据库,
(
SELECT
score
FROM
SC
WHERE
SC.sid = t.sid
AND cid = '001'
) AS 企业管理,
(
SELECT
score
FROM
SC
WHERE
SC.sid = t.sid
AND cid = '005'
) AS 英语,
COUNT(*) AS 有效课程数,
AVG(t.score) AS 平均成绩
FROM
SC AS t
GROUP BY
sid
ORDER BY
avg(t.score)
16.查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT
cid '课程ID',
MAX(score) '最高分',
MIN(score) '最低分'
FROM
sc
GROUP BY
cid
17.按各科平均成绩从低到高和及格率的百分数从高到低排序
17.1.测试:中文升序降序不好使
SELECT
cid '课程ID',
AVG(score) '平均成绩',
SUM(
CASE
WHEN IFNULL(score, 0) > 60 THEN
1
ELSE
0
END
) / COUNT(1) '及格率'
FROM
sc
GROUP BY
cid
ORDER BY
'平均成绩' DESC,
'及格率' DESC
17.2.别名改为英文才可以
SELECT
cid '课程ID',
AVG(score) avg_s,
SUM(
CASE
WHEN IFNULL(score, 0) > 60 THEN
1
ELSE
0
END
) / COUNT(1) pass_rate
FROM
sc
GROUP BY
cid
ORDER BY
avg_s DESC,
pass_rate DESC
18.****查询如下课程平均成绩和及格率的百分数(用1行显示): 企业管理(001),马克思(002),UML (003),数据库(004) ****
SELECT
SUM(
CASE
WHEN cid = '001' THEN
IFNULL(score, 0)
ELSE
0
END
) / SUM(
CASE
WHEN cid = '001' THEN
1
ELSE
0
END
) '企业管理平均成绩',
SUM(
CASE
WHEN IFNULL(score, 0) > 60
AND cid = '001' THEN
1
ELSE
0
END
) / SUM(
CASE
WHEN cid = '001' THEN
1
ELSE
0
END
) * 100 '企业管理百分比',
SUM(
CASE
WHEN cid = '002' THEN
IFNULL(score, 0)
ELSE
0
END
) / SUM(
CASE
WHEN cid = '002' THEN
1
ELSE
0
END
) '马克思平均成绩',
SUM(
CASE
WHEN IFNULL(score, 0) > 60
AND cid = '002' THEN
1
ELSE
0
END
) / SUM(
CASE
WHEN cid = '002' THEN
1
ELSE
0
END
) * 100 '马克思百分比',
SUM(
CASE
WHEN cid = '003' THEN
IFNULL(score, 0)
ELSE
0
END
) / SUM(
CASE
WHEN cid = '003' THEN
1
ELSE
0
END
) 'UML平均成绩',
SUM(
CASE
WHEN IFNULL(score, 0) > 60
AND cid = '003' THEN
1
ELSE
0
END
) / SUM(
CASE
WHEN cid = '003' THEN
1
ELSE
0
END
) * 100 'UML百分比',
SUM(
CASE
WHEN cid = '004' THEN
IFNULL(score, 0)
ELSE
0
END
) / SUM(
CASE
WHEN cid = '004' THEN
1
ELSE
0
END
) '数据库平均成绩',
SUM(
CASE
WHEN IFNULL(score, 0) > 60
AND cid = '004' THEN
1
ELSE
0
END
) / SUM(
CASE
WHEN cid = '004' THEN
1
ELSE
0
END
) * 100 '数据库百分比'
FROM
sc
19.查询不同老师所教不同课程平均分从高到低显示 要求显示:教师ID,教师姓名,课程ID,课程名称,平均成绩
SELECT
MAX(t.tid),
MAX(t.tName),
c.cid,
MAX(c.cName),
AVG(score)
FROM
teacher t
LEFT JOIN course c ON t.tid = c.tid
LEFT JOIN sc ON sc.cid = c.cid
GROUP BY
c.cid
20.统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60] (从本题开始没有使用美化sql)
20.1.使用大于小于号
SELECT
sc.cid,MAX(cName) '课程名称',
SUM(CASE WHEN score >= 85 THEN 1 ELSE 0 END) '[100-85]',
SUM(CASE WHEN score >= 70 AND score < 85 THEN 1 ELSE 0 END) '[70-85]',
SUM(CASE WHEN score < 70 AND score >= 60 THEN 1 ELSE 0 END) '[60-70]',
SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) '[60-0]'
FROM
sc LEFT JOIN course on sc.cid = course.cid
GROUP BY sc.cid
20.2.使用BETWEEN-AND
SELECT SC.cid as 课程ID, Cname as 课程名称
,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS '[100 - 85]'
,SUM(CASE WHEN score BETWEEN 70 AND 84 THEN 1 ELSE 0 END) AS '[85 - 70]'
,SUM(CASE WHEN score BETWEEN 60 AND 69 THEN 1 ELSE 0 END) AS '[70 - 60] '
,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS '[60 -] '
FROM SC,Course
where SC.cid=Course.cid
GROUP BY SC.cid,Cname;
21.查询每门课程被选修的学生数
select COUNT(sid) from sc GROUP BY cid
22.查询出只选修了一门课程的全部学生的学号和姓名
SELECT sc.sid,MAX(sName)
FROM sc
LEFT JOIN student on student.sid = sc.sid
GROUP BY sc.sid
HAVING COUNT(cid) = 1
23.查询男生、女生人数
select SUM(CASE WHEN sSex = '男' THEN 1 ELSE 0 END) '男',
SUM(CASE WHEN sSex = '女' THEN 1 ELSE 0 END) '女'
FROM student
24.查询姓“张”的学生名单
SELECT sName FROM student WHERE sName LIKE '张%'
25.查询同名同性学生名单,并统计同名人数
SELECT sName,sSex,COUNT(1) FROM student GROUP BY sName,sSex HAVING COUNT(1) > 1
26.1981年出生的学生名单(注:Student表中Sage列的类型是datetime)
SELECT SNAME, SAGE
FROM student
WHERE DATE_FORMAT(sAge,'%Y')='1981';
27.查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
SELECT cid,AVG(score) avg_s FROM sc GROUP BY cid ORDER BY avg_s ASC ,cid DESC
28.查询平均成绩大于85的所有学生的学号、姓名和平均成绩
SELECT student.sid,MAX(sName),AVG(score) avg_s
FROM student
LEFT JOIN sc ON student.sid = sc.sid
GROUP BY student.sid
HAVING avg_s > 85
29.查询课程名称为“数据库”,且分数低于60的学生姓名和分数
SELECT sName, score
FROM sc
LEFT JOIN course on course.cid = sc.cid
LEFT JOIN student on student.sid = sc.sid
WHERE course.cName = '数据库' AND sc.score < 60
30.查询所有学生的选课情况;
SELECT student.sid,sName,cName
FROM sc
LEFT JOIN course on sc.cid = course.cid
LEFT JOIN student on student.sid = sc.sid
31.查询任何一门课程成绩在70分以上的姓名、课程名称和分数;
SELECT sName,cName,score
FROM student
LEFT JOIN sc on sc.sid = student.sid
LEFT JOIN course on course.cid = sc.cid
WHERE sc.score > 70
32.查询不及格的课程,并按课程号从大到小排列
SELECT cid,score FROM sc WHERE score < 60 ORDER BY cid DESC
33.查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
SELECT sc.sid,sName
FROM student
LEFT JOIN sc on sc.sid = student.sid
LEFT JOIN course on course.cid = sc.cid
WHERE score > 80
AND sc.cid = '003'
34.求选了课程的学生人数
34.1统计sc表的总数
SELECT COUNT(1) FROM sc;
34.2在sid上去重
SELECT COUNT(DISTINCT sid) FROM sc
35.查询选修“叶平”老师所授课程的学生中,成绩最高的学生姓名及其成绩
SELECT sName,score
FROM student
LEFT JOIN sc on sc.sid = student.sid
LEFT JOIN course on course.cid = sc.cid
LEFT JOIN teacher on teacher.tid = course.tid
WHERE teacher.tName = '叶平'
ORDER BY score DESC
LIMIT 1
36.查询各个课程及相应的选修人数
SELECT cid,count(sid)
FROM sc
GROUP BY cid
37.查询不同课程成绩相同的学生的学号、课程号、学生成绩
SELECT DISTINCT a.sid,b.score
FROM sc a
LEFT JOIN sc b ON a.score = b.score
WHERE a.cid <> b.cid
38.查询每门功课成绩最好的前两名
38.1如果第二名和之后学生的成绩相同,则舍去
SELECT *
FROM sc t1
WHERE (
SELECT COUNT(*)
FROM sc t2
WHERE t1.cid=t2.cid
AND t2.score>=t1.score
) <=2 ORDER BY t1.cid,t1.score DESC
38.2如果第二名和之后学生的成绩相同,则都显示
SELECT *
FROM sc t1
WHERE (
SELECT COUNT(*)
FROM sc t2
WHERE t1.cid=t2.cid
AND t2.score>t1.score
) <=1 ORDER BY t1.cid,t1.score DESC
39.统计每门课程的学生选修人数。要求输出课程号和选修人数,查询结果按人数降序排列,若人数相同,按课程号升序排列
SELECT cid,COUNT(1) c
FROM sc
GROUP BY cid
ORDER BY c DESC,cid
40.检索至少选修两门课程的学生学号
SELECT sid
FROM sc
GROUP BY sid
HAVING COUNT(1) > 1
41.查询全部学生都选修的课程的课程号和课程名
SELECT sc.cid,course.cName
FROM sc
LEFT JOIN course on course.cid = sc.cid
GROUP BY sc.cid
HAVING COUNT(1) = (SELECT COUNT(1) FROM student)
42.****查询没学过“叶平”老师讲授的任一门课程的学生姓名 ****
SELECT sName
FROM student
WHERE sid NOT IN (
SELECT sid
FROM teacher
LEFT JOIN course on course.tid = teacher.tid
LEFT JOIN sc on sc.cid = course.cid
WHERE tName = '叶平')
43.查询两门以上不及格课程的同学的学号及其平均成绩
SELECT student.sid,AVG(score)
FROM sc
LEFT JOIN student on student.sid = sc.sid
GROUP BY sc.sid
HAVING SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) > 1
44.检索“004”课程分数小于60,按分数降序排列的同学学号
SELECT sid
FROM sc
WHERE cid = '004'
ORDER BY sid
45.删除“1002”同学的“001”课程的成绩
delete from Sc where sid='1002' and cid='001';
金融理财师(AFP)双证
注册理财规划师(CFP)四证
出证单位:中国注册理财规划师协会。
适合人群如下:
1.银行工作人员
2.证劵公司工作人员
3.理财投资公司工作人员
4.社会投资理财顾问人员。
查询网址:中国注册理财规划师协会http://www.cfp.org.cn。