整理的sql sever一些数据库查询面试题
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了整理的sql sever一些数据库查询面试题,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含3912字,纯文字阅读大概需要6分钟。
内容图文
![整理的sql sever一些数据库查询面试题](/upload/InfoBanner/zyjiaocheng/1316/ec2d2fadcf7d4959aa7b695d0345215e.jpg)
当然,我整理的只是一些常见的面试题,具体数据库就不给了,相信大家能看懂!!!
--2列出EMPLOYEES表中各部门的部门号,最高工资,最低工资 select Max(salary) as‘最高工资‘,Min(salary) as‘最低工资‘ ,department_id as‘部门号‘from Employees group by department_id --3列出EMPLOYEES表中各部门EMPLOYEE_JOB为‘职员‘的员工的最低工资,最高工资 select Max(salary) as‘最高工资‘,Min(salary) as‘最低工资‘ ,department_id as‘部门号‘from Employees where employee_job=‘职员‘ group by department_id --4对于EMPLOYEES中最低工资小于5000的部门,列出EMPLOYEE_JOB为‘职员‘的员工的部门号,最低工资,最高工资 select a.department_id as‘部门号‘,Max(a.salary) as‘最高工资‘,Min(a.salary) as‘最低工资‘from Employees as a where5000>(select Min(salary) from Employees as b where a.Department_Id=b.Department_Id ) and a.Employee_Job=‘职员‘ group by a.department_id select a.department_id as‘部门号‘,Max(a.salary) as‘最高工资‘,Min(a.salary) as‘最低工资‘from Employees as a group by a.department_id --根据部门号由高而低,工资有低而高列出每个员工的姓名,部门号,工资 select a.employee_name as‘姓名‘,a.department_id as‘部门号‘,a.salary as‘工资‘from Employees as a order by a.Department_Id desc,a.salary asc --6列出‘刘备‘所在部门中每个员工的姓名与部门号 select a.employee_name as‘姓名‘,a.department_id as‘部门号‘from Employees as a where a.Department_Id=(select Department_Id from Employees as b where b.Employee_Name=‘刘备‘) --7列出每个员工的姓名,工作,部门号,部门名 select employee_name ,Employee_job ,employees.department_id,department_name from employees inner join departments on employees.Department_Id=Departments.Department_ID --8列出EMPLOYEES中工作为’职员‘的员工的姓名,工作,部门号,部门名select employee_name ,Employee_job ,employees.department_id,department_name from employees inner join departments on employees.Department_Id=Departments.Department_ID where Employees.Employee_Job=‘职员‘ --8对于DEPARTMENTS表中,列出所有部门名,部门号,同时列出各部门工作为‘职员‘的员工名与工作 select Departments.Department_Name as‘部门名称‘,Departments.Department_ID as‘部门号‘,Employees.Employee_Name as‘员工名‘,Employees.Employee_Job as‘工作‘from Departments left join Employees on Departments.Department_ID=Employees.Department_Id where Employees.Employee_Job=‘职员‘ --9对于工资高于本部门平均水平的员工,列出部门号,姓名,工资,按部门号排序 select a.department_id as‘部门号‘,a.employee_name as‘姓名‘,a.salary as‘工资‘from Employees as a where a.Salary>(select avg(salary) from Employees as b where a.Department_Id=b.Department_Id) order by a.Department_Id SELECT Employees.department_id, Employee_name, Salary FROM Employees, (SELECT AVG(salary) as‘avg‘, department_id FROM Employees GROUP BY department_id) as t WHERE salary > t.avg and Employees.Department_Id = t.Department_Id select a.department_id as‘部门号‘,a.employee_name as‘姓名‘,a.salary as‘工资‘from Employees as a select avg(salary) from Employees where Department_Id=1select avg(salary) from Employees where Department_Id=2select avg(salary) from Employees where Department_Id=3select * from Employees --10对于EMPLOYEES,列出各个部门中工资高于本部门平均水平的员工数和部门号,按部门号排序 select count(*) as‘员工数‘,a.department_id as‘部门号‘,avg(salary) as‘平均工资‘from Employees as a where a.Salary>(select avg(salary) from Employees as b where a.Department_Id=b.Department_Id) group by a.Department_Id having count(a.Department_Id) >1 order by a.Department_Id select salary from Employees where Department_Id=3 --11对于EMPLOYEES中工资高于本部门平均水平,人数多与1人的,列出部门号,人数,按部门号排序 select count(*) as 员工数,a.DEPARTMENT_ID as 部门号,avg(SALARY) as 平均工资 from EMPLOYEES as a where (select count(c.EMPLOYEE_ID) from EMPLOYEES as c where c.DEPARTMENT_ID = a.DEPARTMENT_ID and c.SALARY>(select avg(SALARY) from EMPLOYEES as b where c.DEPARTMENT_ID = b.DEPARTMENT_ID))>1 group by a.DEPARTMENT_ID order by a.DEPARTMENT_ID select count(*) as 员工数,a.DEPARTMENT_ID as 部门号,avg(SALARY) as 平均工资 from EMPLOYEES as a group by a.DEPARTMENT_ID order by a.DEPARTMENT_ID select * from Employees --12对于EMPLOYEES中低于自己工资至少5人的员工,列出其部门号,姓名,工资,以及工资少于自己的人数 select a.DEPARTMENT_ID,a.EMPLOYEE_NAME,a.SALARY,(select count(*) from EMPLOYEES as b where b.SALARY < a.SALARY) as 人数 from EMPLOYEES as a where (select count(*) from EMPLOYEES as b where b.SALARY<a.SALARY)>5
原文:http://www.cnblogs.com/qidakang/p/4802467.html
内容总结
以上是互联网集市为您收集整理的整理的sql sever一些数据库查询面试题全部内容,希望文章能够帮你解决整理的sql sever一些数据库查询面试题所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。