数据库练习四(内连接,外连接)答案.doc

一 、创建学生表，成绩表，课程表如下：A、广义笛卡尔方法操作1、.操作：select *from 学生表,成绩表得到多少条数据？2、操作：select *from 学生表,成绩表,课程表得到多少条数据？3、从学生表和成绩表中查询出已经选课学生的正确选课结果。要求显示出学生的学号、姓名和成绩。use 学生管理 go select学生表.学号,学生表.姓名,成绩表.成绩 from 学生表,成绩表 where 学生表.学号=成绩表.学号4、从学生表、成绩表、课程表中查询出已经选课学生的姓名，课程名称，授课老师。 select 学生表.姓名,成绩表.成绩,课程表.授课教师,课程表.课程名称 from 学生表,成绩表,课程表 where 学生表.学号=成绩表.学号 and 成绩表.课程编号=课程表.课程编号5、从学生表和成绩表中查询出姓名为张三的学生的选课结果。要求显示出学生的学号、姓名和成绩 select 学生表.学号,学生表.姓名,成绩表.成绩 from 学生表,成绩表 where 学生表.学号=成绩表.学号 and 学生表.姓名=张三6、从学生表和成绩表中查询出成绩85的选课结果。要求显示出学生的学号、姓名和成绩 use 学生管理 go select 学生表.学号,学生表.姓名,成绩表.成绩 from 学生表,成绩表 where 学生表.学号=成绩表.学号 and 成绩表.成绩607、选出授课教师为张慧所带学生的信息。要求显示出学生的、姓名和成绩 select 学生表.姓名,成绩表.成绩 from 学生表,成绩表,课程表 where 学生表.学号=成绩表.学号 and 成绩表.课程编号=课程表.课程编号 and 课程表.授课教师=张慧B、自然连接法操作1 使用“内连接”方法实现3到7题的操作3 use 学生管理 go select 学生表.学号,学生表.姓名,成绩表.成绩 from 学生表 inner join 成绩表 on 学生表.学号=成绩表.学号 4 select 学生表.姓名,成绩表.成绩,课程表.授课教师,课程表.课程名称 from 学生表 inner join 成绩表 on 学生表.学号=成绩表.学号 inner join 课程表 on 成绩表.课程编号=课程表.课程编号 5 use 学生管理 go select 学生表.学号,学生表.姓名,成绩表.成绩 from 学生表 inner join 成绩表 on 学生表.学号=成绩表.学号 where 学生表.姓名=张三6select 学生表.学号,学生表.姓名,成绩表.成绩 from 学生表 inner join 成绩表 on 学生表.学号=成绩表.学号 where 成绩表.成绩607 use 学生管理 go select 学生表.姓名,成绩表.成绩 from 学生表 inner join 成绩表 on 学生表.学号=成绩表.学号 inner join 课程表 on 成绩表.课程编号=课程表.课程编号 where 课程表.授课教师=张慧 2 、使用“外连接”方法实现：a、从学生表和成绩表中查询出所有学生的选课信息（包括已选课和未选课的学生）。 select * from 学生表 left join 成绩表 on 学生表.学号=成绩表.学号 b. 以成绩表为主，从学生表和成绩表中查询出所有学生的选课信息。 select * from 学生表 right join 成绩表 on 学生表.学号=成绩表.学号3 、学生表和成绩表做全外连接，并查看结果，比较其与A.3操作的区别。use 学生管理 go select * from 学生表 full join 成绩表 on 学生表.学号=成绩表.学号 4 、使用自连接 查询成绩表中成绩有两门或者以上的学生的学号,成绩,课程编号。 select distinct a.学号,a.成绩,a.课程编号 from 成绩表 as a, 成绩表 as b where a.学号 = b.学号 and a.课程编号 b.课程编号 goC 、 使用子查询 1 从学生表和成绩表中挑选出成绩及格的学生学号和学生姓名。 select 学号,姓名 from 学生表 where 学号 in( select 学号 from 成绩表 where 成绩 60 )2 学生表和成绩表中查询出姓名为张三的学生的学号、姓名 use 学生管理 go select 学号,姓名 from 学生表 where 学号 in( select 学号 from 学生表 where 姓名=张三 ) 二、在“商品管理数据库”做如下操作A. 在“商品管理数据库”数据库中查询客户的购买信息。要求显示客户编号、客户姓名、商品编号和销售日期字段信息。B. 在“商品管理数据库”库中查询出客户购买商品的详细信息，要求显示客户编号、客户姓名、商品编号、商品名称、销售金额和商品类型名字段信息。C. 在B查询结果的基础上，只显示出客户编号为20130001的客户购买商品的信息应如何解题？use 商品管理数据库goselect 客户信息表.客户编号,客户姓名,商品信息表.商品编号,商品名称,销售金额,商品类型 from 客户信息表,商品信息表,销售信息表,商品类型表 where 客户信息表.客户编号 = 销售信息表.客户编号 and 销售信息表.商品编号 = 商品信息表.商品编号 and 商品信息表.商品类型编号 = 商品类型表.商品类型编号 and 客户信息表.客户编号 = 20130001goD. 使用“内连接”方法实现B 的查询，并且只显示客户编号为20130001的客户的购买信息。 use 商品管理数据库 go select 客户信息表.客户编号,客户姓名,商品信息表.商品编号,商品名称,销售金额,商品类型名 from 商品类型表 join 商品信息表 on 商品信息表.商品类型编号 = 商品类型表.商品类型编号 join 销售信息表 on 销售信息表.商品编号 = 商品信息表.商品编号 join 客户信息表 on 客户信息表.客户编号 = 销售信息表.客户编号 where 客户信息表.客户编号 = 20130001goE. 以客户信息表为主表，显示出客户购买商品的全部信息。要求显示客户编号，客户姓名，商品编号和销售日期字段信息。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 (select MAX(销售数量) 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 DESC 38依据36题的关系模式，试用SQL语言查询没有参加任何协会的学生姓名和所在系。答：SELECT Sname，Dept FROM S WHERE Sno NOT IN (SELECT Sno FROM SA );39依据36题的关系模式，试用关系代数查询计算机系的学生姓名和年龄。答：40. 对于学生选课关系，其关系模式为：学生（学号，姓名，年龄，所在系）；选课（学号，课程号 成绩）。用关系代数完成如下查询。1）求学号为010111的学生的数据。2）选出学生的学号，姓名两列。3）选出学