关系代数和SQL练习

SQL查询练习SQL查询练习⼀、有三个关系，试⽤关系代数表达式表⽰下列查询语句：S(sno，sname，age，sex，sdept)C(cno，cname，cdept，tname) tname表⽰授课⽼师名SC(sno，cno，grade)(1) 检索年龄⼩于22岁的男学⽣的学号与姓名。

(2) 检索学号为S3学⽣所学课程的课程名与任课教师名。

(3) 检索王⽼师所授课程的课程号、课程名。

(4) 检索⾄少选修王⽼师所授课程中⼀门课的男学⽣姓名。

(5) 检索陈同学不学的课程的课程号。

(6) 检索全部学⽣都选修的课程的课程号、课程名。

(7) 检索选修课程包含王⽼师所授课程的学⽣学号。

(8) 检索⾄少选修两门课程的学⽣学号。

(9) 查询⾄少选修了２号课程和８号课程的学⽣姓名。

(10) 查询张红的年龄。

(11) 查询李明同学不及格的课程名称。

(12) 查询选修了“计算机⽹络”的学⽣姓名。

(13) 查询“计算机⽹络”成绩在９０分以上的学⽣姓名。

⼆、设有下列四个关系模式：S (SNO， SNAME， CITY)；P (PNO， PNAME， COLOR， WEIGHT)；J (JNO， JNAME， CITY)；SPJ(SNO， PNO， JNO， QTY)。

其中，供应商关系S由供应商号(SNO)、供应商姓名(SNAME)、供应商所在城市(CITY)组成。

零件关系P由零件号(PNO)、零件名称(PNAME)、零件颜⾊(COLOR)、零件重量(WEIGHT)组成，⽤于记录各种零件的情况。

项⽬关系J由项⽬号(JNO)、项⽬名称(JNAME)、项⽬所在城市(CITY) 组成。

供应情况关系SPJ由供应商号(SNO)、零件号(PNO)、项⽬号(JNO)、供应数量(QTY) 组成。

试⽤关系代数表达式完成以下操作：(1) 检索供应项⽬J2零件的供应商号(SNO)。

(2) 检索供应项⽬J2零件P2的供应商号(SNO)。

(3) 检索供应项⽬J2⿊⾊零件的供应商姓名(SNAME)。

一、关系运算1、设有两个关系R(A,B)和S(B,C),请写出与下面关系代数表达式等价的SQL语句。

πB(R) -πB(σc=`C56`(S))2、已知关系R、S如下所示，以表的形式给出下列运算结果。

(1)σX=Z(R ╳ S)已知关系模式如下：学生：S(Sno,Sname,Ssex,Sage,Sdept) 对应学生的学号，姓名，性别，年龄，系；课程：C(Cno,Cname,Tname) 对应课程的课程号，课程名，授课教师名字；学生选课：SC（Sno,Cno,Grade）对应学生选修课程的成绩。

3、检索至少选修两门课程的学生学号及姓名。

4、查询王力同学不学课程的课程名。

5、查询既选修了课程1也选修了课程2的学生学号。

6、检索全部学生都选修的课程的课程号与课程名。

7、查询刘宏老师所授课程的每门课程的学生平均成绩。

8、为“三建”工程项目建立一个供应情况视图，包括SNO、PNO、QTY。

9、试修改Student表结构，实现学生性别取值为‘男’，‘女’的约束。

答案：1、SELECT BFROM RWHERE B NOT IN(SELECT BFROM SWHERE C=`C56`）；2、(bebb,cccc) ,(ccc)3、检索至少选修两门课程的学生学号及姓名。

select sno,sname from student where sno in(select sno from scgroup by snohaving count(*)>=2);4、查询王力同学不学课程的课程名。

SELECT CNAME FROM CWHERE NOT EXISTS(SELECT*FROM SCWHERE O=O AND SNO =(SELECT SNO FROM STUDENT WHERE SNAME=’王力’));5、查询既选修了课程1也选修了课程2的学生学号。

SELECT SnoFROM SCWHERE Cno='1' AND Sno IN(SELECT SnoFROM SCWHERE Cno='2');6、检索全部学生都选修的课程的课程号与课程名。

数据库管理系统原理专升本试题关系代数与SQL语言关系代数是一种描述和操作关系型数据库的数学模型，它是数据库管理系统原理中的重要概念。

SQL语言是一种用于查询、操作和管理关系型数据库的编程语言。

本文将分别介绍关系代数和SQL语言的基本概念和使用方法。

一、关系代数关系代数是用于描述和操作关系数据库的一种形式化语言。

它基于数学的集合论和逻辑学原理，通过一组基本操作来对关系进行处理。

常见的关系代数操作包括选择、投影、并、交、差、连接和除等。

1. 选择（Select）选择操作用于从一个关系中选择满足特定条件的元组。

选择操作的语法为：σP(R)，其中σ表示选择操作，P为条件谓词，R为选择操作的关系。

例如，要从一个学生表中选择年龄大于20岁的学生，可以使用选择操作：σ年龄>20(学生表)。

2. 投影（Project）投影操作用于从一个关系中选取指定的属性组成一个新的关系。

投影操作的语法为：πA1,A2,...,An(R)，其中π表示投影操作，A1,A2,...,An为要选择的属性，R为投影操作的关系。

例如，要从学生表中选取学生的姓名和年龄，可以使用投影操作：π姓名,年龄(学生表)。

3. 并（Union）并操作用于将两个关系的元组合并在一起，重复的元组会被去除。

并操作的语法为：R∪S，其中R和S分别是要并操作的两个关系。

例如，要将学生表和教师表合并在一起，可以使用并操作：学生表∪教师表。

4. 交（Intersection）交操作用于找出两个关系的公共元组。

交操作的语法为：R∩S，其中R和S分别是要交操作的两个关系。

例如，要找出同时是学生和教师的人，可以使用交操作：学生表∩教师表。

5. 差（Difference）差操作用于找出在一个关系中出现而在另一个关系中没有出现的元组。

差操作的语法为：R-S，其中R和S分别是要差操作的两个关系。

例如，要找出不是学生但是是教师的人，可以使用差操作：教师表-学生表。

6. 连接（Join）连接操作用于将两个关系的元组进行匹配，并生成一个新的关系。

一、选择题1、SQL 语言是（ ）语言。

——（SQL 特点）（易）A)层次数据库 B)网络数据库 C)关系数据库 D)非数据库答案：C2、SQL 语言具有两种使用方式，分别称为交互式SQL 和（ ）。

——（SQL 语言使用方式）（易）A) 提示式SQL B)多用户SQL C)嵌入式SQL D)解释式SQL答案：C3-4-5（ ）包括数据库模式定义和数据库存储结构与存取方法定义。

（ ）实现对DB 的操作，包括查询、插入、删除、修改数据库中的数据。

（ ）用于数据保护，包括数据的安全性，完整性，并发控制和恢复等。

——（数据库语言 DDL DML DCL ）（中）A) 数据控制子语言 B)数据定义子语言 C)数据操纵子语言 D)数据库语言答案：B C A6-7-8-9-10、下列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） )('100'R DWH =σ2）∏XB XM R ,)( 3） ∏=DWH XM XB R ,''))((女σ 4） S R ∞ 5） ∏∞=DWH XB XM XB S R ,,''))((男σ解：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=’男’;2、设有如下三个关系： ——（易-易-难）A(A#,ANAME,WQTY ,CITY) ： A#：商店代号；ANAME ：商店名；WQTY :店员人数B(B#,BNAME,PRICE) ：B#：商品号；BNAME ：商品名称；AB(A#,B#,QTY) ：QTY ：商品数量试用关系代数和SQL 语言写出下列查询。

针对书上的学生选课数据库S_T，用关系代数和SQL语言完成以下查询：1、查询“CS”系所有学生的学号和姓名。

2、Slelect sno,sname from student where sdept =’CS’3、查询所有姓“刘”的学生的信息。

4、Select * from student where sname like ‘刘%’5、查询年龄在18至20岁之间的学生信息。

6、Select * from student where sage between 18 and 207、查询不在“CS”系也不在“MA”系的学生的所有信息。

8、Select * from student where sdept not in (‘CS’,’MA’)9、查询“CS”系所有学生的平均年龄。

10、Select avg(sage) from student where sdept like ‘CS’11、查询课程名是以“系统”结尾的课程信息。

12、Select * from course where cname like ‘%系统’13、查询先行课为“6”号课程的课程信息。

14、Select * from course where cpno=615、查询间接先行课为“5”号课程的课程号及课程名。

16、Select o,ame from c c1,c c2 where c1.cpno=o and c2.cpno=517、Select cno ,cname from course where cpno in (select cno from course wherecpno=5)18、查询没有先行课的课程名。

19、Select cname from course where cpno is null20、查询选修了“1”号课程的学生选课信息。

21、Select * from sc where cno=122、查询成绩为90分以上的学生姓名和课程名。

关系代数SQL语句1.以图3-6的数据库为例（可参照图5-1，该数据库的实例），用关系代数完成以下检索：注意：此处暂以 * 表示连接运算符。

1) 检索在仓库WH2工作的职工的工资。

π职工号，工资(σ仓库号="WH2"(职工))2) 检索在上海工作的职工的工资。

π职工号，工资(σ城市="上海"(仓库) * 职工)3) 检索北京的供应商的名称。

π供应商名(σ地址="北京"(供应商))4) 检索目前与职工E6有业务联系的供应商的名称。

π供应商名（σ职工号="E6"(订购单) * 供应商)5) 检索所有职工的工资都大于1220元的仓库所在的城市。

π城市(仓库) -π城市(σ工资<=1220(职工) * 仓库)6) 检索和北京的所有供应商都有业务联系的职工的工资。

π职工号，工资(职工 * (订购单÷π供应商号(σ地址="北京"(供应商)))) 7) 检索至少和职工E1、E4、E7都有联系的供应商的名称。

π供应商名(订购单÷(“E1”,”E4”,”E7”) * 供应商)2. 以图5-1的数据库为例，用SQL完成以下检索：1) 检索在北京的供应商的名称。

SELECT * FROM 供应商 WHERE 地址=‟北京‟2) 检索发给供应商S6的订购单号。

SELECT 订购单号 FROM 订购单 WHERE 供应商号=‟S6‟3) 检索出职工E6发给供应商S6的订购单信息。

SELECT * FROM 订购单 WHERE 供应商号=‟S6‟ AND 职工号=‟E6‟4) 检索出向供应商S3发过订购单的职工的职工号和仓库号。

SELECT 职工号,仓库号 FROM 职工 WHERE 职工号 IN( SELECT 职工号 FROM 订购单 WHERE 供应商号=‟S3‟ )5) 检索出目前与S3供应商没有联系的职工信息。

用关系代数表示数据查询的典型例子[例]设教学数据库中有3个关系：学生关系S(SNO,SNAME,AGE,SEX)学习关系SC(SNO,CNO,GRADE)课程关系C(CNO,CNAME,TEACHER)下面用关系代数表达式和SQL语句分别表达每个查询语句。

(1) 检索学习课程号为C2的学生学号与成绩。

πSNO，GRADE(σCNO=’C2′(SC))SELECT SNO, GRADEFROM SCWHERE CNO=‘C2’(2) 检索学习课程号为C2的学生学号与姓名πSNO，SNAME(σCNO=’C2′(S SC))由于这个查询涉及到两个关系S和SC，因此先对这两个关系进行自然连接，同一位学生的有关的信息，然后再执行选择投影操作。

此查询亦可等价地写成：πSNO，SNAME（S）（πSNO(σCNO=’C2′(SC))）这个表达式中自然连接的右分量为”学了C2课的学生学号的集合”。

这个表达式比前一个表达式优化，执行起来要省时间，省空间。

SELECTSNO,SNAME FROM S WHERE EXISTS(SELECT * FROM SCWHERESNO=S.SNO ANDCNO=‘C2’)（3）检索选修课程名为MATHS的学生学号与姓名。

πSNO，SANME(σCNAME=’MATHS’(S SC C)) SELECT S.SNO, SNAMEFROM S, SC,CWHERE S.SNO=SC.SNO AND O=OAND CNAME=‘MATHS’（4）检索选修课程号为C2或C4的学生学号。

πSNO(σCNO=’C2’∨CNO=’C4′(SC)) SELECT S.SNO, SNAMEFROM S, SC,CWHERE S.SNO=SC.SNO AND O=OAND CNAME=‘MATHS’（5）检索至少选修课程号为C2或C4的学生学号。

π1(σ1=4∧2=’C2’∧5=’C4′（SC×SC）) SELECTSNOFROM SC AS X, SC AS YWHERE X.SNO=Y.SNO AND O=‘C2’AND O=‘C4’这里（SC×SC）表示关系SC自身相乘的乘积操作，其中数字1，2，4，5都为它的结果关系中的属性序号。

关系代数SQL语句练习已知关系：Student（Sno，Sname，Ssex，Sage，Clno, Sdept）学号姓名性别年龄班级号系Course（Cno，Cname，Cpno，Ccredit）课程号课程名先修课号学分SC （Cno，Sno，Grade）课程号学号成绩其中，下划线表示该关系的主码用关系代数表示下列操作：1.查询年龄为20岁的学生姓名及年龄；2.查询成绩在90分以上的学生姓名。

3.查询没有选修1号课程的学生姓名；4．查询选修了全部课程的学生学号用SQL语句实现下列5--8小题：5.检索学号为980101的学生所选课程的课程名；6.查询选修了1号课程的学生姓名和年龄，并按照年龄大小降序排序；7.将选修了数据库概论课程成绩不及格的学生成绩提高5%；8.建立一视图S_AVGAGE（其中包括性别SEX与平均年龄AVG_AGE两列）用以反映男生、女生的平均年龄。

参考答案1.查询年龄为20岁的学生姓名及年龄；Πsname,sage(σsage=20(Student))2.查询成绩在90分以上的学生姓名。

Πsname(σgrade≥90(SC) Student)3.查询没有选修1号课程的学生姓名；Πsname (Student)- Πsname(σcno=’1’(SC) Student) 4．查询选修了全部课程的学生学号Πsno,cno（SC）÷Πcno（Course）5.检索学号为980101的学生所选课程的课程名；SELECT cnameFROM courseWHERE cno IN(SELECT cnoFROM scW HERE sno=’980101’)；或：SELECT cnameFROM course,scWHERE/doc/f215297609.html,o=http://www.docz /doc/f215297609.html,oAND sno=’980101’)；6.查询选修了1号课程的学生姓名和年龄，并按照年龄大小降序排序；SELECT sname,sageFROM studentWHERE sno IN(SELECT snoFROM scWHERE cno=’1’)ORDER BY sage DESC；或：SELECT sname,sageFROM student,scWHERE student.sno=sc.sno AND cno=’1’ORDER BY sage DESC；7.将选修了数据库概论课程成绩不及格的学生成绩提高5%；UPDATE scSET grade=grade+grade*0.05WHERE cno=（SELECT cno FROM course WHEREcname=’数据库概论’）;AND grade<60;8.建立一视图S_A VGAGE（其中包括性别SEX与平均年龄A VG_AGE两列）用以反映男生、女生的平均年龄。

1.设有如图所示的关系S、SC和C,试用关系代数表达式表示下列查询语句:S C SCS# SNAME AGE SEX1 李强23 男2 刘丽22 女5 张友22 男C# CNAME TEACHERk1 C语言王华k5 数据库原理程军k8 编译原理程军S# C# GRADE1 k1 832 k1 855 k1 922 k5 905 k5 845 k8 80(1) 检索”程军”老师所授课的课程号(C#)和课程名(CNAME)。

(2) 检索年龄大于21的男学生学号(S#)和姓名(SNAME)。

(3) 检索至少选修”程军”老师所授全部课程的学生姓名(SNAME)。

(4) 检索”李强”同学不学课程的课程号(C#)。

(5) 检索至少选修两门课程的课程号(S#)。

(6) 检索全部学生都选修的课程的课程号(C#)和课程名(CNAME)。

(7) 检索选修课程包含”程军”老师所授课程之一的学生学号(S#)。

(8) 检索选修课程号为k1和k5的学生学号(S#)。

(9) 检索选修全部课程的学生姓名(SNAME)。

(10) 检索选修课程包含学号为2的学生所选修课程的学生学号(S#)。

(11) 检索选修课程名为”C语言”的学生学号(S#)和姓名(SNAME)。

（12）检索没有一门课程成绩不及格的学生学号，姓名。

答:本题各个查询语句对应的关系代数表达式表示如下:(1) ΠC#,CNAME(σTEACHER ='程军'(C))(2) ΠS#,SNAME(σAGE>21^SEX ='男'(S))(3) ΠSNAME(S(ΠS#,C#(SC)÷ΠC#(σTEACHER ='程军'(C))))(4) ΠC#(C)-ΠC#(σSNAME ='李强'(S)∞SC)(5) ΠS# (σ1=4^2≠5 (S C×SC))(6) ΠC#,CNAME(C∞(ΠS#,C#(SC)÷ΠS#(S)))(7) ΠS# (SC∞ΠC# (σTEACHER ='程军'(C)))(8) ΠS#,C#(SC)÷ΠC#(σC#=’K1’VC#=’K5’ (C))(9) ΠSNAME(S∞(ΠS#,C#(SC)÷ΠC#(C)))(10) ΠS#,C#(SC)÷ΠC#(σC#=’2’ (S C))(11) ΠS#,SNAME(S∞ΠS#(SC∞(σCNAME ='C语言'(C))))（12）П学号，姓名(学生)-П学号，姓名(σ分数<60(学生∞学习))。