你的位置:首页 > 数据库

[数据库]整理的sql sever一些数据库查询面试题


当然,我整理的只是一些常见的面试题,具体数据库就不给了,相信大家能看懂!!!

--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 Employeeswhere 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 Employeesas a where 5000>(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 Employeesas a group by a.department_id --根据部门号由高而低,工资有低而高列出每个员工的姓名,部门号,工资select a.employee_name as '姓名',a.department_id as '部门号',a.salary as '工资' from Employees asa 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 joindepartments on employees.Department_Id=Departments.Department_ID--8列出EMPLOYEES中工作为’职员'的员工的姓名,工作,部门号,部门名select employee_name ,Employee_job ,employees.department_id,department_name from employees inner joindepartments 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 Departmentsleft 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 Employeesas a where a.Salary>(select avg(salary) from Employees as b where a.Department_Id=b.Department_Id)order by a.Department_IdSELECT   Employees.department_id,  Employee_name,  SalaryFROM  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_Idselect a.department_id as '部门号',a.employee_name as '姓名',a.salary as '工资' from Employeesas aselect 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 awhere 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 awhere (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))>1group by a.DEPARTMENT_ID order by a.DEPARTMENT_IDselect count(*) as 员工数,a.DEPARTMENT_ID as 部门号,avg(SALARY) as 平均工资 from EMPLOYEES as agroup by a.DEPARTMENT_ID order by a.DEPARTMENT_IDselect * 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 awhere (select count(*) from EMPLOYEES as b where b.SALARY<a.SALARY)>5