数据库作业实验六.doc

实验六实验名称：连接查询一、实验目的掌握使用连接的方法从多个表中查询数据。理解内连接、外连接（包括左外连接、右外连接和全外连接）、自身连接的概念和使用。要求学生熟练掌握在FROM子句和在WHERE子句中指定连接条件的这两种方法。二、实验原理在查询语句的FROM子句中用以下形式实现各种连接操作：l FROM 表1 INNER JOIN 表2 ON 表1列名=表2列名 （实现内连接）l FROM 表1 LEFT OUTER JOIN 表2 ON 表1列名=表2列名 （实现左外连接）l FROM 表1 RIGHT OUTER JOIN 表2 ON 表1列名=表2列名 （实现右外连接）l FROM 表1 FULL OUTER JOIN 表2 ON 表1列名=表2列名 （实现全外连接）l FROM 表1 AS 别名1 JOIN 表1 AS 别名2 ON 别名1列名=别名2列名 （实现自身连接）在查询语句的WHERE子句中用以下形式实现各种连接操作：l FROM 表1，表2 WHERE 表1列名=表2列名 （实现内连接）l FROM 表1，表2 WHERE 表1列名*=表2列名 （实现左外连接）l FROM 表1，表2 WHERE 表1列名=*表2列名 （实现右外连接）l FROM 表1 AS 别名1 ，表1 AS 别名2 WHERE 别名1列名=别名2列名 （实现自身连接）三、实验设备安装有SQL SERVER 2005的计算机。四、实验示例1、 检索product 表和sale_item表中数量大于2的相同产品的产品编号、产品名称、数量、单价。select d_id,a.qty,a.unit_price,d_namefrom sale_item as a inner join product as b /*如果改成left join/right join 试分析结果*/on (d_id=_id) and a.qty2order by d_id2、查找出employee表中住址相同的员工的姓名、性别、职称、薪水、住址。select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addrfrom employee as a inner join employee as bon (a.emp_no!=b.emp_no) and (a.emp_nameb.emp_name) and (a.addr=b.addr)3、查找商品名称为14寸显示器商品的销售情况，显示该商品的编号、销售数量、单价和金额select d_id,qty,unit_price,unit_price*qty totpricefrom sale_item a,product bwhere d_id=d_id and prod_name=14寸显示器五、实验内容与步骤1、 查找出employee表中部门相同且住址相同的女员工的姓名、性别、职称、薪水、住址。use companygoselect distinct a.emp_name,a.sex,a.title,a.salary,a.addrfrom employee a,employee bwhere a.addr=b.addr and a.dept=b.dept and a.sex=女 and b.sex=女 and a.emp_name!=b.emp_name2、 检索product 表和sale_item表中相同产品的产品编号、产品名称、数量、单价。select distinct d_id,d_name,qty,unit_pricefrom product,sale_itemwhere sale_d_id=d_id3、 检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。select distinct d_id,d_name,qty,unit_pricefrom product,sale_itemwhere sale_d_id=d_id and sale_item.unit_price 24004、 查询在每张订单中订购金额超过24000元的客户名及其地址。select cust_name,addrfrom customer,saleswhere customer.cust_id=sales.cust_id and tot_amt240005、 查找有销售记录的客户编号、名称和订单总额select customer.cust_id,cust_name,zipfrom customer,saleswhere customer.cust_id=sales.cust_id6、 每位客户订购的每种产品的总数量及平均单价，并按客户号，产品号从小到大排列。select cust_id,prod_id,sum(qty),avg(unit_price)from sales,sale_itemwhere sales.order_no=sale_item.order_nogroup by cust_id,prod_idorder by cust_id,prod_id asc7、 查找在1997年中有销售记录的客户编号、名称和订单总额select sales.cust_id,cust_name,sum(tot_amt)from customer,saleswhere customer.cust_id=sales.cust_id and year(order_date)=1996group by sales.cust_id,customer.cust_name8、分别使用左向外连接、右向外连接、完整外部连接检索product 表和sale_item表中单价高于2400元的相同产品的产品编号、产品名称、数量、单价。并分析比较检索的结果。 左连接：select d_id,prod_name,qty,unit_pricefrom sale_item LEFT OUTER JOIN product on(d_id=sale_d_id)where d_id=sale_d_id and sale_item.unit_price2400右连接：select d_id,prod_name,qty,unit_pricefrom sale_item RIGHT OUTER JOIN product on(d_id=sale_d_id)where d_id=sale_d_id and sale_item.unit_price2400完整连接：select d_id,pro