sqlserver 面试题

--是查询A(ID,Name)表中第11至20条记录，ID作为主键可能是不是连续增长的列，完整的查询语句如下：

SELECT TOP 10 * FROM dbo.Employee WHERE EmpIntNo> (SELECT MAX(a.EmpIntNo) FROM (SELECT TOP 10 EmpIntNo FROM dbo.Employee ORDER BY EmpIntNo) a)

要求是查询表Employee中存在EmpName重复三次以上的记录,完整的查询语句如下 SELECT e.* FROM dbo.Employee e,(SELECT COUNT(EmpName) AS counte,EmpName FROM dbo.Employee GROUP BY EmpName) a WHERE e.EmpName=a.EmpName AND a.counte>3

CREATE TABLE [dbo].[Employee]( [EmpIntNo] [int] IDENTITY(1,1) NOT NULL, [EmpName] [varchar](50) NULL, [Emalil] [nvarchar](50) NULL, [Addr1] [nvarchar](50) NULL, [ComIntNo] [int] NULL, CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [EmpIntNo] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]

SET ANSI_PADDING OFF GO

ALTER TABLE [dbo].[Employee] WITH CHECK ADD CONSTRAINT [FK_Employee_Company] FOREIGN KEY([ComIntNo]) REFERENCES [dbo].[Company] ([ComIntNo]) GO

ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Company] GO

为管理业务培训信息，建立3个表：

S(S#,SN,SD,SA)S#,SN,SD,SA分别代表学号，学员姓名，所属单位，学员年龄

C(C#,CN)C#,CN分别代表课程编号，课程名称

SC(S#,C#,G) S#,C#,G分别代表学号，所选的课程编号，学习成绩

（1）使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名?

答案：select s# ,sn from s where S# in(select S# from c,sc where c.c#=sc.c# and cn=’税收基础’)

(2) 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位?

答：select sn,sd from s,sc where s.s#=sc.s# and sc.c#=’c2’

(3) 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位?

答：select sn,sd from s where s# not in(select s# from sc where c#=’c5’)

(4)查询选修了课程的学员人数

答：select 学员人数=count(distinct s#) from sc

(5) 查询选修课程超过5门的学员学号和所属单位?

答：select sn,sd from s where s# in(select s# from sc group by s# having count(distinct c#)>5)

(6)使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位

Select SN,SD FROM S Where [S#] IN( Select [S#] FROM SC RIGHT JOIN C ON SC.[C#]=C.[C#] GROUP BY [S#] HAVING (SELECT COUNT(1) FROM dbo.C)=COUNT([S#]))

4.问题描述:

S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名

C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

(1) 找出没有选修过“李明”老师讲授课程的所有学生姓名

--实现代码:

Select SNAME FROM S Where NOT EXISTS( Select * FROM SC,C Where SC.CNO=C.CNO AND CTEACHER='李明' AND SC.SNO=S.SNO)

5.两张关联表，删除主表中已经在副表中没有的信息

delete from info where not exists(select 1 from infobz where info.infIntNo=infobz.infIntNo)

6.说明：日程安排提前五分钟提醒
SQL: select * from 日程安排 where datediff('mi',f 开始时间,getdate())>5

7.有两个表A 和B ，均有key 和value 两个字段，如果B 的key 在A 中也有，就把B 的value 换为A 中对应的value
这道题的SQL 语句怎么写？

CREATE TABLE [Item1] ( [a] INT,[b] int )

CREATE TABLE [Item2] ( [a] INT,[b] int )

INSERT INTO [Item1] VALUES(1,1) INSERT INTO [Item1] VALUES(2,1) INSERT INTO [Item1] VALUES(3,1) INSERT INTO [Item1] VALUES(4,1)

INSERT INTO [Item2] VALUES(1,2) INSERT INTO [Item2] VALUES(2,2) INSERT INTO [Item2] VALUES(3,2) INSERT INTO [Item2] VALUES(4,2)

update [Item2] SET b=t1.b

from [Item2] t2

inner join [Item1] t1 on t2.a=t1.a

8. CAST("字段名" as varchar) 类型强制转换

9.更新数据

     UPDATE dbo.Notice WITH (ROWLOCK)
   SET NoticeStatus = @Status
    FROM dbo.Notice n
     JOIN dbo.Charge c WITH (NOLOCK) ON c.NotIntNo = n.NotIntNo
     WHERE c.ChgIntNo = @ChgIntNo