SQLSERVER2008實用教程實驗參考問題詳解

1、實驗4數(shù)據(jù)庫的查詢和視圖一、SELECTS句的基本使用1. 查詢Employees表中所有數(shù)據(jù)2. 查詢Employees表中指定字段數(shù)據(jù)3. 查詢Employees表中的部門號和性別，要求使用Distinct消除重復行4. 使用WHERE子句查詢表中指定的數(shù)據(jù) 查詢編號為00001 的雇員的地址和查詢月收入高于2000元的員工查詢1970年以后出生的員工的和住址5. 使用AS子句為表中字段指定別名查詢Employees表中女雇員的地址和，并將列標題顯示為地址和查詢Employees表中男雇員的和出生日期，并將列標題顯示為和出生日期6. 使用使用CASE子句查詢Employees表中員工的和性

2、別，要求Sex值為1時顯示男”，為0時顯示女”查詢Employees表中員工的、住址和收入水平，2000元以下顯示為低收入，20003000地顯 示為中等收入，3000元以上顯示為高收入。7. 使用SELECT語句進行簡單計算計算每個雇員的實際收入8. 使用置函數(shù) 獲得員工總數(shù)計算Salary表中員工月收入的平均數(shù)獲得Employees表中最大的員工計算Salary表中所有員工的總支出查詢財務部雇員的最高和最低實際收入9. 模糊查詢找出所有姓王的雇員的部門號找出所有地址中含有“”的雇員的及部門號找出員工中倒數(shù)第二個數(shù)字為0的員工的、地址和學歷10. Between And和 Or 的使用找出收

3、入在20003000元之間的雇員編號找出部門為“ 1”或“ 2”的雇員的編號11. 使用INTO子句，由源表創(chuàng)建新表由表Salary創(chuàng)建“ SalaryNew”表，要求包括編號和收入，選擇收入在1500元以上的雇員由表Employees創(chuàng)建“ EmployeesNew ”表，要求包括編號和，選擇所有男員工 二、子查詢的使用1. 查找在財務部工作的雇員情況2. 用子查詢的方法查找所有收入在2500以下的雇員的情況3. 查找財務部年齡不低于研發(fā)部雇員年齡的雇員4. 用子查詢的方法查找研發(fā)部比所有財務部雇員收入都高的雇員的5. 查找比所有財務部的雇員收入都高的雇員的6. 用子查詢的方法查找所有年齡比

4、研發(fā)部雇員年齡都大的雇員的 三、連接查詢的使用1. 查詢每個雇員的情況及薪水的情況2. 查詢每個雇員的情況及其工作部門的情況3. 使用連接的方法查詢名字為“王林”的雇員所在的部門4. 使用連接的方法查找出不在財務部工作的所有雇員信息5. 使用外連接方法查找出所有員工的月收入6. 查找財務部收入在2000元以上的雇員及其薪水詳情7查詢研發(fā)部在1976年以前出生的雇員及其薪水詳請四、聚合函數(shù)的使用1. 求財務部雇員的平均收入2. 查詢財務部雇員的最高和最低收入3. 求財務部雇員的平均實際收入4. 查詢財務部雇員的最高和最低實際收入5. 求財務部雇員的總?cè)藬?shù)6. 統(tǒng)計財務部收入在2500元以上的雇員

5、人數(shù)五、GROUP BY ORDER BY子句的使用1. 查找Employees表中男性和女性的人數(shù)2. 按部門列出在該部門工作的員工的人數(shù)3. 按員工的學歷分組，排列出本科、大專、碩士的人數(shù)4. 查找員工數(shù)超過2的部門名稱和雇員數(shù)量5. 按員工的工作年份分組，統(tǒng)計各個工作年份的人數(shù)，例如工作1年的多少人，工作 2年的多少人6. 將雇員的情況按收入由低到高排列7. 將員工信息按出生時間從小到大排列8. 在ORDER BY子句中使用子查詢，查詢員工、性別和工齡信息，要求按實際收入從大到 小排列六、視圖的使用1. 創(chuàng)建視圖（1 ）在數(shù)據(jù)庫 YGGL上創(chuàng)建視圖 Departments_View，視圖

6、包含 Department表的全部列（2）創(chuàng)建視圖Employees_Departments_View，視圖包含員工、所在部門名稱（3）創(chuàng)建視圖Employees_Salary_View,視圖包含員工、和實際收入三列2. 查詢視圖從視圖Employees_Salary_View中查詢出為“王林”的員工的實際收入3. 更新視圖（1） 向視圖Departments_View中添加一條記錄（6廣告部，廣告業(yè)務）執(zhí)行完命令后，分別查看 Departments_View和Department表中發(fā)生的變化（2）嘗試向Employees_Departments_View 中添加一條記錄，看看會發(fā)生什么情況

7、（3）嘗試向Employees_Salary_View中添加一條記錄，看看會發(fā)生什么情況（4）將視圖Departments_View中，部門號為 6 的部門名稱修改為生產(chǎn)車間（5）刪除視圖Departments_View中最新增加的的一條記錄4. 刪除視圖 Employees_Departments_View5. 在界面工具中操作視圖一、SELECTS句的基本使用1. 查詢Employees表中所有數(shù)據(jù)SELECT * FROM Employees ;2. 查詢Employees表中指定字段數(shù)據(jù)SELECT EmployeelD , Name, DepartmentID FROM Employ

8、ees ;3. 查詢Employees表中的部門號和性別，要求使用Distinct消除重復行SELECT DistinctDepartmentID, Sex FROM Employees ;4. 使用WHERE子句查詢表中指定的數(shù)據(jù) 查詢編號為00001 的雇員的地址和Select Address , PhoneNumber FROM Employees WHERE EmployeeID =000001;查詢月收入高于2000元的員工SELECT EmployeeID FROM Salary WHERE In Come 2000 ;查詢1970年以后出生的員工的和住址SELECT Name,A

9、ddress FROM Employees WHERE YEAR(Birthday)1970;SELECT Name, Address FROM Employees WHERE Birthday 1970;5. 使用AS子句為表中字段指定別名查詢Employees表中女雇員的地址和，并將列標題顯示為地址和SELECT Address AS 地址,PhoneNumberAS FROM Employees ;查詢Employees表中男雇員的和出生日期，并將列標題顯示為和出生日期SELECT Name AS , Birthday AS 岀生日期 FROM Employees WHERE Sex =

10、1;6. 使用使用CASE子句查詢Employees表中員工的和性別，要求Sex值為1時顯示男”，為0時顯示女”SELECT Name AS ,CASEWHEN Sex =1 THEN 男WHEN Sex =0 THEN 女ENDAS性別FROM Employees ;查詢Employees表中員工的、住址和收入水平，2000元以下顯示為低收入，20003000地顯 示為中等收入，3000元以上顯示為高收入。SELECTa. Name AS , a. Address as 住址，CASEWHEN b. In Come 3000 THEN 高收入ENDAS收入水平FROM Employees a

11、, Salary bWHERE a. EmployeeID =b. EmployeeID ;7. 使用SELECT語句進行簡單計算在Salary表中，計算每個雇員的實際收入，并顯示雇員編號和實際收入SELECT EmployeeID AS 雇員編號，In Come - OutCome AS 實際收入 FROM Salary ;8. 使用聚合函數(shù)獲得員工總數(shù)SELECT COUNT(*) FROM Employees ;計算Salary表中員工月收入的平均數(shù)SELECT AVG( In Come ) AS 平均收入 FROM Salary ;獲得Employees表中最大的員工SELECT MA

12、X( EmployeelD ) AS 雇員編號 FROM Employees ;計算Salary表中所有員工的總支出SELECT SUM( OutCome ) AS 總支岀 FROM Salary ;查詢財務部雇員的最高和最低實際收入SELECT MAX( In Come - OutCome ) AS 最高收入,MIN ( In Come - OutCome ) AS 最低收入 FROM Salary ;9. 模糊查詢找出所有姓王的雇員的部門號SELECT DepartmentID AS 部門號 FROM Employees WHERE Name like 王%;找出所有地址中含有“”的雇員的

13、編號及部門號SELECT EmployeeID AS 雇員編號,DepartmentID AS 部門號 FROM Employees WHERE Address like %找出雇員編號中倒數(shù)第二個數(shù)字為0雇員的編號的、地址和學歷SELECT EmployeeID AS 雇員編號,Name AS , Address AS 地址,Education AS 學歷FROM Employees WHERE SUBSTRING ( EmployeeID , 5, 1)= 0;10. Between And和 Or 的使用找出收入在20003000元之間的雇員編號SELECT EmployeeID , I

14、n Come FROM Salary WHERE I nCome BETWEEN 2000 AND 3000 ;找出部門為“ 1”或“ 2”的雇員的編號SELECT EmployeeID , DepartmentIDFROM Employees WHEREDepartmentID =1 ORDepartme ntID =2;11. 使用INTO子句，由源表創(chuàng)建新表由表Salary創(chuàng)建“ SalaryNew”表，要求包括編號和收入，選擇收入在1500元以上的雇員SELECT EmployeeID , InComeINTO SalaryNewFROM SalaryWHERE In Come 150

15、0 ;由表Employees創(chuàng)建“ EmployeesNew ”表，要求包括編號和，選擇所有男員工SELECT EmployeeID , NameINTO EmployeesNewFROM EmployeesWHERE Sex =1;二、子查詢的使用1. 查找在財務部工作的雇員情況-分析：雇員表和部門表通過 DepartmentID進行關(guān)聯(lián)-1) 在Departme nts表中查找財務部的編號-2)在Employees 表中查找部門的雇員情況SELECT * FROM Employees WHERE DepartmentID=(SELECT DepartmentIDFROM Departmen

16、tsWHERE DepartmentName=財務咅E ）;2. 用子查詢的方法查找所有收入在2500以下的雇員的情況-分析：員工表和收入表通過 EmployeeID 進行關(guān)聯(lián)SELECT * FROM Employees WHERE EmployeeID IN （SELECT EmployeeID FROM Salary WHERE In Come ALL(SELECT Birthday FROM Employees WHERE DepartmentID in(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName =研發(fā)咅E );4.

17、 用子查詢的方法查找研發(fā)部比所有財務部雇員收入都高的雇員的-1)查找財務部雇員的收入-2)查找研發(fā)部雇員的編號，條件是收入比所有財務部雇員收入都高-3)通過編號找到雇員SELECT Name FROM Employees WHERE EmployeeID IN (SELECT EmployeeIDFROM Salary WHERE EmployeeID IN(SELECT EmployeeIDFROM Employees WHERE DepartmentID=(SELECT Departme ntIDAND In Come ALL(FROM DepartmentsWHERE Departmen

18、tName=研發(fā)咅E )SELECT In Come FROM Salary WHERE EmployeeID IN(SELECT EmployeeID FROM Employees WHERE DepartmentID =( SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName5. 查找比所有財務部的雇員收入都高的雇員的-分析:1)查找所有財務部的雇員收入-2)查找其他雇員編號，條件是收入比所有財務部的雇員收入都高-3)通過編號找到SELECT Name FROM Employees WHERE EmployeeID IN(SELEC

19、T EmployeeID FROM Salary WHERE In Come ALL(SELECT In Come FROM Salary Where EmployeeID IN (SELECT EmployeeID FROM Employees Where DepartmentID =(SELECT DepartmentIDFROM DepartmentsWHERE DepartmentName6. 用子查詢的方法查找所有年齡比研發(fā)部雇員年齡都大的雇員的=財務部);=財務部);-分析:年齡都大等價于生日都小-1）找到所有研發(fā)部雇員的生日-2）找到其他部門雇員的，條件是生日比研發(fā)部的所有雇員的

20、生日都小SELECT Name FROM Employees WHERE Birthday 2000 AND c. DepartmentName =財務咅E ;7. 查詢研發(fā)部在1976年以前出生的雇員及其薪水詳請SELECT a.*, b. InCome FROM Employees aINNER JOIN Salary bON a. EmployeeID =b. EmployeeIDINNER JOIN Departme ntscON a. DepartmentID =c. DepartmentIDWHERE c. DepartmentName=研發(fā)部AND a. Birthday 197

21、6四、聚合函數(shù)的使用1. 求財務部雇員的平均收入SELECT AVG( a. InCome ) FROM Salary a=研發(fā)部);INNER JOIN Employees bON a. EmployeelD =b. EmployeelDINNER JOIN Departme ntscON c. DepartmentID=b. DepartmentIDWHERE c. DepartmentName=財務咅E ;2. 查詢財務部雇員的最高和最低收入SELECT MIN ( a. I nCome ), MAX( a. In Come ) FROM Salary aINNER JOIN Emplo

22、yees bON a. EmployeeID =b. EmployeeIDINNER JOIN Departme nts cON c. DepartmentID=b. DepartmentIDWHERE c. DepartmentName=財務咅E ;3. 求財務部雇員的平均實際收入SELECT AVG( a. InCome - a. OutCome ) FROM Salary aINNER JOIN Employees bON a. EmployeeID =b. EmployeeIDINNER JOIN Departme nts cON c. DepartmentID=b. Departme

23、ntIDWHERE c. DepartmentName=財務咅E ;4. 查詢財務部雇員的最高和最低實際收入SELECT MIN ( a. InCome - a. OutCome ), MAX( a. InCome - a. OutCome ) FROM Salary aINNER JOIN Employees bON a. EmployeeID =b. EmployeeIDINNER JOIN Departme nts cON c. DepartmentID=b. DepartmentIDWHERE c. DepartmentName=財務咅E ;5. 求財務部雇員的總?cè)藬?shù)SELECT CO

24、UNT( a. EmployeeID ) FROM Employees aINNER JOIN Departme ntsbON a. DepartmentID=b. DepartmentIDWHERE b. DepartmentName=財務咅E ;6. 統(tǒng)計財務部收入在2500元以上的雇員人數(shù)SELECT COUNT( a. EmployeeID ) FROM Employees aINNER JOIN Departme ntsbON a. DepartmentID=b. DepartmentIDINNER JOIN Salary cON c. EmployeeID =A. Employee

25、IDWHERE c. InCome 2500 AND b. DepartmentName=財務咅E ;五、GROUP BY ORDER BY子句的使用1. 查找Employees表中男性和女性的人數(shù)SELECT Sex , COUNT( Sex ) FROM Employees GROUP BY Sex ;2. 按部門列出在該部門工作的員工的人數(shù)-使用連接的方法SELECT b. DepartmentName, COUNT( a. EmployeeID ) FROM Employees aINNER JOIN Departme nts bON a. DepartmentID=b. Depart

26、mentIDGROUP BY b. DepartmentName ;-使用多表查詢方法，GROUP BY后面的字段，必須岀現(xiàn)在SELECT語句要查詢的字段中。SELECT b. DepartmentName, COUNT(a. EmployeeID ) FROM Employeesa, DepartmentsbWHERE a. DepartmentID=b. DepartmentIDGROUP BY b. DepartmentName ;3. 按員工的學歷分組，排列出本科、大專、碩士的人數(shù)SELECT a. Education, COUNT( a. EmployeeID ) FROM Empl

27、oyees aGROUP BY a. Education;4. 查找員工數(shù)超過2的部門名稱和雇員數(shù)量SELECT b. DepartmentName, COUNT( a. EmployeeID ) FROM Employeesa, DepartmentsbWHERE a. DepartmentID=b. DepartmentIDGROUP BY b. DepartmentNameHAVING COUNT( a. EmployeeID ) 2;5. 按員工的工作年份分組，統(tǒng)計各個工作年份的人數(shù)，例如工作1年的多少人，工作 2年的多少人SELECT a. WorkYear , COUNT( a.

28、EmployeeID ) FROM Employees aGROUP BY a. WorkYear ;6. 將雇員的情況按收入由低到高排列SELECT a.*, b. InCome FROM Employees a, Salary bWHERE a. EmployeeID =b. EmployeeIDORDER BY b. InCome ASC;7. 將員工信息按出生時間從小到大排列SELECT * FROM Employees ORDER BY Birthday ;8. 在ORDER BY子句中使用子查詢，查詢員工、性別和工齡信息，要求按實際收入從大到 小排列SELECT a. Name,

29、a. Sex , a. WorkYear , b. InCome - b. OutCome FROM Employeesa, Salary bWHERE a. EmployeeID =b. EmployeeIDORDER BY b. InCome - b. OutCome DESC;六、視圖的使用1. 創(chuàng)建視圖(1 )在數(shù)據(jù)庫 YGGL上創(chuàng)建視圖 Departments_View，視圖包含 Department表的全部列USE YGGLGOCREATE VIEW Departments_ViewAS SELECT * FROM Departments;(2)創(chuàng)建視圖Employees_Depa

30、rtments_View，視圖包含員工、所在部門名稱USE YGGLGOCREATE VIEW Employees_Departments_ViewAS SELECT a. EmployeeID , a. Name, b. DepartmentNameFROM Employees a,Departme ntsbWHERE a. DepartmentID=b. DepartmentID;(3)創(chuàng)建視圖Employees_Salary_View,視圖包含員工、和實際收入三列USE YGGLGOCREATE VIEW Employees_Salary_View( EmployeeID , Name, RealInCome )AS SELECT a. EmployeeID , a. Name, b. In Come - b. OutCome FROM Employees a, Salary bWHERE a. EmployeeID =b. EmployeeID ;2. 查詢視圖從視圖Employees_Salary_View中查詢出為王林”的員工的