数据基本查询
1) 查询全体学生的学号和姓名
SELECT Sname, Sno
FROM Student;
2) 查询全体学生的详细记录
SELECT*
FROM Student
3) 查询所有选修过课程的学生学号
SELECT DISTINCT SC.Sno
FROM SC;
或者
SELECT Sno
FROM Student
WHERE Sdept IS NOT NULL;
4) 查询考试有不及格的学生学号
SELECT DISTINCT SC.Sno
FROM SC
WHERE Grade < 60;
5) 查询不是信息系(IS)、计算机系(CS)的学生性别、年龄、系别
SELECT Ssex,Sage,Sdept
FROM Student
WHERE Sdept != "IS" AND Sdept != "CS" ;
6) 查询选修了4号课的学生学号和成绩,结果按成绩降序排列
SELECT SC.Sno ,SC.Grade
FROM SC
WHERE Cno = '004' ORDER BY Grade DESC;
7) 查询每个课程号和相应的选课人数
SELECT Cno,COUNT(Sno)
FROM SC
GROUP BY Cno;
8) 查询计算机系(CS)的学生姓名、年龄、系别
SELECT Sname,Sage,Sdept
FROM Student
WHERE Sdept='CS';
9) 查询年龄18-20岁的学生学号、姓名、系别、年龄;
SELECT Sno,Sname,Sdept,Sage
FROM Student
WHERE Sage BETWEEN 18 AND 20 ;
10) 查询姓刘的学生情况
SELECT *
FROM Student
WHERE Sname LIKE "刘%";
11) 查询学生的姓名和出生年份(今年2022年)
SELECT Sname ,2022-Sage AS "出生年份"
FROM Student;
12) 查询没有成绩的学生学号和课程号
SELECT Sno , Cno
FROM SC
WHERE Grade IS NULL;
13) 查询总成绩大于200分的学生学号
SELECT Sno
FROM SC
GROUP BY Sno HAVING SUM(Grade)>200;
14) 查询每门课程不及格学生人数
SELECT Cno , COUNT(*) AS "不及格人数"
FROM SC
WHERE Grade < 60 GROUP BY cno;
15) 查询不及格课程超过3门的学生学号
SELECT Sno
FROM SC
WHERE Grade <60 GROUP BY Sno HAVING COUNT(*)>=3 ;
或者
SELECT Sno
FROM SC GROUP BY Sno HAVING SUM(Group<60)>3;
16) 查询年龄在10到19岁之间的学生信息
SELECT *
FROM Student
WHERE Sage BETWEEN 10 AND 19;
17) 查询全体学生情况,按所在系升序排列,同一个系的学生按年龄降序排列
SELECT *
FROM Student
ORDER BY Sdept ASC , Sage DESC ;
18) 查询选了1号课程的学生平均成绩
SELECT AVG(Grade) AS "平均成绩"
FROM SC
WHERE Cno = '001';
19) 查询选了3号课程的学生的最高分
SELECT MAX(Grade) AS "最高分"
FROM SC
WHERE Cno = '003';
20) 查询每个同学的总成绩
SELECT Student.Sno ,SUM(Grade) AS "总成绩"
FROM SC INNER JOIN Student ON Student.Sno=SC.Sno GROUP BY SC.Sno;
或者
SELECT Sno,SUM(Grade) AS "总成绩"
FROM SC GROUP BY Sno;