SQL-经典练习

--Createtable学生信息createtableHAND_STUDENT(STUDENT_NOVARCHAR2(10)notnull,STUDENT_NAMEVARCHAR2(20),STUDENT_AGENUMBER(2),STUDENT_GENDERVARCHAR2(5));--AddcommentstothetablecommentontableHAND_STUDENTis'学生信息表';--AddcommentstothecolumnscommentoncolumnHAND_STUDENT.STUDENT_NOis'学号';commentoncolumnHAND_STUDENT.STUDENT_NAMEis'姓名';commentoncolumnHAND_STUDENT.STUDENT_AGEis'年龄';commentoncolumnHAND_STUDENT.STUDENT_GENDERis'性别';--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableHAND_STUDENTaddprimarykey(STUDENT_NO);--Createtable教师信息表createtableHAND_TEACHER(TEACHER_NOVARCHAR2(10)notnull,TEACHER_NAMEVARCHAR2(20),MANAGER_NOVARCHAR2(10));--AddcommentstothetablecommentontableHAND_TEACHERis'教师信息表';--AddcommentstothecolumnscommentoncolumnHAND_TEACHER.TEACHER_NOis'教师编号';commentoncolumnHAND_TEACHER.TEACHER_NAMEis'教师名称';commentoncolumnHAND_TEACHER.MANAGER_NOis'上级编号';--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableHAND_TEACHERaddprimarykey(TEACHER_NO);--Createtable课程信息表createtableHAND_COURSE(COURSE_NOVARCHAR2(10)notnull,COURSE_NAMEVARCHAR2(20),TEACHER_NOVARCHAR2(20)notnull);--AddcommentstothetablecommentontableHAND_COURSEis'课程信息表';--AddcommentstothecolumnscommentoncolumnHAND_COURSE.COURSE_NOis'课程号';commentoncolumnHAND_COURSE.COURSE_NAMEis'课程名称';commentoncolumnHAND_COURSE.TEACHER_NOis'教师编号';--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableHAND_COURSEaddconstraintPK_COURSEprimarykey(COURSE_NO,TEACHER_NO);--Createtable成绩信息表createtableHAND_STUDENT_CORE(STUDENT_NOVARCHAR2(10)notnull,COURSE_NOVARCHAR2(10)notnull,CORENUMBER(4,2));--AddcommentstothetablecommentontableHAND_STUDENT_COREis'学生成绩表';--AddcommentstothecolumnscommentoncolumnHAND_STUDENT_CORE.STUDENT_NOis'学号';commentoncolumnHAND_STUDENT_CORE.COURSE_NOis'课程号';commentoncolumnHAND_STUDENT_CORE.COREis'分数';--Create/Recreateprimary,uniqueandforeignkeyconstraintsaltertableHAND_STUDENT_COREaddconstraintPK_SCprimarykey(STUDENT_NO,COURSE_NO);/*******初始化学生表的数据******/insertintoHAND_STUDENTvalues('s001','张三',23,'男');insertintoHAND_STUDENTvalues('s002','李四',23,'男');insertintoHAND_STUDENTvalues('s003','吴鹏',25,'男');insertintoHAND_STUDENTvalues('s004','琴沁',20,'女');insertintoHAND_STUDENTvalues('s005','王丽',20,'女');insertintoHAND_STUDENTvalues('s006','李波',21,'男');insertintoHAND_STUDENTvalues('s007','刘玉',21,'男');insertintoHAND_STUDENTvalues('s008','萧蓉',21,'女');insertintoHAND_STUDENTvalues('s009','陈萧晓',23,'女');insertintoHAND_STUDENTvalues('s010','陈美',22,'女');commit;/******************初始化教师表***********************/insertintoHAND_TEACHERvalues('t001','刘阳','');insertintoHAND_TEACHERvalues('t002','谌燕','t001');insertintoHAND_TEACHERvalues('t003','胡明星','t002');commit;/***************初始化课程表****************************/insertintoHAND_COURSEvalues('c001','J2SE','t002');insertintoHAND_COURSEvalues('c002','JavaWeb','t002');insertintoHAND_COURSEvalues('c003','SSH','t001');insertintoHAND_COURSEvalues('c004','Oracle','t001');insertintoHAND_COURSEvalues('c005','SQLSERVER2005','t003');insertintoHAND_COURSEvalues('c006','C#','t003');insertintoHAND_COURSEvalues('c007','JavaScript','t002');insertintoHAND_COURSEvalues('c008','DIV+CSS','t001');insertintoHAND_COURSEvalues('c009','PHP','t003');insertintoHAND_COURSEvalues('c010','EJB3.0','t002');commit;/***************初始化成绩表***********************/insertintoHAND_STUDENT_COREvalues('s001','c001',58.9);insertintoHAND_STUDENT_COREvalues('s002','c001',80.9);insertintoHAND_STUDENT_COREvalues('s003','c001',81.9);insertintoHAND_STUDENT_COREvalues('s004','c001',60.9);insertintoHAND_STUDENT_COREvalues('s001','c002',82.9);insertintoHAND_STUDENT_COREvalues('s002','c002',72.9);insertintoHAND_STUDENT_COREvalues('s003','c002',81.9);insertintoHAND_STUDENT_COREvalues('s001','c003','59');commit;SELECT*FROMHAND_TEACHERSELECT*FROMHAND_STUDENTSELECT*FROMHAND_STUDENT_CORESELECT*FROMHAND_COURSE--查询所有课程成绩前三名的按照升序排在最开头，其余数据排序保持默认（7分）,显示（学号、成绩）SELECThs.student_no,hs.coreFROM(SELECTrownumrn,hsc.student_no,hsc.core,row_number()OVER(ORDERBYhsc.coredESC)ranksFROMhand_student_corehsc)hsORDERBYCASEWHENranks=3THEN-ranksELSEnullEND,rn;--查询教师、课程及选课的学生数量：(使用rollup),显示（教师名称、课程名称、选课数量）SELECTht.teacher_name,hc.course_name,COUNT(hsc.student_no)numsFROMhand_student_corehsc,hand_teacherht,hand_coursehcWHEREhsc.course_no=hc.course_noANDhc.teacher_no=ht.teacher_noGROUPBYROLLUP(ht.teacher_name,hc.course_name);--查询分数高于课程“J2SE”的所有学生课程信息，显示（学号，姓名，课程名称、分数）SELECThsc.student_no,hs.student_name,hc.course_name,hsc.coreFROMhand_student_corehsc,hand_coursehc,hand_studenthsWHEREhsc.course_no=hc.course_noANDhsc.student_no=hs.student_noANDhsc.coreALL(SELECThsc.coreFROMhand_student_corehsc,hand_coursehcWHEREhsc.course_no=hc.course_noANDhc.course_name='J2SE')ANDhc.course_name!='J2SE';SELECThsc.student_no,hs.student_name,hc.course_name,hsc.coreFROMhand_student_corehsc,hand_coursehc,hand_studenthsWHEREhsc.course_no=hc.course_noANDhsc.student_no=hs.student_noANDhsc.core(SELECTmax(hsc.core)FROMhand_student_corehsc,hand_coursehcWHEREhsc.course_no=hc.course_noANDhc.course_name='J2SE')/*查询教师“胡明星