sql习题练习

表结构：
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

上面是创建表和数据库的代码

use MyCompanyTest
go
select * from dbo.Departments
select * from Employees

--1.列出EMPLOYEES表中各部门的：部门编号，最高工资，
select e.department_Id 部门编号, MAX(e.Salary) 最高工资,MIN(e.salary)最低工资
from Employees e
group by e.department_Id 

--2.列出EMPLOYEES表中各部门的：部门编号、部门名称、最高工资、最低工资
  
  select 部门编号, 最高工资,最低工资 ,d.Department_Name from
      (select e.Department_ID 部门编号, max(e.Salary)最高工资,min(e.Salary)最低工资
     from Employees e
    group by e.Department_Id )as t join Departments d
on t.部门编号=d.Department_ID


--3.列出EMPLOYEES表中各部门中'职员'（Employee_job为'职员'）的：最低工资，最高工资和部门Id

select min(Salary)最低工资,MAX(Salary) 最高工资,Department_Id 部门Id from Employees
where Employee_Job='职员'
group by Department_Id

--4.对于EMPLOYEES中最低工资小于1000的部门，列出EMPLOYEE_JOB为'职员'的：部门编号，最低工资，最高工资

select * from(
    select Department_Id,MIN(e.Salary)最低工资,MAX(e.Salary)最高工资  from Employees e 
    where e.Employee_Job='职员'
    group by e.Department_Id) as t
    join Departments d on d.Department_ID =t.Department_Id
    where t.最低工资<1000

--5.根据部门编号由高到低，工资由低到高，列出每个员工的姓名，部门号，工资
        select * from Employees
        order by Department_Id desc , Salary asc
--6.列出'吴用'所在部门中每个员工的姓名与部门号
    select * from Employees where Department_Id in (
    select   Department_Id  from Employees where Employee_Name='吴用'
    )
--7.列出每个员工的姓名，头衔，部门号，部门名
    select e.Employee_Name,e.Employee_Job,d.Department_ID,d.Department_Name
     from Employees e join Departments d
    on e.Department_Id=d.Department_ID
--8.列出EMPLOYEES中头衔为'职员'的员工的姓名，工作，部门号，部门名

        select * from Employees e 
        join Departments d on e.Department_Id=d.Department_ID
        where e.Employee_Job='职员'
--9.对于DEPARTMENTS表中，列出所有(说明是左联):部门名称，部门编号，以及该部门的：员工姓名与头衔
    select * from Departments d left join Employees e
    on d.Department_ID=e.Department_Id
--10.列出工资高于本部门工资平均水平的员工的部门编号，姓名，工资，并且按部门编号排序。
select * from Employees e join(
            select Department_Id, AVG(Salary)as 平均工资 from Employees  e
            group by e.Department_Id)as t --求出各个部门的平均工资
    on e.Department_Id=t.Department_Id
    where e.Salary > t.平均工资
    order by e.Department_Id
    
    ---相关子查询
    select * from employees as emp
    where 
    exists(
     select department_id,avg(salary) as avg_salary 
       from employees as emp_sub
        group by department_id
    having emp_sub.department_id=emp.department_id and emp.salary>avg(salary)
    )

----
select * 
from EMPLOYEES as e
where 
e.SALARY >(select avg(SALARY) from EMPLOYEES as b where e.DEPARTMENT_ID = b.DEPARTMENT_ID)
order by e.DEPARTMENT_ID
--11.对于EMPLOYEES，列出各个部门中工资高于本部门平均水平的员工 数和部门号，按部门号排序
select
        emp.department_id as 部门编号,
        count(*) as 员工数
from Employees as emp
where emp.salary > 
        (select avg(salary) from employees  emp_sub 
      where emp_sub.department_id=emp.department_id)
group by emp.department_id
order by emp.department_id


--12.请找出部门中具有2人以上，员工工资大于所在部门平均工资的:部门的id与这些人的人数。
--分解：
--1>.部门中有人的工资比部门的平均工资还高
--2>并且这些人在2人以上
--3>查询出这些部门Id,与工资高于部门平均工资的人的人数。
select
        emp.department_id as 部门编号,
        count(*) as 员工数
from Employees as emp
where emp.salary > (select avg(salary) from employees emp_sub where emp_sub.department_id=emp.department_id)
group by emp.department_id
having count(*) >2
order by emp.department_id


--13.对于EMPLOYEES中低于自己工资至少5人的员工，列出其部门号，姓名，工资，
--以及工资少于自己的人数
select
        
        employee_name 姓名,
        salary 工资,
        小于自己工资的人数=(select count(*) from employees as emp_sub where emp_sub.salary<emp.salary)
from employees as emp
where (select count(*) from employees as emp_sub where emp_sub.salary<emp.salary)<5