CREATE TABLE student stuid number GENERATED BY DEFAULT ON NULL AS IDENTITY stuname VARCHAR20 NOT NULL idcard VARCHAR11 UNIQUE stusex char1 check stusex inFM stuage number cid number ;INSERT

1、查询和余权明'在一个班的所有学生信息

SELECT * FROM student WHERE cid = (SELECT cid FROM student WHERE stuname = '余权明');

2、查询‘C语言’课程的最高成绩

SELECT MAX(score) FROM score WHERE subid = (SELECT subid FROM subjects WHERE subname = 'C语言');

3、查询各个学科的最高分最低分和平均分

SELECT subname, MAX(score) as max_score, MIN(score) as min_score, AVG(score) as avg_score FROM subjects s JOIN score sc ON s.subid = sc.subid GROUP BY subname;

4、查询所有参加过考试的学生的基本信息

SELECT * FROM student WHERE stuid IN (SELECT DISTINCT stuid FROM score);

5、查询参加过html考试的所有学生的基本信息

SELECT * FROM student WHERE stuid IN (SELECT stuid FROM score WHERE subid = (SELECT subid FROM subjects WHERE subname = 'HTML'));

6、查询平均成绩高于80分的学生信息

SELECT * FROM student WHERE stuid IN (SELECT stuid FROM score GROUP BY stuid HAVING AVG(score) > 80);

7、查询java成绩最高的学生的基本信息

SELECT * FROM student WHERE stuid = (SELECT stuid FROM score WHERE subid = (SELECT subid FROM subjects WHERE subname = 'JAVA基础') ORDER BY score DESC LIMIT 1);

8、查询java成绩高于java学科平均成绩的学生信息

SELECT * FROM student WHERE stuid IN (SELECT stuid FROM score WHERE subid = (SELECT subid FROM subjects WHERE subname = 'JAVA基础') AND score > (SELECT AVG(score) FROM score WHERE subid = (SELECT subid FROM subjects WHERE subname = 'JAVA基础')))