mysql exercise two
大家可以采用SQLyog、Navicat 数据库可视化工具进行查看
用于以下试题使用的数据表地址:
mysql exercise ------ two 数据库练习参考资源地址
1、 查询Student表中的所有记录的Sname、Ssex和Class列。
SELECT Sname,Ssex,Class FROM Student;
2、 查询教师所有的单位即不重复的Depart列。
SELECT DISTINCT Depart FROM Teacher;
3、 查询Student表的所有记录。
SELECT * FROM Student;
4、 查询Score表中成绩在60到80之间的所有记录。
SELECT * FROM Score WHERE Degree BETWEEN 60 AND 80;
5、 查询Score表中成绩为85,86或88的记录。
SELECT * FROM Score WHERE Degree=85 OR Degree=86 OR Degree=88;
6、 查询Student表中“95031”班或性别为“女”的同学记录。
SELECT * FROM Student WHERE Class=95031 OR Ssex=“女”;
7、 以Class降序查询Student表的所有记录。
SELECT * FROM Student ORDER BY Class DESC;
8、 以Cno升序、Degree降序查询Score表的所有记录。
SELECT * FROM Score ORDER BY Degree DESC;
9、 查询“95031”班的学生人数。
SELECT COUNT(*) FROM Student WHERE Class=95031;
10、 查询Score表中的最高分的学生学号和课程号。(子查询或者排序)
SELECT s.Sno
,s.Cno
FROM Score s WHERE Degree= (SELECT MAX(Degree) FROM Score);
11、 查询每门课的平均成绩。
SELECT Cno,AVG(Degree) FROM Score GROUP BY Cno;
12、查询Score表中至少有5名学生修的并以3开头的课程的平均分数。
SELECT * FROM Score WHERE cno IN (SELECT cno FROM Score GROUP BY Cno HAVING COUNT(Sno)>=5);
13、查询分数大于70,小于90的Sno列。
SELECT Sno FROM Score WHERE Degree BETWEEN 70 AND 90;
14、查询所有学生的Sname、Cno和Degree列。
SELECT s.Sname
,a.cno,a.degree FROM Student s INNER JOIN (SELECT * FROM Score)a ON s.Sno
=a.sno;
15、查询所有学生的Sno、Cname和Degree列。
SELECT s.Sno
,c.Cname
,s.Degree
FROM Course c INNER JOIN Score s ON c.cno=s.cno;
16、查询所有学生的Sname、Cname和Degree列。
SELECT s.Sname
,a.cname,a.degree FROM Student s INNER JOIN (SELECT b.Sno
,c.Cname
,b.Degree
FROM Course c INNER JOIN Score b ON c.cno=b.cno)a ON s.Sno
=a.sno;
17、 查询“95033”班学生的平均分。
SELECT AVG(Degree) FROM Student s JOIN Score a ON s.Sno
=a.Sno
WHERE s.Class
=95033;
18、查询和“李军”同性别的所有同学的Sname.
SELECT Sname FROM Student WHERE Ssex=(SELECT Ssex FROM Student WHERE sname LIKE ‘李军’ );
19、查询和“李军”同性别并同班的同学Sname.
SELECT Sname FROM Student WHERE Ssex=(SELECT Ssex FROM Student WHERE sname LIKE ‘李军’ )
AND Class=(SELECT Class FROM Student WHERE sname LIKE ‘李军’ );
20、查询score中选学多门课程的同学中分数为非最高分成绩的记录。
SELECT * FROM Score a WHERE Sno IN (SELECT sno FROM Score GROUP BY Sno HAVING COUNT(Sno)>1) AND Degree<(SELECT MAX(degree) FROM Score b WHERE b.Cno
=a.Cno
);
21、 查询课程号为“3-105”的成绩高于学号为“109”的所有记录。
SELECT * FROM Score WHERE Degree>(SELECT Degree FROM Score WHERE Cno=“3-105” AND Sno=109) AND cno=“3-105” ;
22、查询所有选修“计算机导论”课程的“男”同学的成绩表。
SELECT * FROM Student s,Score sc,Course c WHERE s.Sno
=sc.Sno
AND sc.Cno
=c.Cno
AND c.Cname
=“计算机导论” AND s.Ssex
=“男” ;
23、查询“张旭“教师任课的学生成绩。
SELECT * FROM Score WHERE Cno=(SELECT Cno FROM Course WHERE Tno=(SELECT Tno FROM Teacher WHERE Tname=“张旭”));
24、查询选修某课程的同学人数多于5人的教师姓名。
SELECT Tname FROM Teacher WHERE Tno IN (SELECT Tno FROM Course WHERE Cno IN (SELECT Cno FROM Score sc GROUP BY Cno HAVING COUNT(Cno)>5));
25、查询95033班和95031班全体学生的记录。
SELECT * FROM Student WHERE Class IN (95033,95031);
26、 查询存在有85分以上成绩的课程Cno.
SELECT Cno,Degree FROM Score WHERE Degree>=85;
27、查询出“计算机系“教师所教课程的成绩表。
SELECT Cno FROM Course WHERE Tno=(SELECT Tno FROM Teacher WHERE Depart=“计算机系”);
28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。
SELECT Tname,Prof FROM Teacher WHERE Depart IN(“计算机系”,“电子工程系”) GROUP BY Prof HAVING COUNT(prof)<2;
30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.
SELECT * FROM Score a WHERE Cno=“3-105” AND Degree>(SELECT b.Degree FROM Score b WHERE Cno=“3-245” AND a.Sno
=b.Sno
);
31、 查询所有教师和同学的name、sex和birthday.
SELECT s.Sname
,s.Ssex
,s.Sbirthday
,t.Tname
,t.Tsex
,t.Tbirthday
FROM Student s,Teacher t;
32、查询所有“女”教师和“女”同学的name、sex和birthday.
select s.Sname
,s.Ssex
,s.Sbirthday
,t.Tname
,t.Tsex
,t.Tbirthday
from Student s,Teacher t where s.Ssex
=“女” and t.Tsex
=“女”;
33、 查询成绩比该课程平均成绩低的同学的成绩表。
SELECT * FROM Score a WHERE Degree<(SELECT AVG(Degree) FROM Score b GROUP BY Cno having a.Cno
=b.Cno
);
34、 查询所有任课教师的Tname和Depart.
select t.Tname
,t.Depart
from Teacher t where Tno in(SELECT Tno FROM Course WHERE cno IN (SELECT DISTINCT Cno FROM Score));
35 、 查询所有未讲课的教师的Tname和Depart.
select t.Tname
,t.Depart
from Teacher t where Tno in(SELECT Tno FROM Course WHERE cno not IN (SELECT DISTINCT Cno FROM Score));
36、查询至少有2名男生的班号。
SELECT Class FROM Student WHERE Ssex=‘男’ GROUP BY Class HAVING COUNT(Ssex)>=2;
37、查询Student表中不姓“王”的同学记录。
select * from Student where Sname not LIKE “王%”;
38、查询Student表中每个学生的姓名和年龄。
SELECT Sname,Ssex FROM Student;
39、查询Student表中最大和最小的Sbirthday日期值。
select max(Sbirthday),min(Sbirthday) from Student;
40、以班号和年龄从大到小的顺序查询Student表中的全部记录。
SELECT * FROM Student ORDER BY Class DESC,Sbirthday ASC;
41、查询“男”教师及其所上的课程。
select c.Cname
,a.tname from Course c inner join (SELECT * FROM Teacher WHERE Tsex=“男”)a on c.Tno
=a.tno;
42、查询最高分同学的Sno、Cno和Degree列。
select * from Score where Degree=(select max(Degree) from Score);
在这里欢迎大家的点赞、关注、评论,以此来促进大家互相学习交流,同时可以让新加入的小伙伴更快的了解新知识!!!
以上内容如有侵权,请联系作者进行删除
≧◠◡◠≦ 1分2分都是爱,感谢已经打赏的老板,和正在打赏的老板们 ≧◠◡◠≦