MySql_34道经典Sql试题（二）

如果能流畅地把这34道题解答出来的话，那对于大多数开发者来说就不会再为写Sql语句而烦恼，写复杂的Sql语句时要分步骤完成，逐步击破最终就能得到你想要的东西。看完这两篇文章不代表你已经学会了，这只是个小小的开端而已。

十八、列出薪水比”贾跃亭”高的所有员工信息

1、先求出‘贾跃亭’的工资

select EmployeeName,EmployeeCode,Salary from employee
where Salary>(select Salary from Employee where EmployeeName='贾跃亭');

十九、列出所有Worker的姓名及其部门名称、部门人数

1、先找出所有部门的名称及其部门人数

select EmployeeName,b.DeptNo,b.DName,b.employeeCount from Employee
left join(select Employee.Id,Employee.DeptNo,b.DName,count(*)as employeeCount from Employee 
          inner join Dept as b on Employee.DeptNo=b.DeptNo
          group by Employee.Id,Employee.DeptNo,b.DName)as b
on Employee.Id=b.Id
where Employee.Job='Worker';

二十、列出最低薪水>1500的各种工作及从事此工作的全部雇员人数

1、找出各种工作的全部雇员人数
2、找出最低薪水>1500的各种工作

select Employee.Job,min(Salary)as minSalary,b.employeeCount from Employee
inner join(select Job,count(*)as employeeCount from Employee group by Job)as b
on Employee.Job=b.Job
group by Employee.Job
having minSalary>1500;

二十一、列出在财务部工作的员工姓名，假定不知道财务部的编号

1、先找出财务部的编号

select * from Employee
where DeptNo=(select DeptNo from Dept where DName='财务部');

二十二、列出薪水高于公司平均薪水的所有员工，所在部门、薪水等级

1、先求出公司平均薪水
2、求出所有员工的所在部门、薪水等级

select Employee.EmployeeName,Employee.DeptNo,b.DName,Employee.Salary,c.Grade from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo
inner join SalaryGrade as c on employee.Salary between c.Lowest and c.Highest
where Employee.Salary>(select avg(Salary) from Employee);

二十三、列出与”安禄山”从事相同工作的所有员工及部门名称

1、先找出‘安禄山’的工作岗位

select Employee.EmployeeName,Employee.Job,Employee.DeptNo,b.DName from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo
where Job=(select Job from Employee where EmployeeName='安禄山');

二十四、列出薪水等于财务部中员工薪水的其他员工的姓名和薪水

1、先找出财务部中所有员工的薪水

select EmployeeName,Salary from Employee
where Salary in(select Salary from Employee where DeptNo='101') and DeptNo != '101';

二十五、列出薪水大于财务部所有员工薪水的员工的姓名、薪水、部门名称

1、找出财务部中的最高薪水

select Employee.EmployeeName,Employee.Salary,Employee.DeptNo,b.DName from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo
where Salary >(select max(Salary) from Employee where DeptNo='101');

二十六、列出每个部门的员工数量、平均薪水和平均服务期限

1、获取系统当前时间now()
2、日期转换为天数to_days()

select DeptNo,count(*),avg(Salary),avg(to_days(now())-to_days(CreateDate)) from Employee
group by DeptNo;

二十七、列出所有员工的姓名、部门名称和工资

1、常见的内连接

select Employee.EmployeeName,Employee.DeptNo,b.DName,Employee.Salary from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo;

二十八、列出所有部门的详细信息和人数

1、先求出所有部门的人数
2、IFNULL(expr1,expr2) 如果expr1是NULL，IFNULL()返回expr2，否则返回expr1

select Dept.DeptNo,DName,Location,ifnull(b.employeeCount,0) from Dept
left join (select DeptNo,count(*)as employeeCount from Employee group by DeptNo)as b
on Dept.DeptNo=b.DeptNo;

二十九、列出各种工作的最低工资及从事此工作的员工姓名

1、找出各工作的最低工资

select Employee.EmployeeName,Employee.Job,Employee.Salary from Employee
inner join (select Job,min(Salary)as minSalary from Employee group by Job)as b
on Employee.Job=b.Job and Employee.Salary=b.minSalary;

三十、列出各部门Manager的最低薪水

1、按照部门分类
2、工作岗位=’Manager’

select DeptNo,min(Salary) from Employee 
where Job='Manager'
group by DeptNo;

三十一、列出所有员工的年薪，按年薪升序排列

1、字段可以进行运算

select EmployeeName,(Salary+ifnull(Bonus,0))*12 as YearSalary from Employee
order by YearSalary asc;

三十二、求出员工领导的薪水超过7000的员工名称和领导名称

1、先把员工、员工领导、员工领导的薪水关联起来

select Employee.EmployeeName,b.EmployeeName as LeaderName from Employee
inner join Empoloyee as b on Employee.LeaderId=b.Id
where b.Salary >7000;

三十三、求出部门名称中带”务”字符的工资合计和部门人数

1、求出所有部门的部门名称、工资合计、部门人数

select Employee.DeptNo,b.DName,sum(Salary),count(*) from Employee
inner join Dept as b on Employee.DeptNo=b.DeptNo
where b.DName like '%务%'
group by Employee.DeptNo,b.DName;

三十四、给任职超过1年的员工加薪10%

1、修改表中的信息

update Employee set Salary=Salary*1.1
where (to_days(now())-to_days(CreateDate))>365