數(shù)據(jù)庫實驗2無錯無漏

1、西南石油大學(xué)實驗報告課程名稱：數(shù)據(jù)庫原理實驗項目名稱：實驗項目2 sql數(shù)據(jù)操縱語言專業(yè)年級班級、姓名、學(xué)號：軟件13級4班、張璋、201305020420實驗時間地點：2015.11.20 明理樓實驗指導(dǎo)教師：劉小玲 成績批改人批改日期一、實驗課時：4二、實驗?zāi)康?1) 理解查詢的概念和方法。(2) 掌握select語句在單表查詢中的應(yīng)用。(3) 掌握select語句在多表連接查詢中的應(yīng)用。(4) 掌握select語句在嵌套查詢中的應(yīng)用。(5) 掌握select語句在集合查詢中的應(yīng)用。(6) 主要掌握使用“查詢分析器”進(jìn)行查詢。三、實驗要求(1) 使用“查詢分析器”進(jìn)行查詢。(2) 嚴(yán)格依照

2、題目要求編寫sql查詢語句(鼓勵同學(xué)們在按照題目要求完成查詢的前提下嘗試其他的查詢方式)。(3) 為了今后可重用本實驗項目中編寫的sql語句，最好按實驗題目編號將sql語句保存為.sql文件。四、實驗環(huán)境(1) pc機(jī)。(2) sql server 2000。五、實驗內(nèi)容及步驟以下查詢均使用實驗項目1中創(chuàng)建的company數(shù)據(jù)庫。1單表查詢(1)基本查詢。q0. 使用查詢分析器從employee表中檢索出所有員工的姓名。select fname,lname from employeeq1. 使用查詢分析器從employee表中檢索出員工的fname、lname、ssn、bdate、salary

3、等字段，并分別加上“名”、“姓”、“社會保險號”、“生日”、“工資”的標(biāo)題。員工的排序規(guī)則為：首先按工資的降序排列，然后按fname的字母升序排列。select fname as 名,lname as 姓 ,ssn as 社會保險號 , bdate as 生日 ,salary as 工資 from employee order by salary desc,fname ascq2. 查詢不重復(fù)的員工工資值。select distinct salary as 工資 from employeeq3. 查詢沒有直接上司的員工姓名。select fname,lname from employee wh

4、ere superssn is null(2) 基于where子句進(jìn)行數(shù)據(jù)查詢。1）基于比較條件。q4. 從employee表中查詢出工資大于等于40000的員工資料。select * from employee where salary =400002）基于between子句的查詢。q5. 從employee表中查詢出1960年1970年之間出生的員工資料。select * from employee where bdate between 1960-1-1 and 1970-1-13）基于in子句的查詢。q6. 從employee表中查詢出部門號為4或者5的員工資料。select * fr

5、om employee where dno in (4,5)4）基于like子句的查詢。q7. 從employee表中查詢出lname中含有字母o的員工資料。select * from employee where lname like %o%(3) 使用計算列查詢。q8. 從employee表中檢索出員工的fname、lname、ssn、salary等字段(其中salary需換算成人民幣，匯率假定為1美元=8人民幣元)，并分別加上“名”、“姓”、“社會保險號”、“人民幣工資”的標(biāo)題。select fname as 名 ,lname as 姓,ssn as 社會保險號,salary*8 as

6、人民幣工資 from employee2多表連接查詢(使用join)q9. 查詢所有為research部門工作的員工姓名及地址。select fname,lname,address from employee join department on dno=dnumber where dname=researchq10.對于所有位于stafford的項目，查詢項目的編號、項目負(fù)責(zé)部門編號以及該部門經(jīng)理的姓、地址、生日。select pnumber,dnum,fname,lname,address,bdatefrom (project join department on dnum=dnumber

7、)join employeeon ssn=mgrssnwhere plocation=staffordq11. 查詢有兩個或以上家屬的員工姓名(此題較難，若不能完成者可只查詢出員工的ssn而不是姓名)。select fname,lnamefrom dependent join employeeon essn=ssngroup by essn,fname,lnamehaving count(*)=2q12. 查詢在productx項目上每周工作時間超過10小時的部門5的員工姓名。select distinct fname,lnamefrom employee join (works_on joi

8、n project on pname=productx and pno=pnumber) on ssn=essnwhere hours10q13. 對于每個項目，列出項目名稱以及所有員工在此項目上工作的總時間。select pname,sum(hours) as 總時間from project join works_on on pno=pnumber group by pnameq14. 對于每個部門，列出部門名稱以及此部門員工的平均工資。select dname,sum(salary) as 平均工資from department join employee on dno=dnumber g

9、roup by dname order by 平均工資q15. 對于每個員工，查詢其姓名以及他/她的直接上司的姓名。select e.fname,e.lname,m.fname,m.lnamefrom employee as e join employee as m on e.superssn=m.ssnq16. 查詢company數(shù)據(jù)庫中所有員工ssn與dname(部門名稱)的組合。select distinct ssn ,dname 部門名稱from employee join department on dno=dnumber*q17. 查詢有兩個或以上“工資大于等于30000員工”的部

10、門名稱。select dnamefrom department where dnumber in(select dnumberfrom department join employee on dno=dnumberwhere salary=30000group by dnumberhaving count(*)=2)3嵌套查詢(子查詢)q18.查詢沒有參與任何部門5控制項目的員工姓名。select distinct fname,lname from employee where ssn not in ( select essn from works_on where pno in( selec

11、t pnumber from project where dnum=5)q19. 查詢工資超過部門5所有員工工資的員工姓名。1. select fname,lnamefrom employee where salary(select top 1 salaryfrom employee , department where dno=5 order by salary desc)2. select fname,lnamefrom employee where salary(select max(salary) from employee where dno=5)q20. 查詢參與了所有項目的員工姓

12、名。select fname,lname from employee where ssn in(select essn from works_on join projecton pno=pnumbergroup by essn having count(*)=(select count(*) from project)q21. 查詢至少參與了所有部門4控制項目的員工姓名。select fname,lnamefrom employee where ssn not in(select essn from works_on where pno not in(select pnumber from p

13、roject where dnum=4)q22. 查詢至少參與了所有john smith參與項目的員工姓名。select fname ,lname from employeewhere ssn not in(select essn from works_on where pno not in(select pno from works_on where essn in(select ssn from employee where fname=john and lname=smith)q23. 查詢至少有一個家屬的部門經(jīng)理姓名。1. select fname,lname from employe

14、e where ssn in(select essn from department join dependent on mgrssn=essn)2. select mgrssnfrom department join dependent on mgrssn=essngroup by mgrssn having count(*)=1q24. 查詢有兩個或以上隸屬員工的部門名稱及其“工資大于等于30000員工”總數(shù)。select dname from department join employee on dno= dnumber and salary=30000group by dnameha

15、ving count(*)=2q25.查詢有兩個或以上家屬的員工姓名。select fname,lnamefrom employee join dependenton essn=ssngroup by fname,lnamehaving count(*)=24集合查詢q26. 查詢符合以下任意條件的員工：(1) superssn為123456789的員工；select fname,lnamefrom employee where superssn=123456789(2) superssn不為123456789的員工。select fname,lnamefrom employee where

16、superssn not in(select superssnfrom employee where superssn=123456789)q27. 查詢符合以下任意條件的員工：(1) ssn為123456789的員工；select fname,lnamefrom employee where ssn=123456789(2) ssn不為123456789的員工。select fname,lnamefrom employee where ssn not in(select superssnfrom employee where ssn=123456789)六、注意事項(1) 請在本地服務(wù)器中的數(shù)據(jù)庫“company_你的拼音名字”中進(jìn)行查詢。(2) 如果由于題目翻譯時的失誤導(dǎo)致中英文題意不相符時，請以英文為準(zhǔn)(中文翻譯僅供參考)。七、考核要求(1)熟練使用select語句進(jìn)行各種類型數(shù)據(jù)