如果能流畅地把这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