假设学生表studentsidnaneage课程表coursecidname成绩表scsidcidgrade

1. 查询所有学生的姓名和年龄

SELECT name, age FROM student;

2. 查询所有课程的名称和编号

SELECT cid, name FROM course;

3. 查询所有学生的姓名、课程名称以及成绩

SELECT student.name, course.name, sc.grade FROM student JOIN sc ON student.sid = sc.sid JOIN course ON sc.cid = course.cid;

4. 查询所有选修了特定课程的学生的姓名和年龄

SELECT student.name, student.age FROM student JOIN sc ON student.sid = sc.sid JOIN course ON sc.cid = course.cid WHERE course.name = '特定课程名称';

5. 查询所有没有选修特定课程的学生的姓名和年龄

SELECT student.name, student.age FROM student LEFT JOIN sc ON student.sid = sc.sid AND sc.cid = '特定课程编号' WHERE sc.sid IS NULL;

6. 查询所有学生的平均成绩

SELECT student.name, AVG(sc.grade) FROM student JOIN sc ON student.sid = sc.sid GROUP BY student.name;

7. 查询特定课程的平均成绩

SELECT course.name, AVG(sc.grade) FROM course JOIN sc ON course.cid = sc.cid GROUP BY course.name;

8. 查询选修了特定课程且成绩高于某个阈值的学生的姓名和成绩

SELECT student.name, sc.grade FROM student JOIN sc ON student.sid = sc.sid JOIN course ON sc.cid = course.cid WHERE course.name = '特定课程名称' AND sc.grade > '阈值';

9. 查询每个学生选修的课程数量

SELECT student.name, COUNT(sc.cid) FROM student LEFT JOIN sc ON student.sid = sc.sid GROUP BY student.name;

10. 查询成绩表中成绩最高的学生的姓名、课程名称和成绩

SELECT student.name, course.name, sc.grade FROM student JOIN sc ON student.sid = sc.sid JOIN course ON sc.cid = course.cid WHERE sc.grade = (SELECT MAX(grade) FROM sc)