数据库习题课

1、数据库原理及应用A习题课（一）第一章第四章基本概念基本概念:1.数据模型数据模型:数据结构、数据操作、完整性约束数据结构、数据操作、完整性约束2.完整性约束完整性约束:实体、参照、用户定义实体、参照、用户定义3.实体、属性、码实体、属性、码4.三级模式结构：外模式、模式、内模式三级模式结构：外模式、模式、内模式第一章（15题）百货公司百货公司连锁店连锁店职工职工组成组成1k聘用聘用商品商品销售销售nm1n名称名称地址地址电话电话名称名称地址地址电话电话名称名称价格价格生产商生产商姓名姓名年龄年龄性别性别工资工资销售量销售量第一章（16题）基本概念基本概念:1.关系数据库系统关系数据库系统2.关

2、系模型中常用的操作关系模型中常用的操作3.域、笛卡儿积、关系、主码、主属性等域、笛卡儿积、关系、主码、主属性等4.关系的完整性：实体完整性、参照完整性、关系的完整性：实体完整性、参照完整性、用户定义完整性用户定义完整性5.关系运算的定义关系运算的定义关系代数运算关系代数运算二元二元二元二元按列按列辅助专门的关系运算辅助专门的关系运算按行按行按行运算按行运算说明说明不等于不等于小于等于小于等于大于等于大于等于等于等于小于小于或或与与除除 连接连接投影投影 广义笛卡尔积广义笛卡尔积交交差差大于大于比较运比较运算符算符集合运集合运算符算符并并非非 逻辑运逻辑运算符算符选择选择 专门的专门的关系运关系

3、运算符算符含义含义运算符运算符传统的集合运算传统的集合运算： 其运算是按元组（记录）进行的。其运算是按元组（记录）进行的。 设设R1R1和和R2R2为两个度为为两个度为n n的关系（表）且相对应的属的关系（表）且相对应的属性值取自性值取自同一个域同一个域，则可定义三种传统的集合运算：，则可定义三种传统的集合运算：并运算：并运算：R1R1R2R2交运算：交运算：R1R1R2R2差运算：差运算：R1R1R2R2并运算并运算： R1R2 R1R2，结果：度为，结果：度为n n，元组属于，元组属于R1R1或或R2R2 交运算交运算： 交运算：交运算：R1R2R1R2，结果：度为，结果：度为n n 其中

4、元组既属于其中元组既属于R1R1又属于又属于R2R2差运算差运算： 差运算：差运算：R1-R2R1-R2，结果：度为，结果：度为n n，元组属于，元组属于R1R1ABCa1b1c1R1-R2R1-R2条件连接条件连接： 取两个关系的笛卡尔积中属性符合条件取两个关系的笛卡尔积中属性符合条件A B的元的元组，组， A B 为为R.ZS.T:R SA BXR.YZS.YTx13574x13501x45301R SR.ZS.T等值连接等值连接： 取两个关系笛卡尔积中取两个关系笛卡尔积中A、B属性值相等的元组属性值相等的元组R SA=BXR.YZS.YTx16165x45358R SR.Y=S.Y自然连

5、接自然连接： A、B为相同属性组的等值连接为相同属性组的等值连接R SXYZTx1615x4538R S象集象集： 某属性组在其他属性上对应分量的集合某属性组在其他属性上对应分量的集合关系关系R上属性上属性A的象集：的象集：a1的象集：的象集：(b1,c2),(b2,c4)a2的象集：的象集：(b3,c1)a3的象集：的象集：(b1,c2),(b2,c4)a4的象集：的象集：(b1,c2)R(X,Y)R(X,Y)S(Y,Z)=R(X,Y)S(Y,Z)=R(X,Y)Y Y(S(Y,Z)=T(X)(S(Y,Z)=T(X)除法除法：X在在R上的象集包含上的象集包含Y在在S上的投影的元素上的投影的元素

6、a1的象集：的象集：(b1,c2),(b2,c3),(b2,c1)a2的象集：的象集：(b3,c7),(b2,c3)a3的象集：的象集：(b4,c6)c1b2a1c3b2a2c3b2a1c6b4a3c7b3a2c2b1a1CBAd2c3b2d1c1b2d1c2b1DCBR Sa1ARS(1) 1 1()SNOJNOJPNOPSPJ 1()SNOJNOJSPJ 1()SNOJNOJCOLORSPJP红( )-()JNOJNOCOLORCITYJSPJS红天津, 1()()PNO JNOPNOSNOSSPJSPJP(2)(3)(4)(5)第二章（10题）第二章（11题）#,( )CCNAMETEA

7、CHERLIUC#,22( )SSNAMEAGEsexS 男#, 3()CTEACHERSNOsSC C#( )()CCSNAMEWANGCS SC()SNAMESEXTEACHERLIUS SC C女(1)(2)(3)(4)(5)#,#,#()( )SCCNAMESC SCS#,#( )( )SCCTEACHERLIUCC(6)(7)第二章（11题）SQL中连接查询的主要类型：中连接查询的主要类型：l 广义笛卡儿积广义笛卡儿积l 等值连接（含自然连接）等值连接（含自然连接）l 非等值连接非等值连接l 自身连接查询自身连接查询l 外连接查询外连接查询l 复合条件连接查询复合条件连接查询l 嵌套

8、查询嵌套查询l 广义笛卡儿积：广义笛卡儿积： select Student.* , SC.* from Student,SC;注：这样不带连接谓词的连接很少使用注：这样不带连接谓词的连接很少使用,无意义无意义l等值连接（含自然连接）等值连接（含自然连接）:. = . 任何子句中引用表任何子句中引用表1和表和表2中同名属性时，都中同名属性时，都必须加表名前缀。引用唯一属性名时可以加也必须加表名前缀。引用唯一属性名时可以加也可以省略表名前缀。可以省略表名前缀。 例：查询每个学生及其选修课的情况例：查询每个学生及其选修课的情况 (表见表见P59)。 Select Student.*, SC.* fr

9、om Student,SC Where Student.sno=SC.sno;l 自身连接查询自身连接查询 一个表与其自己进行连接，需要给表起别名以一个表与其自己进行连接，需要给表起别名以示区别，由于所有属性都是同名属性，因此必须示区别，由于所有属性都是同名属性，因此必须使用别名前缀。使用别名前缀。例：查询每一门课的间接先修课。例：查询每一门课的间接先修课。 Select Fo, First.cpno, So, Second.cpno From Course First, Course Second Where First.cpno=So;l外连接查询外连接查询:外连接与普通连接的区别：外连接

10、与普通连接的区别： 普通连接只输出满足连接条件的元组普通连接只输出满足连接条件的元组 外连接以指定表为连接主体，将主体表不满足外连接以指定表为连接主体，将主体表不满足连接条件的元组一并输出连接条件的元组一并输出例：查询每个学生及其选修课程的情况包括没有选例：查询每个学生及其选修课程的情况包括没有选修课程的学生。修课程的学生。Select Student.sno,sname,Ssex,Sage,Sdept,cno,gradefrom Student, SCwhere Student.sno=SC.sno;from Studentleft join SC on (student.sno=sc.sn

11、o);l 复合条件连接查询复合条件连接查询 where子句中可以有多个连接条件。子句中可以有多个连接条件。例：查询选修例：查询选修2号课程且成绩在号课程且成绩在90分以上的分以上的所有学生学号和姓名。所有学生学号和姓名。 Select Student.sno, sname from Student, SC where Student.sno=SC.sno and SC.cno=2 and SC.grade90;l 嵌套查询嵌套查询 将一个查询块嵌套在另一个查询块的将一个查询块嵌套在另一个查询块的where子子句或句或having短语的条件中的查询短语的条件中的查询例：查询与例：查询与“刘晨刘晨

12、”同一个系学习的学生信息同一个系学习的学生信息 select sno,sname,sdept from Student where sdept in (select sdept from Student where sname=刘晨刘晨);(1)求供应工程求供应工程J1零件的供应商号码零件的供应商号码SNO: select sno from SPJ where jno=J1;(2)求供应工程求供应工程J1零件零件P1的供应商号码的供应商号码SNO: select sno from SPJ where jno=J1 and pno=P1;(3)求供应工程求供应工程J1零件为零件为红色红色的供应商

13、号码的供应商号码SNO: select sno from SPJ where jno=J1 and pno in (select pno from P where color=红红); 或或: select sno from SPJ,P where SPJ.pno=P.pno and SPJ.jno=J1 and P.color=红红;(4)求没有使用求没有使用天津天津供应商生产的供应商生产的红色红色零件的工程号零件的工程号JNO： 方法一方法一(相关子查询相关子查询): select jno from J where jno not in (select jno from SPJ,S,P w

14、here SPJ.sno=S.sno and SPJ.pno=P.pno and city=天津天津 and color=红红)；(4)求没有使用天津供应商生产的红色零件的工程号求没有使用天津供应商生产的红色零件的工程号JNO： 方法二方法二(不相关子查询不相关子查询): select jno from J where not exists (select * from SPJ where SPJ.jno=J.jno and sno in(select sno from S where city=天津天津) and pno in(select pno from P where color=红红

15、);(4)求没有使用天津供应商生产的红色零件的工程号求没有使用天津供应商生产的红色零件的工程号JNO： 方法三方法三(不相关子查询不相关子查询): select jno from J where not exists (select * from SPJ,S,P where SPJ.jno=J.jno and SPJ.sno=S.sno and SPJ.pno=P.pno and city=天津天津 and color=红红);(5)求至少使用了供应商求至少使用了供应商S1所供应的全部零件的工程所供应的全部零件的工程号号JNO： select distinct jno from SPJ A w

16、here not exists (select * from SPJ B where sno=S1 and not exists (select * from SPJ C where C.pno=B.pno and C.jno=A.jno);(1)找出所有供应商的姓名和所在城市：找出所有供应商的姓名和所在城市： select sname,city from S;(2)找出所有零件的名称找出所有零件的名称,颜色颜色,重量重量: select pname,color,weight from P;(3)找出使用供应商找出使用供应商S1所供应零件的工程号码所供应零件的工程号码: select jno

17、from SPJ where sno=S1;(4)找出工程项目找出工程项目J2使用的各种零件的名称和数量使用的各种零件的名称和数量: select pname,sum(qty) from spj,p where jno=j2 and spj.pno=p.pno group by pname;供应商没确定供应商没确定(5)找出上海厂商供应的所有零件号码找出上海厂商供应的所有零件号码: select distinct pno from SPJ where sno in(select sno from S where city=上海上海);或或: select pno from SPJ,S wher

18、e SPJ.sno=S.sno and city=上海上海;(6)找出使用上海产的零件的工程名称找出使用上海产的零件的工程名称: select jname from J, S, SPJ where J.jno=SPJ.jno and S.sno=SPJ.sno and city=上海上海;或或: select jname from J where jno in(select jno from SPJ where sno in(select sno from S where city=上海上海);(7)找出没有使用天津产的零件的工程号码找出没有使用天津产的零件的工程号码:select jno f

19、rom J where jno not in(select jno from SPJ,S where SPJ.sno=S.sno and city=天津);或: select J.jno from J,S,SPJ where SPJ.jno=J.jno and SPJ.sno=S.sno and city!=天津;或: select jno from J where not exists (select * from SPJ where SPJ.jno=J.jno and sno in (select sno from S where city=天津);(8)把全部红色零件的颜色改成蓝色把全部

20、红色零件的颜色改成蓝色: update P set color=蓝色蓝色 where color=红色红色;(9)由由S5供给供给J4的零件的零件P6改为由改为由S3供应供应: update SPJ set sno=S3 where sno=S5 and pno=P6 and jno=J4;(10)从供应商关系中删除从供应商关系中删除S2的记录的记录,并从供应情况关并从供应情况关系中删除相应的记录系中删除相应的记录: delete from SPJ where sno=S2; delete from S where sno=S2;或或: delete from S where sno=S2 d

21、escade; (11)请将请将(S2,J6,P4,200)插入供应情况关系插入供应情况关系: insert into SPJ values(S2,J6,P4,200);200 ?第三章（第6题）(1) Select * from Customers where city=shanghai;(2) Select Gname,Stocks,Price,Price*0.75 Discount from Goods order by Price(3) Select GoodId,Gname,Price from Goods where Gname like %computer%(4) Select

22、Stocks,Gname,Price from Goods where Stocks between 100 and 500第三章（第6题）(5) Select Cname,Gname,Price,Quantity,Ordersum from Customers,Goods,Orders where Customers. CustomeID=Orders. CustomeID and Goods. GoodID=Orders. GoodID and Date between 2007-7-1 and 2007-12-31 and Ordersum30000(6) Select CustomeI

23、D, GoodID, OrderID from Orders where Quantity200 Group by Date(7) Select * from Orders where CustomeID in (Select CustomeID from Customers where City=beijing )第三章（第6题）(8) Select * from Orders where CustomeID not in (Select CustomeID from Customers where City=tianjing )(9) Select Customers. CustomeID

24、,Cname,City,OrderID,GoodID, Quantity,Ordersum,Date from Orders ,Customers where Customers. CustomeID= Orders . CustomeID and City=xian第三章（第6题）(10) Upade Goods Set Price=Price*0.85 where Stocks500(11) Delete from Goods where Stocks=0(12) Insert into Goods values(T10056,计算机,5300,清华同方,20,0)视图视图:create

25、view V_sanjian as select pno,sno,qty from SPJ,J where SPJ.jno=J.jno and jname=三建三建;(1)找出三建工程项目使用的各种零件代码及数量找出三建工程项目使用的各种零件代码及数量: select pno,sum(QTY) from V_sanjian group by pno;(2)找出供应商找出供应商S1的供应情况的供应情况: select * from V_sanjian where sno=S1;(1)把对表把对表S的的insert权限授予用户张勇权限授予用户张勇,并允许他再将并允许他再将此权限授予其他用户此权限授

26、予其他用户:grant insert on table S to 张勇张勇 with grant option;(2)把查询把查询SPJ表和修改表和修改QTY属性的权限授予用户李天属性的权限授予用户李天明明:grant select, update(QTY) on table SPJ to 李天明李天明;注：用户名不要加单引号注：用户名不要加单引号第三章（第11题）(1) create view Bj_orders(OrderID,Gname,Quantity,CustomerID,Cname) As select OrderID,Gname,Quantity, Orders.Customer

27、ID,Cname from Orders,Customers,Goods where Customers. CustomeID=Orders. CustomeID and Goods. GoodID=Orders. GoodID and City=beijing(2)Select * from Bj_orders where Cname=张三张三第四章（第5题）(1) Create procedure sp_shanghai As select * from Customers where City=shanghai(2) Create procedure sp_Goods Goodname

28、Varchar(10) As select * from Goods where Gname= Goodname第四章（第5题）(3) Create procedure sp_goodsum cnum Varchar(10) snum int output As select snum =sum(Ordersum) from Orders Group by CustomerID having CustomerID= cnum (4) Create procedure sp_insertGoods Goodnum char(10), Goodname char(10),Gprice int,Gprovider char(10),Gstocks i