SQL语句练习及答案.doc

sql语句练习题1数据库有如下四个表格：student(sno,sname,sage,ssex，sdpt) 学生表系表（dptno,dname）course(cno,cname, gradet, tno) 课程表sc(sno,cno,score) 成绩表teacher(tno,tname) 教师表 要求：完成以下操作1. 查询姓欧阳且全名为三个汉字的学生的姓名。selectsnamefromstudent wheresnamelike“欧阳_；2. 查询名字中第2个字为阳字的学生的姓名和学号。selectsname，snofromstudent wheresnamelike_阳%；3. 查询所有不姓刘的学生姓名。selectsname，sno，ssexfromstudent wheresnamenotlike“刘%”；4. 查询db_design课程的课程号和学分。selectcno，ccredit fromcoursewherecnamelikedb_design5. 查询以db_开头，且倒数第3个字符为i的课程的详细情况。select*fromcourse wherecnamelikedb%i_；6. 某些学生选修课程后没有参加考试，所以有选课记录，但没有考试成绩。查询缺少成绩的学生的学号和相应的课程号。selectsno,cnofromscwheregradeisnull；7. 查所有有成绩的学生学号和课程号。selectsno，cnofromscwheregradeisnotnull；8. 查询计算机系年龄在20岁以下的学生姓名。selectsnamefromstudent wheresdept=csandsage3; 16. 查询每个学生及其选修课程的情况。select student.*，sc.*, course.* from student，sc , course where student.sno=sc.sno and o=o；17. 查询每个学生及其选修课程的情况包括没有选修课程的学生18. 查询选修2号课程且成绩在90分以上的所有学生的学号、姓名select student.sno, student.snamefrom student,scwhere student.sno=sc.sno and o=”2and sc.grade90；19. 查询每个学生的学号、姓名、选修的课程名及成绩。select student.sno，sname，ssex，sage，sdept，cno，gradefrom student left outjoin sco on(student.sno=sc.sno)；20. 查询与“刘晨”在同一个系学习的学生。selectsno，sname，sdeptfrom studentwhere sdept in(select sdept from student where sname=”刘晨)；21. 查询选修了课程名为“信息系统”的学生学号和姓名select sno，sname from student where sno in(select sno from sc where cno in (select cno from course where cname=”信息系统);22. 找出每个学生超过他选修课程平均成绩的课程号。select sno，cno from sc x where grade=(select avg(grade) from sc y where y.sno=x.sno)；23. 将一个新学生记录（学号：200215128；姓名：陈冬；性别：男；所在系：is；年龄：18岁）插入到student表中。insert into student values (200215128，陈冬，男，is，18)；24. 将学生200215121的年龄改为22岁。update student setsage=22 where sno=200215121；25. 将所有学生的年龄增加1岁。update student setsage=sage+1；26. 将计算机科学系全体学生的成绩置零。update sc set grade=0 where exits(selete * from student where student.sno=sc.sno and sdept=” 计算机科学系”)；27. 删除学号为20021528的学生记录delete from student where sno=”200215128；28. 删除所有的学生选课记录。delete from sc；29. 删除2号课程的所有选课记录。delete from sc where cno=2；30. 删除计算机科学系所有学生的选课记录。delete from sc where sno in (selete sno from student where sdept=” 计算机科学系”)；31. 建立信息系学生的视图。create view is_student asselect sno，sname，sage from student where sdept=is；sql语句练习题2设教学数据库education,有三个关系： 学生关系s（sno，sname，age，sex，sdept）；学习关系sc（sno，cno，grade）； 课程关系c（cno，cname，cdept，tname）查询问题： 1：查所有年龄在20岁以下的学生姓名及年龄。select sname，sagefrom swhere sage=20);2：查考试成绩有不及格的学生的学号 select distinct snofrom scwhere grade60；3：查所年龄在20至23岁之间的学生姓名、系别及年龄。select sname，sdept，sagefrom swhere sage between 20 and 23；4：查计算机系、数学系、信息系的学生姓名、性别。select sname，ssex from s where sdept in（cs,is,math）；5：查既不是计算机系、数学系、又不是信息系的学生姓名、性别select sname，ssex from s where sdept not in（cs,is,math）； 6：查所有姓“刘”的学生的姓名、学号和性别。select sname，sno，ssex from s where sname like刘%；7：查姓“上官”且全名为3个汉字的学生姓名。select sname from s where sname like 上官_；8：查所有不姓“张”的学生的姓名。select sname，sno，ssex from s where sname not like 张%；9：查db_design课程的课程号。select cno from c where cname like db_design;10：查缺考的学生的学号和课程号。select sno，cno from sc where grade is null；11：查年龄为空值的学生的学号和姓名。select sno，sname from s where sage is null；12：查计算机系20岁以下的学生的学号和姓名。select sno，snamefrom swhere sdept=cs and sage3;22：求基本表s中男同学的每一年龄组（超过50人）有多少人？要求查询结果按人数升序排列，人数相同按年龄降序排列。select sage，count（sno）from swhere ssex=mgroup by sagehaving count（*）50order by 2，sage desc；23：查询每个学生及其选修课程的情况。select s.sno, sname, sage, ssex, sdept, cno, gradefrom s, scwhere s.sno=sc.sno；24：查询选修了c2课程且成绩在90分以上的所有学生。select s.sno，snamefrom s，scwhere s.sno=sc.snoand o=c2 and sc.grade90;25：查询每个学生选修的课程名及其成绩。select s.sno，sname，cname，sc.gradefrom s，sc，cwhere s.sno=sc.sno and o=o26：统计每一年龄选修课程的学生人数。select sage，count（distinct s.sno）from s，scwhere s.sno=sc.snogroup by sage；27：查询选修了c2课程的学生姓名。select sname from s where sno in(select sno from sc where cno=c2);28：查询与“张三”在同一个系学习的学生学号、姓名和系别。select sno，sname，sdept from where sdept=(select sdept from s where sname=张三);29：查询选修课程名为“数据库”的学生学号和姓名。select sno，sname from s where sno in（select sno from sc where cno in (select cno from c where cname=db);30：查询与“张三”在同一个系学习的学生学号、姓名和系别。select sno，sname，sdept from s where sdept=（select sdept from s where sname=张三）；31：查询选修课程名为“数据库”的学生学号和姓名。select sno，sname from s where sno in （select sno from sc where cno=（select cno from c where cname=db）；32：查询选修了c2课程的学生姓名。1. select sname from s where sno in（select sno from sc where cno=c2);2. select sname from s where exists（select * from sc where sc.sno=s.sno and cno=c2);33：查询选修了全部课程的学生姓名。select sname from s where not exists（select * from c where not exists（select * from sc where sc.sno=s.sno and o=o)； 36：查询所学课程包含学生s3所学课程的学生学号select distinct sno from sc as x where not exists（select * from sc as y where y.sno=s3 and not exists（select * from sc as z where z.sno=x.sno and o=o)；sql语句练习题 3一、简单查询1、列出全部学生的信息。select * from学生2、列出软件专业全部学生的学号及姓名。select学号,姓名from学生where专业=软件3、列出所有必修课的课号。select distinct课号from必修课4、求1号课成绩大于80分的学生的学号及成绩，并按成绩由高到低列出。select学号,成绩from选课where课号=1and 成绩80 order by成绩desc5、列出非软件专业学生的名单。方法一：select姓名from学生where专业软件方法二：select姓名from学生where not专业=软件方法三：select姓名from学生where专业!=软件6、查询成绩在7080分之间的学生选课得分情况方法一：select*from选课where成绩=70and成绩150（二）自连接查询1、列出那些专业相同的学生相应的姓名及专业信息。select a.姓名,b.姓名,专业from学生a,学生bwherea.学号b.学号anda.专业=b.专业2、求至少选修1号课和2号课的学生的学号。selectx.学号from选课x,选课ywherex.学号=y.学号andx.课号=1andy.课号=23、有以下表rate.dbf币种1代码c(2)、币种2代码c(2)、买入价n(8,4)、卖出价n(8,4)外汇汇率.dbf币种1c(4)、币种2c(4)、买入价n(8,4)、卖出价n(8,4)外汇代码.dbf外汇名称c(10)、外汇代码c(10)要求：将所有“外汇汇率”表中的数据插入rate表中并且顺序不变，由于“外汇汇率”中的币种1和币种2存放的是外币名称，而rate表中的币种1代码和币种2代码应该存放外币代码，所以插入时要做相应的改动，外币名称与外向代码的对应关系存储在“外汇代码”表中。selecta.外币代码as币种1代码,b.外币代码as币种2代码,;买入价,卖出价from外汇代码a,外汇汇率,外汇代码b;wherea.外币名称=外汇汇率.币种1andb.外币名称=外汇汇率.币种2intotablerate4、假定有“雇员”表（雇员号c（2），雇员姓名c（6），经理号c（2），根据雇员关系列出上一级经理及其所领导的职员清单。（教案中的例题）select领导,s.雇员姓名,雇员,e.雇员姓名from雇员s,雇员ewheres.雇员号=e.经理（三）超连接1、列出选修1号课的学生姓名及成绩。方法一：（使用简单连接查询格式）select姓名,成绩from学生,选课where学生.学号=选课.学号and课号=1方法二：（使用内部连接格式）select姓名,成绩from学生innerjoin选课on学生.学号=选课.学号where课号=1方法三：内部连接的inner短语可以省略。（与方法二等价）select姓名,成绩from学生join选课on学生.学号=选课.学号where课号=12、查询订货管理数据库中数据的仓库号、城市、供应商名和地址信息。方法一：使用简单连接格式。select仓库.仓库号,城市,供应商名,地址from供应商,订购单,职工,仓库;where供应商.供应商号=订购单.供应商号and订购单.职工号=职工.职工号;and职工.仓库号=仓库.仓库号方法二：使用超连接的内部连接格式。（注意连接条件的顺序）select仓库.仓库号,城市,供应商名,地址from供应商join订购单join职工join仓库;on职工.仓库号=仓库.仓库号on订购单.职工号=职工.职工号on供应商.供应商号=订购单.供应商号3、查询没有选修任何课程的学生姓名。方法一：使用嵌套查询select姓名from学生where学号notin(select学号from选课)方法二：使用超连接的右连接。select姓名from选课rightjoin学生on选课.学号=学生.学号where选课.学号学生.学号方法三：使用超连接的左连接。（注意表名顺序和方法二的不同）select姓名from学生leftjoin选课on选课.学号=学生.学号where选课.学号学生.学号三、嵌套查询（一）普通嵌套与谓词exists1、列出选修汇编语言课的学生的学号。方法一：select学号from选课where课号=(select课号from课程where课名=汇编语言)方法二：使用谓词exists。注意和方法一格式上的不同。select学号from选课whereexist(select*from课程;where课名=汇编语言and选课.课号=课程.课号)2、求软件专业所有必修课的课程信息。方法一：select*from课程where课号in;(select课号from必修课where必修专业=软件)方法二：select*from课程whereexist(select*from必修课where必修专业=软件;and课程.课号=必修课.课号)（二）量词any、some、all1、求选修2号课的学生中，成绩比选修1号课的最低成绩要高的学生的学号和成绩。方法一：select学号,成绩from选课where课号=2and成绩;(selectmin(成绩)from选课where课号=1)方法二：any等价于some，所以可将any换成some。select学号,成绩from选课where课号=2and成绩any;(select成绩from选课where课号=1)2、求选修2号课的学生中，成绩比选修1号课的任何学生的成绩都要高的那些学生的学号和成绩。方法一：select学号,成绩from选课where课号=2and成绩;(selectmax(成绩)from选课where课号=1)方法二：select学号,成绩from选课where课号=2and成绩all;(select成绩from选课where课号=1)（三）内外层互相关嵌套（外层依赖于内层的查询结果，内层依赖于外层来进一步查询）1、列出每门课程中成绩最高的选课信息。select*from选课awhere成绩=(selectmax(成绩)from选课bwherea.课号=b.课