數(shù)據(jù)庫實驗多表查詢參考答案

1、數(shù)據(jù)庫原理實驗教材實驗答案實驗二實驗二(1)找出同一天進入公司服務(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)查找有銷售記錄的客戶編號、名稱和訂單總額。a.customerNoa.customerNameb.derNosum(quantityprice)fromCustomeraderMasterbderDetailcwherea.customerNo=b.customerNob.derNo=c.

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

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

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