SQLSever實用教程第三版實驗四答案

1、200901501116 劉玉佩數(shù)據(jù)庫的查詢和視圖（1） select語句的基本使用用select語句查詢departments和salary表中的一列或若干列select *from departmentsselect employeeid,incomefrom salary查詢employees表中的部門號和性別，要求使用distinct消除重復(fù)行select distinct departmentid,sexfrom employees查詢月收入高于2000元的員工號碼select employeeidfrom salarywhere income>2000查詢1970年以后出生的

2、員工的姓名和住址select name,addressfrom employeeswhere birthday>='1970'查詢所有財務(wù)部的員工的號碼和姓名select employeeid,namefrom employeeswhere departmentid in(select departmentid from departments where departmentname='財務(wù)部')查詢employees表中男員工的姓名和出生日期，要求將各列標(biāo)題用中文表示select name as '姓名',birthday as 

3、9;出生日期'from employeeswhere sex=1查詢employees員工的姓名、住址和收入水平，2000元以下顯示為低收入，2000-3000元顯示為中等收入，3000元以上顯示為高收入select name,address,income=casewhen income<2000 then '低收入'when income between 2000 and 3000 then '中等收入'when income>3000 then '高收入'endfrom employees,salarywhere empl

4、oyees.employeeid=salary.employeeid計算salary表中員工月收入的平均值select avg(income) as '平均收入'from salary獲得employees表中最大的員工號碼select max(employeeid)from employees計算salary表中所有員工的總支出select sum(outcome) as '總支出'from salary查詢財務(wù)部雇員的最高和最低實際收入select max(income-outcome),min(income-outcome)from salarywhere

5、 employeeid in(select employeeid from employees where departmentid in (select departmentid from departments where departmentname='財務(wù)部' ) )找出地址中含有“中山”的雇員的號碼和部門號select employeeid,departmentidfrom employeeswhere address like '%中山%'找出員工號碼的倒數(shù)第二個數(shù)字為0的員工的姓名、地址和學(xué)歷select name,address,educatio

6、nfrom employeeswhere employeeid like '%0_'找出所有部門“1”或“2”工作的雇員的號碼select employeeidfrom employeeswhere departmentid='1' or departmentid='2'使用into子句，由表employees創(chuàng)建“男員工”表，包括編號和姓名select employeeid,nameinto 男員工from employeeswhere sex=1（2） 子查詢的使用用子查詢的方法查找所有收入在2500元以下的雇員的情況select *from

7、 employeeswhere employeeid in( select employeeid from salary where income<2500)用子查詢的方法查找研發(fā)部比所有財務(wù)部雇員收入都高的雇員的姓名select namefrom employees,salary,departmentswhere income>all(select income from employees,salary,departments where employees.employeeid=salary.employeeid and employees.departmentid=depa

8、rtments.departmentid and departmentname='財務(wù)部' )and employees.employeeid=salary.employeeid and employees.departmentid=departments.departmentidand departmentname='研發(fā)部'用子查詢的方法查找所有年齡比研發(fā)部都大的雇員的姓名select name from employeeswhere birthday<all( select birthday from employees where departme

9、ntid in ( select departmentid from departments where departmentname='研發(fā)部' ) )（3） 連接查詢的使用查詢每個雇員的情況及其工作部門的情況select employees.*,departments.*from employees,departmentswhere employees.departmentid=departments.departmentid使用內(nèi)連接的方法查找出不在財務(wù)部工作的所有員工信息select employees.*from employees inner join depart

10、mentson employees.departmentid=departments.departmentidwhere departmentname!='財務(wù)部'使用外連接方法查找出使用員工的月收入select incomefrom employees left outer join salaryon employees.employeeid=salary.employeeid（4） 聚合函數(shù)的使用查詢財務(wù)部雇員的最高和最低收入select max(income),min(income)from salary,employees,departmentswhere salary

11、.employeeid=employees.employeeid and employees.departmentid=departments.departmentidand departmentname='財務(wù)部'查詢財務(wù)部雇員的最高和最低實際收入select max(income-outcome),min(income-outcome)from salary,employees,departmentswhere salary.employeeid=employees.employeeid and employees.departmentid=departments.depa

12、rtmentidand departmentname='財務(wù)部'統(tǒng)計財務(wù)部收入在2500元以上的雇員人數(shù)select count(salary.employeeid)from salary,employees,departmentswhere salary.employeeid=employees.employeeid and employees.departmentid=departments.departmentidand income>2500 and departmentname='財務(wù)部'（5） group by、order by子句的使用按部門

13、列出在該部門工作的員工的人數(shù)select departmentid,count(employeeid)from employeesgroup by departmentid按員工的學(xué)歷分組，排列出本科、大專和碩士的人數(shù)select education,count(employeeid)from employeesgroup by education按員工的工作年份分組，統(tǒng)計各個工作年份的人數(shù)select workyear,count(employeeid)from employeesgroup by workyear將員工信息按出生時間從小到大排列select *from employeeso

14、rder by birthday asc在order by子句中使用子查詢，查詢員工姓名、性別和工齡信息，要求按實際收入從小到大排列select name,sex,workyearfrom employees,salarywhere employees.employeeid=salary.employeeidorder by income-outcome desc視圖的使用（1）創(chuàng)建視圖創(chuàng)建視圖時select語句有哪些限制1. 定義視圖的用戶必須對所參照的表或視圖有查詢權(quán)限2. 不能使用compute或compute by子句3. 不能使用order by 子句4. 不能使用into 子句5.

15、 不能再臨時表或表變量上創(chuàng)建視圖在創(chuàng)建視圖時有哪些注意點1. 只有在當(dāng)前數(shù)據(jù)庫中才能創(chuàng)建視圖。視圖的命名必須遵循標(biāo)識符命名規(guī)則，不能與表同名。2. 不能把規(guī)則、默認(rèn)值或觸發(fā)器與視圖相關(guān)聯(lián)。創(chuàng)建視圖，包含員工號碼、姓名、所在部門名稱和實際收入這幾列create view a_view(employeeid,name,departmentname,realincome)as select employees.employeeid,name,departmentname,income-outcomefrom employees,salary,departmentswhere employees.employeeid=salary.employeeid and employees.departmen