11111

1) 查询所有学生的学号、姓名、年龄:

SELECT sno, sname, sage
FROM Student;

2) 查询年龄大于 20 的学生学号、姓名、年龄:

SELECT sno, sname, sage
FROM Student
WHERE sage > 20;

3) 查询所有姓'张'的学生学号、姓名、年龄:

SELECT sno, sname, sage
FROM Student
WHERE sname LIKE '张%';

4) 查询计算机系每个同学的平均成绩:

SELECT s.sno, s.sname, AVG(so.grade) AS avg_grade
FROM Student s
INNER JOIN so ON s.sno = so.sno
INNER JOIN Course c ON so.cno = c.cno
WHERE s.sdept = '计算机系'
GROUP BY s.sno, s.sname;

5) 查询计算机系中年龄小于所有学生平均年龄的学号、姓名:

SELECT s.sno, s.sname
FROM Student s
WHERE s.sdept = '计算机系'
AND s.sage < (SELECT AVG(sage) FROM Student);

6) 查询学生成绩单，成绩单包括学号、姓名、课号、课名、成绩:

SELECT s.sno, s.sname, c.cno, c.cname, so.grade
FROM Student s
INNER JOIN so ON s.sno = so.sno
INNER JOIN Course c ON so.cno = c.cno;

7) 查询所有课程的课号、课名、学号、成绩(包括没有被任何学生学习的课程):

SELECT c.cno, c.cname, so.sno, so.grade
FROM Course c
LEFT JOIN so ON c.cno = so.cno;

8) 增加student表学生记录('20191534','赵浩',20):

INSERT INTO Student (sno, sname, sage)
VALUES ('20191534', '赵浩', 20);

9) 删除so表中学号为201915123的学生学习课号为C1的记录:

DELETE FROM so
WHERE sno = '201915123'
AND cno = 'C1';

10) 修改course表中课号为'C1'的课名为'数据库':

UPDATE Course
SET cname = '数据库'
WHERE cno = 'C1';