sqlsever的简单练习

最新推荐文章于 2023-02-28 15:01:45 发布

codingCoge

最新推荐文章于 2023-02-28 15:01:45 发布

阅读量404

点赞数

分类专栏： SQL

本文链接：https://blog.csdn.net/qq_38409944/article/details/79086022

版权

本文主要介绍了SQL Server的基本操作，包括数据库的创建、数据表的建立、数据的增删改查以及简单的查询语句示例，是初学者进行SQL Server练习的良好起点。

摘要由CSDN通过智能技术生成

题目：
--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