數(shù)據(jù)庫實驗二

1、西南石油大學實驗報告課程名稱：數(shù)據(jù)庫原理插入你的照片實驗項目名稱：實驗項目2 SQL數(shù)據(jù)操縱語言專業(yè)年級班級、姓名、學號：軟件13級2班電子郵件地址：實驗所用機器名：實驗時間地點：2015.12.03 明理樓實驗指導教師：孫瑜 成績批改人批改日期注意：在粘貼截圖時請保留窗口完整標題，但只需保留關鍵界面，多余的空白界面請刪除。一、實驗課時：4二、實驗目的(1) 理解查詢的概念和方法。(2) 掌握SELECT語句在單表查詢中的應用。(3) 掌握SELECT語句在多表連接查詢中的應用。(4) 掌握SELECT語句在嵌套查詢中的應用。(5) 掌握SELECT語句在集合查詢中的應用。(6) 主要掌握使用

2、“查詢分析器”進行查詢。三、實驗環(huán)境(1) PC機。(2) SQL Server 2008。四、實驗內(nèi)容及步驟以下查詢均使用實驗項目1中創(chuàng)建的COMPANY數(shù)據(jù)庫。1單表查詢(1)基本查詢。Q0. 使用查詢分析器從Employee表中檢索出所有員工的姓名。SQL文本：SELECT FNAME,LNAME FROM EMPLOYEE;(以下所有題目都必須按照上圖的要求截圖)Q1.從Employee表中檢索出員工的FNAME、LNAME、SSN、BDATE、SALARY等字段，并分別加上“名”、“姓”、“社會保險號”、“生日”、“工資”的標題。員工的排序規(guī)則為：首先按工資的降序排列，然后按FNAM

3、E的字母升序排列。SQL文本:SELECT Fname 名,Lname 姓,Ssn 社會保險號,Bdate 生日,Salary 工資FROM EMPLOYEEORDER BY Salary DESC,Fname ; Q2. 查詢不重復的員工工資值。SQL文本:SELECT DISTINCT SalaryFROM EMPLOYEE;Q3. 查詢沒有直接上司的員工姓名。SQL文本:SELECT Fname,LnameFROM EMPLOYEEWHERE Super_ssn IS NULL;(2) 基于WHERE子句進行數(shù)據(jù)查詢。1）基于比較條件。Q4. 從Employee表中查詢出工資大于等于40

4、000的員工資料。SQL文本:SELECT *FROM EMPLOYEEWHERE Salary = 40000;Q5.檢索姓名為John B.Smith的員工的SSN、性別、出生日期和地址。SQL文本:SELECT Ssn,Sex,Bdate,AddressFROM EMPLOYEEWHERE Fname = JOHN AND Lname = SMITH AND Minit = B;2）基于BETWEEN子句的查詢。Q6. 從Employee表中查詢出1960年1970年之間出生的員工資料。SQL文本:SELECT * FROM EMPLOYEEWHERE Bdate BETWEEN 196

5、0-01-01 AND 1970-01-01;3）基于IN子句的查詢。Q7. 從Employee表中查詢出部門號為4或者5的員工資料。SQL文本:SELECT *FROM EMPLOYEEWHERE Dno IN (4,5);4）基于LIKE子句的查詢。Q8. 從Employee表中查詢出LNAME中含有字母o的員工資料。SQL文本:SELECT *FROM EMPLOYEEWHERE Lname LIKE %O%;Q9.檢索居住在Houston的員工所有信息。SQL文本:SELECT *FROM EMPLOYEEWHERE Address LIKE %Houston%;(3) 使用計算列查詢

6、。Q10. 從Employee表中檢索出員工的FNAME、LNAME、SSN、SALARY等字段(其中SALARY需換算成人民幣，匯率假定為1美元=8人民幣元)，并分別加上“名”、“姓”、“社會保險號”、“人民幣工資”的標題。SQL文本:SELECT Fname 名,Lname 姓,Ssn 社會保險號,Salary * 8 人民幣工資FROM EMPLOYEE;2多表連接查詢(使用JOIN)Q11. 查詢所有為Research部門工作的員工姓名及地址。SELECT Fname, Lname, Address FROM EMPLOYEE E JOIN DEPARTMENT D ON E.Dno=

7、D.Dnumber WHERE Dname=Research;Q12. 對于所有位于Stafford的項目，查詢項目的編號、項目負責部門編號以及該部門經(jīng)理的姓、地址、生日。SQL文本:SELECT E.Lname,E.Address,E.Bdate,P.Pnumber,P.DnumFROM PROJECT P JOINDEPARTMENT D ON (P.Dnum = D.Dnumber)JOIN EMPLOYEE E ON (D.Mgr_ssn = E.Ssn)WHERE Plocation = Stafford;Q13. 查詢有兩個或以上家屬的員工姓名(此題較難，若不能完成者可只查詢出員工

8、的SSN而不是姓名)。SQL文本:SELECT E.Fname,E.LnameFROM EMPLOYEE EJOIN DEPENDENT D ON (D.Essn = E.Ssn) GROUP BY D.Essn,E.Fname,E.LnameHAVING COUNT(*) = 2;Q14. 查詢在ProductX項目上每周工作時間超過10小時的部門5的員工姓名。(對于連接操作必須是主外鍵，但是對于where條件并沒有什么特殊要求)SQL文本:SELECT E.Fname,E.LnameFROM EMPLOYEE EJOINWORKS_ON W ON (W.Essn = E.Ssn)JOIN

9、PROJECT P ON (P.Pnumber = W.Pno)WHERE W.Hours 10 AND P.Pname = PRODUCTX AND Dno = 5;Q15. 對于每個項目，列出項目名稱以及所有員工在此項目上工作的總時間。SQL文本:SELECT P.Pname,SUM(W.Hours) 總時間FROM WORKS_ON W JOIN PROJECT P ON (P.Pnumber = W.Pno) GROUP BY P.Pname;Q16. 對于每個部門，列出部門名稱以及此部門員工的平均工資。SQL文本:SELECT D.Dname,AVG(E.Salary)AS 平均工資

10、 FROM EMPLOYEE E JOIN DEPARTMENT D ON D.Dnumber=E.Dno GROUP BY D.Dname;Q17. 對于每個員工(包括無上司員工)，查詢其姓名以及他/她的直接上司的姓名。SQL文本:SELECT E.Lname 員工姓,E.Fname 員工名,EM.Lname 上司姓, EM.Fname 上司名FROMEMPLOYEE E LEFTJOINEMPLOYEE EM ON(E.Super_ssn = EM.Ssn);Q18. 查詢Company數(shù)據(jù)庫中所有員工SSN與DNAME(部門名稱)的組合。SQL文本:SELECT E.Ssn,D.Dnam

11、eFROMEMPLOYEE E JOINDEPARTMENT D ON (D.Dnumber = E.Dno);Q19. 查詢有兩個或以上“工資大于等于30000員工”的部門名稱。SQL文本:SELECT D.DnameFROMEMPLOYEE E JOINDEPARTMENT D ON (D.Dnumber = E.Dno)WHERE E.Salary = 30000-先執(zhí)行WHERER在執(zhí)行GROUP BYGROUP BY D.DnameHAVING COUNT(*)=2;3嵌套查詢(子查詢)Q20. 查詢沒有參與任何部門5控制項目的員工姓名。SQL文本:SELECT Fname,Lnam

12、eFROM EMPLOYEE WHERE Ssn NOT IN(SELECT EssnFROM WORKS_ONWHERE Pno IN (SELECT PnumberFROM PROJECTWHERE Dnum = 5);Q21. 查詢工資超過部門5所有員工工資的員工姓名。SQL文本:SELECT FNAME,LNAMEFROM EMPLOYEEWHERE Salary ALL(SELECT SalaryFROM EMPLOYEEWHERE Dno = 5);Q22. 查詢參與了所有項目的員工姓名。(選做)SQL文本:SELECT FNAME,LNAMEFROM EMPLOYEEWHERE

13、SSN IN(SELECT EssnFROM WORKS_ONGROUP BY EssnHAVING COUNT(Essn) = (SELECT COUNT(*) FROM PROJECT); Q23. 查詢至少參與了所有部門4控制項目的員工姓名。(選做)SQL文本:SELECT Fname,LnameFROM EMPLOYEEWHERE Ssn IN(SELECT EssnFROM WORKS_ONWHERE Pno IN (SELECT Pnumber FROMPROJECT WHERE Dnum = 4)GROUP BY ESSNHAVING COUNT(*) = (SELECT COU

14、NT(*) FROM PROJECT WHERE Dnum = 4);Q24. 查詢至少參與了所有John Smith參與項目的員工姓名。(選做)SQL文本:SELECT Fname,Lname FROM EMPLOYEEWHERE Ssn IN(SELECT Essn FROM WORKS_ONWHERE Pno IN(SELECT DISTINCT PnoFROM WORKS_ON WHERE Essn IN(SELECT Ssn FROM EMPLOYEEWHERE Fname = JOHN AND Lname = SMITH)GROUP BY EssnHAVING COUNT(*) =

15、 (SELECT COUNT(*)FROM WORKS_ON WHERE Essn IN(SELECT Ssn FROM EMPLOYEEWHERE Fname = JOHN AND Lname = SMITH)AND Ssn != (SELECT Ssn FROM EMPLOYEEWHERE Fname = JOHN AND Lname = SMITH);Q25. 查詢至少有一個家屬的部門經(jīng)理姓名。(必須用EXISTS謂詞)SQL文本:SELECT FNAME,LNAMEFROM EMPLOYEEWHERE EXISTS(SELECT * FROM DEPARTMENTWHERE Mgr_s

16、sn = Ssn AND Mgr_ssn = ANY(SELECT DISTINCT ESSN FROM DEPENDENT);Q26. 查詢有兩個或以上隸屬員工的部門名稱及其“工資大于等于30000員工”總數(shù)。(選做)SQL文本:SELECT D.Dname,COUNT(*) 總數(shù)FROM EMPLOYEE EJOIN DEPARTMENT D ON E.Dno = D.DnumberWHERE Salary = 30000 GROUP BY D.DnameHAVING D.Dname IN (SELECT D.DnameFROM EMPLOYEE EJOIN DEPARTMENT D ON

17、 E.Dno = D.DnumberGROUP BY DnameHAVING COUNT(*) = 2);Q27. 查詢有兩個或以上家屬的員工姓名。SQL文本:SELECT FNAME,LNAMEFROM EMPLOYEEWHERE Ssn = ANY(SELECT ESSN FROM DEPENDENTGROUP BY Essn HAVING COUNT(*) = 2);4集合查詢Q28. 查詢符合以下任意條件的項目編號：(1)參與此項目員工的LNAME是Smith；(2)控制此項目的部門經(jīng)理的LNAME是Smith。SQL文本:SELECT PnoFROM WORKS_ONJOIN EMPLOYEE ON Essn = SsnWHERE Lname = SMITHUNION SELECT PnumberFROM EMPLOYEE JOIN DEPARTMENT ON Mgr_ssn = SsnJOIN PROJECT ON Dnum = DnumberWHERE Lname = SMITH;五、收獲，體會及問題(請詳細書寫，寫得越詳細、越個性化、越真實越好，否則我不知道你