MySQL 之 SQL练习

SQL练习

本文是针对SQL的练习，练完这几十道题，会对SQL的理解更深，写SQL的能力也会更强
本文的思路或者答案或者理解是错的
欢迎讨论和纠正

练习前准备

创建表以及添加基础的表数据

CREATE DATABASE stumsc;
CREATE TABLE student(
	sno VARCHAR(10) PRIMARY KEY,
	sname VARCHAR(20),
	sage INT,
	ssex VARCHAR(5)
);
CREATE TABLE teacher(
	tno VARCHAR(10) PRIMARY KEY,
	tname VARCHAR(20)
);
CREATE TABLE course(
	cno VARCHAR(10),
	cname VARCHAR(20),
	tno VARCHAR(20),
	PRIMARY KEY (cno,tno)
);
CREATE TABLE sc(
	sno VARCHAR(10),
	cno VARCHAR(10),
	score DOUBLE(4,2),
	PRIMARY KEY (sno,cno)
);
/*******初始化学生表的数据******/
INSERT INTO student VALUES ('s001','张三',23,'男');
INSERT INTO student VALUES ('s002','李四',23,'男');
INSERT INTO student VALUES ('s003','吴鹏',25,'男');
INSERT INTO student VALUES ('s004','琴沁',20,'女');
INSERT INTO student VALUES ('s005','王丽',20,'女');
INSERT INTO student VALUES ('s006','李波',21,'男');
INSERT INTO student VALUES ('s007','刘玉',21,'男');
INSERT INTO student VALUES ('s008','萧蓉',21,'女');
INSERT INTO student VALUES ('s009','陈萧晓',23,'女');
INSERT INTO student VALUES ('s010','陈美',22,'女');
COMMIT;
/******************初始化教师表***********************/
INSERT INTO teacher VALUES ('t001', '刘阳');
INSERT INTO teacher VALUES ('t002', '谌燕');
INSERT INTO teacher VALUES ('t003', '胡明星');
COMMIT;
/***************初始化课程表****************************/
INSERT INTO course VALUES ('c001','J2SE','t002');
INSERT INTO course VALUES ('c002','Java Web','t002');
INSERT INTO course VALUES ('c003','SSH','t001');
INSERT INTO course VALUES ('c004','Oracle','t001');
INSERT INTO course VALUES ('c005','SQL SERVER 2005','t003');
INSERT INTO course VALUES ('c006','C#','t003');
INSERT INTO course VALUES ('c007','JavaScript','t002');
INSERT INTO course VALUES ('c008','DIV+CSS','t001');
INSERT INTO course VALUES ('c009','PHP','t003');
INSERT INTO course VALUES ('c010','EJB3.0','t002');
COMMIT;
/***************初始化成绩表***********************/
INSERT INTO sc VALUES ('s001','c001',78.9);
INSERT INTO sc VALUES ('s002','c001',80.9);
INSERT INTO sc VALUES ('s003','c001',81.9);
INSERT INTO sc VALUES ('s004','c001',60.9);
INSERT INTO sc VALUES ('s001','c002',82.9);
INSERT INTO sc VALUES ('s002','c002',72.9);
INSERT INTO sc VALUES ('s003','c002',81.9);
INSERT INTO sc VALUES ('s001','c003',59.0);
COMMIT;

SQL 练习题及答案

-- 查询学生表所有信息
SELECT * FROM student;
-- 查询教师表所有信息
SELECT * FROM teacher;
-- 查询课程表的所有信息
SELECT * FROM course;
-- 查询成绩表的所有信息
SELECT * FROM sc;

-- 查询出 相同姓名学生的学号和名字
select s1.sno,s1.sname  from student s1 where s1.sname IN (
select  s1.sname from student s1 JOIN student s2 on s1.sno = s2.sno and s1.sname = s2.sname group by s1.sname HAVING COUNT(1) > 1 )

-- 查询所有学生的学号、姓名、选课数
select s.sno,s.sname,COUNT(1) '选课数' from student s left join sc  sc on s.sno =sc.sno group by s.sno

-- 查询各科成绩最高分，以以下形式显示：课程号，课程名、最高分，最低分
select c.cno,c.cname,MAX(sc.score),MIN(sc.score) from course c join sc sc on c.cno = sc.cno   GROUP BY c.cno,c.cname

-- 1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；
SELECT s1.sno FROM sc s1 LEFT JOIN sc s2 ON s1.sno = s2.sno AND s1.cno = 'c001' AND s2.cno='c002' WHERE s1.score>s2.score
-- 2、查询平均成绩大于60 分的同学的学号和平均成绩；
SELECT sno ,AVG(IFNULL(score,0)) '平均成绩' FROM sc GROUP BY sno HAVING  AVG(IFNULL(score,0))>60  

-- 2、查询平均成绩大于60 分的同学的学号和平均成绩 并按照成绩降序排列 
-- DESC降序   ASC 升序
SELECT sno ,AVG(IFNULL(score,0)) '平均成绩' FROM sc GROUP BY sno HAVING  AVG(IFNULL(score,0))>60  ORDER BY  AVG(IFNULL(score,0))  DESC

-- 3、查询所有同学的学号、姓名、选课数、总成绩；
SELECT s.sno,sname,COUNT(cno) '选课数',SUM(IFNULL(score,0)) '总成绩' FROM student s LEFT JOIN sc ON s.sno = sc.sno GROUP BY sno;

SELECT s.sno,s.sname,IFNULL(s2.c,0),IFNULL(s2.s,0) FROM student s LEFT JOIN 
(SELECT sno,COUNT(1) c,SUM(IFNULL(score,0)) s 
FROM sc GROUP BY sno) s2 ON s.sno=s2.sno
-- 4、查询姓“刘”的老师的个数
SELECT COUNT(1) '个数' FROM teacher WHERE tname like '刘%'

-- 5、查询没学过“谌燕”老师课的同学的学号、姓名；
SELECT s.sno,s.sname FROM student  s WHERE  s.sno NOT IN
(SELECT DISTINCT sc.sno FROM teacher t  JOIN course c ON t.tname='谌燕' AND  t.tno = c.tno JOIN sc ON c.cno = sc.cno)

-- 6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；
 
SELECT s.sno,s.sname FROM student s WHERE s.sno IN
(SELECT s1.sno FROM sc s1 JOIN sc s2  ON s1.sno = s2.sno AND s1.cno='c001' AND s2.cno = 'c002' ) #子查询实现

SELECT s.sno, s.sname FROM student s JOIN sc s1 ON s.sno = s1.sno JOIN sc s2 ON s2.sno = s1.sno AND s1.cno='c001' AND s2.cno = 'c002' 

-- 7、查询学过“谌燕”老师所教的所有课的同学的学号、姓名；


SELECT sc.* FROM sc sc  WHERE sc.cno IN (
SELECT cno FROM teacher t1 JOIN course c1 ON t1.tname='谌燕' AND t1.tno=c1.tno)
GROUP BY sno HAVING COUNT(1)=
(SELECT COUNT(1) FROM teacher t1 JOIN course c1 ON t1.tname='谌燕' AND t1.tno=c1.tno)

-- 8、查询课程编号“c002”的成绩比课程编号“c001”课程低的所有同学的学号、姓名；

SELECT s.sno,s.sname FROM student s LEFT JOIN sc s1 ON s.sno = s1.sno  AND s1.cno = 'c001'
	LEFT JOIN sc s2 ON s1.sno = s2.sno  AND s2.cno = 'c002'  WHERE s1.score > s2.score

-- 9、查询所有课程成绩小于60 分的同学的学号、姓名；
SELECT sno,sname FROM student WHERE sno NOT IN
(SELECT DISTINCT sno FROM sc WHERE score >= 60 )

-- 10、查询没有学全所有课的同学的学号、姓名；
-- 先查出所有课程数
SELECT sno,sname FROM student WHERE sno NOT IN (
SELECT sno FROM sc GROUP BY sno HAVING COUNT(1) = (SELECT COUNT(1) FROM course ))

-- 11、查询至少有一门课与学号为“s001”的同学所学相同的同学的学号和姓名；
#找出s001这个同学学习的课程
SELECT DISTINCT s.sno,s.sname FROM student  s JOIN sc sc ON s.sno = sc.sno WHERE sc.cno IN (
SELECT cno FROM sc WHERE sno = 's001'
) AND s.sno != 's001'

-- 12、查询至少学过学号为“s001”同学所有一门课的其他同学学号和姓名；


-- 13、把“SC”表中“谌燕”老师教的课的成绩都更改为此课程的平均成绩；
#先查出平均成绩
UPDATE sc,(SELECT sc.cno,AVG(sc.score) av FROM teacher t JOIN course c ON t.tno = c.tno JOIN sc sc ON c.cno = sc.cno where t.tname = '谌燕' GROUP BY sc.cno ) sc2 SET sc.score = sc2.av WHERE sc.cno = sc2.cno 

SELECT * from sc

-- 14、查询和“s001”号的同学学习的课程完全相同的其他同学学号和姓名；
#s001 的课程数 在切除没有选s001中的 在在选了之中剔除 再利用选科数筛选

select sno from sc where sno not in(
(select sno from sc where cno not  in(
select cno from sc where sno='s001'))) and sno!='s001'
group by sno having 
count(1)=(select count(1) FROM sc WHERE sno='s001')

-- 15、删除学习“谌燕”老师课的SC 表记录；
delete from sc WHERE cno IN (
SELECT c.cno FROM teacher t JOIN course c ON t.tno = c.tno where t.tname = '谌燕'
)

-- 17、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
SELECT cno,MAX(score),MIN(score) from sc GROUP BY cno

-- 18、按各科平均成绩从低到高和及格率的百分数从高到低顺序
SELECT cno,AVG(score) '平均分',SUM(CASE WHEN score>=60 THEN	1 ELSE	0 END)/COUNT(1) '及格率' FROM sc GROUP BY cno  ORDER BY 2,3 DESC 

-- 19、查询不同老师所教不同课程平均分从高到低显示 
SELECT t.tname,sc.cno,AVG(score) '平均分',SUM(CASE WHEN score>=60 THEN	1 ELSE	0 END)/COUNT(1) '及格率' FROM  teacher t  JOIN course c ON t.tno = c.tno  JOIN sc sc ON c.cno = sc.cno 
GROUP BY tname,cno ORDER BY 3,4 DESC

-- 20、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]
SELECT c.cno,c.cname,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) '[100-85]',
SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) '[85-70]',
SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) '[70-60]',
SUM(CASE WHEN score <60 THEN 1 ELSE 0 END) '[<60]'
FROM course c LEFT JOIN sc ON c.cno = sc.cno GROUP BY c.cno,c.cname

-- 21、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT s1.cno,s1.score FROM sc s1 WHERE 
(SELECT COUNT(1) from sc s2 where s1.cno = s2.cno AND s2.score > s1.score)<3
ORDER BY s1.cno,s1.score DESC

-- 22、查询每门课程被选修的学生数
SELECT c1.cno,c1.cname,IFNULL(c2.c,0) FROM course c1 LEFT JOIN
(SELECT cno,COUNT(1) c FROM sc GROUP BY cno) c2 ON c1.cno = c2.cno

-- 23、查询出只选修了一门课程的全部学生的学号和姓名
#子查询
select s.sno,s.sname from student s WHERE sno in (
select sno FROM sc  GROUP BY sno HAVING COUNT(1)=1 )
#连接查询
select s.sno,s.sname from student s JOIN sc sc ON s.sno = sc.sno 
GROUP BY s.sno HAVING COUNT(1) =1

-- 24、查询男生、女生人数
SELECT ssex,COUNT(1) FROM student GROUP BY ssex

-- 25、查询姓“张”的学生名单
SELECT sname FROM student WHERE sname LIKE  '张%'

-- 26、查询同名同性学生名单，并统计同名人数
SELECT sname,ssex,COUNT(1) FROM student GROUP BY sname,ssex

-- 28、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
#默认ASC从小到大 升序 DESC 从大到小降序
SELECT cno,AVG(score) FROM sc GROUP BY cno ORDER BY  AVG(score) ASC,cno DESC

-- 29、查询平均成绩大于85 的所有学生的学号、姓名和平均成绩
SELECT s.sno,sc.cno,s.sname,AVG(score) FROM student s JOIN sc sc ON s.sno = sc.sno 
GROUP BY  s.sno,sc.cno HAVING AVG(score) >85
SELECT cno,AVG(score),s.sname FROM student s JOIN sc ON s.sno=sc.sno 
GROUP BY cno,s.sname HAVING AVG(score)>85

-- 30、查询课程名称为“数据库”，且分数低于60 的学生姓名和分数
SELECT s.sno,s.sname,score FROM course c JOIN sc ON c.cno=sc.cno AND 
c.cname='数据库' AND score<60
JOIN student s ON sc.sno=s.sno
-- 31、查询所有学生的选课情况；统计
SELECT s.sno,s.sname,COUNT(sc.sno) FROM student s LEFT JOIN sc sc
ON s.sno = sc.sno GROUP BY s.sno

-- 32、查询任何一门课程成绩在70 分以上的姓名、课程名称和分数；
SELECT s.sno,s.sname,c.cname,sc.score FROM student s JOIN sc sc ON s.sno = sc.sno AND sc.score >=70 
JOIN course c ON sc.cno = c.cno WHERE s.sno NOT IN 
(SELECT sno FROM sc WHERE score <70)

SELECT a.sno,a.sname,c.cname,sc.score FROM 
(SELECT s.sno,s.sname FROM student s WHERE s.sno NOT IN(
SELECT sno FROM sc WHERE sc.`score`<70)) a JOIN sc ON a.sno=sc.sno 
JOIN course c ON sc.cno=c.cno

-- 33、查询不及格的课程，并按课程号从大到小排列
SELECT sc.* FROM sc WHERE score<60 ORDER BY sno,cno DESC

-- 34、查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名；
SELECT s.sno,s.sname FROM sc sc JOIN student s ON sc.sno = s.sno AND sc.cno='c001' 
WHERE sc.score >=80

-- 35、求选了课程的学生人数
SELECT COUNT(a.sno) FROM( 
SELECT DISTINCT sno FROM sc) a

-- 36、查询选修“谌燕”老师所授课程的学生中，成绩最高的学生姓名及其成绩
SELECT s.sno,s.sname,a.m FROM student s JOIN sc sc ON s.sno = sc.sno JOIN
(SELECT sc.cno,MAX(score) m FROM teacher t JOIN course c ON t.tno = c.tno AND t.tname = '谌燕'
JOIN sc sc ON c.cno = sc.cno GROUP BY sc.cno ) a ON sc.cno = a.cno AND a.m = sc.score

-- 37、查询各个课程及相应的选修人数
SELECT c.cname,COUNT(sc.cno) FROM course c LEFT JOIN sc ON c.cno = sc.cno GROUP BY c.cname

SELECT c.cno,COUNT(sc.cno) FROM course c LEFT JOIN sc ON c.cno=sc.cno GROUP BY c.cno
-- 38、查询不同课程成绩相同的学生的学号、课程号、学生成绩
# 不同课程
SELECT sc1.sno,sc1.cno,sc1.score FROM sc sc1 JOIN sc sc2 ON 
sc1.sno =sc2.sno AND sc1.score =sc2.score AND sc1.cno != sc2.cno

-- 39、查询每门功课成绩最好的前两名
SELECT sc1.sno,sc1.cno,sc1.score FROM sc sc1 WHERE (
SELECT COUNT(1) FROM sc sc2 WHERE sc1.cno=sc2.cno AND sc1.score<sc2.score
)<2 GROUP BY sc1.sno,sc1.cno ORDER BY sc1.cno,sc1.score DESC

-- 40、统计每门课程的学生选修人数（超过10 人的课程才统计）。
-- 要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT cno,COUNT(cno)  c FROM sc GROUP BY cno HAVING c >10 ORDER BY c DESC, cno ASC

-- 41、检索至少选修两门课程的学生学号
SELECT sno from sc GROUP BY sno HAVING COUNT(cno) >=2
-- 42、查询全部学生都选修的课程的课程号和课程名
#每门课程选的人数
SELECT cno,COUNT(cno) FROM sc GROUP BY cno;
#所有学生树
SELECT COUNT(1) FROM student
#每人选课数
SELECT sno,COUNT(sno) FROM sc GROUP BY sno; 
SELECT * from sc;

SELECT cno,cname FROM course where cno IN(
SELECT sc.cno FROM sc sc  GROUP BY sc.cno
HAVING COUNT(cno) = (SELECT COUNT(*) FROM student)
)

-- 43、查询没学过“谌燕”老师讲授的任一门课程的学生姓名
SELECT sname FROM student WHERE sno NOT IN
(SELECT DISTINCT s.sno FROM student s JOIN sc sc ON s.sno = sc.sno WHERE sc.cno  IN (
SELECT cno FROM teacher t JOIN course c ON t.tno = c.tno AND  t.tname = '谌燕'
))

-- 44、查询两门及以上不及格课程的同学的学号及其平均成绩
SELECT sno,AVG(score) FROM sc  WHERE sno IN(
SELECT sno FROM sc WHERE score <60 GROUP BY sc.sno HAVING COUNT(cno)>=2
) GROUP BY sno

-- 45、检索“c004”课程分数小于60，按分数降序排列的同学学号
SELECT sno,score FROM sc WHERE cno='c004' AND score <60 ORDER BY score DESC

-- 46、删除“s002”同学的“c001”课程的成绩
DELETE FROM sc WHERE sno='s002' AND cno = 'c001'