一、實驗目的
掌握使用連接配接的方法從多個表中查詢資料。了解内連接配接、外連接配接（包括左外連接配接、右外連接配接和全外連接配接）、自身連接配接的概念和使用。要求學生熟練掌握在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 2000的計算機。
四、實驗示例
1、檢索product 表和sale_item表中數量大于2的相同産品的産品編号、産品名稱、數量、單價。
select a.prod_id,a.qty,a.unit_price,b.prod_name
from sale_item as a inner join product asb /*如果改成left join/right join 試分析結果*/
on (a.prod_id=b.pro_id) and a.qty>2
order by a.prod_id
2、查找出employee表中住址相同的員工的姓名、性别、職稱、薪水、住址。
select a.emp_name,a.sex,a.title,a.salary,a.addr,b.emp_name,b.sex,b.title,b.salary,b.addr
from employee as a inner join employee asb
on (a.emp_no!=b.emp_no) and(a.emp_name>b.emp_name) and (a.addr=b.addr)
3、查找商品名稱為14寸顯示器商品的銷售情況，顯示該商品的編号、銷售數量、單價和金額
selecta.prod_id,qty,unit_price,unit_price*qty totprice
from sale_item a,product b
where a.prod_id=b.prod_id andprod_name='14寸顯示器'
五、實驗内容
1、查找出employee表中部門相同且住址相同的女員工的姓名、性别、職稱、薪水、住址。
select distinct a.emp_name,a.sex,a.dept,a.title,a.salary,a.addr
from employeea,employeeb
where a.dept=b.deptand a.sex='女'and a.addr=b.addrand a.emp_no!=b.emp_no 複制
2、檢索product 表和sale_item表中相同産品的産品編号、産品名稱、數量、單價。
select distinctb.prod_id,a.prod_name,b.qty,b.unit_price
from producta,sale_itemb
where a.prod_id=b.prod_id 複制
SELECT dbo.product.prod_id,dbo.product.prod_name, dbo.sale_item.qty, dbo.sale_item.unit_price
FROM dbo.product INNER JOIN
dbo.sale_item ONdbo.product.prod_id = dbo.sale_item.prod_id 複制
3、檢索product 表和sale_item表中單價高于2400元的相同産品的産品編号、産品名稱、數量、單價。
select distinct a.prod_id,a.prod_name,b.qty,b.unit_price
from producta,sale_itemb
where a.prod_id=b.prod_idand unit_price>2400 複制
SELECT dbo.product.prod_id, dbo.product.prod_name, dbo.sale_item.qty,dbo.sale_item.unit_price
FROM dbo.productINNER JOIN
dbo.sale_item ON dbo.product.prod_id = dbo.sale_item.prod_id
WHERE (dbo.sale_item.unit_price > 2400) 複制
4、查詢在每張訂單中訂購金額超過24000元的客戶名及其位址。
select a.cust_name,a.addr,b.tot_amt
from customera,salesb
where a.cust_id=b.cust_idand b.tot_amt>24000 複制
SELECT dbo.customer.cust_name, dbo.sales.tot_amt
FROM dbo.customerINNER JOIN
dbo.sales ON dbo.customer.cust_id = dbo.sales.cust_id
WHERE (dbo.sales.tot_amt> 24000) 複制
5、查找有銷售記錄的客戶編号、名稱和訂單總額
select a.cust_id,a.cust_name,sum(tot_amt)訂單總額
from customera,salesb
where a.cust_id= b.cust_id
group bya.cust_id,a.cust_name 複制
SELECT dbo.customer.cust_id, dbo.sales.tot_amt, dbo.customer.cust_name
FROM dbo.customerINNER JOIN
dbo.sales ON dbo.customer.cust_id = dbo.sales.cust_id 複制
6、每位客戶訂購的每種産品的總數量及平均單價，并按客戶号，産品号從小到大排列。
select prod_id,sum(qty)as 總數量,avg(unit_price)as 平均單價
from sale_item,sales
where sale_item.order_no=sales.order_no
group byprod_id
order by prod_id asc 複制
SELECT TOP (100) PERCENTdbo.sale_item.qty, dbo.sale_item.prod_id, dbo.sales.cust_id,AVG(dbo.sale_item.unit_price) AS Expr1
FROM dbo.sale_itemINNER JOIN
dbo.sales ON dbo.sale_item.order_no = dbo.sales.order_no
GROUP BY dbo.sale_item.qty, dbo.sale_item.prod_id,dbo.sales.cust_id
ORDER BY dbo.sales.cust_id, dbo.sale_item.prod_id 複制
7、查找在1997年中有銷售記錄的客戶編号、名稱和訂單總額
select sales.cust_id,cust_name,tot_amt
from sales,customer
where sales.cust_id=customer.cust_idand YEAR(order_date)=1997; 複制
SELECT dbo.sales.cust_id, dbo.customer.cust_name,dbo.sales.tot_amt, YEAR(dbo.sales.order_date) AS year
FROM dbo.sales INNER JOIN
dbo.customer ONdbo.sales.cust_id = dbo.customer.cust_id
WHERE (YEAR(dbo.sales.order_date) = 1997)
8、分别使用左向外連接配接、右向外連接配接、完整外部連接配接檢索product 表和sale_item表中單價高于2400元的相同産品的産品編号、産品名稱、數量、單價。并分析比較檢索的結果。
左向外連接配接：
select b.prod_id,a.prod_name,b.qty,b.unit_price
from product a left outer join sale_item b
on(a.prod_id=b.prod_id)and b.unit_price>2400 複制
右向外連接配接：
select a.prod_id,a.prod_name,b.qty,b.unit_price
from product a right outer join sale_item b
on a.prod_id=b.prod_id and b.unit_price>2400 複制
完整向外連接配接
select a.prod_id,a.prod_name,b.qty,b.unit_price
from product a full join sale_item b
on(a.prod_id=b.prod_id)and b.unit_price>2400 複制