SQL语句与关系代数习题--答案已做

1、、选择题1、SQL语言是（）语言。一一 （SQL特点）（易）A）层次数据库 B）网络数据库 C）关系数据库 D）非数据库答案：C2、SQL语言具有两种使用方式，分别称为交互式$、1和（）。（SQL语言使用方式）（易）A） 提示式SQL B）多用户SQL C）嵌入式SQL D）解释式SQL 答案：C3、（）包括数据库模式定义和数据库存储结构与存取方法定义。（）实现对DB的操作，包括查询、插入、删除、修改数据库中的数据。（）用于数据保护，包括数据的安全性，完整性，并发控制和恢复等。一一（数据库语言 DDL DML DCL）（中）A）数据控制子语言 B）数据定义子语言 C）数据操纵子语言 D）数据库

2、语言答案：B C A4、下列SQL语句中，实现数据检索的语句是（），修改表结构的是（），修改属性值的是（），删除表结构的是（），删除表记录的是（）。（DROP TABLE, ALTER TABLE,UPDATE, DELETE,SELECT 语句）（难）A）SELECT B）DROP C）UPDATE D）ALTER E）DELETE 答案：A D C B E用关系代数表达式及 SQL语句描述关系查询(易)1、设有如下关系表 R、S和T:R（BH, XM, XB, DWH）S（DWH, DWM）T（BH, XM, XB, DWH）写出实现下列关系代数的 SQL语句:1) DWH '10

3、0'( R)2) xm,xb(R)3) XM ,DWH ( XB 女'(R)4) ) R SXM ,XB,DWHXB ,男,(R S)解:1 ) SELECT * FROM R WHERE DWH= '100'2 ) SELECT XM,XB FROM R;3 ) SELECT XM,DWH FROM R WHERE XB= '女'；4 ) SELECT R.*,S.DWM FROM R, S WHERE R.DWH=S.DWH;5 ) SELECT XM,XB,DWH FROM R,S WHERE R.DWH=S.DWH AND XB= 

4、9;男2、设有如下关系模式:student(NO, NAME , SEX ,BIRTHDAY , CLASS)teacher(NO,NAME,SEX,BIRTHDAY ,PROF,DEPART) PROF 为职称，DEPART 为系另ijcourse(CNO, CNAME, TNO)score(NO, CNO, DEGREE)DEGREE 为成绩写出实现以下各题功能的 SQL语句：(1) 查询至少有2名男生的班号；一一(难)Select CLASS from student where SEX='男'Group by SEX Having Count(*)>=2(2) 查

5、询不姓“王”的同学记录；一一(易)Select * from student where NAME NOT Like 王 '(3) 查询每个学生的姓名和年龄；一一(难)Select NAME year(date()-year(BIRTHDAY) as age From student(4) 查询学生中最大和最小的birthday日期值；(中)Select max(BIRTHDAY ), min( BIRTHDAY ) From student(5) 查询学生表的全部记录并按班号和年龄从大到小的顺序；一一(中)Select * from student order by CLASS DE

6、SC, BIRTHDAY DESC(6) 查询男教师及其所上的课程；一一(中)Select NO,NAME, CNAME From teacher , course where teacher.NO= course.TNO and SEX='男(7) 查询最高分同学的学号，课程号和成绩；一一 (中)Select * from score where DEGREE=(select max(DEGREE) from score)(8) 查询和“李军”同性别并同班的所有同学的姓名；一一(中)Select NAME from student where SEX=(select SEX from

7、 student where NAME='李军')AND CLAEE=(select CLASS from student where NAME='李军')(9) 查询选修“数据库系统概论”课程的男同学的成绩表；一一(中)(D Select NO,NAME, CNO, CNAME DEGREE from student, course, scoreWhere student.NO= score.NO and course.CNO= score.CNOand CNAME='数据库系统概论'and SEX='男' Select * f

8、rom score where NO in(select NO from student where Sex='男')AND CNO=(select CNO from course where CNAME='数据库系统概论')(10) 查询所有未讲课的教师的姓名和所在系别；一一 (难)Select NAME DEPART from teacher where NOT EXISTS(select * from score where teacher.NO=score.TNO)(11) 查询“计算机系”教师所教课程的成绩表；一一 (难)(D Select * fro

9、m score where CNO IN(select CNO from course where TNO in(select TNO from teacher where DEPART=计算机系')Select Student.NO, Student.NAME, score.CNO, course.CNAME, Teacher.NO, Teacher.NAME, from student,teacher,course,score where student.NO=score.NO and Course.CNO=score.CNO and Teacher.NO=Course.TNOan

10、d DEPART=计算机系'(12) 查询选修“ 3-105”课程的成绩高于“ 109 号同学成绩的所有同学的记录；一一(难)Select student.NO, NAME, score.CNO,CNAME, DEGREE where score.CNO= '3-105' and DEGREE >(select DEGREE from score where NO='109' and CNO='3-105' ) and student.NO=score.NO and score.CNO=course.CNO(13) 查询最低分大于7

11、0,最高分小于90的学生的学号；一一 (中)Select NO from score Group by NO having min(DEGREE)>70 and max(DEGREE)<90(14) 查询成绩在60到80之间的所有记录；一一 (中)Select * from score where DEGREE BETWEEN 60 AND 80(15) 查询成绩比该课程平均成绩低的同学的成绩表；(相关子查询)(难)Select * from score x where DEGREE <(select AVE(DEGREE) from score y where x.CNO=

12、y.CNO Group by y.CNO)(16) 查询所有女教师和女同学的姓名、性别和生日；(中)Select NAME, SEX, BIRTHDAY from student where SEX='女'UNIONSelect NAME, SEX, BIRTHDAY from teacher where SEX='女'(17) 查询“计算机系”和“无线电系”不同职称的教师的姓名和职称；一一(中)Select NAME, PROF from teacher where DEPART=计算机系'OR DEPART =无线电系'order by PR

13、OF;(以上是我自己做的答案)参考答案：解：(1) SELECT CLASS FROM student WHERE SEX= '男'GROUP BY CLASS HA VING COUNT(*)>=2;(2) SELECT * FROM student WHERE NAME NOT LIKE '王* '(3) SELECT NAME , year(date()-year(birthday) as age FROM student;(4) SELECT MAX(BIRTHDAY), MIN(BIRTHDAY) FROM student ;(5) SELECT

14、 * FROM student ORDER BY CLASS,BIRTHDAY DESC;(6) SELECT , ame FROM teacher x, course y WHERE x.no=y.tno and x.sex='男'；(7) SELECT * FROM score WHERE degree=(SELECT max(degree) FROM score);(8) SELECT name FROM student WHERE sex=(SELECT sex FROM student WHERE name='李军')and class=(

15、SELECT class FROM student WHERE name='李军')；(9) SELECT * FROM score WHERE no IN(SELECT no FROM student WHERE sex= '男')and cno=(SELECT cno FROM course WHERE cname='数据库系统概论');(10) SELECT name, depart FROM teacher t WHERE NOT EXIST (SELECT * FROM course c WHERE c.tno=t.no);(11) S

16、ELECT * FROM score s, teacher t, course c WHERE t.depart=计算机系'and t.no=c.tno and o=o;(12) SELECT * FROM student s, score sc WHERE s.no=sc.no and cno='3-105'and degree>(SELECT degree FROM sc WHERE no= '109' and cno='3-105');(13) SELECT no FROM score GROUP BY no HA VING min(degree)>70 and max(degree)<90;(14) SELECT * FROM score WHERE degree BETWEEN 60 AND 80;(15) SELECT * FROM score a WHERE degree < (SELECT avg(degree) FR