1.
--是查询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)
2.
要求是查询表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]
GO
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.
为管理业务培训信息，建立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