当前位置：文档之家› SQL实验(实验4至实验7的答案)

SQL实验(实验4至实验7的答案)

ＳＱＬ实验

实验4

1.用select 语句查询departments和salary表中的所有数据：

select salary.*, departments.*

from salary ,departments

2、查询departments 中的departmentid：

select departmentid from departments

3、查询 salary中的 income，outcome：

select income,outcome from salary

4、查询employees表中的部门号，性别，要用distinct消除重复行：select distinct(departmentid), sex

from employees

5、查询月收入高于2000元的员工号码：

select employeeid from salary

where income>2000

6、查询1970年以后出生的员工的姓名和住址：

select name,address

from employees

where birthday>1970

7、查询所有财务部的员工的号码和姓名：

select employeeid ,name

from employees

where departmentid in(select departmentid from departments where

departmentname='财务部')

8、查询employees员工的姓名，住址和收入水平，2000元以下显示为低收入，2000~3000元显示为中等收入，3000元以上显示为高收入：

select name,address,

case

when income-outcome<2000 then'低收入'

when income-outcome>3000 then'高收入'

else'中等收入'

end as'收入等级'

from employees,salary

where employees.employeeid=salary.employeeid

9、计算salary表中员工月收入的评价数：

select avg(income)as'平均收入'from salary

10、查找employees表中最大的员工号码：

select max(employeeid)as'最大员工号码'from employees

11、计算salary表中的所有员工的总支出：

select sum(outcome)as'总支出'from salary

12、查询财务部雇员的最高实际收入：

select max(income-outcome)from salary ,employees,departments

where salary.employeeid=employees.employeeid and

employees.departmentid=departments.departmentid and departmentname='财

务部'

13、查询财务部雇员的最低实际收入：

select min(income-outcome)from salary ,employees,departments

where salary.employeeid=employees.employeeid and

employees.departmentid=departments.departmentid and departmentname='财务部'

14、找出所用地址中含有“中山”的雇员的号码及部门号：

select employeeid ,departmentid

from employees

where address like'%中山%'

15、查找员工号码中倒数第二个数字为0的员工的姓名，地址和学历：

select education,address,name

from employees

where employeeid like'%0_'

16、使用into字句，由表employees创建“男员工1”表，包括编号和姓名：

select employeeid,name

into男员工表

from employees

where sex='1'

17、用子查询的方法查找收入在2500元以下的雇员的情况：

select*from employees

where employeeid in

(select employeeid from salary where income<2500)

18、用子查询的方法查找查找研发部比所有财务部雇员收入都高的雇员的姓名：SELECT Name FROM Employees WHERE EmployeeID IN

(

SELECT EmployeeID FROM Salary

WHERE EmployeeID IN

(

SELECT EmployeeId FROM Employees

WHERE DepartmentID IN

(

SELECT DepartmentID FROM Departments

WHERE DepartmentName='研发部'

)

AND InCome>ALL

(

SELECT InCome FROM Salary

WHERE EmployeeID IN

(

SELECT EmployeeId FROM Employees

WHERE DepartmentID IN

(

SELECT DepartmentID FROM Departments

WHERE DepartmentName='财务部'

)

19、用子查询的方法查找所有年龄比研发部雇员都大的雇员的姓名：

select name

from employees

where Birthday

(select birthday

from employees

where departmentid in

(select departmentid

from departments

where departmentname='研发部'

)

20、查询每个员工的情况及其薪水的情况：

select employees.*,departments.departmentname

from employees,departments

where employees.departmentid=departments.departmentid

21、使用内连接方法查找不在财务部工作的所有员工信息：

select employees.*

from employees inner join departments on

employees.departmentid=departments.departmentid

where departmentname!='财务部'

22、使用外连接方法查找出所有员工的月收入：

select employees.*,salary.income

from employees join salary on employees.employeeid=salary.employeeid

23、查找财务部雇员的最高收入：

select max(income)

from salary

where employeeid in

(select employeeid

from employees

where departmentid in

(select departmentid

from departments

where departmentname='财务部'

)

24、查询财务部雇员的最高实际收入：

select max(income-outcome)

from salary

where employeeid in

(select employeeid

from employees

where departmentid in

(select departmentid

from departments

where departmentname='财务部'

)

25、统计财务部收入在2500元以上的雇员人数：select count(employeeid)

from employees

where departmentid in

(select departmentid from departments where departmentname='财务部')

and employeeid in

(

select employeeid

from salary

where income>2500)

26、按部门列出在该部门工作的员工的人数：select departmentid ,count(*)as人数from employees

group by departmentid

27、按员工的学历分组：

select education ,count(*)as人数

from employees

group by education

28、按员工的工作年份分组，统计年份人数：

select workyear ,count(*)as人数

from employees

group by workyear

29、按各雇员的情况收入由低到高排列：

select employees.*,salary.income

from employees ,salary

where employees.employeeid=salary.employeeid

order by income

30、将员工信息按出生时间从小到大排列：

select*

from employees

order by birthday

31、在order by 字句中使用子查询，查询员工姓名，性别和工龄信息，要求按实际收入从大到小排列：

select name,sex,workyear,income-outcome

from salary ,employees

where salary.employeeid=employees.employeeid

order by income-outcome desc

视图部分

1、创建view1：

Create view view1

select employees.employeeid,name,departmentname,(income-outcome)as come

from employees , departments , salary

where employees.departmentid=departments.departmentid and employees.employeeid=salary.employeeid

2、查询视图employeeid：

3、向视图view1中插入一行数据：

insert into view1 values('111111','谎言','1','30000')

4、查看视图（没有影响）基本表：

实验5

1、定义一个变量，用于描述YGGL数据库的salary表中000001号员工的实际收入，然后

查询该变量：

declare @hy int

set @hy=(select income-outcome

from salary

where employeeid='000001')

select @hy

2、使用运算符“>”：

select name

from employees

where birthday>'1974-10-10'

3、判断姓名为“王林”的员工实际收入是否高于3000元，如果是则显示“高收入”，否则

显示“收入不高于3000”：

if((select income

from salary,employees

where salary.employeeid=employees.employeeid and https://www.doczj.com/doc/e34584119.html,='刘明')>3000)

select income as'高收入'

from salary,employees

where salary.employeeid=employees.employeeid and https://www.doczj.com/doc/e34584119.html,='刘明' else

select'收入不高于'

4、使用循环输出一个“*”三角形：

declare @i int

declare @j int

set @j=20

set @i=1

while @i<@j

begin

print(space((@j-@i)/2)+replicate('*',@i))

set @i=@i+2

end

4、按部门进行分类，使用if语句实现：

Create function hy1(@departmentid1 char(3))

returns char(10)as

begin

declare @hy1 char(10)