數(shù)據(jù)庫課間實(shí)習(xí)

1、數(shù)據(jù)庫課間實(shí)習(xí)microsoft sql sever進(jìn)入sql server management studio服務(wù)器名稱：所在機(jī)位的機(jī)號(hào)身份驗(yàn)證：windows身份驗(yàn)證microsoft sql sever1.建立數(shù)據(jù)庫 在e：盤新建一個(gè)名為dbf的文件夾。 創(chuàng)建數(shù)據(jù)庫：右擊數(shù)據(jù)庫，在下拉菜單中選擇新建數(shù)據(jù)庫。1.建立數(shù)據(jù)庫文件夾中出現(xiàn)兩個(gè)數(shù)據(jù)庫文件2.在數(shù)據(jù)庫中建立各表右擊已建的數(shù)據(jù)庫下拉菜單中 任務(wù)-導(dǎo)入數(shù)據(jù)3.利用sql語言完成各題（1）求出各部門的黨員人數(shù)。select wdepartid as 部門號(hào), count(wparty) as 黨員人數(shù)from workerwhere (

2、wparty = 1)group by wdepartid（2）顯示所有職工的職工號(hào)、姓名和平均工資。select sid as 職工號(hào), sname as 姓名, avg(smoney)as 平均工資 from salary group by sid, sname;（3）顯示所有平均工資低于全部職工平均工資的職工的職工號(hào)和姓名。select sid as 職工號(hào), sname as 姓名, avg(smoney) as 平均工資from salary group by sid,snamehaving avg(smoney) (select avg(smoney) from salary)or

3、der by sid ;（4）顯示所有職工的職工號(hào)、姓名、部門名和2004年2月份工資，并按部門名順序排列。select worker.wid as 職工號(hào), worker.wname as 姓名, depart.dname as 部門名, salary.smoney as 2004年2月份工資 from worker,depart,salary where worker.wdepartid=depart.ddepartid and worker.wid=salary.sid and salary.stime=2004-02-03order by worker.wdepartid asc;（5

4、）顯示各部門名和該部門的所有職工平均工資。select depart.dname as 部門名, avg(salary.smoney) as 部門平均工資 from worker,depart,salary where worker.wdepartid=depart.ddepartid and worker.wid=salary.sidgroup by depart.dname;（6）顯示最高工資的職工的職工號(hào)、姓名、部門名、工資發(fā)放日期和工資。select salary.sid as 職工號(hào), salary.sname as 姓名,depart.dname as 部門名, salary.st

5、ime as 工資發(fā)放日期, salary.smoney as 工資from worker,depart,salary where worker.wdepartid=depart.ddepartid and worker.wid=salary.sid and salary.smoney = (select max(smoney) from salary);（7）若存在職工號(hào)為10的職工，則顯示其工作部門名字。存在量詞查詢select depart.dname as 部門名from depart where exists (select * from worker where wdepartid

6、=depart.ddepartid and wid=10);嵌套查詢，謂詞“in”select depart.dname as 部門名from depart where ddepartid in (select wdepartid from worker where wid=10);復(fù)合連接select depart.dname as 部門名from depart,worker where depart.ddepartid=worker.wdepartid and worker.wid=10;（8）在salary表的“職工號(hào)”和“日期”列創(chuàng)建聚集索引，并且強(qiáng)制唯一性, 若該索引已存在，則刪除后

7、重建。索引建立： create unique clustered index idx_salary on salary(sid,stime);exec sp_helpindex salary /*查看表中所有約束*/ （9）建立視圖view1，顯示所有職工的姓名和2004年1月份工資數(shù)。 create view v1_salary as select sname, smoney from salary where stime=2004-01-04（10）建立視圖view2，查詢各部門女職工的姓名和其平均工資。 create view v2_salary (sname, mavg) as select salary.s