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 in (85,86,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 cno asc,degree desc;
9. 查询“95031”班的学生人数。
select count(*) as 学生人数 from Student where class="95031"
10. 查询 Score 表中的最高分的学生学号和课程号。
select cno,sno from Score order by degree desc limit 1;
11. 查询每门课的平均成绩。
select cno,avg(degree) from Score GROUP BY cno;
12. 查询分数大于 70，小于 90 的 Sno 列。
select sno,degree from score where degree > 70 and degree < 90;
13. 查询所有学生的 Sname、Cno 和 Degree 列。
SELECT
sname,
cno,
degree
FROM
student
INNER JOIN score ON score.sno = Student.sno;
14. 查询所有学生的 Sno、Cname 和 Degree 列。
SELECT
sno,
cname,
degree
FROM
course
INNER JOIN score ON score.cno = course.cno;
15. 查询所有学生的 Sname、Cname 和 Degree 列。
SELECT
sname,
cname,
degree
FROM
Student,
course,
Score
WHERE
Student.sno = score.sno
AND course.cno = score.cno;
SELECT
sname,
cname,
degree
FROM
course
INNER JOIN score ON score.cno = course.cno
INNER JOIN student ON score.sno = Student.sno;
16. 查询 95033 班和 95031 班全体学生的记录。
SELECT
*
FROM
course
INNER JOIN score ON score.cno = course.cno
INNER JOIN student ON score.sno = Student.sno
WHERE
class = "95031"
OR class = "95033";
17. 查询存在有 85 分以上成绩的课程 Cno。
select cno from score WHERE degree > 85;
18. 查询 Student 表中不姓“王”的同学记录。
select * from student WHERE sname not like "王%";
19. 以班号和年龄从大到小的顺序查询 Student 表中的全部记录。
select * from student order by class desc,sbirthday asc;
快速跳转
