SQL经典练习题

最新推荐文章于 2024-08-22 01:19:55 发布

一只固执的刺猬

最新推荐文章于 2024-08-22 01:19:55 发布

阅读量649

点赞数 2

文章标签： mysql sql练习题

本文链接：https://blog.csdn.net/weixin_44385222/article/details/102532852

版权

SQL练习题

create table Student(S varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10));
insert into Student values(‘01’,‘赵英’,‘1998-02-19’,‘男’);
insert into Student values(‘02’,‘钱雄’,‘1996-08-21’,‘男’);
insert into Student values(‘03’,‘孙豪’,‘1990-05-20’,‘男’);
insert into Student values(‘04’,‘李杰’,‘1990-08-06’,‘男’);
insert into Student values(‘05’,‘周梅’,‘1991-07-26’,‘女’);
insert into Student values(‘06’,‘吴兰’,‘1993-03-11’,‘女’);
insert into Student values(‘07’,‘郑竹’,‘1989-07-01’,‘女’);
insert into Student values(‘08’,‘王菊’,‘1990-01-20’,‘女’);
insert into Student values(‘09’,‘风筝’,‘1991-06-20’,‘女’);
create table Course(C varchar(10),Cname varchar(10),T varchar(10));
insert into Course values(‘01’,‘语文’,‘02’);
insert into Course values(‘02’,‘数学’,‘01’);
insert into Course values(‘03’,‘英语’,‘03’);
insert into Course values(‘04’,‘物理’,‘01’);
create table Teacher(T varchar(10),Tname varchar(10));
insert into Teacher values(‘01’,‘张三’);
insert into Teacher values(‘02’,‘李四’);
insert into Teacher values(‘03’,‘王五’);
insert into Teacher values(‘04’,‘李六’);
create table SC(S varchar(10),C varchar(10),score decimal(18,1));
insert into SC values(‘01’,‘01’,80);
insert into SC values(‘01’,‘02’,90);
insert into SC values(‘01’,‘03’,99);
insert into SC values(‘02’,‘01’,70);
insert into SC values(‘02’,‘02’,60);
insert into SC values(‘02’,‘03’,80);
insert into SC values(‘03’,‘01’,80);
insert into SC values(‘03’,‘02’,80);
insert into SC values(‘03’,‘03’,80);
insert into SC values(‘04’,‘01’,50);
insert into SC values(‘04’,‘02’,30);
insert into SC values(‘04’,‘03’,20);
insert into SC values(‘05’,‘01’,76);
insert into SC values(‘05’,‘02’,87);
insert into SC values(‘06’,‘01’,31);
insert into SC values(‘06’,‘03’,34);
insert into SC values(‘07’,‘02’,89);
insert into SC values(‘07’,‘03’,98);
insert into SC values(‘07’,‘01’,89);
insert into SC values(‘07’,‘04’,98);

student表
学生信息表
sc表
成绩信息表
course表
课程信息表
teacher表
教师表
1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数

SELECT s.*,c1.score'01课程',c2.score'02课程',c3.score'03课程' 
FROM student s,
     (SELECT score,S FROM sc WHERE C=01)c1,
     (SELECT score,S FROM sc WHERE C=02)c2,
     (SELECT score,S FROM sc WHERE C=03)c3
WHERE c1.S=c2.S AND s.`S`=c1.S AND c3.S=c2.S
AND c1.score>c2.score;

查询截图
2、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩*/

SELECT s.`S`,s.`Sname`,AVG(sc.`score`)平均成绩 FROM student s,sc
WHERE s.`S`=sc.`S` GROUP BY s.`S`
HAVING AVG(sc.`score`)>=60

查询截图
3、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩

SELECT s.`S`,s.`Sname`,AVG(sc.`score`) FROM student s,sc
WHERE s.`S`=sc.`S` GROUP BY s.`S`
HAVING AVG(sc.`score`)<60

查询截图
4、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

SELECT s.`S`,s.`Sname`,COUNT(sc.`C`)选课总数,SUM(sc.`score`)所有课程的总成绩 FROM student s,sc
WHERE s.`S`=sc.`S` GROUP BY s.`S`;

查询截图
5、查询"李"姓老师的数量

SELECT COUNT(*) FROM teacher WHERE Tname LIKE '李%';

查询截图
6、查询学过"张三"老师授课的同学的信息

SELECT s.* FROM student s,teacher t,sc,course c
WHERE s.`S`=sc.`S` AND t.`T`=c.`T` AND c.`C`=sc.`C` AND t.Tname="张三"
GROUP BY s.`Sname`;

查询截图
7、查询没学过"张三"老师授课的同学的信息

7、查询没学过"张三"老师授课的同学的信息 
(1)
SELECT s.* FROM student s,teacher t,sc,course c
WHERE t.`T`=c.`T` AND sc.`C`=c.`C` AND s.`Sname` NOT IN
(SELECT s.`Sname` FROM student s,teacher t,sc,course c
WHERE s.`S`=sc.`S` AND t.`T`=c.`T` AND c.`C`=sc.`C` AND t.`Tname`="张三")
GROUP BY s.`Sname`;

(2)
SELECT s.* FROM student s
WHERE s.`S` NOT IN
(SELECT s.`S` FROM student s,teacher t,sc,course c
WHERE s.`S`=sc.`S` AND t.`T`=c.`T` AND c.`C`=sc.`C` AND t.`Tname`="张三")
GROUP BY s.`Sname`;

查询结果

8、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

SELECT s.* FROM student s
WHERE s.`S` IN
(SELECT s1.S FROM (SELECT S FROM sc WHERE C="01")s1,
		  (SELECT S FROM sc WHERE C="02")s2
		  WHERE s1.S=s2.S)

查询截图

9、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

SELECT * FROM student s WHERE s.`S` 
IN(SELECT s1.S FROM 
(SELECT sc.`S` FROM sc WHERE C="01")s1,
(SELECT sc.`S` FROM sc WHERE sc.`S` NOT IN (SELECT sc.`S` FROM sc WHERE C="02"))s2
 WHERE s1.S=s2.S)

查询截图
10、查询没有学全所有课程的同学的信息

SELECT s.* FROM student s JOIN sc ON sc.`S`=s.`S`
GROUP BY sc.`S`
HAVING COUNT(sc.`C`) < (SELECT COUNT(*) FROM course);

查询截图
11、查询至少有一门课与学号为"01"的同学所学相同的同学的信息

SELECT s.* FROM student s,sc
WHERE s.`S`=sc.`S` AND sc.`C` IN
(SELECT sc.`C` FROM sc WHERE sc.`C`=01)
AND NOT s.`S`=01
GROUP BY s.`S`;

查询截图
12、查询和"01"号的同学学习的课程完全相同的其他同学的信息

SELECT s.* FROM student s WHERE s.`S` IN 
(SELECT S FROM sc WHERE S NOT IN
(SELECT S FROM sc WHERE C NOT IN (SELECT C FROM sc WHERE S='01'))
GROUP BY S 
HAVING COUNT(*)=(SELECT COUNT(*)课程数 FROM sc WHERE S='01') AND S != '01');

查询截图
13、查询没学过"张三"老师讲授的任一门课程的学生姓名

SELECT s.S,s.Sname FROM student s,teacher t,sc,course c
WHERE sc.`C`=c.`C` AND t.`T`=c.`T` AND s.`Sname` NOT IN
(SELECT s.`Sname` FROM student s,teacher t,sc,course
  WHERE s.`S`=sc.`S` AND sc.`C`=course.`C` AND course.`T`=t.`T` AND t.`Tname`="张三")
GROUP BY s.`Sname`;

查询截图
14、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

SELECT s.`S`,s.`Sname`,b.ss AS 平均分 FROM student s 
RIGHT JOIN
(SELECT sc.`S`,AVG(score) AS ss FROM sc WHERE score<60
  GROUP BY sc.`S`
  HAVING COUNT(score)>=2
)b 
ON b.S=s.`S`;

查询截图
15、检索"01"课程分数小于60，按分数降序排列的学生信息

SELECT s.*,sc.`score` FROM student s,sc
WHERE s.`S`=sc.`S` AND sc.`score`<60 AND sc.`C`=01
ORDER BY sc.`score` DESC;

查询截图
16、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

SELECT s.*,b.01,b.02,b.03,b.平均成绩 FROM student s LEFT JOIN
(SELECT s,MAX(CASE C WHEN '01' THEN score ELSE 0 END)'01',
	  MAX(CASE C WHEN '02' THEN score ELSE 0 END)'02',
	  MAX(CASE C WHEN '03' THEN score ELSE 0 END)'03',
	  AVG(sc.score)'平均成绩' FROM sc GROUP BY sc.`S`)B 
ON s.`S`=b.S
ORDER BY b.平均成绩 DESC;

查询截图
17、查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
–及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90*/

SELECT DISTINCT c.`C`,c.`Cname`,B.`平均分`,B.`最低分`,B.`最高分`,b.`及格率`
FROM course c
LEFT JOIN 
(SELECT AVG(sc.`score`)'平均分',MAX(sc.`score`)'最高分',MIN(sc.`score`)'最低分',sc.`C`,
CONCAT(ROUND(SUM(CASE WHEN sc.`score`>=60 THEN 1 ELSE 0 END)/COUNT(*)*100,2),'%') AS '及格率' 
FROM sc GROUP BY sc.`C`)B
ON c.`C`=B.C;

查询截图
18、查询学生的总成绩并进行排名

/*SELECT s.`S`,s.`Sname`,SUM(sc.`score`)总成绩 
FROM student s LEFT JOIN sc ON s.`S`=sc.`S`
GROUP BY s.`S`
ORDER BY SUM(sc.`score`) DESC;*/
 
 SELECT S,总成绩,
( SELECT COUNT(*) 
  FROM (SELECT S,SUM(score)AS '总成绩' FROM sc GROUP BY S)AS b 
  WHERE b.总成绩>a.总成绩)+ 1 AS 排名 
FROM (SELECT S,SUM(score)AS 总成绩 FROM sc GROUP BY sc.`S`)AS a
ORDER BY S,总成绩 DESC;

查询截图
19、查询不同老师所教不同课程平均分从高到低显示

SELECT sc.`C`,t.`Tname`,t.`T`,AVG(sc.`score`)平均分 FROM teacher t,sc,course c
WHERE c.`C`=sc.`C` AND c.`T`=t.`T`
GROUP BY sc.`C`
ORDER BY AVG(sc.`score`) DESC;

/*SELECT sc.`C`,t.`Tname`,c.`Cname`,AVG(sc.`score`)平均分,
 concat((select count(*) from sc sc1 where sc1.score>=60 and sc1.C=sc.C)/count(*)*100,"%")
 FROM teacher t,sc,course c
 where c.`C`=sc.`C` and c.`T`=t.`T`
 group by sc.`C`
 order by avg(sc.`score`) desc;*/

查询截图
20、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

SELECT s.*,c1.C,c.`Cname`,c1.score FROM sc c1,course c,student s
WHERE c.`C`=c1.`C` AND s.`S`=c1.`S` AND
    ( SELECT COUNT(*) FROM sc c2 
      WHERE c1.`score`<c2.`score` AND c1.`C`=c2.`C`)BETWEEN 1 AND 2
ORDER BY c1.`C` ASC,c1.`score` DESC;

查询截图
21、查询学生平均成绩及其名次

SELECT S,平均成绩,
( SELECT COUNT(*) 
  FROM (SELECT S,AVG(score)AS '平均成绩' FROM sc GROUP BY S)AS b 
  WHERE b.平均成绩>a.平均成绩)+ 1 AS 排名 
FROM (SELECT S,AVG(score)AS 平均成绩 FROM sc GROUP BY sc.`S`)AS a
ORDER BY S,平均成绩 DESC;

查询截图
22、查询各科成绩前三名的记录

SELECT c1.S,c1.C,c.`Cname`,c1.score FROM sc c1,course c
WHERE c1.`C`=c.`C` AND
    ( SELECT COUNT(*) FROM sc c2 
      WHERE c1.`score`<c2.`score` AND c1.`C`=c2.`C`)<=2
ORDER BY c1.`C` ASC,c1.`score` DESC;

查询截图
23、查询每门课程被选修的学生数

SELECT sc.`C`,COUNT(sc.`S`)学生数 FROM sc GROUP BY sc.`C`;

查询截图
24、查询出只有两门课程的全部学生的学号和姓名

SELECT s.`S`,s.`Sname` FROM student s
WHERE s.`S` IN 
(SELECT `S` FROM (SELECT S,COUNT(sc.`C`)课程数 FROM sc GROUP BY sc.`S`)K
WHERE K.课程数=2)

查询截图
25、查询男生、女生人数

SELECT Ssex,COUNT(Ssex)人数 FROM student GROUP BY Ssex;

查询截图
26、查询名字中含有"风"字的学生信息

SELECT * FROM student
WHERE Sname LIKE '%风%';

查询截图
27、查询1990年出生的学生名单

SELECT * FROM student 
WHERE YEAR(Sage)=1990;

查询截图
28、查询每门课程的平均成绩，结果按平均成绩降序排列，
平均成绩相同时，按课程编号升序排列

SELECT sc.`C`,AVG(score)平均成绩 FROM sc
GROUP BY sc.`C`
ORDER BY 平均成绩 DESC,sc.`C`;

查询截图
29、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT s.`S`,s.`Sname`,b.平均成绩 FROM student s LEFT JOIN 
(SELECT sc.`S`,AVG(score)平均成绩 FROM sc GROUP BY sc.`S`)b
ON s.`S`=b.`S`
WHERE b.平均成绩>=85;

查询截图
30、查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT s.`Sname`,sc.`score` FROM student s,course c,sc
WHERE s.`S`=sc.`S` AND sc.`C`=c.`C`
AND c.`Cname`='数学' AND sc.`score`<60;

查询截图
31、查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT s.`Sname`,c.`Cname`,sc.`score` FROM student s,sc,course c
WHERE s.`S`=sc.`S` AND sc.`C`=c.`C`
AND sc.`score`>70;

查询截图
32、求每门课程的学生人数（同23）

SELECT c.`C`,c.`Cname`,COUNT(s.`S`)学生人数 FROM course c,student s,sc
WHERE s.`S`=sc.`S` AND sc.`C`=c.`C`
GROUP BY c.`Cname`;

查询截图
33、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT s.*,MAX(sc.`score`)成绩 FROM student s,sc,course c,teacher t
WHERE s.`S`=sc.`S` AND sc.`C`=c.`C` AND c.`T`=t.`T`
AND t.`Tname`='张三';

查询截图
34、统计每门课程的学生选修人数（超过5人的课程才统计）。
要求输出课程号和选修人数，查询结果按人数降序排列，
若人数相同，按课程号升序排列

SELECT c.`C`,COUNT(s.`S`)学生人数 FROM course c,student s,sc
WHERE s.`S`=sc.`S` AND sc.`C`=c.`C`
GROUP BY sc.`C`
HAVING COUNT(s.`S`)>5;

查询截图
35、查询选修了全部课程的学生信息

SELECT s.*,COUNT(*)课程数 FROM sc,student s
WHERE s.`S`=sc.`S`
GROUP BY sc.`S`
HAVING COUNT(*)=(SELECT COUNT(*) FROM course)

查询截图
36、查询各学生的年龄

SELECT s.`S`,s.Sname,TIMESTAMPDIFF(YEAR,s.Sage,CURDATE()) AS 年龄
FROM student s;

查询截图
37、附加、查询本周过生日的学生

SELECT * FROM student s
WHERE WEEKOFYEAR(DATE_FORMAT(NOW(),'%Y%m%d'))
      =
      WEEKOFYEAR(DATE_FORMAT(Sage,'%Y%m%d')
	  -
	  DATE_FORMAT(Sage,'%Y')*10000+DATE_FORMAT(NOW(),'%Y')*10000)

查询截图