题目：
--1.列出EMPLOYEES表中各部门的：部门编号，最高工资，最低工资
--2.列出EMPLOYEES表中各部门的：部门编号、部门名称、最高工资、最低工资
select "D".Department_ID,"D".Department_Name,"E".最高工资,"E".最高工资
from (select Department_ID,Department_Name from Departments ) "D"
left join (select Department_Id,MAX(Salary) 最高工资,MIN(Salary) 最低工资 from Employees
group by Department_Id) "E"
on "D".Department_ID="E".Department_Id
--3.列出EMPLOYEES表中各部门中'职员'（Employee_job为'职员'）的：最低工资，最高工资和部门Id
select MIN(Salary) "最低工资",MAX(salary) "最高工资","D".Department_ID
from (select Department_ID from Departments) "D"
left join (select * from Employees where Employee_Job='职员') "E"
on "D".Department_ID="E".Department_Id
group by "D".Department_Id
--4.对于EMPLOYEES中最低工资小于1000的部门，列出EMPLOYEE_JOB为'职员'的：部门编号，最低工资，最高工资。
select MIN(Salary) "最低工资",MAX(salary) "最高工资","D".Department_ID
from (select Department_ID from Departments) "D"
left join (select * from Employees where Employee_Job='职员') "E"
on "D".Department_ID="E".Department_Id
group by "D".Department_Id
having MIN(Salary) <1000.0000
--5.根据部门编号由高到低，工资由低到高，列出每个员工的姓名，部门号，工资
select Employee_Name "名字",Department_Id as "部门号",Salary as "工资"
from Employees
order by Department_Id desc,Salary asc
--6.列出'吴用'所在部门中每个员工的姓名与部门号。
select distinct Employees.Employee_Name as "姓名",Employees.Department_Id from
(select distinct Department_Id
from Employees
where Employee_Name='吴用') "E"
left join Employees
on Employees.Department_Id="E".Department_Id
where Employees.Employee_Name!='吴用'
--7.列出每个员工的姓名，头衔，部门号，部门名。
select Employee_Name "名字",Employee_Job "头衔",Employees.Department_Id as "部门号",Department_Name "部门名" from
Employees
left join Departments
on Employees.Department_Id=Departments.Department_Id
--8.列出EMPLOYEES中衔为'职员'的员工的姓名，工作，部门号，部门名。
select distinct Employees.Employee_Name "名字",Employees
.Department_Id as "部门号",Employees.Employee_Job "工作",Departments.Department_Name as "部门号" from
Employees
join Departments
on Employees.Department_Id=Departments.Department_ID
--9.对于DEPARTMENTS表中，列出所有:部门名称，部门编号，以及该部门的：员工姓名与头衔。
select "D".名称,"D".编号,"E".员工,"E".头衔 from
(select Department_Name "名称",Department_ID "编号" from Departments) "D"
join (select Employee_Name "员工",Employee_Job "头衔",Department_Id "编号" from Employees ) "E"
on "D".编号="E".编号
order by "D".编号
select "D".名称,"D".编号,"E".员工,"E".头衔,COUNT(1) OVER (PARTITION BY "D".编号) from
(select Department_Name "名称",Department_ID "编号" from Departments) "D"
join (select Employee_Name "员工",Employee_Job "头衔",Department_Id "编号" from Employees ) "E"
on "D".编号="E".编号 还包括了 每组的数量 OVER (PARTITION BY "D" ) 是用来查询到每位员工本来的具体信息和它所在部门的总人数：而且显示在每一组中每条记录的后面 而且OVER 前面必须跟聚合函数 而且只能放一个聚合函数 聚合喊出插入select列中间也不行
--10.列出工资高于本部门工资平均水平的员工的部门编号，姓名，工资，并且按部门编号排序。
select Employees.Department_Id as "部门编号",Employee_Name as "姓名",Salary as "工资" from
Employees
join (select Department_Id,AVG(Salary) "工资" from Employees group by Department_Id) "D"
on Employees.Department_Id="D".Department_Id
where Salary>"D".工资
order by "D".Department_Id
--11.对于EMPLOYEES，列出各个部门中工资高于本部门平均水平的员工数和部门号，按部门号排序
select COUNT(1) as"员工数","D".Department_Id as "部门号" from
Employees
join(select Department_Id,AVG(Salary) "工资" from Employees group by Department_Id) "D"
on Employees.Department_Id="D".Department_Id
where Salary>"D".工资
group by "D".Department_Id
--12.请找出部门中具有两人以上，员工工资大于所在部门平均工资的:部门的id与这些人的人数。
select COUNT(1) as"员工数","D".Department_Id as "部门号" from
Employees
join(select Department_Id,AVG(Salary) "工资" from Employees group by Department_Id) "D"
on Employees.Department_Id="D".Department_Id
where Salary>"D".工资
group by "D".Department_Id
having COUNT(1)>2
--分解：
--1>.部门中有人的工资比部门的平均工资还高
--2>并且这些人在人以上
--3>查询出这些部门Id,与工资高于部门平均工资的人的人数。
--13.对于EMPLOYEES中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数。
select Employees.Department_Id as "部门号",Employee_Name as "姓名，",Employees.Salary as "工资","S".人数 from(select "E1".Employee_Id,COUNT(1) as "人数" from
Employees "E1"
join Employees "E2"
on "E1".Salary>"E2".Salary
group by "E1".Employee_Id
having COUNT(1)>=5) "S"
join Employees
on "S".Employee_Id=Employees.Employee_Id
表结构：
脚本开始：
create database MyCompany
go
use MyCompany
go
create table Departments
(
Department_ID int identity(1,1) primary key,
Department_Name nvarchar(50),
)
go
create table Employees
(
Employee_Id int identity(1,1) primary key,
Employee_Name nvarchar(50),
Employee_Job nvarchar(50),
Salary money,
Department_Id int foreign key references Departments(Department_ID)
)
Go
--------------------------------------------插入数据----------------------------------------------------------------------------------
----------------------------------部门表-------------------------------------------------------------------
SET IDENTITY_INSERT departments ON
insert departments(Department_ID,Department_Name) values( 1 , N'财务部' )
insert departments(Department_ID,Department_Name) values( 2 , N'行政部' )
insert departments(Department_ID,Department_Name) values( 3 , N'开发部' )
insert departments(Department_ID,Department_Name) values( 4 , N'市场部' )
SET IDENTITY_INSERT departments OFF
------------------------=============================员工表================================================================================================
SET IDENTITY_INSERT employees ON insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 1 , N'曹操' , N'组长' , 20000.00 , 1 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 2 , N'刘备' , N'经理' , 30000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 3 , N'诸葛亮' , N'CEO' , 10000.00 , 2 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 4 , N'黄月英' , N'职员' , 5000.00 , 1 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 5 , N'关羽' , N'职员' , 8000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 6 , N'张飞' , N'职员' , 8000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 7 , N'赵云' , N'职员' , 7000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 8 , N'马谡' , N'职员' , 4000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 9 , N'宋江' , N'职员' , 13000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 10 , N'林冲' , N'职员' , 7600.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 11 , N'鲁智深' , N'职员' , 8000.00 , 2 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 12 , N'李逵' , N'职员' , 820.00 , 1 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 13 , N'吴用' , N'职员' , 8300.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 14 , N'张顺' , N'职员' , 6200.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 15 , N'时迁' , N'职员' , 600.00 , 2 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 16 , N'石秀' , N'职员' , 1900.00 , 1 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 17 , N'孙悟空' , N'职员' , 8000.00 , 1 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 18 , N'唐僧' , N'职员' , 17000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 19 , N'诸葛亮' , N'职员' , 10000.00 , 2 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 20 , N'黄月英' , N'职员' , 5000.00 , 1 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 21 , N'关羽' , N'职员' , 8000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 22 , N'张飞' , N'职员' , 8000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 23 , N'赵云' , N'职员' , 7000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 24 , N'马谡' , N'职员' , 4000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 25 , N'宋江' , N'职员' , 13000.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 26 , N'林冲' , N'职员' , 7600.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 27 , N'鲁智深' , N'职员' , 8000.00 , 2 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 28 , N'李逵' , N'职员' , 820.00 , 1 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 29 , N'吴用' , N'职员' , 8300.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 30 , N'张顺' , N'职员' , 6200.00 , 3 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 31 , N'时迁' , N'职员' , 600.00 , 2 )
insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 32 , N'石秀' , N'职员' , 1900.00 , 1 )
SET IDENTITY_INSERT employees OFF
脚本结束
sqlsever的简单练习
最新推荐文章于 2024-08-09 21:25:57 发布
499
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
