资源描述：
《sql课堂大作业(答案)》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、SQL查询语言练习题目:一、请你首先建立下面的四张表:Student表NONAMESEXBIRTHDAYCLASS108曾华男09/01/7795033105匡明男10/02/7595031107王丽女01/23/7695033101李军男02/20/7695033109王芳女02/10/7595031103陆军男06/03/7495031Teacher表NONAMESEXBIRTHDAYPROFDEPART804李城男12/02/58副教授计算机系856张旭男03/12/69讲师电子工程系825王萍女05/05/72助教计算机系831刘冰女08/1
2、4/77助教电子工程系Course表CNOCNAMETNO3-105计算机导论8253-245操作系统8046-166数字电路8569-888高等数学100Score表NOCNODEGREE1033-245841053-245671093-245981033-105781053-105911093-105681013-105931073-105721083-105661016-166801076-166771086-166621•查询student表所记录的name,sex,class歹Qselectsname,ssex,classfromstude
3、nt2.显示教师工作的单位(不许重复)selectdistinctdepartfromteacher3.以汉字属性显示student表所有记录selectsno学号sname姓名；ssex"性另ij',sbiiihday'出生日期class'班级'fromstudent4.查询成绩在60到80之间的同学姓名selectsnamefromstudent,scorewherestudent.sno=score.snoanddegreebetween60cind805.查询成绩为80,85,或X8的记录select*fromscorewheredegre
4、ein(80,85,88)6.查询'95031'班或者性别为'女'的同学记录select*fromstudentwhereclass-9503Torssex=‘女7.以“班级”降序显示student中的记录select*fromstudentorderbyclassdesc8.以eno升序、degree降序显示score中的记录select*fromscoreorderbyeno.degreedesc9.显示'95031'班中的学生人数selectcount(*)fromstudentwhereclass='95O31'2.显示score表中分数最高
5、的学生的学号和课程号selecttop1sno,cnofromscoreorderbydegreedescselectsno^cnofromscorewheredegree=(selectmax(degree)fromscore)11・显示'3・105'号课程的平均分数selectavg(degree)fromscorewherecno=,3-105'12.显示至少有5名学生选修的并以'3'开头的课程号的平均分数selectcno,avg(degree)fromscorewhereenolike*3%*groupbyenohavingcount(sn
6、o)>=513・显示最低分数大于70,最高分数低于90的no列selectdistinctsnofromscoreassiwhere70<(selectmin(degree)fromscoreass2wheresl.sno=s2.sno)and90>(selectmax(degree)fromscoreass3wheresl.sno=s3.sno)14.查询学生的姓名、所学的课程号以及该课程的成绩selectsname,cno,degreefromstudent,scorewherestudent.sno=score.sno14.查询95033班所选
7、课程的平均分数selectavg(degree)fromstudent,scorewherestudent.sno=score.snoandclass=,95033,15.显示选修3-105课程的成绩高于109号同学成绩的所有同学的记录select*fromscorewherecno=,3-105,anddegree>(selectdegreefromscorewherecno='3-105'andsno='109')16.查询选修多门课程的同学中分数为非最高成绩的记录select*fromscorewheredegreeo(selectmax(de
8、gree)fromscore)andsnoin(selectsnofromscoregroupbysnoha
