sql习题练习
1 表结构： 2 create database MyCompany 3 go 4 use MyCompany 5 go 6 create table Departments 7 ( 8 Department_ID int identity(1,1) primary key, 9 Department_Name nvarchar(50), 10 ) 11 go 12 create table Employees 13 ( 14 Employee_Id int identity(1,1) primary key, 15 Employee_Name nvarchar(50), 16 Employee_Job nvarchar(50), 17 Salary money, 18 Department_Id int foreign key references Departments(Department_ID) 19 ) 20 Go 21 22 --------------------------------------------插入数据---------------------------------------------------------------------------------- 23 ----------------------------------部门表------------------------------------------------------------------- 24 SET IDENTITY_INSERT departments ON 25 insert departments(Department_ID,Department_Name) values( 1 , N'财务部' ) 26 insert departments(Department_ID,Department_Name) values( 2 , N'行政部' ) 27 insert departments(Department_ID,Department_Name) values( 3 , N'开发部' ) 28 insert departments(Department_ID,Department_Name) values( 4 , N'市场部' ) 29 SET IDENTITY_INSERT departments OFF 30 ------------------------=============================员工表================================================================================================ 31 SET IDENTITY_INSERT employees ON insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 1 , N'曹操' , N'组长' , 20000.00 , 1 ) 32 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 2 , N'刘备' , N'经理' , 30000.00 , 3 ) 33 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 3 , N'诸葛亮' , N'CEO' , 10000.00 , 2 ) 34 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 4 , N'黄月英' , N'职员' , 5000.00 , 1 ) 35 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 5 , N'关羽' , N'职员' , 8000.00 , 3 ) 36 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 6 , N'张飞' , N'职员' , 8000.00 , 3 ) 37 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 7 , N'赵云' , N'职员' , 7000.00 , 3 ) 38 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 8 , N'马谡' , N'职员' , 4000.00 , 3 ) 39 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 9 , N'宋江' , N'职员' , 13000.00 , 3 ) 40 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 10 , N'林冲' , N'职员' , 7600.00 , 3 ) 41 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 11 , N'鲁智深' , N'职员' , 8000.00 , 2 ) 42 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 12 , N'李逵' , N'职员' , 820.00 , 1 ) 43 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 13 , N'吴用' , N'职员' , 8300.00 , 3 ) 44 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 14 , N'张顺' , N'职员' , 6200.00 , 3 ) 45 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 15 , N'时迁' , N'职员' , 600.00 , 2 ) 46 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 16 , N'石秀' , N'职员' , 1900.00 , 1 ) 47 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 17 , N'孙悟空' , N'职员' , 8000.00 , 1 ) 48 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 18 , N'唐僧' , N'职员' , 17000.00 , 3 ) 49 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 19 , N'诸葛亮' , N'职员' , 10000.00 , 2 ) 50 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 20 , N'黄月英' , N'职员' , 5000.00 , 1 ) 51 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 21 , N'关羽' , N'职员' , 8000.00 , 3 ) 52 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 22 , N'张飞' , N'职员' , 8000.00 , 3 ) 53 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 23 , N'赵云' , N'职员' , 7000.00 , 3 ) 54 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 24 , N'马谡' , N'职员' , 4000.00 , 3 ) 55 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 25 , N'宋江' , N'职员' , 13000.00 , 3 ) 56 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 26 , N'林冲' , N'职员' , 7600.00 , 3 ) 57 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 27 , N'鲁智深' , N'职员' , 8000.00 , 2 ) 58 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 28 , N'李逵' , N'职员' , 820.00 , 1 ) 59 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 29 , N'吴用' , N'职员' , 8300.00 , 3 ) 60 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 30 , N'张顺' , N'职员' , 6200.00 , 3 ) 61 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 31 , N'时迁' , N'职员' , 600.00 , 2 ) 62 insert employees(Employee_Id,Employee_Name,Employee_Job,Salary,Department_Id) values( 32 , N'石秀' , N'职员' , 1900.00 , 1 ) 63 64 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
