重要sql语句

一、表结构

--测试表：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[EMPLOYEE]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[EMPLOYEE](
	[ID] [int] NOT NULL,
	[NAME] [varchar](30) NOT NULL,
	[JOB] [varchar](30) NOT NULL,
	[TIME] [varchar](30) NOT NULL,
	[SAL] [int] NOT NULL,  --工资
	[COMM] [int] NULL,
	[SECTION] [int] NULL,
	[HIGHERUP] [int] NULL
) ON [PRIMARY]
END

 

问题：查询工资排在第3到第10的员工信息

select top 7 id,name,sal from 
(select top 10 id,name,sal from employee order by sal desc) b
where id not in (select top 3 id from (select top 10 id,name,sal from employee order by sal desc) c)

 

--各部门工资大于部门平均工资的员工信息

select *
  from personnel p
 where sal > (select avg(sal) from personnel where p.section = section);

 

--打印选课数大于3的学生的id和姓名

select em.id,em.name from text em where em.age > (select age from text where em.manager = id);

 

--删除相同数据

delete from text t where t.id > (select min(x.id) from text x where t.name = x.name);