SQL语句练习题

一、对于学生和课程之间的学习关系，有如下的属性：学生（学号，姓名，性别，出生日期，民族，班级，家庭住址，联系电话）课程（课程ID，课程名称）学习（学号，课程ID，成绩）1．画出这种关系的E-R图。2．指出各表的主键字段。3．在此数据库的基础上，写出SQL语句。（1）查出所有男同学的基本信息（2）查出学生的学号，姓名，班级，课程名称，成绩（3）查出成绩表中成绩大于平均成绩的信息（4）查出民族为汉族或者回族的学生的所有基本信息（5）查出成绩在70－80分之间的学生姓名，课程名称和成绩二、.设职工社团数据库有三个基本表:职工（职工号，姓名，年龄，性别）；社会团体（编号，名称，负责人，活动地点）；参加（职工号，编号，参加日期）。其中：1）职工表的主码为职工号。2）社会团体表的主码为编号；外码为负责人，被参照表为职工表，对应属性为职工号。3）参加表的职工号和编号为主码；职工号为外码，其被参照表为职工表，对应属性为职工号；编号为外码，其被参照表为社会团体表，对应属性为编号。试用SQL语句表达下列操作：l）定义职工表、社会团体表和参加表，并说明其主码和参照关系。2）建立下列两个视图。社团负责人（编号，名称，负责人职工号，负责人姓名，负责人性别）；参加人情况（职工号，姓名，社团编号，社团名称，参加日期）3）查找参加唱歌队或篮球队的职工号和姓名。4）查找没有参加任何社会团体的职工情况。5）查找参加了全部社会团体的职工情况。6）查找参加了职工号为“1001”的职工所参加的全部社会团体的职工号。7）求每个社会团体的参加人数。8）求参加人数最多的社会团体的名称和参加人数。9）求参加人数超过100人的社会团体的名称和负责人。10）把对社会团体和参加两个表的数据查看、插入和删除数据的权力赋给用户李平，并允许他再将此权力授予其他用户。建立一个数据库表student,数据表computer,字段名name,number,sex,SQL2000,flash,net,其中SQL2000,flash,net设置为浮点型float.1、输出所有男生的成绩usestudentselectyuyanasSQL数据库,flashas网络动画,netas计算机网络fromcomputerwheresex='男'2、输出所有SQL成绩在90以上的女生的成绩usestudentselectSQL2000asSQL数据库fromcomputerwheresex='女'andSQL2000=903、输出某一科目不合格所有的男生的成绩usestudentselectyuyanasSQL数据库,flashas网络动画,netas计算机网络fromcomputerwheresex='男'andSQL200060orflash60ornet604、计算并显示每位同学各科的总分和平均分，并按总分从高到低排序usestudentselectSQL2000+flash+netas总分,(SQL2000+flash+net/3)as平均分fromcomputerorderbySQL2000+flash+netdesc5、输出所有计算机网络成绩在70-79之间的同学usestudentselect*fromcomputerwhereflashbetween70and796、输出所有姓“陈”和姓“李”的男生usestudentselect*fromcomputerwheresex='男'andleft(name,1)in('李','陈')或者usestudentselect*fromcomputerwheresex='男'(andnamelike'李__'ornamelike'陈__')7、输出所有学号为偶数的同学成绩usestudentselectnumas学号,SQL2000asSQL数据库,flashas网络动画,netas计算机网络fromcomputerwherenum%2=08、输出Flash成绩最好的5位同学usestudentselecttop5*fromcomputerorderbyflashdesc9、更新同学的成绩，把计算机网络成绩在55-59之间的同学该科的成绩调整为60分usestudentupdatecomputersetnet=60wherenetbetween55and5910、删除平均分最低的3位同学usestudentselecttop3*,(SQL2000+flash+net)/3as平均分fromcomputerorderby(SQL2000+flash+net)/3deletefromcomputerwherenumberin(033001,033003,033011)11、统计成绩表中平均分为90以上（含90分）人数usestudentselectcount(*)fromcomputerwhere(SQL2000+flash+net)/3=9012、用SQL命令向成绩表添加一个新字段——C语言usestudentaltertablecomputeraddc语言float问题描述：已知关系模式：s(sno,sname）学生关系。sno为学号，sname为姓名c(cno,cname,cteacher)课程关系。cno为课程号，cname为课程名，cteacher为任课教师sc(sno,cno,scgrade)选课关系。scgrade为成绩要求实现如下5个处理：1．找出没有选修过“李明”老师讲授课程的所有学生姓名2．列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩3．列出既学过“1”号课程，又学过“2”号课程的所有学生姓名4．列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号5．列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩1.找出没有选修过“李明”老师讲授课程的所有学生姓名--实现代码：selectsnamefromswherenotexists(select*fromsc,cwheresc.cno=c.cnoandc.cteacher='李明'andsc.sno=s.sno)2.列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩--实现代码：selects.sno,s.sname,avg_scgrade=avg(sc.scgrade)froms,sc,(selectsnofromscwherescgrade60groupbysnohavingcount(distinctcno)=2)awheres.sno=a.snoandsc.sno=a.snogroupbys.sno,s.sname3.列出既学过“1”号课程，又学过“2”号课程的所有学生姓名--实现代码：selects.sno,s.snamefroms,(selectsc.snofromsc,cwheresc.cno=c.cnoandc.cnamein('1','2')groupbysnohavingcount(distinctcno)=2)scwheres.sno=sc.sno4.列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号--实现代码：selects.sno,s.snamefroms,scsc1,scsc2wheresc1.cno='1'andsc2.sno='2'andsc1.cno=s.cnoandsc1.scgradesc2.scgrade5.列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩--实现代码：selectsc1.sno,[1号课成绩]=sc1.scgrade,[2号课成绩]=sc2.scgradefromscsc1,scsc2wheresc1.cno='1'andsc2.cno='2'andsc1.sno=sc2.snoandsc1.scgradesc2.scgrade创建表和输入数据CREATETABLESTUDENT(SNOVARCHAR(3)NOTNULL,SNAMEVARCHAR(4)NOTNULL,SSEXVARCHAR(2)NOTNULL,SBIRTHDAYDATETIME,CLASSVARCHAR(5))goCREATETABLECOURSE(CNOVARCHAR(5)NOTNULL,CNAMEVARCHAR(10)NOTNULL,TNOVARCHAR(10)NOTNULL)goCREATETABLESCORE(SNOVARCHAR(3)NOTNULL,CNOVARCHAR(5)NOTNULL,DEGREENUMERIC(10,1)NOTNULL)goCREATETABLETEACHER(TNOVARCHAR(3)NOTNULL,TNAMEVARCHAR(4)NOTNULL,TSEXVARCHAR(2)NOTNULL,TBIRTHDAYDATETIMENOTNULL,PROFVARCHAR(6),DEPARTVARCHAR(10)NOTNULL)INSERTINTOSTUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(108,'曾华','男',1977-09-01,95033);INSERTINTOSTUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(105,'匡明','男',1975-10-02,95031);INSERTINTOSTUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(107,'王丽','女',1976-01-23,95033);INSERTINTOSTUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(101,'李军','男',1976-02-20,95033);INSERTINTOSTUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(109,'王芳','女',1975-02-10,95031);INSERTINTOSTUDENT(SNO,SNAME,SSEX,SBIRTHDAY,CLASS)VALUES(103,'陆君','男',1974-06-03,95031);GOINSERTINTOCOURSE(CNO,CNAME,TNO)VALUES('3-105','计算机导论',825)INSERTINTOCOURSE(CNO,CNAME,TNO)VALUES('3-245','操作系统',804);INSERTINTOCOURSE(CNO,CNAME,TNO)VALUES('6-166','数据电路',856);INSERTINTOCOURSE(CNO,CNAME,TNO)VALUES('9-888','高等数学',100);GOINSERTINTOSCORE(SNO,CNO,DEGREE)VALUES(103,'3-245',86);INSERTINTOSCORE(SNO,CNO,DEGREE)VALUES(105,'3-245',75);INSERTINTOSCORE(SNO,CNO,DEGREE)VALUES(109,'3-245',68);INSERTINTOSCORE(SNO,CNO,DEGREE)VALUES(103,'3-105',92);INSERTINTOSCORE(SNO,CNO,DEGREE)VALUES(105,'3-105',88);INSERTINTOSCORE(SNO,CNO,DEGREE)VALUES(109,'3-105',76);INSERTINTOSCORE(SNO,CNO,DEGREE)VALUES(101,'3-105',64);INSERTINTOSCORE(SNO,CNO,DEGREE)VALUES(107,'3-105',91);INSERTINTOSCORE(SNO,CNO,DEGREE)VALUES(108,'3-105',78);INSERTINTOSCORE(SNO,