# SQL Server全程

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

1、查询“001”课程比“002”课程成绩高的所有学生的学号；

select a.S# from (select s#,score from SC where C#='001') a,(select s#,score

from SC where C#='002') b

where a.score>b.score and a.s#=b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩；

select S#,avg(score)

from sc

group by S# having avg(score) >60;
3、查询所有同学的学号、姓名、选课数、总成绩；

select Student.S#,Student.Sname,count(SC.C#),sum(score)

from Student left Outer join SC on Student.S#=SC.S#

group by Student.S#,Sname
4、查询姓“李”的老师的个数；

select count(distinct(Tname))

from Teacher

where Tname like '李%';
5、查询没学过“叶平”老师课的同学的学号、姓名；

select Student.S#,Student.Sname

from Student

where S# not in (select distinct( SC.S#) from SC,Course,Teacher where  SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平');
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；

select Student.S#,Student.Sname from Student,SC where Student.S#=SC.S# and SC.C#='001'and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002');
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名；

select S#,Sname

from Student

where S# in (select S# from SC ,Course ,Teacher where SC.C#=Course.C# and Teacher.T#=Course.T# and Teacher.Tname='叶平' group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='叶平'));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；

Select S#,Sname from (select Student.S#,Student.Sname,score ,(select score from SC SC_2 where SC_2.S#=Student.S# and SC_2.C#='002') score2

from Student,SC where Student.S#=SC.S# and C#='001') S_2 where score2 <score;
9、查询所有课程成绩小于60分的同学的学号、姓名；

select S#,Sname

from Student

where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>60);
10、查询没有学全所有课的同学的学号、姓名；

select Student.S#,Student.Sname

from Student,SC

where Student.S#=SC.S# group by  Student.S#,Student.Sname having count(C#) <(select count(C#) from Course);
11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；

select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；

select distinct SC.S#,Sname

from Student,SC

where Student.S#=SC.S# and C# in (select C# from SC where S#='001');
13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；

update SC set score=(select avg(SC_2.score)

from SC SC_2

where SC_2.C#=SC.C# ) from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平');
14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；

select S# from SC where C# in (select C# from SC where S#='1002')

group by S# having count(*)=(select count(*) from SC where S#='1002');
15、删除学习“叶平”老师课的SC表记录；
Delect SC

from course ,Teacher

where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname='叶平';
16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2
号课的平均成绩；

Insert SC select S#,'002',(Select avg(score)

from SC where C#='002') from Student where S# not in (Select S# from SC where C#='002');
17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分

SELECT S# as 学生ID
,(
SELECT score FROM SC WHERE SC.S#=t.S# AND C#='004') AS 数据库
,(
SELECT score FROM SC WHERE SC.S#=t.S# AND C#='001') AS 企业管理
,(
SELECT score FROM SC WHERE SC.S#=t.S# AND C#='006') AS 英语
,
COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩

FROM SC AS t

GROUP BY S#

ORDER BY avg(t.score)
18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分

SELECT L.C# As 课程ID,L.score AS 最高分,R.score AS 最低分

FROM SC L ,SC AS R

WHERE L.C# = R.C# and
L.score
= (SELECT MAX(IL.score)

FROM SC AS IL,Student AS IM

WHERE L.C# = IL.C# and IM.S#=IL.S#

GROUP BY IL.C#)

AND
R.Score
= (SELECT MIN(IR.score)

FROM SC AS IR

WHERE R.C# = IR.C#

GROUP BY IR.C#
);
19、按各科平均成绩从低到高和及格率的百分数从高到低顺序

SELECT t.C# AS 课程号,max(course.Cname)AS 课程名,isnull(AVG(score),0) AS 平均成绩
,
100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格百分数

FROM SC T,Course

where t.C#=course.C#

GROUP BY t.C#

ORDER BY 100 * SUM(CASE WHEN  isnull(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(*) DESC
20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004

SELECT SUM(CASE WHEN C# ='001' THEN score ELSE 0 END)/SUM(CASE C# WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分
,
100 * SUM(CASE WHEN C# = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数
,
SUM(CASE WHEN C# = '002' THEN score ELSE 0 END)/SUM(CASE C# WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分
,
100 * SUM(CASE WHEN C# = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数
,
SUM(CASE WHEN C# = '003' THEN score ELSE 0 END)/SUM(CASE C# WHEN '003' THEN 1 ELSE 0 END) AS UML平均分
,
100 * SUM(CASE WHEN C# = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '003' THEN 1 ELSE 0 END) AS UML及格百分数
,
SUM(CASE WHEN C# = '004' THEN score ELSE 0 END)/SUM(CASE C# WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分
,
100 * SUM(CASE WHEN C# = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN C# = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数

FROM SC
21、查询不同老师所教不同课程平均分从高到低显示

SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ＩＤ,MAX(C.Cname) AS 课程名称,AVG(Score) AS 平均成绩

FROM SC AS T,Course AS C ,Teacher AS Z

where T.C#=C.C# and C.T#=Z.T#

GROUP BY C.C#

ORDER BY AVG(Score) DESC
22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004

[学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩

SELECT  DISTINCT top 3
SC.S#
As 学生学号,
Student.Sname
AS 学生姓名 ,
T1.score
AS 企业管理,
T2.score
AS 马克思,
T3.score
AS UML,
T4.score
AS 数据库,

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分

FROM Student,SC  LEFT JOIN SC AS T1

ON SC.S# = T1.S# AND T1.C# = '001'

LEFT JOIN SC AS T2

ON SC.S# = T2.S# AND T2.C# = '002'

LEFT JOIN SC AS T3

ON SC.S# = T3.S# AND T3.C# = '003'

LEFT JOIN SC AS T4

ON SC.S# = T4.S# AND T4.C# = '004'

WHERE student.S#=SC.S# and

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)

NOT IN
(
SELECT

DISTINCT

TOP 15 WITH TIES

ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)

FROM sc

LEFT JOIN sc AS T1

ON sc.S# = T1.S# AND T1.C# = 'k1'

LEFT JOIN sc AS T2

ON sc.S# = T2.S# AND T2.C# = 'k2'

LEFT JOIN sc AS T3

ON sc.S# = T3.S# AND T3.C# = 'k3'

LEFT JOIN sc AS T4

ON sc.S# = T4.S# AND T4.C# = 'k4'

ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);

23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

SELECT SC.C# as 课程ID, Cname as 课程名称
,
SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85]
,
SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70]
,
SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60]
,
SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -]

FROM SC,Course

where SC.C#=Course.C#

GROUP BY SC.C#,Cname;

24、查询学生平均成绩及其名次

SELECT 1+(SELECT COUNT( distinct 平均成绩)

FROM (SELECT S#,AVG(score) AS 平均成绩

FROM SC

GROUP BY S#
)
AS T1

WHERE 平均成绩 > T2.平均成绩) as 名次,
S#
as 学生学号,平均成绩

FROM (SELECT S#,AVG(score) 平均成绩

FROM SC

GROUP BY S#
)
AS T2

ORDER BY 平均成绩 desc;

25、查询各科成绩前三名的记录:(不考虑成绩并列情况)

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

FROM SC t1

WHERE score IN (SELECT TOP 3 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC
)

ORDER BY t1.C#;
26、查询每门课程被选修的学生数

select c#,count(S#) from sc group by C#;
27、查询出只选修了一门课程的全部学生的学号和姓名

select SC.S#,Student.Sname,count(C#) AS 选课数

from SC ,Student

where SC.S#=Student.S# group by SC.S# ,Student.Sname having count(C#)=1;
28、查询男生、女生人数

Select count(Ssex) as 男生人数 from Student group by Ssex having Ssex='';

Select count(Ssex) as 女生人数 from Student group by Ssex having Ssex=''
29、查询姓“张”的学生名单

SELECT Sname FROM Student WHERE Sname like '张%';
30、查询同名同性学生名单，并统计同名人数

select Sname,count(*) from Student group by Sname having  count(*)>1;;
31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)

select Sname,  CONVERT(char (11),DATEPART(year,Sage)) as age

from student

where  CONVERT(char(11),DATEPART(year,Sage))='1981';
32、查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

Select C#,Avg(score) from SC group by C# order by Avg(score),C# DESC ;
33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩

select Sname,SC.S# ,avg(score)

from Student,SC

where Student.S#=SC.S# group by SC.S#,Sname having    avg(score)>85;
34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数

Select Sname,isnull(score,0)

from Student,SC,Course

where SC.S#=Student.S# and SC.C#=Course.C# and  Course.Cname='数据库'and score <60;
35、查询所有学生的选课情况；

SELECT SC.S#,SC.C#,Sname,Cname

FROM SC,Student,Course

where SC.S#=Student.S# and SC.C#=Course.C# ;
36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；

SELECT  distinct student.S#,student.Sname,SC.C#,SC.score

FROM student,Sc

WHERE SC.score>=70 AND SC.S#=student.S#;
37、查询不及格的课程，并按课程号从大到小排列

select c# from sc where scor e <60 order by C# ;
38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名；

select SC.S#,Student.Sname from SC,Student where SC.S#=Student.S# and Score>80 and C#='003';
39、求选了课程的学生人数

select count(*) from sc;
40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩

select Student.Sname,score

from Student,SC,Course C,Teacher

where Student.S#=SC.S# and SC.C#=C.C# and C.T#=Teacher.T# and Teacher.Tname='叶平' and SC.score=(select max(score)from SC where C#=C.C# );
41、查询各个课程及相应的选修人数

select count(*) from sc group by C#;
42、查询不同课程成绩相同的学生的学号、课程号、学生成绩

select distinct  A.S#,B.score from SC A  ,SC B where A.Score=B.Score and A.C# <>B.C# ;
43、查询每门功成绩最好的前两名

SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数

FROM SC t1

WHERE score IN (SELECT TOP 2 score

FROM SC

WHERE t1.C#= C#

ORDER BY score DESC
)

ORDER BY t1.C#;
44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列

select  C# as 课程号,count(*) as 人数

from  sc

group  by  C#

order  by  count(*) desc,c#
45、检索至少选修两门课程的学生学号

select  S#

from  sc

group  by  s#

having  count(*>  =  2
46、查询全部学生都选修的课程的课程号和课程名

select  C#,Cname

from  Course

where  C#  in  (select  c#  from  sc group  by  c#)
47、查询没学过“叶平”老师讲授的任一门课程的学生姓名

select Sname from Student where S# not in (select S# from Course,Teacher,SC where Course.T#=Teacher.T# and SC.C#=course.C# and Tname='叶平');
48、查询两门以上不及格课程的同学的学号及其平均成绩

select S#,avg(isnull(score,0)) from SC where S# in (select S# from SC where score <60 group by S# having count(*)>2)group by S#;
49、检索“004”课程分数小于60，按分数降序排列的同学学号

select S# from SC where C#='004'and score <60 order by score desc;
50、删除“002”同学的“001”课程的成绩
delete from Sc where S#='001'and C#='001';

*****************************************************************************************************************

*****************************************************************************************************************

CARD     借书卡。   CNO 卡号，NAME  姓名，CLASS 班级
BOOKS    图书。     BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW   借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期

．对CARD表做如下修改：
a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。
BORROW(
CARD(CNO),
BOOKS(BNO),

1． 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。

2． 找出借书超过5本的读者,输出借书卡号及所借图书册数。

3． 查询借阅了"水浒"一书的读者，输出姓名及班级。

4． 查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

5． 查询书名包括"网络"关键词的图书，输出书号、书名、作者。

6． 查询现有图书中价格最高的图书，输出书名及作者。

7． 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。

8． 将"C01"班同学所借图书的还期都延长一周。

9． 从BOOKS表中删除当前无人借阅的图书记录。

10．如果经常按书名查询图书信息，请建立合适的索引。

11．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。

12．建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）。

13．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。

14．假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。

15    b. 为该表增加1列NAME（系名），可变长，最大20个字符。

------------>
1. 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束
--实现代码：CREATETABLE    CNO intFOREIGNKEYREFERENCES    BNO intFOREIGNKEYREFERENCES    RDATE datetime,

PRIMARYKEY(CNO,BNO))

2. 找出借书超过5本的读者,输出借书卡号及所借图书册数
--实现代码：SELECT CNO,借图书册数=COUNT(*)
FROM BORROW
GROUPBY CNO
HAVINGCOUNT(*)>53. 查询借阅了"水浒"一书的读者，输出姓名及班级
--实现代码：SELECT*FROM CARD c
WHEREEXISTS(

SELECT*FROM BORROW a,BOOKS b

WHERE a.BNO=b.BNO

AND b.BNAME=N'水浒'AND a.CNO=c.CNO)

4. 查询过期未还图书，输出借阅者（卡号）、书号及还书日期
--实现代码：SELECT*FROM BORROW
WHERE RDATE<GETDATE()

5. 查询书名包括"网络"关键词的图书，输出书号、书名、作者
--实现代码：SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE BNAME LIKE N'%网络%'6. 查询现有图书中价格最高的图书，输出书名及作者
--实现代码：SELECT BNO,BNAME,AUTHOR FROM BOOKS
WHERE PRICE=(

SELECTMAX(PRICE) FROM BOOKS)

7. 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出
--实现代码：SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO AND b.BNAME=N'计算方法'ANDNOTEXISTS(

SELECT*FROM BORROW aa,BOOKS bb

WHERE aa.BNO=bb.BNO

AND bb.BNAME=N'计算方法习题集'AND aa.CNO=a.CNO)
ORDERBY a.CNO DESC8. 将"C01"班同学所借图书的还期都延长一周
FROM CARD a,BORROW b
WHERE a.CNO=b.CNO

AND a.CLASS=N'C01'9. 从BOOKS表中删除当前无人借阅的图书记录
--实现代码：DELETE A FROM BOOKS a
WHERENOTEXISTS(

SELECT*FROM BORROW

WHERE BNO=a.BNO)

10. 如果经常按书名查询图书信息，请建立合适的索引
--实现代码：CREATECLUSTEREDINDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)

11. 在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）
--实现代码：CREATETRIGGER TR_SAVE ON BORROW
FORINSERT,UPDATEASIF@@ROWCOUNT>0INSERT BORROW_SAVE SELECT i.*FROM INSERTED i,BOOKS b
WHERE i.BNO=b.BNO

AND b.BNAME=N'数据库技术及应用'12. 建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）
--实现代码：CREATEVIEW V_VIEW
ASSELECT a.NAME,b.BNAME
FROM BORROW ab,CARD a,BOOKS b
WHERE ab.CNO=a.CNO

AND ab.BNO=b.BNO

AND a.CLASS=N'力01'13. 查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出
--实现代码：SELECT a.CNO
FROM BORROW a,BOOKS b
WHERE a.BNO=b.BNO

AND b.BNAME IN(N'计算方法',N'组合数学')
GROUPBY a.CNO
HAVINGCOUNT(*)=2ORDERBY a.CNO DESC14. 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句

15.1 将NAME最大列宽增加到10个字符（假定原为6个字符）
--实现代码：ALTERTABLE CARD ALTERCOLUMN NAME varchar(10)

15.2 为该表增加1列NAME（系名），可变长，最大20个字符

*****************************************************************************************************************
*****************************************************************************************************************

S (S#,SN,SD,SA)   S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN )        C#,CN       分别代表课程编号、课程名称
SC ( S#,C#,G )    S#,C#,G     分别代表学号、所选修的课程编号、学习成绩

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

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

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

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

5. 查询选修了课程的学员人数

6. 查询选修课程超过5门的学员学号和所属单位

---------->

1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
--实现代码：SELECT SN,SD FROM S
WHERE[S#]IN(

SELECT[S#]FROM C,SC

WHERE C.[C#]=SC.[C#]AND CN=N'税收基础')

2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
--实现代码：SELECT S.SN,S.SD FROM S,SC
WHERE S.[S#]=SC.[S#]AND SC.[C#]='C2'3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
--实现代码：SELECT SN,SD FROM S
WHERE[S#]NOTIN(

SELECT[S#]FROM SC

WHERE[C#]='C5')

4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
--实现代码：SELECT SN,SD FROM S
WHERE[S#]IN(

SELECT[S#]FROM SC

RIGHTJOIN C ON SC.[C#]=C.[C#]GROUPBY[S#]HAVINGCOUNT(*)=COUNT(DISTINCT[S#]))

5. 查询选修了课程的学员人数
--实现代码：SELECT 学员人数=COUNT(DISTINCT[S#]) FROM SC

6. 查询选修课程超过5门的学员学号和所属单位
--实现代码：SELECT SN,SD FROM S
WHERE[S#]IN(

SELECT[S#]FROM SC

GROUPBY[S#]HAVINGCOUNT(DISTINCT[C#])>5)

if not object_id('cj')is null

drop table cj
go
create table cj(stuName nvarchar(10),KCM nvarchar(10),cj numeric(5,2))
insert into cj select '张三','语文',98
union select '李四','语文',89
union select '王五','语文',67
union select '周攻','语文',56
union select '张三','数学',89
union select '李四','数学',78
union select '王五','数学',90
union select '周攻','数学',87

select stuname from
(select stuName,kcm,(select count(*) from cj where stuname!=a.stuname and kcm=a.kcm and cj>a.cj) cnt from cj a) x

group by stuname having max(cnt)<=1
go

SELECT stuname FROM cj1 a
where cj IN(SELECT TOP 2 cj FROM cj1 WHERE kcm=a.kcm ORDER BY cj desc)
GROUP BY stuname HAVING(count(1)>1)

select distinct stuname from cj a

where not exists(select kcm from cj b where a.stuname=stuname

and (select count(*) from cj where kcm=b.kcm and stuname!=a.stuname and cj>b.cj)>1)

*****************************************************************************************************************
*****************************************************************************************************************

SQLSEREVER测试题(上)

1 请讲出身份验证模式与登录帐号的关系及如何用各种帐号进行登录，并画出示意图

2 请讲出登录帐号、数据库用户及数据库角色之间的关系，并画出示意图

3 请讲出数据库用户、数据库角色与数据库对象之间的关系，并画出直接对用户授权与间接对用户授权（系统权限与对象权限）的方法

4 请讲出服务器角色、数据库角色、标准角色与应用程序角色的区别与验证其权限的方法

5 请讲出数据库还原模型对数据库的影响

6 有一个执行关键任务的数据库，请设计一个数据库备份策略

7 请使用文件与文件组恢复的方式恢复数据库

8 请使用事务日志恢复数据库到一个时间点

9 请设计作业进行周期性的备份数据库

10 如何监控数据库的阻塞，并实现数据库的死锁测试

11 如何监控数据库的活动，并能使用索引优化向导生成索引

12 理解数据库框图的作用并可以设计表与表之间的关系

SQLSEREVER测试题(中)

1 有订单表，需要实现它的编号，格式如下：200211030001……200222039999等
2 有表T1,T2，现有一事务，在向表T1添加数据时，同时也必须向T2也添加数据，如何实现该事务
3 如何向T1中的编号字段（code varchar(20)）添加一万条记录，不充许重复，规则如下：编号的数据必须从小写的a-z之间取值
4 如何删除表中的重复数据,请使用游标与分组的办法
5 如何求表中相邻的两条记录的某字段的值之差
6 如何统计数据库中所有用户表的数据，显示格式如下：

sales      23
7 如何删除数据库中的所有用户表（表与表之间有外键关系）
8 表A　editor_id　　　　　　　lb2_id
123　　　　　　　　　　 000
123　　　　　　　　　　 003
123　　　　　　　　　　 003
456　　　　　　　　　　 007
456　　　　　　　　　　 006

000　　　　　　　　　　 a
003　　　　　　　　　　 b
006　　　　　　　　　　 c
007　　　　　　　　　　 d

b　　 共2条（表A内lb2_id为003的条数）
9 人员情况表（employee）:里面有一字段文化程度（wh）：包括四种情况（本科以上，大专，高中，初中以下）,现在我要根据年龄字段查询统计出：表中文化程度为本科以上，大专，高中，初中以下，各有多少人，占总人数多少。
SELECT wh AS 学历,age as 年龄, Count(*) AS 人数,
Count(*) * 100 /(SELECT Count(*) FROM employee) AS 百分比
FROM employee GROUP BY wh,age

10 现在有三个表student:（FID  学生号，FName  姓名），
subject:（FSubID  课程号，FSubName 课程名）,
Score(FScoreId  成绩记录号,FSubID    课程号,FStdID    学生号,FScore    成绩)

SELECT a.FName AS 姓名,
英语 = SUM(CASE b.FSubName WHEN '英语' THEN c.FScore END),
数学 = SUM(CASE b.FSubName WHEN '数学' THEN c.FScore END),
语文 = SUM(CASE b.FSubName WHEN '语文' THEN c.FScore END),
历史 = SUM(CASE b.FSubName WHEN '历史' THEN c.FScore END)
FROM Student a, Subject b, Score c
WHERE a.FID = c.FStdId AND b.FSubID = c.FsubID GROUP BY a.FName

11 原始表的数据如下：

PID PTime    PNo

111111    2003-01-28 04:30:09

111111    2003-01-28 18:30:00

222222    2003-01-28 04:31:09

333333    2003-01-28 04:32:09

111111    2003-02-09 03:35:25

222222    2003-02-09 03:36:25

333333    2003-02-09 03:37:25

PDate        111111        222222    333333      ......

2003-01-28    04:30:09    04:31:09      04:32:09    ......

2003-01-28    18:30:00

2003-02-09    03:35:25    03:36:25      03:37:25    ......

12  表一(AAA)

A        100

B        120

A        10

A        20

B        10

B        20

B        30

declare @AAA table (商品名称  varchar(10), 商品总量  int)

insert into @AAA values('A',100)

insert into @AAA values('B',120)

declare @BBB table (商品名称 varchar(10), 出库数量 int)

insert into @BBB values('A', 10)

insert into @BBB values('A', 20)

insert into @BBB values('B', 10)

insert into @BBB values('B', 20)

insert into @BBB values('B', 30)

select TA.商品名称,A-B AS 剩余数量 FROM

(select 商品名称,sum(商品总量) AS A

from @AAA

group by 商品名称)TA,

(select 商品名称,sum(出库数量) AS B

from @BBB

group by 商品名称)TB

where TA.商品名称=TB.商品名称

select 商品名称,sum(商品总量) 剩余数量 from (select * from @aaa union all select 商品名称,-出库数量 from @bbb) a group by 商品名称

13 优化这句SQL语句

UPDATE tblExlTempYear

SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC

FROM tblExlTempYear,tblExlTempMonth

where tblExlTempMonth.GDXM=tblExlTempYear.GDXM and tblExlTempMonth.TXDZ=tblExlTempYear.TXDZ

（1）、加索引：

tblExlTempYear（GDXM，TXDZ）

tblExlTempMonth （GDXM，TXDZ）

（2）、删除无用数据

（3）、转移过时数据

（4）、加服务器内存，升级服务器

（5）、升级网络系统

UPDATE tblExlTempYear

SET tblExlTempYear.GDQC = tblExlTempMonth.GDQC

FROM tblExlTempYear (index indexY),tblExlTempMonth (index indexM)

where tblExlTempMonth.GDXM=tblExlTempYear.GDXM and tblExlTempMonth.TXDZ=tblExlTempYear.TXDZ

14 品种    日期    数量

P0001  2002-1-10  10

P0001  2002-1-10  11

P0001  2002-1-10  50

P0001  2002-1-12  9

P0001  2002-1-12  8

P0001  2002-1-12  7

P0002  2002-10-10  5

P0002  2002-10-10  7

P0002  2002-10-12  0.5

P0003  2002-10-10  5

P0003  2002-10-12  7

P0003  2002-10-12  9

P0001  2002-1-10    71

P0001    2002-1-12  24

P0002    2002-10-10  12

P0002    2002-10-12  0.5

P0003    2002-10-10  5

P0003    2002-10-12  16

SQL SERVER能做出这样的汇总吗…

15 在分組查循中with{cube|rollup}的區別是什么?

use pangu

select firm_id,p_id,sum(o_price_quantity)as sum_values

from orders

group by firm_id,p_id

with cube

與

use pangu

select firm_id,p_id,sum(o_price_quantity)as sum_values

from orders

group by firm_id,p_id

with rollup

的區別是什么?

CUBE 和 ROLLUP 之间的区别在于：

CUBE 生成的结果集显示了所选列中值的所有组合的聚合。

ROLLUP 生成的结果集显示了所选列中值的某一层次结构的聚合。

Item                Color                Quantity

-------------------- -------------------- --------------------------

Table                Blue                124

Table                Red                  223

Chair                Blue                101

Chair                Red                  210

SELECT CASE WHEN (GROUPING(Item) = 1) THEN 'ALL'

ELSE ISNULL(Item, 'UNKNOWN')

END AS Item,

CASE WHEN (GROUPING(Color) = 1) THEN 'ALL'

ELSE ISNULL(Color, 'UNKNOWN')

END AS Color,

SUM(Quantity) AS QtySum

FROM Inventory

GROUP BY Item, Color WITH ROLLUP

Item                Color                QtySum

-------------------- -------------------- --------------------------

Chair                Blue                101.00

Chair                Red                  210.00

Chair                ALL                  311.00

Table                Blue                124.00

Table                Red                  223.00

Table                ALL                  347.00

ALL                  ALL                  658.00

(7 row(s) affected)

ALL                  Blue                225.00

ALL                  Red                  433.00

CUBE 操作为 Item 和 Color 中值的可能组合生成行。例如，CUBE 不仅报告与 Item 值 Chair 相组合的 Color 值的所有可能组合（Red、Blue 和 Red + Blue），而且报告与 Color 值 Red 相组合的 Item 值的所有可能组合（Chair、Table 和 Chair + Table）。对于 GROUP BY 子句中右边的列中的每个值，ROLLUP 操作并不报告左边一列（或左边各列）中值的所有可能组合。例如，ROLLUP 并不对每个 Color 值报告 Item 值的所有可能组合。ROLLUP 操作的结果集具有类似于 COMPUTE BY 所返回结果集的功能；然而，

ROLLUP 具有下列优点： ROLLUP 返回单个结果集；COMPUTE BY 返回多个结果集，而多个结果集会增加应用程序代码的复杂性。ROLLUP 可以在服务器游标中使用；COMPUTE BY 不可以。有时，查询优化器为 ROLLUP 生成的执行计划比为 COMPUTE BY 生成的更为高效。

16 假如我有两个表

UPDATE 表1 SET 是否存在=1
WHERE EXISTS(SELECT * FROM　表2 WHERE 表2.电话号码 = 表1.电话号码)

17 用存储过程调用外部程序.

DECLARE @object int
DECLARE @hr int
DECLARE @src varchar(255), @desc varchar(255)
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END
1、在MS SQL Server中，用来显示数据库信息的系统存储过程是（ ）
A sp_ dbhelp
B sp_ db
C sp_ help
D sp_ helpdb

2、SQL语言中，删除一个表的命令是（ ）
A DELETE
B DROP
C CLEAR
D REMORE

3、关系数据库中，主键是（__）
A、为标识表中唯一的实体
B、创建唯一的索引，允许空值
C、只允许以表中第一字段建立
D、允许有多个主键的

4、在Transact-SQL语法中，SELECT语句的完整语法较复杂，但至少包括的部分（1___），使用关键字（2___）可以把重复行屏蔽，将多个查询结果返回一个结果集合的运算符是（3___），如果在SELECT语句中使用聚合函数时，一定在后面使用（4___）。
⑴ A、SELECT，INTO              B、SELECT，FROM
C、SELECT，GROUP            D、仅SELECT
⑵ A、DISTINCT                          B、UNION
C、ALL                                  C、TOP
⑶ A、JOIN                                B、UNION
C、INTO                              C、LIKE
⑷ A、GROUP BY                      B、COMPUTE BY
C、HAVING                          D、COMPUTE

5、语句DBCC SHRINKDATABASE (Sample, 25)中的25表示的意思是
A、25M
B、剩余占整个空间的25%
C、已用空间占整个空间的25%
D、以上都不对

6、你是一个保险公司的数据库开发人员，公司的保单信息存储在SQL Server 2000数据库中，你使用以下脚本建立了一个名为Policy的表：
CREATE TABLE Policy
(
PolicyNumber int NOT NULL DEFAULT (0),
InsuredLastName char (30) NOT NULL,
InsuredFirstName char (20) NOT NULL,
InsuredBirthDate datetime NOT NULL,
PolicyDate datetime NOT NULL,
FaceAmount money NOT NULL,
CONSTRAINT PK_Policy PRIMARY KEY (PolicyNumber)
)

c.建立一个AFTER UPDATE触发器来产生一个新的保单号，并将这个保单号插入数据表中。
d.用AFTER UPDATE触发器替代DEFAULT约束条件产生一个新的保单号，并将这个保单号插入数据表中。

７、在SQL语言中，如果要建立一个工资表包含职工号，姓名，职称。工资等字段。若要保证工资字段的取值不低于800元，最合适的实现方法是：
A。在创建工资表时为”工资“字段建立缺省
B。在创建工资表时为”工资“字段建立检查约束
C。在工资表建立一个触发器
D。为工资表数据输入编写一个程序进行控制

８、Select 语句中用来连接字符串的符号是______.
A. “+” B. “&” C.“||” D.“|”

９、你是一个出版公司的数据库开发人员，对特定的书名的每天的销售情况建立了如下的存储过程：
CREATE PROCEDURE get_sales_for_title
title varchar(80), @ytd_sales int OUTPUT
AS
SELECT @ytd_sales = ytd_sales
FROM titles
WHERE title = @title
IF @@ROWCOUNT = 0
RETURN(-1)
ELSE
RETURN(0)

Ａ. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ‘Net Etiquette’, @ytd
IF @retval < 0
PRINT ‘No sales found’
ELSE
PRINT ‘Year to date sales: ’ + STR (@ytd)
GO

Ｂ. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT
IF @retval < 0
PRINT ‘No sales found’
ELSE
PRINT ‘Year to date sales: ’ + STR (@ytd)
GO

Ｃ. DECLARE @retval int
DECLARE @ytd int
EXEC get_sales_for_title ‘Net Etiquette’,@retval OUTPUT
IF @retval < 0
PRINT ‘No sales found’
ELSE
PRINT ‘Year to date sales: ’ + STR (@ytd)
GO

Ｄ. DECLARE @retval int
DECLARE @ytd int
EXEC @retval = get_sales_for_title ‘Net Etiquette’, @ytd OUTPUT
IF @retval < 0
PRINT ‘No sales found’
ELSE
PRINT ‘Year to date sales: ’ + STR (@ytd)
GO

１０、You are a database developer for a container manufacturing company. The containers produced by your company are a number of different sizes and shapes. The tables that store the container information are shown in the Size, Container, and Shape Tables exhibit:
Size
SizeID
SizeName
Height
Container
ContainerID
ShapeID
SizeID
Shape
ShapeID
ShapeName
Measurements

A sample of the data stored in the tables is shown below:
Size Table
SizeID        SizeName        Height
1            Small          40
2            Medium          60
3            Large          80
4            Jumbo          100
Shape Table
ShapeID  ShapeName  Measurement
1        Triangle        10
2        Triangle        20
3        Triangle        30
4        Square          20
5        Square          30
6        Square          40
7        Circle          15
8        Circle          25
9        Circle          35
Periodically, the dimensions of the containers change. Frequently, the database users require the volume of a container. The volume of a container is calculated based on information in the shape and size tables.
You need to hide the details of the calculation so that the volume can be easily accessed in a SELECT query with the rest of the container information. What should you do?
A.    Create a user-defined function that requires ContainerID as an argument and returns the volume of the container.
B.    Create a stored procedure that requires ContainerID as an argument and returns the volume of the container.
C.    Add a column named volume to the container table. Create a trigger that calculates and stores volume in this column when a new container is inserted into the table.
D.    Add a computed column to the container table that calculates the volume of the container.

1、 如果设计的表不符合第二范式，可能会导致_______，________，_______。
2、 ＳＱＬ是由_______语言，________语言，_______语言组成。
3、 SQL Server在两个安全级上验证用户，分别是______________,_____________________。
4、 自定义函数由___________函数，_______________函数，___________________函数组成。
5、 备份策略的三种类型是__________备份，_______________备份，___________________备份组成。
6、 启动一个显式事务的语句为__________，提交事务的语句为__________，回滚事务的语句为__________
7、 表的每一行在表中是惟一的实体属于__________完整性，使列的输入有效属于__________完整性，两个表的主关键字和外关键字的数据应该对应一致属于__________完整性。

1、 在帮助中[ ,...n ] 意思是什么？（４分）
2、 请简述一下第二范式（４分）
3、 现有1销售表，它们结构如下：（４分）
id int                      (标识号)
codno char(7)              (商品编码)
codname varchar(30)        (商品名称)
spec varchar(20)                        （商品规格）
price numeric(10,2)            （价格）
sellnum int                          （销售数量）
deptno char(3)                    （售出分店编码）
selldate datetime                    （销售时间）

４、写一个存储过程，要求传入一个表名，返回该表的记录数（假设传入的表在数据库中都存在）（４分）
５、请简述UPDATE 触发器如何工作原理。（４分）

1、（５分）使用一条SQL语句找到重复的值及重复的次数：有一数据表ZD_ks，其中有字段BM，MC，。。。，请查询出在ZD_ks中BM有重复的值及重复的次数，没有的不要列出。如下表：
BM 　DUPCOUNT
001 　　3
002 　　2

2、描述(5分)

ID    int      学生编号
Name  varchar  学生姓名
Sex    bit      性别(男0女1)
Class int      班级编号

ID    int      课程编号
Name  varchar  课程名称

ID    int      自动编号
UID    int      学生编号
SID    int      课程编号
Num    int      考试成绩

(a)求各班的总人数（１分）
(b)求1班女生和男生的平均成绩（２分）
(c)各班"数据结构"(课程名称)不及格的人数（２分）

３、问题描述：(30分)

CARD    借书卡。  CNO 卡号，NAME 姓名，CLASS 班级
BOOKS    图书。    BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
BORROW  借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期

1．找出借书超过5本的读者,输出借书卡号及所借图书册数。(2分)
2．查询借阅了"水浒"一书的读者，输出姓名及班级。(3分)
3．查询过期未还图书，输出借阅者（卡号）、书号及还书日期。(3分)
4．查询书名包括"网络"关键词的图书，输出书号、书名、作者。(2分)
5．查询现有图书中价格最高的图书，输出书名及作者。(2分)
6．查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。(4分)
7．将"C01"班同学所借图书的还期都延长一周。(2分)
8．从BOOKS表中删除当前无人借阅的图书记录。(2分)
9．在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。(4分)
10．建立一个视图，显示"力01"班学生的借书信息（只要求显示姓名和书名）。(3分)
11．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。(3分)

1、问题描述：

S (SNO,SNAME）                      学生关系。SNO 为学号，SNAME 为姓名
C (CNO,CNAME,CTEACHER)  课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

1． 找出没有选修过“李明”老师讲授课程的所有学生姓名
2． 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
3． 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
4． 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号
5． 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩

2、问题描述：

S (S#,SN,SD,SA)  S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN )        C#,CN      分别代表课程编号、课程名称
SC ( S#,C#,G )    S#,C#,G    分别代表学号、所选修的课程编号、学习成绩

1. 使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
2. 使用标准SQL嵌套语句查询选修课程编号为’C2’的学员姓名和所属单位
3. 使用标准SQL嵌套语句查询不选修课程编号为’C5’的学员姓名和所属单位
4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位
5. 查询选修了课程的学员人数
6. 查询选修课程超过5门的学员学号和所属单位

Question2：Can you use a SQL statement to calculating it!
How can I print "10 to 20" for books that sell for between $10 and$20，"unknown" for books whose price is null, and "other" for all other prices?
select bookid,bookname,price=case when price is null then 　　'unknown'
when  price between 10 and 20 then '10 to 20' else price end
from books
Question3：Can you use a SQL statement to finding duplicate 　　values!
How can I find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result as below:
Output:
au_lname                                number_dups
---------------------------------------- -----------
Ringer                                  2
(1 row(s) affected)
select au_lname,number_dups=count(1) from authors group by au_lname
Question4：Can you create a cross-tab report in my SQL Server!
How can I get the report about sale quality for each store and each quarter and the total sale quality for each quarter at year 1993?
You can use the table sales and stores in datatabase pubs.
Table Sales record all sale detail item for each store. Column store_id is the id of each store, ord_date is the order date of each sale item, and column qty is the sale qulity. Table stores record all store information.
I want to get the result look like as below:
Output:
stor_name                                Total      Qtr1        Qtr2        Qtr3        Qtr4
---------------------------------------- ----------- ----------- ----------- ----------- -----------
Barnum's                                50          0          50          0          0
Bookbeat                                55          25          30          0          0
Doc-U-Mat: Quality Laundry and Books    85          0          85          0          0
Fricative Bookshop                      60          35          0          0          25
Total                                    250        60          165        0          25

Question5: The Fastest Way to Recompile All Stored Procedures
I have a problem with a database running in SQL Server 6.5 (Service Pack 4). We moved the database (object transfer) from one machine to another last night, and an error (specific to a stored procedure) is cropping up. However, I can't tell which procedure is causing it. Permissions are granted in all of our stored procedures; is there a way from the isql utility to force all stored procedures to recompile?
Tips: sp_recompile can recomplie a store procedure each time
Question6: How can I add row numbers to my result set?
In database pubs, have a table titles , now I want the result shown as below,each row have a row number, how can you do that?
Result:
line-no    title_id
----------- --------
1          BU1032
2          BU1111
3          BU2075
4          BU7832
5          MC2222
6          MC3021
7          MC3026
8          PC1035
9          PC8888
10          PC9999
11          PS1372
12          PS2091
13          PS2106
14          PS3333
15          PS7777
16          TC3218
17          TC4203
18          TC7777
--SQL 2005的写法
select row_number() as line_no ,title_id from titles
--SQL 2000的写法
select line_no identity(int,1,1),title_id into #t from titles
select * from #t
drop table #t
Question 7: Can you tell me what the difference of two SQL statements at performance of execution?
Statement 1:
if NOT EXISTS ( select * from publishers where state = 'NY')
begin

SELECT 'Sales force needs to penetrate New York market'end
else
begin
SELECT 'We have publishers in New York'
end
Statement 2:
if EXISTS ( select * from publishers where state = 'NY')
begin
SELECT 'We have publishers in New York'
end
else
begin
SELECT 'Sales force needs to penetrate New York market'
end
Question8: How can I list all California authors regardless of whether they have written a book?
In database pubs, have a table authors and titleauthor , table authors has a column state, and titleauhtor have books each author written.
CA behalf of california in table authors.
select * from  authors where state='CA'
Question9: How can I get a list of the stores that have bought both 'bussiness' and 'mod_cook' type books?
In database pubs, use three table stores,sales and titles to implement this requestment.
Now I want to get the result as below:
stor_id stor_name
------- ----------------------------------------
...
7896    Fricative Bookshop
...
...
...
select distinct a.stor_id, a.stor_name from stores a,sales b,titles c
where a.stor_id=b.stor_id and b.title_id=c.title_id and c.type='business' and
exists(select 1 from sales k,titles g where stor_id=b.stor_id
and k.title_id=g.title_id and g.type='mod_cook')
Question10: How can I list non-contignous data?
In database pubs, I create a table test using statement as below, and I insert several row as below
create table test
( id int primary key )
go
insert into test values (1 )
insert into test values (2 )
insert into test values (3 )
insert into test values (4 )
insert into test values (5 )
insert into test values (6 )
insert into test values (8 )
insert into test values (9 )
insert into test values (11)
insert into test values (12)
insert into test values (13)
insert into test values (14)
insert into test values (18)
insert into test values (19)
go
Now I want to list the result of the non-contignous row as below,how can I do it?
Missing after Missing before
------------- --------------
6            8
9            11
...

select id from test t where not exists(select 1 from test where id=t.id+1)
or not exists(select 1 from test where id=t.id-1)
Question11: How can I list all book with prices greather than the average price of books of the same type?
In database pubs, have a table named titles , its column named price mean the price of the book, and another named type mean the type of books.
Now I want to get the result as below:
type        title                                                                            price
------------ -------------------------------------------------------------------------------- ---------------------
business    The Busy Executive's Database Guide                                              19.9900
...
...
...
...
select a.type,a.title,a.price from titles a,
(select type,price=avg(price) from titles group by type)b
where a.type=b.type and a.price>b.price

create table #tmp(id char(1),name char(1))
create table #tmp1(id varchar(10))
go
insert #tmp(id,name) values(0,'a')
insert #tmp(id,name) values(1,'b')
insert #tmp(id,name) values(2,'c')
insert #tmp(id,name) values(3,'d')
insert #tmp(id,name) values(4,'e')
insert #tmp(id,name) values(5,'f')
insert #tmp(id,name) values(6,'g')
insert #tmp(id,name) values(7,'h')
insert #tmp(id,name) values(8,'i')
insert #tmp(id,name) values(9,'j')
go
declare @t varchar(10)
set @t=10000
while @t <=20000
begin
insert #tmp1 values(@t)
set @t=@t+1
end
go
insert t1(code)
select (select name from #tmp where id=left(a.id,1))+
(select name from #tmp where id=substring(a.id,2,1))+
(select name from #tmp where id=substring(a.id,3,1))+
(select name from #tmp where id=substring(a.id,4,1))+
(select name from #tmp where id=right(a.id,1))
from #tmp1 a
go
drop table #tmp
drop table #tmp1
go

declare @i int,@j int,@r int,@acount int
create table #tmp(id int null, code varchar(3) null)
declare @ichar char(1)
declare @jchar char(1)
declare @rchar char(1)
select @i=0
select @j=0
select @r=0
select @acount=0

select @ichar=''
while @i <26
begin
select @ichar=char(97+@i)
select @j=0
while @j <26
begin
select @jchar=char(97+@j)
select @r=0
while @r <26
begin
select @rchar=char(97+@r)
select @acount=@acount+1
if @acount>10000
break
insert into #tmp(id,code)
select @acount,@ichar+@jchar+@rchar
select @r=@r+1
end
if @acount>10000
break
select @j=@j+1
end
if @acount>10000
break
select @i=@i+1
end
insert t1(code)
select code from #tmp order by id
go
drop table #tmp面试题：怎么把这样一个表儿
year  month amount
1991  1    1.1
1991  2    1.2
1991  3    1.3
1991  4    1.4
1992  1    2.1
1992  2    2.2
1992  3    2.3
1992  4    2.4

year m1  m2  m3  m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

select year, t1.amount, t2.amount, t3.amount, t4.amount from tableA a
join (select amount, year from teableA where year=a.year and month=1) t1
on (a.year=t1.year)
join (select amount, year from teableA where year=a.year and month=2) t2
on (a.year=t2.year)
join (select amount, year from teableA where year=a.year and month=3) t3
on (a.year=t3.year)
join (select amount, year from teableA where year=a.year and month=4) t4
on (a.year=t4.year)
a group by year;

select year,
(select amount from  aaa m where month=1  and m.year=aaa.year) as m1,
(select amount from  aaa m where month=2  and m.year=aaa.year) as m2,
(select amount from  aaa m where month=3  and m.year=aaa.year) as m3,
(select amount from  aaa m where month=4  and m.year=aaa.year) as m4
from aaa  group by year

0

SELECT *
FROM page where url like '%baidu%' or title like '%baidu%' or like ''
ORDER BY CHARINDEX('baidu', url) DESC, CHARINDEX('baidu', title) DESC,
CHARINDEX('baidu', body) DESC

select a.[id],a.mark from
(
select [page].[id],100 as mark from [page] where [page].[url] like '%baidu%'
union
select [page].[id],50 as mark from [page] where [page].[title] like '%baidu%'
union
select [page].[id],10 as mark from [page] where [page].[body] like '%baidu%'
) as a order by mark desc

********************

1.磁盘柜上有14块73G的磁盘， 数据库为200G 大小包括日志文件，如何设置磁盘（要说明这14磁盘是怎么用的）？

1）、如果偏重于性能考虑，而且不用存放数据库备份文件的话，考虑使用raid0+1，这样可使用的磁盘容量为：14*73*50%=511G。
2）、如果读/写性能要求不高，而且还比较抠门的话，可以考虑raid5，这样可使用的磁盘容量为：13*73=949G。

2.有两服务器群集，分别为node1和node2 现在要打win200系统补丁，打完后，要重新启动，如何打补丁，不能影响用户使用（要用群集的术语详细说明）。

3.有一个A 数据库，分别复制到B和C B 要求 每次数据更新 也同时更新，C 每天更新一次就行，如何制定复制策略!

a->b
1）、如果使用SQL Server复制功能，那么让a->b使用事务性复制方式（同步复制）。

a->c
1）、如果使用SQL Server复制功能，那么让a->b使用快照复制方式，在某一时间点进行一次性复制。
2）、也可以自己写bat，将a备份后，通过ftp传输备份介质，恢复c。（比较麻烦，不推荐）

4.有一个order 表，有90个字段，20个索引，15个复合索引，其中有3个索引字段超过10个，如何进行优化

5.有一个数据库200G大小，每天增加50M 允许用户随时访问，制定备份策略（详细说明）。

6.管理50台数据库，日常工作是检查数据库作业是否完成，你该如何完成这项检查工作？

create view vw_job
as

select '机器一' as MName,* from linkserver1..sysjobactivity
union all
select '机器二' as MName,* from linkserver2..sysjobactivity
union all
select '机器三' as MName,* from linkserver3..sysjobactivity
。。。

7.自定义函数和存储过程的区别是什么，什么情况下只能用自定义函数，什么情况下只能用存储过程

select yourfunc(...) from table

8.SQL 2005 的新特性是什么 ？ 与oracle 有什么区别？

SQL 2005 的新特性一般都是和Oracle学的。

1、字段类型。
varchar(max)\nvarchar(max)类型的引入大大的提高了编程的效率，可以使用字符串函数对CLOB类型进行操作，这是一个亮点。但是这就引发了对varchar和char效率讨论的老问题。到底如何分配varchar的数据，是否会出现大规模的碎片？是否碎片会引发效率问题？这都是需要进一步探讨的东西。

varbinary(max)代替image也让SQL Server的字段类型更加简洁统一。

XML字段类型更好的解决了XML数据的操作。XQuery确实不错，但是个人对其没好感。（CSDN的开发者应该是相当的熟了！）

2、外键的级联更能扩展

3、索引附加字段

4、计算字段的持久化

5、分区表

6、CLR类型

7、索引视图

8、语句和事务快照

9、数据库快照

10、Mirror
Mirror可以算是SQL Server的Data guard了。但是能不能被大伙用起来就不知道了。

1、Ranking函数集

select ROW_NUMBER() OVER (order by aa)
from tbl
order by bb

2、top

3、Apply

4、CTE

5、try/catch

6、pivot/unpivot

1、数据库级触发器

2、多加的系统视图和实时系统信息

3、优化器的改进

4、profiler的新事件观察

5、sqlcmd

1、登陆的控制

2、数据库物理框架没有变动
undo和redo都放在数据库得transaction中，个人感觉是个败笔。如果说我们在设计数据库的时候考虑分多个数据库，可能能在一定程度上避免I/O效率问题。但是同样会为索引视图等应用带来麻烦。看看行级和事务级的快照数据放在tempdb中，就能感觉到目前架构的尴尬。

3、还是没有逻辑备份

4、SSIS(DTS)太复杂了

9.DBA 的品质应该有哪些，你有哪些， 有什么欠缺的？

10。如果想配置SQL Mail 应该在服务器安装哪些软件！

－－－－－－－－－－－－－－－－－－－－－－－－－－－－

1、SQL Server2005之前是不支持分区表的，所以要在设计系统时考虑今后数据量大以后的数据转移问题。
3、由于SQL Server有锁升级的毛病，频繁DML的表最好减少字段数量，以减少锁升级带来的阻塞！
4、在设计数据库物理分布的时侯，由于SQL Server每个数据库都有自己的Transaction Log（其中包含Undo和Redo信息），为了减轻Transaction Log的I/O争用，可以考虑多数据库（使用聚集索引视图Clustered View的除外）。而Oracle是数据库和实例一一对应的（RAC除外），多个表空间使用公用Undo segement和redo file。
5、SQL Server的索引只有cluster index和nocluster index，而Oracle有Btree index\bitmap index\function index等。
6、SQL Server的最基本存储结构是页(8K)，而Oracle最基本的是block可以根据OLTP和DSS的应用不同（后者可以选择大一点，利于查找效率），选择2K-32K不同block大小。
7、SQL Server的结构是实例->多个数据库->表、存储过程...。Oracle的是数据库=实例（RAC是多个实例对应一个数据库存储）->schema（用户）表空间 ->表、存储过程...。

－－－－－－－－－－－－－－－－－－－－－－－－－－－－－

1.磁盘柜上有14块73G的磁盘， 数据库为200G 大小包括日志文件，如何设置磁盘（要说明这14磁盘是怎么用的）？

a. 磁盘比较充裕，做RAID10 ,就是14块硬盘分一半，2个7块做Raid0,容量：7*73G=511G，然后把它们组成Raid1,最后容量(14/2)*73G=511G. RAID10 优点，数据一次需要写入两个区块，读的时候可以从任意一个比较快的地方读取，效率很高，缺点成本较高。
b.Raid5, 13块硬盘做Raid5,1块做热冗余； 容量: (14-2)*73G=876G, Raid5优点，稳定系数高,性价比高。
c.Raid51,7块硬盘做Raid5,另7块也做Raid5,再把这2个Raid5做成Raid1,容量: (14/2-1)*73G=438G,优点:比RAID10更稳定,效率和RAID10相当.

2.有两服务器群集，分别为node1和node2 现在要打win200系统补丁，打完后，要重新启动，如何打补丁，不能影响用户使用（要用群集的术语详细说明）。

a.假设node1联机并控制资源,那么先给node2 先打补丁,重起node2,这时联机并控制资源情况不会改变,不影响客户服务.
b.等node2 重起补丁完毕,手工把node1的服务和资源切换到node2,使node2处于联机状态,然后给node1打补丁,重起补丁完毕,再把node2的服务和资源切换后node1正常运作.

SQL Server, 数据库
This one always gets asked. For a while the database interview questions were limited to Oracle and generic database design questions. This is a set of more than a hundred Microsoft SQL Server interview questions. Some questions are open-ended, and some do not have answers.

1. What is normalization? – Well a relational database is basically composed of tables that contain related data. So the Process of organizing this data into tables is actually referred to as normalization.
2. What is a Stored Procedure? – Its nothing but a set of T-SQL statements combined to perform a single task of several tasks. Its basically like a Macro so when you invoke the Stored procedure, you actually run a set of statements.
3. Can you give an example of Stored Procedure? – sp_helpdb , sp_who2, sp_renamedb are a set of system defined stored procedures. We can also have user defined stored procedures which can be called in similar way.
4. What is a trigger? – Triggers are basically used to implement business rules. Triggers is also similar to stored procedures. The difference is that it can be activated when data is added or edited or deleted from a table in a database.
5. What is a view? – If we have several tables in a db and we want to view only specific columns from specific tables we can go for views. It would also suffice the needs of security some times allowing specfic users to see only specific columns based on the permission that we can configure on the view. Views also reduce the effort that is required for writing queries to access specific columns every time.
6. What is an Index? – When queries are run against a db, an index on that db basically helps in the way the data is sorted to process the query for faster and data retrievals are much faster when we have an index.
7. What are the types of indexes available with SQL Server? – There are basically two types of indexes that we use with the SQL Server. Clustered and the Non-Clustered.
8. What is the basic difference between clustered and a non-clustered index? – The difference is that, Clustered index is unique for any given table and we can have only one clustered index on a table. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index. Whereas in case of non-clustered index the leaf level is actually a pointer to the data in rows so we can have as many non-clustered indexes as we can on the db.
9. What are cursors? – Well cursors help us to do an operation on a set of data that we retreive by commands such as Select columns from table. For example : If we have duplicate records in a table we can remove it by declaring a cursor which would check the records during retreival one by one and remove rows which have duplicate values.
10. When do we use the UPDATE_STATISTICS command? – This command is basically used when we do a large processing of data. If we do a large amount of deletions any modification or Bulk Copy into the tables, we need to basically update the indexes to take these changes into account. UPDATE_STATISTICS updates the indexes on these tables accordingly.
11. Which TCP/IP port does SQL Server run on? – SQL Server runs on port 1433 but we can also change it for better security.
12. From where can you change the default port? – From the Network Utility TCP/IP properties –> Port number.both on client and the server.
13. Can you tell me the difference between DELETE & TRUNCATE commands? – Delete command removes the rows from a table based on the condition that we provide with a WHERE clause. Truncate will actually remove all the rows from a table and there will be no data in the table after we run the truncate command.
14. Can we use Truncate command on a table which is referenced by FOREIGN KEY? – No. We cannot use Truncate command on a table with Foreign Key because of referential integrity.
15. What is the use of DBCC commands? – DBCC stands for database consistency checker. We use these commands to check the consistency of the databases, i.e., maintenance, validation task and status checks.
16. Can you give me some DBCC command options?(Database consistency check) – DBCC CHECKDB – Ensures that tables in the db and the indexes are correctly linked.and DBCC CHECKALLOC – To check that all pages in a db are correctly allocated. DBCC SQLPERF – It gives report on current usage of transaction log in percentage. DBCC CHECKFILEGROUP – Checks all tables file group for any damage.
17. What command do we use to rename a db? – sp_renamedb ‘oldname’ , ‘newname’
18. Well sometimes sp_reanmedb may not work you know because if some one is using the db it will not accept this command so what do you think you can do in such cases? – In such cases we can first bring to db to single user using sp_dboptions and then we can rename that db and then we can rerun the sp_dboptions command to remove the single user mode.
19. What is the difference between a HAVING CLAUSE and a WHERE CLAUSE? – Having Clause is basically used only with the GROUP BY function in a query. WHERE Clause is applied to each row before they are part of the GROUP BY function in a query.
20. What do you mean by COLLATION? – Collation is basically the sort order. There are three types of sort order Dictionary case sensitive, Dictonary – case insensitive and Binary.
21. What is a Join in SQL Server? – Join actually puts data from two or more tables into a single result set.
22. Can you explain the types of Joins that we can have with Sql Server? – There are three types of joins: Inner Join, Outer Join, Cross Join
23. When do you use SQL Profiler? – SQL Profiler utility allows us to basically track connections to the SQL Server and also determine activities such as which SQL Scripts are running, failed jobs etc..
24. What is a Linked Server? – Linked Servers is a concept in SQL Server by which we can add other SQL Server to a Group and query both the SQL Server dbs using T-SQL Statements.
25. Can you link only other SQL Servers or any database servers such as Oracle? – We can link any server provided we have the OLE-DB provider from Microsoft to allow a link. For Oracle we have a OLE-DB provider for oracle that microsoft provides to add it as a linked server to the sql server group.
27. What are the OS services that the SQL Server installation adds? – MS SQL SERVER SERVICE, SQL AGENT SERVICE, DTC (Distribution transac co-ordinator)
28. Can you explain the role of each service? – SQL SERVER – is for running the databases SQL AGENT – is for automation such as Jobs, DB Maintanance, Backups DTC – Is for linking and connecting to other SQL Servers
29. How do you troubleshoot SQL Server if its running very slow? – First check the processor and memory usage to see that processor is not above 80% utilization and memory not above 40-45% utilization then check the disk utilization using Performance Monitor, Secondly, use SQL Profiler to check for the users and current SQL activities and jobs running which might be a problem. Third would be to run UPDATE_STATISTICS command to update the indexes
30. Lets say due to N/W or Security issues client is not able to connect to server or vice versa. How do you troubleshoot? – First I will look to ensure that port settings are proper on server and client Network utility for connections. ODBC is properly configured at client end for connection ——Makepipe & readpipe are utilities to check for connection. Makepipe is run on Server and readpipe on client to check for any connection issues.
31. What are the authentication modes in SQL Server? – Windows mode and mixed mode (SQL & Windows).
32. Where do you think the users names and passwords will be stored in sql server? – They get stored in master db in the sysxlogins table.
33. What is log shipping? Can we do logshipping with SQL Server 7.0 – Logshipping is a new feature of SQL Server 2000. We should have two SQL Server – Enterprise Editions. From Enterprise Manager we can configure the logshipping. In logshipping the transactional log file from one server is automatically updated into the backup database on the other server. If one server fails, the other server will have the same db and we can use this as the DR (disaster recovery) plan.
34. Let us say the SQL Server crashed and you are rebuilding the databases including the master database what procedure to you follow? – For restoring the master db we have to stop the SQL Server first and then from command line we can type SQLSERVER .m which will basically bring it into the maintenance mode after which we can restore the master db.
35. Let us say master db itself has no backup. Now you have to rebuild the db so what kind of action do you take? – (I am not sure- but I think we have a command to do it).
36. What is BCP? When do we use it? – BulkCopy is a tool used to copy huge amount of data from tables and views. But it won’t copy the structures of the same.
37. What should we do to copy the tables, schema and views from one SQL Server to another? – We have to write some DTS packages for it.
38. What are the different types of joins and what dies each do?
39. What are the four main query statements?
40. What is a sub-query? When would you use one?
41. What is a NOLOCK?
42. What are three SQL keywords used to change or set someone’s permissions?
43. What is the difference between HAVING clause and the WHERE clause?
44. What is referential integrity? What are the advantages of it?
45. What is database normalization?
46. Which command using Query Analyzer will give you the version of SQL server and operating system?
47. Using query analyzer, name 3 ways you can get an accurate count of the number of records in a table?
48. What is the purpose of using COLLATE in a query?
49. What is a trigger?
50. What is one of the first things you would do to increase performance of a query? For example, a boss tells you that “a query that ran yesterday took 30 seconds, but today it takes 6 minutes”
51. What is an execution plan? When would you use it? How would you view the execution plan?
52. What is the STUFF function and how does it differ from the REPLACE function?
53. What does it mean to have quoted_identifier on? What are the implications of having it off?
54. What are the different types of replication? How are they used?
55. What is the difference between a local and a global variable?
56. What is the difference between a Local temporary table and a Global temporary table? How is each one used?
57. What are cursors? Name four types of cursors and when each one would be applied?
58. What is the purpose of UPDATE STATISTICS?
59. How do you use DBCC statements to monitor various aspects of a SQL server installation?
60. How do you load large data to the SQL server database?
61. How do you check the performance of a query and how do you optimize it?
62. How do SQL server 2000 and XML linked? Can XML be used to access data?
63. What is SQL server agent?
64. What is referential integrity and how is it achieved?
65. What is indexing?
66. What is normalization and what are the different forms of normalizations?
67. Difference between server.transfer and server.execute method?
68. What id de-normalization and when do you do it?
69. What is better – 2nd Normal form or 3rd normal form? Why?
70. Can we rewrite subqueries into simple select statements or with joins? Example?
71. What is a function? Give some example?
72. What is a stored procedure?
73. Difference between Function and Procedure-in general?
74. Difference between Function and Stored Procedure?
75. Can a stored procedure call another stored procedure. If yes what level and can it be controlled?
76. Can a stored procedure call itself(recursive). If yes what level and can it be controlled.?
77. How do you find the number of rows in a table?
78. Difference between Cluster and Non-cluster index?
79. What is a table called, if it does not have neither Cluster nor Non-cluster Index?
80. Explain DBMS, RDBMS?
81. Explain basic SQL queries with SELECT from where Order By, Group By-Having?
82. Explain the basic concepts of SQL server architecture?
83. Explain couple pf features of SQL server
84. Scalability, Availability, Integration with internet, etc.)?
85. Explain fundamentals of Data ware housing & OLAP?
86. Explain the new features of SQL server 2000?
87. How do we upgrade from SQL Server 6.5 to 7.0 and 7.0 to 2000?
88. What is data integrity? Explain constraints?
89. Explain some DBCC commands?
90. Explain sp_configure commands, set commands?
91. Explain what are db_options used for?
92. What is the basic functions for master, msdb, tempdb databases?
93. What is a job?
95. What are primary keys and foreign keys?
96. How would you Update the rows which are divisible by 10, given a set of numbers in column?
97. If a stored procedure is taking a table data type, how it looks?
98. How m-m relationships are implemented?
99. How do you know which index a table is using?
100. How will oyu test the stored procedure taking two parameters namely first name and last name returning full name?
101. How do you find the error, how can you know the number of rows effected by last SQL statement?
102. How can you get @@error and @@rowcount at the same time?
103. What are sub-queries? Give example? In which case sub-queries are not feasible?
104. What are the type of joins? When do we use Outer and Self joins?
105. Which virtual table does a trigger use?
106. How do you measure the performance of a stored procedure?
107. Questions regarding Raiseerror?
108. Questions on identity?
109. If there is failure during updation of certain rows, what will be the state?

***********************************************************************
Sql Server2005 4个排名函数: RANK(),DENSE_RANK(),ROW_NUMBER(),NTILE()

RANK()

DENSE_RANK()

ROW_NUMBER()

ORDER BY 子句可确定在特定分区中为行分配唯一 ROW_NUMBER 的顺序。

NTILE()

--演示例题，建一个table
create table rankorder(
orderid int,
qty int
)
go
--插入数据
insert rankorder values(30,10)
insert rankorder values(10,10)
insert rankorder values(80,10)
insert rankorder values(40,10)
insert rankorder values(30,15)
insert rankorder values(30,20)
insert rankorder values(22,20)
insert rankorder values(21,20)
insert rankorder values(10,30)
insert rankorder values(30,30)
insert rankorder values(40,40)
go
--查询出各类排名
SELECT orderid,qty,
ROW_NUMBER() OVER(ORDER BY qty) AS rownumber,
RANK() OVER(ORDER BY qty) AS [rank],
DENSE_RANK() OVER(ORDER BY qty) AS denserank ,
NTILE(3) OVER(ORDER BY qty) AS [NTILE]
FROM rankorder
ORDER BY qty

--结果
--ROW_NUMBER()是按qty由小到大逐一排名，不并列，排名连续
--RANK()是按qty由小到大逐一排名，并列，排名不连续
--DENSE_RANK()是按qty由小到大逐一排名，并列，排名连续
--NTILE()是按qty由小到大分成3组逐一排名，并列，排名连续
orderid qty rownumber rank denserank NTILE
30 10 1 1 1 1
10 10 2 1 1 1
80 10 3 1 1 1
40 10 4 1 1 1
30 15 5 5 2 2
30 20 6 6 3 2
22 20 7 6 3 2
21 20 8 6 3 2
10 30 9 9 4 3
30 30 10 9 4 3
40 40 11 11 5 3

sql 2005实现排名非常方便，但是用sql 2000实现排名就比较麻烦，下面是sql 2000的实现代码:

--RANK在sql 2000中的实现
select orderid,qty,
(select count(1)+1 from rankorder where qty<r.qty) as [rank]
from rankorder r
ORDER BY qty
go

--ROW_NUMBER在sql 2000中的实现
--利用临时表和IDENTITY（函数）
select identity(int,1,1) as [ROW_NUMBER],orderid,qty
into #tem
from rankorder

select orderid,qty,[ROW_NUMBER]
from #tem

drop table #tem
go

--DENSE_RANK在sql 2000中的实现
select identity(int,1,1) as ids, qty
into #t
from rankorder
group by qty
order by qty

select r.orderid,r.qty,t.ids as [DENSE_RANK]
from rankorder r join #t t
on r.qty=t.qty

drop table #t
go

--例题
SELECT orderid,qty,
DENSE_RANK() OVER(ORDER BY qty) AS a ,
DENSE_RANK() OVER(PARTITION BY orderid ORDER BY qty) AS b
FROM rankorder
ORDER BY qty

--说明：
--a列是在全部记录上进行的排名
--b列是把orderid中的记录分成了10,21,22,30,40,80这6个区，再在每个区上进行的排名。
orderid qty a b
10 10 1 1
30 10 1 1
40 10 1 1
80 10 1 1
30 15 2 2
30 20 3 3
21 20 3 1
22 20 3 1
10 30 4 2
30 30 4 4
40 40 5 2

RANK() 不一定连续 有并列
DENSE_RANK() 连续 有并列
ROW_NUMBER() 连续 无并列
NTILE() 连续 有并列

***********************************************************************
SET QUOTED_IDENTIFIER && SET ANSI_NULLS 这些是 SQL-92 设置语句，使 SQL Server 2000/2005 遵从 SQL-92 规则。

SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE。当 SET ANSI_NULLS 为 ON 时，即使 column_name 中包含空值，使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。即使 column_name 中包含非空值，使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。

***********************************************************************

create procedure Page
@PageSize int,
@PageIndex int,
@SortField nvarchar(50)

as

begin

with slist as
(
select ROW_NUMBER() over (ORDER By year) as RowId,
salary
from Salary
)

select Salary from slist
where RowId between (@PageIndex-1)*@PageSize and @PageIndex*@PageSize
order by case @SortField
when '1' then salary
else RowId end
desc

end

***********************************************************************

Create Procedure Pro1(
@biaoming varchar(50)) --此为你要传入的表名
AS
Declare
@Sql varchar(4000)--定义一个变量存放拼接的sql语句
Set @Sql='select * from '+@biaoming --具体你要返回什么自己写了
Exec @sql 如果想用exec sp_executesql语句看下面例子
-------------------------------------------------------
Create Procedure Pro1(
@biaoming varchar(50)) --此为你要传入的表名
AS
Declare
@Sql nvarchar(4000)--此处为nvarchar类型
Set @Sql='select * from '+@biaoming --具体你要返回什么自己写了
Exec sp_executesql @sql 这个语句执行起来效果好

WITH AS的含义

WITH AS短语，也叫做子查询部分(subquery factoring)，可以让你做很多事情，定义一个SQL片断，该SQL片断会被整个SQL语句所用到。有的时候，是为了让SQL语句的可读性更高些，也有可能是在UNION ALL的不同部分，作为提供数据的部分。

特别对于UNION ALL比较有用。因为UNION ALL的每个部分可能相同，但是如果每个部分都去执行一遍的话，则成本太高，所以可以使用WITH AS短语，则只要执行一遍即可。如果WITH AS短语所定义的表名被调用两次以上，则优化器会自动将WITH AS短语所获取的数据放入一个TEMP表里，如果只是被调用一次，则不会。而提示materialize则是强制将WITH AS短语里的数据放入一个全局临时表里。很多查询通过这种方法都可以提高速度。

CREATE PROCEDURE [dbo].[getPage]
@pageSize int, --页尺寸
@currentPage int, --当前页
@tableFields varchar(2000), --返回的字段
@tableName varchar(200), --表名
@orderString varchar(1000), --排序字符串
@whereString varchar(1000), --条件字符串
@IsReCount bit = 0 -- 返回记录总数, 非 0 值则返回
AS
BEGIN

if @currentPage < 1 set @currentPage = 1

DECLARE @strSql varchar(2000)
DECLARE @strOrder varchar(2000)
DECLARE @strWhere varchar(2000)

set @strOrder = REPLACE(RTRIM(LTRIM(@orderString)), 'order By ', ' ')
if @strOrder != '' set @strOrder = ' order By ' + @strOrder

set @strWhere = REPLACE(RTRIM(LTRIM(@whereString)), 'where ', ' ')
if @strWhere != ''
set @strWhere = ' where ' + @strWhere
else
set @strWhere = ' where 1=1 '

if @pageSize = 0
set @strSql = 'select ' + @tableFields + ' from ' + @tableName + @strWhere + @strOrder
else
if @currentPage = 1
set @strSql = 'select top( ' + Str(@pageSize) + ') ' + @tableFields + ' from ' + @tableName + @strWhere + @strOrder
else
begin
set @strSql = 'select top( ' + Str(@pageSize) + ') * from (select top( ' + Str(@pageSize * @currentPage) + ') ' + @tableFields + ', ROW_NUMBER() OVER ( '
set @strSql = @strSql + @strOrder
set @strSql = @strSql + ') As RowNumber From ' + @tableName
set @strSql = @strSql + @strWhere
set @strSql = @strSql + ') as t where t.RowNumber > ' + Str(@pageSize * (@currentPage - 1))
set @strSql = @strSql + @strOrder
end

if @IsReCount != 0
set @strSql = 'select count(*) as Total from [' + @tableName + '] ' + @strWhere

exec(@strSql)
END

• 1
点赞
• 4
收藏
觉得还不错? 一键收藏
• 0
评论
01-30
08-11 3470
02-16 3785
04-20
04-11 1280
04-22 8856
09-29 2426
10-07 1万+
09-27 293

### “相关推荐”对你有帮助么？

• 非常没帮助
• 没帮助
• 一般
• 有帮助
• 非常有帮助

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。