SQL数据库练习：学生信息管理系统

CREATE TABLE student(\n stuid number GENERATED BY DEFAULT ON NULL AS IDENTITY ,\n stuname VARCHAR(20) NOT NULL ,\n idcard VARCHAR(11) UNIQUE ,\n stusex char(1) check (stusex in('F','M')),\n stuage number ,\n cid number \n);\n\nINSERT INTO student VALUES(null,'余权明','74110','F',18,1001)\nINSERT INTO student VALUES(null,'余权红','74111','M',19,1001)\nINSERT INTO student VALUES(null,'余权黄','74112','M',18,1002)\nINSERT INTO student VALUES(null,'博文','74113','F',16,1002)\nINSERT INTO student VALUES(null,'小蓝','74114','M',17,1003); \n\nSELECT * FROM STUDENT;\ndrop table STUDENT\n\n\nCREATE TABLE classes (\n cid number PRIMARY KEY ,\n cname VARCHAR(20)\n);\n\nINSERT INTO classes VALUES(1001,'st2201');\nINSERT INTO classes VALUES(1002,'st2202');\nINSERT INTO classes VALUES(1003,'st2301');\nINSERT INTO classes VALUES(1004,'st2302');\n\n\nCREATE TABLE subjects(\n subid INT PRIMARY KEY,\n subname VARCHAR(20) UNIQUE \n);\n\nINSERT INTO subjects(subid,subname)\nVALUES (101,'HTML');\n\nINSERT INTO subjects(subid,subname)\nVALUES (102,'C语言')\nINSERT INTO subjects(subid,subname)\nVALUES(103,'JAVA基础')\nINSERT INTO subjects(subid,subname)\nVALUES(104,'MySQL基础');\n\n\nCREATE TABLE score(\n scid number GENERATED BY DEFAULT ON NULL AS IDENTITY,\n stuid number,\n subid number,\n score number\n);\nINSERT INTO SCORE \nVALUES(NULL,1,101,90)\n\nINSERT INTO SCORE \nVALUES(NULL,1,102,60)\n\nINSERT INTO SCORE \nVALUES(NULL,1,103,70)\n\nINSERT INTO SCORE \nVALUES(NULL,2,101,80)\n\nINSERT INTO SCORE \nVALUES(NULL,2,102,50)\n\nINSERT INTO SCORE \nVALUES(NULL,2,103,40)\n\nINSERT INTO SCORE \nVALUES(NULL,3,101,50)\n\nINSERT INTO SCORE \nVALUES(NULL,3,102,80)\n\nINSERT INTO SCORE \nVALUES(NULL,3,103,90)\n\nINSERT INTO SCORE \nVALUES(NULL,4,101,90)\n\nINSERT INTO SCORE \nVALUES(NULL,4,102,80)\n\nINSERT INTO SCORE \nVALUES(NULL,5,101,90)\nSELECT * FROM SCORE;\n\n请按要求完成如下练习：\n1、查询和余权明'在一个班的所有学生信息\n2、查询‘C语言’课程的最高成绩\n3、查询各个学科的最高分最低分和平均分\n4、查询所有参加过考试的学生的基本信息\n5、查询参加过html考试的所有学生的基本信息\n6、查询平均成绩高于80分的学生信息\n7、查询java成绩最高的学生的基本信息\n8、查询java成绩高于java学科平均成绩的学生信息内容：1、查询和余权明'在一个班的所有学生信息\n\nSELECT * FROM student WHERE cid = (SELECT cid FROM student WHERE stuname = '余权明');\n\n2、查询‘C语言’课程的最高成绩\n\nSELECT MAX(score) FROM score WHERE subid = (SELECT subid FROM subjects WHERE subname = 'C语言');\n\n3、查询各个学科的最高分最低分和平均分\n\nSELECT subname, MAX(score) as max_score, MIN(score) as min_score, AVG(score) as avg_score\nFROM subjects s\nJOIN score sc ON s.subid = sc.subid\nGROUP BY subname;\n\n4、查询所有参加过考试的学生的基本信息\n\nSELECT * FROM student WHERE stuid IN (SELECT DISTINCT stuid FROM score);\n\n5、查询参加过html考试的所有学生的基本信息\n\nSELECT * FROM student WHERE stuid IN (SELECT stuid FROM score WHERE subid = (SELECT subid FROM subjects WHERE subname = 'HTML'));\n\n6、查询平均成绩高于80分的学生信息\n\nSELECT * FROM student WHERE stuid IN (SELECT stuid FROM score GROUP BY stuid HAVING AVG(score) > 80);\n\n7、查询java成绩最高的学生的基本信息\n\nSELECT * FROM student WHERE stuid = (SELECT stuid FROM score WHERE subid = (SELECT subid FROM subjects WHERE subname = 'JAVA基础') ORDER BY score DESC LIMIT 1);\n\n8、查询java成绩高于java学科平均成绩的学生信息\n\nSELECT * 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基础')));