SQL Sever实用教程（第三版）实验四答案.doc

200901501116 刘玉佩数据库的查询和视图（1） SELECT语句的基本使用用SELECT语句查询Departments和Salary表中的一列或若干列SELECT *FROM DepartmentsSELECT EmployeeID,InComeFROM Salary查询Employees表中的部门号和性别，要求使用DISTINCT消除重复行SELECT DISTINCT DepartmentID,SexFROM Employees查询月收入高于2000元的员工号码SELECT EmployeeIDFROM SalaryWHERE InCome>2000查询1970年以后出生的员工的姓名和住址SELECT Name,AddressFROM EmployeesWHERE Birthday>=1970查询所有财务部的员工的号码和姓名SELECT EmployeeID,NameFROM EmployeesWHERE DepartmentID IN(SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部)查询Employees表中男员工的姓名和出生日期，要求将各列标题用中文表示SELECT Name AS 姓名,Birthday AS 出生日期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 Employees.EmployeeID=Salary.EmployeeID计算Salary表中员工月收入的平均值SELECT AVG(InCome) AS 平均收入FROM Salary获得Employees表中最大的员工号码SELECT MAX(EmployeeID)FROM Employees计算Salary表中所有员工的总支出SELECT SUM(OutCome) AS 总支出FROM Salary查询财务部雇员的最高和最低实际收入SELECT MAX(InCome-OutCome),MIN(InCome-OutCome)FROM SalaryWHERE EmployeeID IN(SELECT EmployeeID FROM Employees WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName=财务部 ) )找出地址中含有“中山”的雇员的号码和部门号SELECT EmployeeID,DepartmentIDFROM EmployeesWHERE Address LIKE %中山%找出员工号码的倒数第二个数字为0的员工的姓名、地址和学历SELECT Name,Address,EducationFROM EmployeesWHERE EmployeeID LIKE %0_找出所有部门“1”或“2”工作的雇员的号码SELECT EmployeeIDFROM EmployeesWHERE DepartmentID=1 OR DepartmentID=2使用INTO子句，由表Employees创建“男员工”表，包括编号和姓名SELECT EmployeeID,NameINTO 男员工FROM EmployeesWHERE Sex=1（2） 子查询的使用用子查询的方法查找所有收入在2500元以下的雇员的情况SELECT *FROM EmployeesWHERE EmployeeID IN( SELECT EmployeeID FROM Salary WHERE InCome<2500)用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名SELECT NameFROM Employees,Salary,DepartmentsWHERE InCome>ALL(SELECT InCome FROM Employees,Salary,Departments WHERE Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentID AND DepartmentName=财务部 )AND Employees.EmployeeID=Salary.EmployeeID and Employees.DepartmentID=Departments.DepartmentIDAND DepartmentName=研发部用子查询的方法查找所有年龄比研发部都大的雇员的姓名SELECT Name FROM EmployeesWHERE Birthday<ALL( SELECT Birthday FROM Employees WHERE DepartmentID IN ( SELECT DepartmentID FROM Departments WHERE DepartmentName=研发部 ) )（3） 连接查询的使用查询每个雇员的情况及其工作部门的情况SELECT Employees.*,Departments.*FROM Employees,DepartmentsWHERE Employees.DepartmentID=Departments.DepartmentID使用内连接的方法查找出不在财务部工作的所有员工信息SELECT Employees.*FROM Employees INNER JOIN DepartmentsON Employees.DepartmentID=Departments.DepartmentIDWHERE DepartmentName!=财务部使用外连接方法查找出使用员工的月收入SELECT InComeFROM Employees LEFT OUTER JOIN SalaryON Employees.EmployeeID=Salary.EmployeeID（4） 聚合函数的使用查询财务部雇员的最高和最低收入SELECT MAX(InCome),MIN(InCome)FROM Salary,Employees,DepartmentsWHERE Salary.EmployeeID=Employees.EmployeeID AND Employees.DepartmentID=Departments.DepartmentIDAND DepartmentName=财务部查询财务部雇员的最高和最低实际收入SELECT MAX(InCome-OutCome),MIN(InCome-OutCome)FROM Salary,Employees,DepartmentsWHERE Salary.EmployeeID=Employees.EmployeeID AND Employees.DepartmentID=Departments.DepartmentIDAND DepartmentName=财务部统计财务部收入在2500元以上的雇员人数SELECT COUNT(Salary.EmployeeID)FROM Salary,Employees,DepartmentsWHERE Salary.EmployeeID=Employees.EmployeeID AND Employees.DepartmentID=Departments.DepartmentIDAND InCome>2500 AND DepartmentName=财务部（5） GROUP BY、ORDER BY子句的使用按部门列出在该部门工作的员工的人数SELECT DepartmentID,COUNT(EmployeeID)FROM EmployeesGROUP BY DepartmentID按员工的学历分组，排列出本科、大专和硕士的人数SELECT Education,COUNT(EmployeeID)FROM EmployeesGROUP BY Education按员工的工作年份分组，统计各个工作年份的人数SELECT WorkYear,COUNT(EmployeeID)FROM EmployeesGROUP BY WorkYear将员工信息按出生时间从小到大排列SELECT *FROM EmployeesORDER BY Birthday ASC在ORDER BY子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从小到大排列SELECT Name,Sex,WorkYearFROM Employees,SalaryWHERE Employees.EmployeeID=Salary.EmployeeIDORDER BY InCome-OutCome DESC视图的使用（1）创建视图创建视图时SELECT语句有哪些限制1. 定义视图的用户必须对所参照的表或视图有查询权限2. 不能使用COMPUTE或COMPUTE BY子句3. 不能使用ORDER BY 子句4. 不能使用INTO 子句5. 不能再临时表或表变量上创建视图在创建视图时有哪些注意点1. 只有在当前数据库中才能创建视图。视图的命名必须遵循标识符命名规则，不能与表同名。2. 不能把规则、默认值或触发器与视图相关联。创建视图，包含员工号码、姓名、所在部门名称和实际收入这几列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.DepartmentID=Departments.DepartmentID（2）查询视图若视图关联了某表中的所有字段，而此时该表中添加了新字段，视图中能否查询到该字段？不能，必须重新创建视图才能查询到新字段。视图Employees_view中无法插入和删除数据，其中的RealIncome字段也无法修改，为什么？因为视图Employees_view依赖于多个基本表，所以无法进行插入和删除数据。视图Employees_view中的字段RealIncome是基本表列通过计算所得的列，所以无法修改。