数据分析 NO.6 SQL练习题

TeddyBob

已于 2022-04-26 22:11:00 修改

阅读量839

点赞数 2

文章标签： sql

于 2019-05-17 00:46:30 首次发布

本文链接：https://blog.csdn.net/TeddyBob/article/details/90279071

版权

SQL练习题
– 1、查询Student表中的所有记录的Sname、Ssex和Class列。

SELECT Sname,Ssex,class FROM exercises2.student;

– 2、查询教师所有的单位即不重复的Depart列。

SELECT DISTINCT(Depart) FROM exercises2.teacher;

– 3、查询Student表的所有记录。

SELECT * FROM exercises2.student;

– 4、查询Score表中成绩在60到80之间的所有记录。

SELECT degree FROM exercises2.score
WHERE degree>=60 AND degree<=80;
老师参考答案：
WHERE degree BETWEEN 60 AND 80;

– 5、查询Score表中成绩为85，86或88的记录。

SELECT degree FROM exercises2.score
WHERE degree IN (85,86,88);

SELECT degree FROM exercises2.score
WHERE degree=85 OR degree=86 OR degree=86=88;

– 6、查询Student表中“95031”班或性别为“女”的同学记录。

SELECT * FROM exercises2.student
WHERE class="95031" OR ssex="女";

– 7、以Class降序查询Student表的所有记录。

SELECT * FROM exercises2.student
ORDER BY class;

– 8、以Cno升序、Degree降序查询Score表的所有记录。

SELECT * FROM exercises2.score
ORDER BY cno,degree DESC;

– 9、查询“95031”班的学生人数。

SELECT count(*) FROM exercises2.student
WHERE class="95031";

– 10、查询Score表中的最高分的学生学号和课程号。

SELECT sno,cno FROM exercises2.score
ORDER BY degree DESC
LIMIT 1;

如果有相同的最高分，上述方法不完整，建议下面方法使用子查询的方法。
SELECT sno,cno FROM exercises2.score
WHERE degree IN (SELECT MAX(degree) FROM exercises2.score  )

– 11、查询‘3-105’号课程的平均分。

SELECT cno,AVG(degree) FROM exercises2.score
GROUP BY CNO
HAVING CNO="3-105";

– 12、查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

SELECT cno,AVG(degree) FROM exercises2.score
GROUP BY CNO
HAVING count( CNO="3%")>=5;

– 13、查询最低分大于70，最高分小于90的Sno列。

SELECT sno FROM exercises2.score
GROUP BY sno
having MAX(degree)<90 AND MIN(degree)>70;

– 14、查询所有学生的Sname、Cno和Degree列。

SELECT Sname,Cno,Degree FROM exercises2.student,exercises2.score
WHERE exercises2.student.sno=exercises2.score.sno;

– 15、查询所有学生的Sno、Cname和Degree列。

SELECT Sno,Cname,Degree 
FROM exercises2.score,exercises2.course
WHERE exercises2.score.cno=exercises2.course.cno;

– 16、查询所有学生的Sname、Cname和Degree列。

SELECT Sname,Cname,Degree 
FROM exercises2.score,exercises2.student,exercises2.course
WHERE exercises2.score.sno=exercises2.student.sno 
  AND exercises2.score.cno=exercises2.course.cno;

– 17、查询“95033”班所选课程的平均分。

SELECT AVG(Degree) 
FROM exercises2.score,exercises2.student
WHERE exercises2.score.sno=exercises2.student.sno 
GROUP BY class
having class="95033"

SELECT AVG(degree) FROM exercises2.score
join 
exercises2.student
on  exercises2.score.sno=exercises2.student.sno
where class="95033";

– 18、假设使用如下命令建立了一个grade表：
– create table grade(low INT(3),upp INT(3),rank VARCHAR(2));
– insert into grade values(90,100,‘A’);
– insert into grade values(80,89,‘B’);
– insert into grade values(70,79,‘C’);
– insert into grade values(60,69,‘D’);
– insert into grade values(0,59,‘E’);
– commit;
– 现查询所有同学的Sno、Cno和rank列。

SELECT Sno,Cno,rank FROM exercises2.score
JOIN
exercises2.grade
ON  exercises2.score.degree BETWEEN low AND upp;

– 19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

SELECT * FROM exercises2.score 
WHERE cno="3-105" 
AND 
exercises2.score.degree>(SELECT DISTINCT degree FROM exercises2.score WHERE sno="109" AND cno="3-10

– 20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。

SELECT * FROM exercises2.score
WHERE degree<( SELECT MAX(degree) FROM exercises2.score) 
AND  CNO IN (SELECT CNO FROM exercises2.score GROUP BY CNO HAVING count(cno)>1)
老师答案：
SELECT * FROM score
GROUP BY sno
HAVING COUNT(cno) > 1
AND degree < MAX(degree);

– 21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。

SELECT * FROM exercises2.score
WHERE degree>(SELECT DISTINCT degree FROM exercises2.score WHERE sno="109" AND cno="3-105")

– 22、查询和学号为108的同学同年出生的所有学生的Sno、Sname和Sbirthday列。

SELECT sno,sname,sbirthday FROM exercises2.student
WHERE YEAR(sbirthday)=(SELECT YEAR(sbirthday) FROM exercises2.student WHERE sno="108")

– 23、查询“张旭“教师任课的学生成绩。

SELECT DISTINCT(degree) FROM exercises2.score 
JOIN 
exercises2.course
ON exercises2.score.cno=exercises2.course.cno
JOIN
exercises2.teacher
ON exercises2.course.tno=exercises2.teacher.tno

– 24、查询选修某课程的同学人数多于5人的教师姓名。

SELECT DISTINCT tname FROM exercises2.teacher
JOIN
exercises2.course 
on exercises2.course.tno=exercises2.teacher.tno
JOIN
(SELECT cno FROM exercises2.score GROUP BY cno HAVING COUNT(CNO)>5) a
on a.cno=exercises2.course .cno
老师答案：
SELECT tname FROM score AS a
JOIN (course AS b , teacher AS c)
ON a.cno = b.cno AND b.tno = c.tno
GROUP BY a.cno 
HAVING COUNT(a.sno) >5;

– 25、查询95033班和95031班全体学生的记录。

SELECT * FROM exercises2.student
WHERE class="95033" OR class="95031";

– 26、查询存在有85分以上成绩的课程Cno.

SELECT cno FROM score
GROUP BY cno
HAVING MAX(degree) > 85

– 27、查询出“计算机系“教师所教课程的成绩表。

SELECT DISTINCT depart,degree FROM exercises2.score 
JOIN
exercises2.course
ON exercises2.score.cno=exercises2.course.cno
JOIN
exercises2.teacher
ON exercises2.course.tno =exercises2.teacher.tno
WHERE exercises2.teacher.depart="计算机系"

– 28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

SELECT tname,prof FROM teacher 
WHERE depart in ('计算机系','电子工程系')
GROUP BY prof 
HAVING COUNT(depart) = 1;

– 29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的Cno、Sno
– 和Degree,并按Degree从高到低次序排序。 至少关于没有理解正确感觉 老师讲解就是any的用法

SELECT Cno,Sno,Degree FROM exercises2.score
WHERE cno="3-105" AND Degree>(SELECT MIN(degree) FROM exercises2.score WHERE cno ="3-245")
ORDER BY  Degree DESC;
老师答案：
SELECT cno,sno,degree FROM score
WHERE cno = '3-105'
AND degree > ANY(SELECT degree FROM score
WHERE cno = '3-245')
ORDER BY degree ASC;

– 30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

SELECT Cno,Sno,Degree FROM exercises2.score
WHERE cno="3-105" AND Degree>(SELECT MIN(degree) FROM exercises2.score WHERE cno ="3-245")
老师答案：
SELECT cno,sno,degree FROM score
WHERE cno in ('3-245','3-105')
GROUP BY sno
HAVING MAX(cno) = '3-105';

– 31、查询所有教师和同学的name、sex和birthday. 感觉子查询要通过中间的表，好像也行

SELECT  tname as name,tsex as sex,tbirthday as birthday FROM exercises2.teacher
UNION
SELECT  sname as name,ssex as sex,sbirthday as birthday FROM exercises2.student

– 32、查询所有“女”教师和“女”同学的name、sex和birthday.

SELECT  tname as name,tsex as sex,tbirthday as birthday FROM exercises2.teacher
WHERE tsex="女"
UNION
SELECT  sname as name,ssex as sex,sbirthday as birthday FROM exercises2.student
WHERE ssex="女"

– 33、查询成绩比该课程平均成绩低的同学的成绩表。

SELECT * FROM exercises2.score
WHERE degree<all(SELECT avg(degree) FROM exercises2.score GROUP BY cno)
老师答案：
SELECT a.* FROM score AS a
JOIN (SELECT cno,AVG(degree) AS a_degree FROM score
GROUP BY cno) AS b
ON a.cno = b.cno
WHERE a.degree < b.a_degree;

自我总结：这里的all应该是吧里面的每个都拿出来比较

– 34、查询所有任课教师的Tname和Depart.

SELECT Tname,Depart FROM exercises2.teacher
WHERE tno IN (SELECT DISTINCT tno FROM exercises2.course)

– 35 查询所有未讲课的教师的Tname和Depart.

SELECT Tname,Depart FROM exercises2.teacher
WHERE tno NOT IN (SELECT DISTINCT tno FROM exercises2.course)

– 36、查询至少有2名男生的班号。

SELECT class FROM exercises2.student
GROUP BY class
HAVING COUNT("男")>=2
老师答案：
SELECT class FROM student
WHERE ssex = '男'
GROUP BY class
HAVING COUNT(sno) >= 2;

– 37、查询Student表中不姓“王”的同学记录。

SELECT * FROM exercises2.student
WHERE sname not like "王%"

– 38、查询Student表中每个学生的姓名和年龄。

SELECT Sname,Year(Curdate())-YEAR(sbirthday)  as age FROM exercises2.student

– 39、查询Student表中最大和最小的Sbirthday日期值。

SELECT MAX(sbirthday),MIN(sbirthday) FROM exercises2.student

– 40、以班号和年龄从大到小的顺序查询Student表中的全部记录。

SELECT * FROM exercises2.student
ORDER BY class DESC,sbirthday

– 41、查询“男”教师及其所上的课程。

SELECT DISTINCT tname,cname FROM exercises2.teacher
JOIN
exercises2.course
ON exercises2.teacher.tno=exercises2.course.tno
WHERE tsex="男"

– 42、查询最高分同学的Sno、Cno和Degree列。

SELECT Sno,Cno,Degree FROM exercises2.score
ORDER BY degree DESC
LIMIT 1

上述方法不严谨
SELECT Sno,Cno,Degree FROM exercises2.score
WHERE Degree in (SELECT MAX(Degree) FROM exercises2.score)

– 43、查询和“李军”同性别的所有同学的Sname.

SELECT Sname FROM exercises2.student
WHERE ssex=(SELECT ssex FROM exercises2.student WHERE Sname="李军")

– 44、查询和“李军”同性别并同班的同学Sname.

SELECT Sname FROM exercises2.student
WHERE ssex=(SELECT ssex FROM exercises2.student WHERE Sname="李军") 
AND class=(SELECT class FROM exercises2.student WHERE Sname="李军")
老师答案：
#自链接的方法
SELECT a.sname FROM student AS a
JOIN student AS b
ON (a.ssex = b.ssex AND a.class = b.class)
WHERE b.sname = '李军';

– 45、查询所有选修“计算机导论”课程的“男”同学的成绩表

SELECT DISTINCT degree FROM exercises2.score
JOIN
(SELECT cno FROM exercises2.course WHERE cname="计算机导论") a
on exercises2.score.cno=a.cno
join
(SELECT sno FROM exercises2.student WHERE ssex="男") b
on b.sno=exercises2.score.sno
老师答案：
SELECT b.* FROM student AS a
JOIN (score AS b,course AS c)
ON a.sno = b.sno AND b.cno = c.cno
WHERE c.cname = '计算机导论'
AND a.ssex = '男';