数据库练习四内连接-外连接答案.doc

. . 一 、创建学生表，成绩表，课程表如下：A、广义笛卡尔方法操作1、.操作：select*from学生表,成绩表得到多少条数据？2、操作：select*from学生表,成绩表,课程表得到多少条数据？3、从学生表和成绩表中查询出已经选课学生的正确选课结果。要求显示出学生的学号、和成绩。use学生管理goselect学生表.学号,学生表.,成绩表.成绩from学生表,成绩表where学生表.学号=成绩表.学号4、从学生表、成绩表、课程表中查询出已经选课学生的，课程名称，授课老师。select学生表.,成绩表.成绩,课程表.授课教师,课程表.课程名称from学生表,成绩表,课程表where学生表.学号=成绩表.学号and成绩表.课程编号=课程表.课程编号5、从学生表和成绩表中查询出为三的学生的选课结果。要求显示出学生的学号、和成绩select学生表.学号,学生表.,成绩表.成绩from学生表,成绩表where学生表.学号=成绩表.学号and学生表.='三'6、从学生表和成绩表中查询出成绩>85的选课结果。要求显示出学生的学号、和成绩use学生管理goselect学生表.学号,学生表.,成绩表.成绩from学生表,成绩表where学生表.学号=成绩表.学号and成绩表.成绩>607、选出授课教师为慧所带学生的信息。要求显示出学生的、和成绩select学生表.,成绩表.成绩from学生表,成绩表,课程表where学生表.学号=成绩表.学号and成绩表.课程编号=课程表.课程编号and课程表.授课教师='慧'B、自然连接法操作1使用“连接”方法实现3到7题的操作3 use学生管理goselect学生表.学号,学生表.,成绩表.成绩from学生表innerjoin成绩表on学生表.学号=成绩表.学号 4 select学生表.,成绩表.成绩,课程表.授课教师,课程表.课程名称from学生表innerjoin成绩表on学生表.学号=成绩表.学号innerjoin课程表on成绩表.课程编号=课程表.课程编号 5 use学生管理goselect学生表.学号,学生表.,成绩表.成绩from学生表innerjoin成绩表on学生表.学号=成绩表.学号where学生表.='三'6select学生表.学号,学生表.,成绩表.成绩from学生表innerjoin成绩表on学生表.学号=成绩表.学号where成绩表.成绩>607use学生管理goselect学生表.,成绩表.成绩from学生表innerjoin成绩表on学生表.学号=成绩表.学号innerjoin课程表on成绩表.课程编号=课程表.课程编号where课程表.授课教师='慧'2 、使用“外连接”方法实现：a、从学生表和成绩表中查询出所有学生的选课信息（包括已选课和未选课的学生）。select*from学生表leftjoin成绩表on学生表.学号=成绩表.学号b. 以成绩表为主，从学生表和成绩表中查询出所有学生的选课信息。select*from学生表rightjoin成绩表on学生表.学号=成绩表.学号3 、学生表和成绩表做全外连接，并查看结果，比较其与A.3操作的区别。use学生管理goselect*from学生表fulljoin成绩表on学生表.学号=成绩表.学号4 、使用自连接 查询成绩表中成绩有两门或者以上的学生的学号,成绩,课程编号。selectdistincta.学号,a.成绩,a.课程编号from成绩表asa,成绩表asbwherea.学号=b.学号anda.课程编号<>b.课程编号goC 、 使用子查询 1 从学生表和成绩表中挑选出成绩与格的学生学号和学生。select学号,from学生表where学号in(select学号from成绩表where成绩>60 )2 学生表和成绩表中查询出为三的学生的学号、use学生管理goselect学号,from学生表where学号in(select学号from学生表where='三')二、在“商品管理数据库”做如下操作A. 在“商品管理数据库”数据库中查询客户的购买信息。要求显示客户编号、客户、商品编号和销售日期字段信息。B. 在“商品管理数据库”库中查询出客户购买商品的详细信息，要求显示客户编号、客户、商品编号、商品名称、销售金额和商品类型名字段信息。C. 在B查询结果的基础上，只显示出客户编号为20130001的客户购买商品的信息应如何解题？use 商品管理数据库goselect 客户信息表.客户编号,客户,商品信息表.商品编号,商品名称,销售金额,商品类型 from 客户信息表,商品信息表,销售信息表,商品类型表 where 客户信息表.客户编号 = 销售信息表.客户编号 and 销售信息表.商品编号 = 商品信息表.商品编号 and 商品信息表.商品类型编号 = 商品类型表.商品类型编号 and 客户信息表.客户编号 = '20130001'goD. 使用“连接”方法实现B 的查询，并且只显示客户编号为20130001的客户的购买信息。 use 商品管理数据库 go select 客户信息表.客户编号,客户,商品信息表.商品编号,商品名称,销售金额,商品类型名 from 商品类型表 join 商品信息表 on 商品信息表.商品类型编号 = 商品类型表.商品类型编号 join 销售信息表 on 销售信息表.商品编号 = 商品信息表.商品编号 join 客户信息表 on 客户信息表.客户编号 = 销售信息表.客户编号 where 客户信息表.客户编号 = '20130001'goE. 以客户信息表为主表，显示出客户购买商品的全部信息。要求显示客户编号，客户，商品编号和销售日期字段信息。use 商品管理数据库 go select 客户信息表.客户编号,客户,商品编号,销售日期 from 客户信息表 left join 销售信息表 on 客户信息表.客户编号 = 销售信息表.客户编号 goF. 以销售信息表为主表，显示所有商品客户购买情况。要求显示客户编号，客户，商品编号和销售日期字段信息。G. 将客户信息表和销售信息表做全外连接，并查看结果。H“商品管理数据库”中查询购买了两种或以上商品的客户编号和商品编号。use 商品管理数据库 go select distinct a.商品编号, a.客户编号 from 销售信息表 a, 销售信息表 as b where a.客户编号 = b.客户编号 and a.商品编号 <> b.商品编号 goI 从“商品管理数据库”的销售信息表查询出销售单价最高的商品详细信息。select商品编号,销售数量from销售信息表where销售数量in(selectMAX(销售数量)from销售信息表)J从“商品管理数据库”中查询出在2012年12月11日购买商品的客户和联系。select客户,联系from客户信息表where客户编号in(select客户编号from销售信息表where销售日期>'2012-12-11')go三、自考真题36设学生社团管理数据库有三个关系：S(Sno，Sname，Age，Sex，Dept)A(Ano，Aname，Location，Manager)SA(Sno，Ano，Date)其中表S的属性分别表示学号、年龄、性别和所在系；表A的属性分别表示会员编号、协会名、协会的办公地点和负责人(负责人为学号)；表SA描述了学生参加社团的情况，其属性分别表示学号、协会编号、加入协会时间。试用SQL语言查询参加“篮球”协会的学生和所在系。答：SELECT Sname ,Dept FROM S,A ,SA WHERE(Aname=篮球 AND S. Sno=SA. Sno AND A. Ano=SA. Ano );37依据36题的关系模式，试用SQL语言查询每个协会的协会编号和学生数，并按人数降序排列。 答：SELECT Ano,COUNT(*) FROM SA GROUP BY Ano ORDER BY 2 DESC38依据36题的关系模式，试用SQL语言查询没有参加任何协会的学生和所在系。答：SELECT Sname，Dept FROM S WHERE Sno NOT IN (SELECT Sno FROM SA );39依据36题的关系模式，试用关系代数查询计算机系的学生和年龄。答：40. 对于学生选课关系，其关系模式为：学生（学号，年龄，所在系）；选课（学号，课程号 成绩）。用关系代数完成如下查询。1）求学号为010111的学生的数据。2）选出学生的学号，两列。3）选出学号为010111的学生学号，两列。4）求选课成绩>90的学生的和学号。学号=010111(学生)学号，(学生)学号，(学号=010111(学生)学号，(成绩>90（选课）(学生)40 .有下面三个关系模式：学生（学号，系别号）项目（项目号，项目名称，报酬）参加（学号，项目号，工时）用关系代数实习，列出“王明”同学所参加项目的名称。项目名称 (=王明（学生）(参加)（项目）)7 / 7