SQL练习题1.doc

已知教学数据库(TeachDB)中包含四个表教师情况基本表(Teacher_No#,Teacher_Name,Teacher_Dep)字段含义：Teacher_No：教师号，Teacher_Name：教师名，Teacher_Dep：教师所在系课程基本表(Class_No#, Class_Name,Teacher_No)字段含义：Class_No：课程号，Class_Name：课程名，Teacher_No：教师号学生情况基本表(Student_No#，Student_Name,Student_Age,Student_Sex)字段含式：Student_No：学号，Student_Name：学生名，Student_Age：年龄，Student_Sex：性别学习成绩基本表(Student_No#,Class_No#,Grade)字段含义：Student_No：学生号，Class_No：课程号，Grade：成绩各个表中数据大家可参看企业管理器，或者使用select语句察看试用SQL语句完成如下查询：（1）查询计算机系的所有教师use TeachDBgo select * from TeacherInfowhere Teacher_Dep=计算机（2）查询所有女同学的姓名，年龄use TeachDBgo select Student_Name,Student_Sexfrom StudentInfowhere Student_Sex=女（3）查询计算机系教师开设的所有课程的课程号和课程名use TeachDBgo select Class_NO,Class_Namefrom CourseInfowhere Teacher_No IN(select Teacher_No from TeacherInfo where Teacher_Dep=计算机)或者use TeachDBgo select Class_NO,Class_Namefrom CourseInfo join TeacherInfoon CourseInfo.Teacher_No=TeacherInfo.Teacher_Nowhere TeacherInfo.Teacher_Dep=计算机或者use TeachDBgo select Class_NO,Class_Namefrom CourseInfo,TeacherInfowhere CourseInfo.Teacher_No=TeacherInfo.Teacher_Noand TeacherInfo.Teacher_Dep=计算机（4）查询所有女同学所选课程的课程号use TeachDBgo select distinct Class_NOfrom GradeInfowhere Student_No In(select Student_No from StudentInfowhere Student_Sex=女)或者use TeachDBgo select distinct Class_NOfrom GradeInfo join StudentInfoon GradeInfo.Student_No=StudentInfo.Student_Nowhere StudentInfo.Student_Sex=女（5）查询至少有一门课程的成绩高于90分的学生姓名和年龄use TeachDBgo select Student_Name,Student_Agefrom StudentInfowhere StudentInfo.Student_No IN(select distinct Student_No from GradeInfowhere Grade90)或者use TeachDBgo select distinct Student_Name,Student_Agefrom StudentInfo join GradeInfoon StudentInfo.Student_No=GradeInfo.Student_Nowhere GradeInfo.Grade90（6）查询选修“微机原理”的所有学生的姓名和成绩use TeachDBgo select Student_Name,Gradefrom CourseInfo,GradeInfo,StudentInfowhere CourseInfo.Class_No=GradeInfo.Class_Noand GradeInfo.Student_No=StudentInfo.Student_Noand Class_Name=微机原理或者use TeachDBgo select Student_Name,Gradefrom CourseInfo join GradeInfoon CourseInfo.Class_No=GradeInfo.Class_Nojoin StudentInfoon GradeInfo.Student_No=StudentInfo.Student_Nowhere CourseInfo.Class_Name=微机原理（7）查询未选修“高等数学”的学生的学号和姓名use TeachDBgo select Student_No,Student_Namefrom StudentInfowhere Student_No not In(select Student_Nofrom GradeInfo where Class_No IN(select Class_Nofrom CourseInfowhere Class_Name=高等数学)（8）查询不是计算机系教师所讲授的课程的课程名和课程号use TeachDBgo select Class_No,Class_Namefrom CourseInfowhere Teacher_No not In(select Teacher_No from TeacherInfo where Teacher_Dep=计算机)（9）查询未选修“21”号课程的学生的学号和姓名use TeachDBgo select Student_No,Student_Namefrom StudentInfowhere Student_No not In(select Student_No from GradeInfowhere Class_No=21)（10）查询年龄在1820（包括18岁和20岁）之间的所有的学生信息。use TeachDBgo select * from StudentInfowhere Student_Age between 18 and 20（11）查询年龄小于20岁的所有男同学的学号和姓名use TeachDBgo select Student_No,Student_Namefrom StudentInfowhere Student_Sex=男 and Student_Age20（12）查询至少选修课程号为“21”和“41”两门课程的学生学号use TeachDBgoselect X.Student_NOfrom GradeInfo X,GradeInfo as YWhere X.Student_No=Y.Student_Noand X.Class_No=21and Y.Class_No=41（13）查询选修了“高等数学”或者“普通物理”的学生姓名use TeachDBgo select Student_Namefrom StudentInfowhere Student_NO In(select Student_No from GradeInfowhere Class_No IN(select Class_Nofrom CourseInfowhere Class_Name=高等数学 or Class_Name=普通物理 )（14）查询选修了洪文源老师所讲授的课程的学生的学号和成绩use TeachDBgo select Student_NO,Gradefrom GradeInfowhere Class_No In(select Class_Nofrom CourseInfowhere Teacher_No In(select Teacher_Nofrom TeacherInfowhere Teacher_Name=洪文源)或者use TeachDBgoselect Student_No,Gradefrom GradeInfo join CourseInfoon GradeInfo.Class_No=CourseInfo.Class_Nojoin TeacherInfo on CourseInfo.Teacher_No=TeacherInfo.Teacher_NoWhere TeacherInfo.Teacher_Name=洪文源（15）查询选修了计算机系教师所讲授的课程的学生姓名和成绩use TeachDBgoselect Student_Name,Gradefrom GradeInfo join StudentInfoon GradeInfo.Student_No=StudentInfo.Student_Noand Class_No IN(select Class_No from CourseInfowhere Teacher_No In(select Teacher_Nofrom TeacherInfowhere Teacher_Dep=计算机)或者use TeachDBgoselect Student_Name,Gradefrom GradeInfo join