數(shù)據(jù)庫(kù)實(shí)驗(yàn)多表查詢參考答案

1、數(shù)據(jù)庫(kù)原理實(shí)驗(yàn)教材實(shí)驗(yàn)答案實(shí)驗(yàn)二實(shí)驗(yàn)二(1)找出同一天進(jìn)入公司服務(wù)的員工。a.employeeNoa.employeeNamea.hireDateb.employeeNob.employeeNamefromEmployeeaEmployeeasbwherea.employeeNo!=b.employeeNoa.employeeNameb.employeeName(a.hireDate=b.hireDate)參考解答：DISTINCTa.

2、hireDatea.employeeNoa.employeeName消除重復(fù)的元組消除重復(fù)的元組FROMemployeeaemployeebWHEREa.employeeNo!=b.employeeNo本人不跟本人連接a.hireDate=b.hireDateDERBYa.hireDatea.employeeNoa.hireDatea.employeeNoa.employeeNameb.employeeNob.employeeNamef

3、romemployeeaemployeebwherea.employeeNo(avg(salary)avgsalaryFROMEmployee)(4)查找有銷售記錄的客戶編號(hào)、名稱和訂單總額。a.customerNoa.customerNameb.derNosum(quantityprice)fromCustomeraderMasterbderDetailcwherea.customerNo=b.customerNob.derNo=c.

4、derNogroupbya.customerNoa.customerNameb.derNo(7)查詢derMaster表中訂單金額最高的訂單號(hào)及訂單金額。derNoderSumfromderMasterwherederSum=(max(derSum)fromderMaster)(8)在訂單主表中查詢訂單金額大于“E2005002業(yè)務(wù)員在200819這天所接的任一張訂單的金額”的所有訂單信息。FROMderMasterWHEREderSu

5、many(derSumFROMderMasterWHEREsalerNo=E2005002derDate=20080109)(9)查詢單價(jià)高于400元的商品編號(hào)、商品名稱、訂貨數(shù)量和訂貨單價(jià)。a.productNoproductNamequantitypriceFROMProductaderDetailbWHEREa.productNo=b.productNoprice400DERBYproductName(10)分別使用左外連接、右外

6、連接、完整外部連接查詢單價(jià)高于400元的商品編號(hào)、商品名稱、訂貨數(shù)量和訂貨單價(jià)，并分析比較檢索的結(jié)果。左外連接命令：左外連接命令：a.productNoproductNamequantitypriceFROMProductaLEFTOUTERJOINderDetailbONa.productNo=b.productNoWHEREprice400a.productNoa.productNameb.quantityb.priceFromde