SQLSERVER实用教程实验参考答案实验4.docx
《SQLSERVER实用教程实验参考答案实验4.docx》由会员分享，可在线阅读，更多相关《SQLSERVER实用教程实验参考答案实验4.docx（14页珍藏版）》请在冰豆网上搜索。
SQLSERVER实用教程实验参考答案实验4
实验4数据库的查询和视图
一、SELECT语句的基本使用
1.查询Employees表中所有数据
2.查询Employees表中指定字段数据
3.查询Employees表中的部门号和性别，要求使用Distinct消除重复行
4.使用WHERE子句查询表中指定的数据
查询编号为’000001’的雇员的地址和电话
查询月收入高于2000元的员工号码
查询1970年以后出生的员工的姓名和住址
5.使用AS子句为表中字段指定别名
查询Employees表中女雇员的地址和电话，并将列标题显示为地址和电话
查询Employees表中男雇员的姓名和出生日期，并将列标题显示为姓名和出生日期
6.使用使用CASE子句
查询Employees表中员工的姓名和性别，要求Sex值为1时显示“男”，为0时显示“女”
查询Employees表中员工的姓名、住址和收入水平，2000元以下显示为低收入，2000~3000地显示为中等收入，3000元以上显示为高收入。
7.使用SELECT语句进行简单计算
计算每个雇员的实际收入
8.使用内置函数
获得员工总数
计算Salary表中员工月收入的平均数
获得Employees表中最大的员工号码
计算Salary表中所有员工的总支出
查询财务部雇员的最高和最低实际收入
9.模糊查询
找出所有姓王的雇员的部门号
找出所有地址中含有“中山”的雇员的号码及部门号
找出员工号码中倒数第二个数字为0的员工的姓名、地址和学历
10.Between…And…和Or的使用
找出收入在2000~3000元之间的雇员编号
找出部门为“1”或“2”的雇员的编号
11.使用INTO子句，由源表创建新表
由表Salary创建“SalaryNew”表，要求包括编号和收入，选择收入在1500元以上的雇员
由表Employees创建“EmployeesNew”表，要求包括编号和姓名，选择所有男员工
二、子查询的使用
1.查找在财务部工作的雇员情况
2.用子查询的方法查找所有收入在2500以下的雇员的情况
3.查找财务部年龄不低于研发部雇员年龄的雇员姓名
4.用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名
5.查找比所有财务部的雇员收入都高的雇员的姓名
6.用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名
三、连接查询的使用
1.查询每个雇员的情况及薪水的情况
2.查询每个雇员的情况及其工作部门的情况
3.使用内连接的方法查询名字为“王林”的雇员所在的部门
4.使用内连接的方法查找出不在财务部工作的所有雇员信息
5.使用外连接方法查找出所有员工的月收入
6.查找财务部收入在2000元以上的雇员姓名及其薪水详情
7.查询研发部在1976年以前出生的雇员姓名及其薪水详请
四、聚合函数的使用
1.求财务部雇员的平均收入
2.查询财务部雇员的最高和最低收入
3.求财务部雇员的平均实际收入
4.查询财务部雇员的最高和最低实际收入
5.求财务部雇员的总人数
6.统计财务部收入在2500元以上的雇员人数
五、GROUPBY、ORDERBY子句的使用
1.查找Employees表中男性和女性的人数
2.按部门列出在该部门工作的员工的人数
3.按员工的学历分组，排列出本科、大专、硕士的人数
4.查找员工数超过2的部门名称和雇员数量
5.按员工的工作年份分组，统计各个工作年份的人数，例如工作1年的多少人，工作2年的多少人
6.将雇员的情况按收入由低到高排列
7.将员工信息按出生时间从小到大排列
8.在ORDERBY子句中使用子查询，查询员工姓名、性别和工龄信息，要求按实际收入从大到小排列
六、视图的使用
1.创建视图
（1）在数据库YGGL上创建视图Departments_View，视图包含Department表的全部列
（2）创建视图Employees_Departments_View，视图包含员工号码、姓名、所在部门名称
（3）创建视图Employees_Salary_View，视图包含员工号码、姓名和实际收入三列
2.查询视图
从视图Employees_Salary_View中查询出姓名为“王林”的员工的实际收入
3.更新视图
（1）向视图Departments_View中添加一条记录（‘6’，‘广告部’，‘广告业务’）
执行完命令后，分别查看Departments_View和Department表中发生的变化
（2）尝试向Employees_Departments_View中添加一条记录，看看会发生什么情况
（3）尝试向Employees_Salary_View中添加一条记录，看看会发生什么情况
（4）将视图Departments_View中，部门号为‘6’的部门名称修改为‘生产车间’
（5）删除视图Departments_View中最新增加的的一条记录
4.删除视图Employees_Departments_View
5.在界面工具中操作视图
一、SELECT语句的基本使用
1.查询Employees表中所有数据
SELECT*FROMEmployees;
2.查询Employees表中指定字段数据
SELECTEmployeeID,Name,DepartmentIDFROMEmployees;
3.查询Employees表中的部门号和性别，要求使用Distinct消除重复行
SELECTDistinctDepartmentID,SexFROMEmployees;
4.使用WHERE子句查询表中指定的数据
查询编号为’000001’的雇员的地址和电话
SelectAddress,PhoneNumberFROMEmployeesWHEREEmployeeID='000001';
查询月收入高于2000元的员工号码
SELECTEmployeeIDFROMSalaryWHEREInCome>2000;
查询1970年以后出生的员工的姓名和住址
SELECTName,AddressFROMEmployeesWHEREYEAR（Birthday）>'1970';
SELECTName,AddressFROMEmployeesWHEREBirthday>'1970';
5.使用AS子句为表中字段指定别名
查询Employees表中女雇员的地址和电话，并将列标题显示为地址和电话
SELECTAddressAS地址,PhoneNumberAS电话FROMEmployees;
查询Employees表中男雇员的姓名和出生日期，并将列标题显示为姓名和出生日期
SELECTNameAS姓名,BirthdayAS出生日期FROMEmployeesWHERESex=1;
6.使用使用CASE子句
查询Employees表中员工的姓名和性别，要求Sex值为1时显示“男”，为0时显示“女”
SELECTNameAS姓名,
CASE
WHENSex=1THEN'男'
WHENSex=0THEN'女'
END
AS性别
FROMEmployees;
查询Employees表中员工的姓名、住址和收入水平，2000元以下显示为低收入，2000~3000地显示为中等收入，3000元以上显示为高收入。
SELECT
a.NameAS姓名,a.Addressas住址,
CASE
WHENb.InCome<2000THEN'低收入'
WHENb.InComeBETWEEN2000AND3000THEN'中等收入'
WHENb.InCome>3000THEN'高收入'
END
AS收入水平
FROMEmployeesa,Salaryb
WHEREa.EmployeeID=b.EmployeeID;
7.使用SELECT语句进行简单计算
在Salary表中，计算每个雇员的实际收入，并显示雇员编号和实际收入
SELECTEmployeeIDAS雇员编号,InCome-OutComeAS实际收入FROMSalary;
8.使用聚合函数
获得员工总数
SELECTCOUNT（*）FROMEmployees;
计算Salary表中员工月收入的平均数
SELECTAVG（InCome）AS平均收入FROMSalary;
获得Employees表中最大的员工号码
SELECTMAX（EmployeeID）AS雇员编号FROMEmployees;
计算Salary表中所有员工的总支出
SELECTSUM（OutCome）AS总支出FROMSalary;
查询财务部雇员的最高和最低实际收入
SELECTMAX（InCome-OutCome）AS最高收入,MIN（InCome-OutCome）AS最低收入FROMSalary;
9.模糊查询
找出所有姓王的雇员的部门号
SELECTDepartmentIDAS部门号FROMEmployeesWHERENamelike'王%';
找出所有地址中含有“中山”的雇员的编号及部门号
SELECTEmployeeIDAS雇员编号,DepartmentIDAS部门号FROMEmployeesWHEREAddresslike'%中山%'
找出雇员编号中倒数第二个数字为0雇员的编号的姓名、地址和学历
SELECTEmployeeIDAS雇员编号,NameAS姓名,AddressAS地址,EducationAS学历FROMEmployeesWHERESUBSTRING（EmployeeID,5,1）='0';
10.Between…And…和Or的使用
找出收入在2000~3000元之间的雇员编号
SELECTEmployeeID,InComeFROMSalaryWHEREInComeBETWEEN2000AND3000;
找出部门为“1”或“2”的雇员的编号
SELECTEmployeeID,DepartmentIDFROMEmployeesWHEREDepartmentID='1'ORDepartmentID='2';
11.使用INTO子句，由源表创建新表
由表Salary创建“SalaryNew”表，要求包括编号和收入，选择收入在1500元以上的雇员
SELECTEmployeeID,InCome
INTOSalaryNew
FROMSalary
WHEREInCome>1500;
由表Employees创建“EmployeesNew”表，要求包括编号和姓名，选择所有男员工
SELECTEmployeeID,Name
INTOEmployeesNew
FROMEmployees
WHERESex=1;
二、子查询的使用
1.查找在财务部工作的雇员情况
--分析:
雇员表和部门表通过DepartmentID进行关联
--1）在Departments表中查找财务部的编号
--2）在Employees表中查找部门的雇员情况
SELECT*FROMEmployeesWHEREDepartmentID=（
SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='财务部'）;
2.用子查询的方法查找所有收入在2500以下的雇员的情况
--分析:
员工表和收入表通过EmployeeID进行关联
SELECT*FROMEmployeesWHEREEmployeeIDIN（
SELECTEmployeeIDFROMSalaryWHEREInCome<2500）;
3.查找财务部年龄不低于研发部雇员年龄的雇员姓名
--分析:
雇员表和部门表通过DepartmentID进行关联
--1）先找到研发部的部门编号
--2）再通过部门编号找到研发部的雇员年龄
--3）再找到财务部的部门编号,并通过部门编号查找雇员姓名,条件是年龄不低于所有研发部雇员年龄
SELECTNameFROMEmployeesWHEREDepartmentIDin（
SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='财务部'）
ANDBirthday!
>ALL（
SELECTBirthdayFROMEmployeesWHEREDepartmentIDin（
SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='研发部'））;
4.用子查询的方法查找研发部比所有财务部雇员收入都高的雇员的姓名
--1）查找财务部雇员的收入
--2）查找研发部雇员的编号,条件是收入比所有财务部雇员收入都高
--3）通过编号找到雇员姓名
SELECTNameFROMEmployeesWHEREEmployeeIDIN（
SELECTEmployeeIDFROMSalaryWHEREEmployeeIDIN（
SELECTEmployeeIDFROMEmployeesWHEREDepartmentID=（
SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='研发部'））
ANDInCome>ALL（
SELECTInComeFROMSalaryWHEREEmployeeIDIN（
SELECTEmployeeIDFROMEmployeesWHEREDepartmentID=（
SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='财务部'））））;
5.查找比所有财务部的雇员收入都高的雇员的姓名
--分析:
1）查找所有财务部的雇员收入
--2）查找其他雇员编号,条件是收入比所有财务部的雇员收入都高
--3）通过编号找到姓名
SELECTNameFROMEmployeesWHEREEmployeeIDIN（
SELECTEmployeeIDFROMSalaryWHEREInCome>ALL（
SELECTInComeFROMSalaryWhereEmployeeIDIN（
SELECTEmployeeIDFROMEmployeesWhereDepartmentID=（
SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='财务部'））））;
6.用子查询的方法查找所有年龄比研发部雇员年龄都大的雇员的姓名
--分析:
年龄都大等价于生日都小
--1）找到所有研发部雇员的生日
--2）找到其他部门雇员的姓名,条件是生日比研发部的所有雇员的生日都小
SELECTNameFROMEmployeesWHEREBirthdaySELECTBirthdayFROMEmployeesWHEREDepartmentIDIN（
SELECTDepartmentIDFROMDepartmentsWHEREDepartmentName='研发部'））;
三、连接查询的使用
1.查询每个雇员的情况及薪水的情况
SELECTa.*,b.*
FROMEmployeesa,Salaryb
WHEREa.EmployeeID=b.EmployeeID;
2.查询每个雇员的情况及其工作部门的情况
SELECTa.*,b.*
FROMEmployeesa,Departmentsb
WHEREa.DepartmentID=b.DepartmentID;
3.使用内连接的方法查询名字为“王林”的雇员所在的部门
SELECTb.DepartmentNameFROMDepartmentsb
INNERJOINEmployeesa
ONa.DepartmentID=b.DepartmentID
Wherea.Name='王林';
4.使用内连接的方法查找出不在财务部工作的所有雇员信息
SELECTa.*FROMEmployeesa
INNERJOINDepartmentsb
ONa.DepartmentID=b.DepartmentID
WHEREb.DepartmentName!
='财务部';
5.使用外连接方法查找出所有员工的月收入
SELECT*FROMEmployeesa
LEFTOUTERJOINSalarybON
a.EmployeeID=b.EmployeeID;
6.查找财务部收入在2000元以上的雇员姓名及其薪水详情
SELECTa.*,b.InComeFROMEmployeesa
INNERJOINSalaryb
ONa.EmployeeID=b.EmployeeID
INNERJOINDepartmentsc
ONa.DepartmentID=c.DepartmentID
WHEREb.InCome>2000ANDc.DepartmentName='财务部';
7.查询研发部在1976年以前出生的雇员姓名及其薪水详请
SELECTa.*,b.InComeFROMEmployeesa
INNERJOINSalaryb
ONa.EmployeeID=b.EmployeeID
INNERJOINDepartmentsc
ONa.DepartmentID=c.DepartmentID
WHEREc.DepartmentName='研发部'ANDa.Birthday>'1976'
四、聚合函数的使用
1.求财务部雇员的平均收入
SELECTAVG（a.InCome）FROMSalarya
INNERJOINEmployeesb
ONa.EmployeeID=b.EmployeeID
INNERJOINDepartmentsc
ONc.DepartmentID=b.DepartmentID
WHEREc.DepartmentName='财务部';
2.查询财务部雇员的最高和最低收入
SELECTMIN（a.InCome）,MAX（a.InCome）FROMSalarya
INNERJOINEmployeesb
ONa.EmployeeID=b.EmployeeID
INNERJOINDepartmentsc
ONc.DepartmentID=b.DepartmentID
WHEREc.DepartmentName='财务部';
3.求财务部雇员的平均实际收入
SELECTAVG（a.InCome-a.OutCome）FROMSalarya
INNERJOINEmployeesb
ONa.EmployeeID=b.EmployeeID
INNERJOINDepartmentsc
ONc.DepartmentID=b.DepartmentID
WHEREc.DepartmentName='财务部';
4.查询财务部雇员的最高和最低实际收入
SELECTMIN（a.InCome-a.OutCome）,MAX（a.InCome-a.OutCome）FROMSalarya
INNERJOINEmployeesb
ONa.EmployeeID=b.EmployeeID
INNERJOINDepartmentsc
ONc.DepartmentID=b.DepartmentID
WHEREc.DepartmentName='财务部';
5.求财务部雇员的总人数
SELECTCOUNT（a.EmployeeID）FROMEmployeesa
INNERJOINDepartmentsb
ONa.DepartmentID=b.DepartmentID
WHEREb.DepartmentName='财务部';
6.统计财务部收入在2500元以上的雇员人数
SELECTCOUNT（a.EmployeeID）FROMEmployeesa
INNERJOINDepartmentsb
ONa.DepartmentID=b.DepartmentID
INNERJOINSalaryc
ONc.EmployeeID=A.EmployeeID
WHEREc.InCome>2500ANDb.DepartmentName='财务部';
五、GROUPBY、ORDERBY子句的使用
1.查找Employees表中男性和女性的人数
SELECTSex,COUNT（Sex）FROMEmployeesGROUPBYSex;
2.按部门列出在该部门工作的员工的人数
--使用内连接的方法
SELECTb.DepartmentName,COUNT（a.EmployeeID）FROMEmployeesa
INNERJOINDepartmentsb
ONa.DepartmentID=b.DepartmentID
GROUPBYb.DepartmentName;
--使用多表查询方法,GROUPBY后面的字段,必须出现在SELECT语句要查询的字段中。
SELECTb.DepartmentName,COUNT（a.EmployeeID）FROMEmployeesa,Departmentsb
WHEREa.DepartmentID=b.DepartmentID
GROUPBYb.DepartmentName;
3.按员工的学历分组，排列出本科、大专、硕士的人数
SELECTa.Education,COUNT（a.EmployeeID）FROMEmployeesa
GROUPBYa.Education;
4.查找员工数超过2的部门名称和雇员数量
SELECTb.DepartmentName,COUNT（a.EmployeeID）FROMEmployeesa,Departmentsb
WHEREa.DepartmentID=b.DepartmentID
GROUPBYb.DepartmentName
HAVINGCOUNT（a.EmployeeID）>2;
5.按员工的工作年份分组，统计各个工作年份的人数，例如工作1年的多少人，工作2年的多少人
SELECTa.WorkYear,COUNT（a.EmployeeID）FROMEmployeesa
GROUPBYa
升级会员 