西南石油大學(xué)數(shù)據(jù)庫實驗項目二

1、西南石油大學(xué)實驗報告課程名稱：數(shù)據(jù)庫原理與應(yīng)用插入你的照片實驗項目名稱：實驗項目2專業(yè)年級班級、姓名、學(xué)號：電子郵件地址：實驗所用機器名： 實驗時間地點：實驗指導(dǎo)教師： 成績批改人批改日期一、實驗課時：4二、實驗?zāi)康?1) 理解查詢的概念和方法。(2) 掌握SELECT語句在單表查詢中的應(yīng)用。(3) 掌握SELECT語句在多表連接查詢中的應(yīng)用。(4) 掌握SELECT語句在嵌套查詢中的應(yīng)用。(5) 掌握SELECT語句在集合查詢中的應(yīng)用。(6) 主要掌握使用“查詢分析器”進行查詢。三、實驗環(huán)境(1) PC機。(2) SQL Server 2008。四、實驗內(nèi)容及步驟  &#

2、160;以下查詢均使用實驗項目1中創(chuàng)建的COMPANY數(shù)據(jù)庫。1單表查詢(1)基本查詢。Q0. 使用查詢分析器從Employee表中檢索出所有員工的姓名。SELECT FNAME,LNAME FROM EMPLOYEE;Q1. 查詢所有員工的FNAME、LNAME、SSN、SEX和SALARY，并分別將SEX和SALARY的列名重命名為“性別”、“工資”。SELECT FNAME,LNAME,SSN,SEX 性別,SALARY 工資FROM EMPLOYEE;Q2.從Employee表中檢索出員工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分別加上“名”、“姓”、“社會

3、保險號”、“生日”、“工資”的標(biāo)題。員工的排序規(guī)則為：首先按工資的降序排列，然后按FNAME的字母升序排列。SELECT FNAME 姓,LNAME 名,SSN 社會保險號,BDATE 生日,SALARY 工資FROM EMPLOYEEORDER BY SALARY DESC,FNAME;Q3. 查詢不重復(fù)的員工工資值。SELECT DISTINCT SALARY FROM EMPLOYEE;Q4. 查詢沒有直接上司的員工姓名。SELECT FNAME,LNAME,SUPERSSN FROM EMPLOYEEWHERE SUPERSSN IS NULL;(2) 基于WHERE子句進行數(shù)據(jù)查詢。

4、1）基于比較條件。Q5. 從Employee表中查詢出工資大于等于40000的員工資料。SELECT * FROM EMPLOYEEWHERE SALARY>=40000;Q6. 查詢所有的女性家屬的全部信息，并按生日的降序排列。SELECT * FROM DEPENDENT WHERE SEX='F'ORDER BY BDATE DESC ;Q7. 查詢所有不屬于5號部門的員工信息。SELECT * FROM EMPLOYEEWHERE (NOT DNO='5');Q8. 利用OR關(guān)鍵字查詢屬于1號或4號部門的員工信息。SELECT * FROM EMP

5、LOYEEWHERE DNO='1' OR DNO='4'Q9.檢索姓名為John B.Smith的員工的SSN、性別、出生日期和地址。SELECT SSN,SEX,BDATE,ADDRESS FROM EMPLOYEEWHERE FNAME='JOHN' AND LNAME ='SMITH' AND MINIT='B'Q10. 查詢1960年以后出生的員工信息(注意日期寫完整)。SELECT * FROM EMPLOYEEWHERE BDATE >='1960-01-01'2）基于BETWE

6、EN子句的查詢。Q11. 從Employee表中查詢出1960年1970年之間出生的員工資料(注意日期寫完整)。SELECT * FROM EMPLOYEEWHERE BDATE BETWEEN '1960-01-01' AND '1970-12-31' ;3）基于IN子句的查詢。Q12. 從Employee表中查詢出部門號為4或者5的員工資料。SELECT * FROM EMPLOYEEWHERE DNO IN (4,5);4）基于LIKE子句的查詢。Q13. 從Employee表中查詢出LNAME中含有字母o的員工資料。SELECT * FROM EMPLO

7、YEEWHERE LNAME LIKE '%o%'Q14. 查詢項目名稱以P開頭的項目信息。SELECT * FROM PROJECTWHERE PNAME LIKE 'P%'Q15.檢索居住在Houston的員工所有信息。SELECT * FROM EMPLOYEEWHERE ADDRESS LIKE '%Houston%'(3) 使用計算列和匯總函數(shù)查詢。Q16. 從Employee表中檢索出員工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需換算成人民幣，匯率假定為1美元=6人民幣元)，并分別加上“名”、“姓”、“社

8、會保險號”、“人民幣工資”的標(biāo)題。SELECT FNAME 名,LNAME 姓,SSN 社會保險號,SALARY*8 人民幣工資 FROM EMPLOYEE;Q17. 統(tǒng)計所有員工的平均工資。SELECT AVG(SALARY) AS 員工平均工資FROM EMPLOYEE;Q18. 查詢所有員工在所有項目上每周工作的總小時數(shù)。SELECT SUM(HOURS) AS 總小時數(shù)FROM WOEKS_ON;Q19. 查詢各個部門員工的平均工資，顯示部門號和對應(yīng)的平均工資值。SELECT DNO,AVG(SALARY) AS 平均工資FROM EMPLOYEEGROUP BY DNO;Q20. 查

9、詢每個員工所有參與項目的每周工作總小時數(shù)，顯示員工SSN和總小時數(shù)。SELECT ESSN, SUM(HOURS) AS 總小時數(shù) FROM WOEKS_ONGROUP BY ESSN;Q21. 列出所有參與了項目的員工編號及其參與項目數(shù)。SELECT DISTINCT ESSN,COUNT(*)FROM WOEKS_ON GROUP BY ESSN Q22. 統(tǒng)計參與了2個以上項目的員工編號和其參與項目的每周工作平均時間。SELECT ESSN ,COUNT(*) AS 項目數(shù),AVG(HOURS) AS 每周工作平均時間FROM WOEKS_ONGROUP BY ESSNHAVING CO

10、UNT(*)>2; Q23. 查詢有2個以上男性員工的部門編號和該部門的總?cè)藬?shù)（包括男女員工）(選做)。SELECT DNO, COUNT(*) AS 總?cè)藬?shù)FROM EMPLOYEEGROUP BY SEX,DNOHAVING COUNT(*)>2 AND SEX='M'2多表連接查詢(使用JOIN)Q24. 查詢部門編號、名稱及該部門經(jīng)理的姓名。SELECT DEPARTMENT.DNUMBER,DEPARTMENT.DNAME,EMPLOYEE.FNAME+','+EMPLOYEE.MINIT+','+EMPLOYEE.LNAM

11、E AS 姓名FROM DEPARTMENTJOIN EMPLOYEEON DEPARTMENT.MGRSSN=EMPLOYEE.SSNQ25. 查詢所有員工（包括沒有家屬的員工）姓名、編號及其家屬姓名。SELECT EMPLOYEE.*,DEPENDENT.DEPENDENT_NAMEFROM EMPLOYEELEFT OUTER JOIN DEPENDENTON EMPLOYEE.SSN=DEPENDENT.ESSNQ26. 查詢所有為Research部門工作的員工姓名及地址。SELECT FNAME,MINIT,LNAME,ADDRESSFROM EMPLOYEE JOIN DEPART

12、MENT ON EMPLOYEE.DNO=DEPARTMENT.DNUMBERWHERE DEPARTMENT.DNAME='RESEARCH'Q27. 對于所有位于Stafford的項目，查詢項目的編號、項目負(fù)責(zé)部門編號以及該部門經(jīng)理的姓、地址、生日。SELECT PNUMBER,DNUM,LNAME,ADDRESS,BDATEFROM EMPLOYEEJOIN PROJECT ON (EMPLOYEE.DNO=PROJECT.DNUM)JOIN DEPT_LOCATIONSON (PROJECT.DNUM=DEPT_LOCATIONS.DNUMBER)WHERE DLOCA

13、TION='Stafford'Q28. 查詢有兩個或以上家屬的員工姓名(此題較難，若不能完成者可只查詢出員工的SSN而不是姓名)。SELECT SSNFROM EMPLOYEEJOIN DEPENDENT ON(EMPLOYEE.SSN=DEPENDENT.ESSN)GROUP BY SSNHAVING COUNT(SSN)>2;Q29. 查詢在ProductX項目上每周工作時間超過10小時的部門5的員工姓名。SELECT FNAME,LNAMEFROM EMPLOYEEJOIN WOEKS_ON ON(EMPLOYEE.SSN=WOEKS_ON.ESSN)JOIN PR

14、OJECT ON (WOEKS_ON.PNO=PROJECT.PNUMBER)WHERE PROJECT.PNAME='PRODUCTX'Q30. 對于每個項目，列出項目名稱以及所有員工在此項目上工作的總時間。SELECT PNAME,SUM(HOURS)FROM WOEKS_ONJOIN PROJECT ON (WOEKS_ON.PNO=PROJECT.PNUMBER)GROUP BY PNAME;Q31. 對于每個部門，列出部門名稱以及此部門員工的平均工資。SELECT DNAME,AVG(SALARY)FROM EMPLOYEEJOIN DEPARTMENT ON (EM

15、PLOYEE.DNO=DEPARTMENT.DNUMBER)GROUP BY DNAME;Q32. 對于每個員工(包括無上司員工)，查詢其姓名以及他/她的直接上司的姓名。SELECT E1.LNAME+','+E1.FNAME 員工姓名,E2.LNAME+','+E2.FNAME 上司姓名FROM Company_Henianchen.dbo.EMPLOYEE E1left JOIN Company_Henianchen.dbo.EMPLOYEE E2 ON (E1.SUPERSSN=E2.SSN)Q33. 查詢Company數(shù)據(jù)庫中所有員工SSN與DNAME(

16、部門名稱)的組合。SELECT SSN,DNAMEFROM EMPLOYEEJOIN DEPARTMENT ON (EMPLOYEE.DNO=DEPARTMENT.DNUMBER)Q34. 查詢有兩個或以上“工資大于等于30000員工”的部門名稱。3嵌套查詢(子查詢)Q35. 查詢Research部門的員工姓名。SELECT LNAME+','+FNAME 姓名FROM EMPLOYEEwhere DNO=(SELECT DNUMBER FROM DEPARTMENT WHERE DNUMBER=5)Q36. 查詢參與了項目的員工姓名。(必須用IN謂詞)SELECT LNAME

17、+','+FNAME 姓名FROM EMPLOYEEwhere DNO IN (SELECT DNUM FROM PROJECT)Q37. 查詢參與了項目的員工姓名。(必須用EXISTS謂詞)SELECT LNAME+','+FNAME 姓名FROM EMPLOYEEwhere EXISTS(SELECT* FROM PROJECT WHERE EMPLOYEE.DNO=PROJECT.DNUM)Q38. 查詢沒有參與任何部門5控制項目的員工姓名。SELECT LNAME+','+FNAME 姓名FROM EMPLOYEEwhere DNO<

18、;>ALL(SELECT DNUMBER FROM DEPARTMENT WHERE DNUMBER=5)Q39. 查詢工資超過部門5所有員工工資的員工姓名。SELECT LNAME+','+FNAME 姓名FROM EMPLOYEEwhere SALARY >ALL(SELECT SALARY FROM EMPLOYEE WHERE DNO=5)Q40. 查詢參與了所有項目的員工姓名。(選做)Q41. 查詢至少參與了所有部門4控制項目的員工姓名。(選做)Q42. 查詢至少參與了所有John Smith參與項目的員工姓名。(選做)Q43. 查詢至少有一個家屬的部門經(jīng)理姓名。(必須用EXISTS謂詞)SELECT LNAME FNAME FROM EMPLOYEEwhere EXISTS(SELECT * FROM Company_Henianchen.dbo.DEPENDENT)JOIN Company_Henianchen.dbo.DEPARTMENT ON (DEPENDENT.ESSN=DEPARTMENT.MGASSN)Q44. 查詢有兩個或以上隸屬員工的部門名稱及其“工資大于等于300