Stude
tSS
ameSageSsex学生表S：学号；S
ame：学生姓名；Sage：学生年龄；Ssex：学生性别r
CourseCC
ameT课程表C课程编号；C
ame：课程名字；T：教师编号r
SCSCscore成绩表S：学号；C课程编号；score：成绩r
TeacherTT
ame教师表T：教师编号；T
ame：教师名字r
r
问题r
1、查询“001”课程比“002”课程成绩高的所有学生的学号；r
selectaSfromselectsscorefromSCwhereC001aselectsscorer
fromSCwhereC002br
whereascorebscorea
dasbsr
2、查询平均成绩大于60分的同学的学号和平均成绩；r
selectSavgscorer
fromscr
groupbyShavi
gavgscore60r
3、查询所有同学的学号、姓名、选课数、总成绩；r
selectStude
tSStude
tS
amecou
tSCCsumscorer
fromStude
tleftOuterjoi
SCo
Stude
tSSCSr
groupbyStude
tSS
amer
4、查询姓“李”的老师的个数；r
selectcou
tdisti
ctT
amer
fromTeacherr
whereT
amelike李r
5、查询没学过“叶平”老师课的同学的学号、姓名；r
selectStude
tSStude
tS
amer
fromStude
tr
whereS
oti
selectdisti
ctSCSfromSCCourseTeacherwhereSCCCourseCa
dTeacherTCourseTa
dTeacherT
ame叶平r
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；r
selectStude
tSStude
tS
amefromStude
tSCwhereStude
tSSCSa
dSCC001a
dexistsSelectfromSCasSC_2whereSC_2SSCSa
dSC_2C002r
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；r
selectSS
amer
fromStude
tr
whereSi
selectSfromSCCourseTeacherwhereSCCCourseCa
dTeacherTCourseTa
dTeacherT
ame叶平groupbyShavi
gcou
tSCCselectcou
tCfromCourseTeacherwhereTeacherTCourseTa
dT
ame叶平r
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；r
SelectSS
amefromselectStude
tSStude
tS
amescoreselectscorefromSCSC_2whereSC_2SStude
tSa
dSC_2C002score2r
fromStude
tSCwhereStude
tSSCSa
dC001S_2wherescore2scorer
9、查询所有课程成绩小于60分的同学的学号、姓名；r
selectSS
amer
fromStude
tr
whereS
oti
selectStude
tSfromStude
tSCwhereSSSCSa
dscore60r
10、查询没有学全所有课的同学的学号、姓名；r
selectStude
tSStude
tS
amer
fromStude
tSCr
whereStude
tSSCSgroupbyStude
tSStude
tS
amehavi
gcou
tCselectcou
tCfromCourser
r
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；r
selectSS
amefromStude
tSCwhereStur