SqlServer 常见SQL笔试题之语句操作题详解
by:授客 QQ：1033553122
测试数据库
CREATE DATABASE handWriting
ON PRIMARY
(
name = 'bishi',
fileName = 'E:数据库ishi.mbf',
size = 5MB,
maxSize = 30MB,
fileGrowth = 10%
)
LOG ON
(
name = 'bishilog',
fileName = 'E:数据库ishilog.ldf',
size = 5MB,
fileGrowth = 10%
)
1、
有个表：
employee 职工(工号，姓名，性别，年龄，部门)(num，name，sex，age，departmentno)
wage工资(编号，工资金额)(ID，amount)
attend出勤(工号，工资编号，出勤率)(num，ID，attendance)
请根据要求，编写相应的SQL语句。
--写一个SQL语句，查询工资金额为8000的职工工号和姓名。
分析：多表查询(3表或以上)，先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT 指定的列返回查询结果。
SELECT name,employee.num FROM employee, wage, attend
WHERE employee.num = attend.num and attend.ID = wage.ID and wage.amount = 8000
--写一个SQL语句，查询职工张三的出勤率
分析:只涉及到两张表。。如下
SELECT attendance FROM attend, employee
WHERE attend.num = employee.num AND employee.name = '张三'
--WHERE num = (SELECT num FROM employee WHERE employee.name = '张三')
--写一个SQL语句，查询3次出勤率为0的职工姓名和工号【常见题目】
分析：设计到两张表，相关子查询，employee表中每给出一个工号，就去全表扫描attend表，先在表attend中查找满足WHERE条件的记录，然后对结果进行分组，再过滤出勤率为0的，对每一个num，虚表都有值，那么exists 虚表返回true，把结果放入结果集表中，然后再指向下一个记录，给定一个num重复。。。。
SELECT name, num FROM employee
WHERE EXISTS(
SELECT num FROM attend
WHERE employee.num = attend.num and attendance = 0
GROUP BY num HAVING COUNT(*) = 3)
--写一个SQL语句，查询出勤率为10并且工资金额小于2500的职工信息。[常见题目]
分析：不可行的思路：职工信息在表employee,工资在表wage，出勤率在表attend中，工资表只和出勤表相关联，先从表attend和表wage选出满足条件的结果集，再通过num和表employee相关联
SELECT * FROM employee,(
SELECT * FROM attend, wage
WHERE attendance = 10 AND attend.ID = wage.ID and amount <</span> 2500) A
WHERE employee.num = A.num
说明：多次为'A' 指定了列'ID' ，这种方法不可行，字段太多，会同名，要全部写出来。。
思路二:多表连接，【一般这种做法错不了，具有普遍性】，num的选择，来自attend中num集合，ID来自满足条件的集合
SELECT * FROM employee
WHERE num IN(
SELECT num FROM attend
WHERE attendance = 10 and ID IN(
SELECT ID FROM wage
WHERE amount <</span> 2500))
2、
为管理岗位业务培训信息，建立个表:
S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄
C (C#,CN ) C#,CN 分别代表课程编号、课程名称
SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩
-- 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位【常见题目】
错误做法如下，
SELECT sn, sd FROM s
WHERE EXISTS(
SELECT * FROM c
WHERE EXISTS(
SELECT * FROM sc
WHERE s.s# = sc.s# and c.c# = sc.c#))
说明：先装载s表，然后，指针p1指向第一个记录了，然后装载c表，指针p2指向第一个记录，
然后装载sc表，进行全sc表扫描，指针指向第一个记录，然后查询满足WHERE的记录，如果找到记录(不管一条还是多条)，则内层中的EXISTS虚表返回值为真，执行SELECT * FROM C，把结果放入虚表中，显然虚表中有数据，外层EXISTS 虚表为真，然后把sn，sd放入最终结果表，这不说明他选了全部课程，只能说明他至少选了一门课程
正确做法：语义上：查询这样的学生，他没有选的课程不存在。--双重否定
对每个学生，去课程表中查找没被他选修的课程，结果找不到数据。
SELECT sn, sd
FROM s
WHERE NOT EXISTS
(
SELECT * FROM c
WHERE NOT EXISTS
(
SELECT * FROM sc
WHERE s.s# = sc.s# and sc.c# = c.c#
)
)
--以下子查询即为查找他没选修的课程
SELECT * FROM c
WHERE NOT EXISTS
(
SELECT * FROM sc
WHERE s.s# = sc.s# and sc.c# = c.c#
)
--查询选修了课程的学员人数【常见题目】
分析：选修课中去掉重复的学员号s#,剩下的记录个数就表示人数
SELECT 学员人数= COUNT(DISTINCT s#) FROM SC
--查询选修课程超过5门的学员学号和所属单位【常见题目】
分析：同一个学生（分组使相同值的学号放在一组），选修课程超过5门（该组的记录个数超过5个）
(HAVING提出要求，记录个数用count实现)；
先从sc表中查找，然后分组，选择记录大于5的，然后再从s表查找，条件是学生号在刚才那个sc表查询结果集中。
SELECT sn, sd FROM S
WHERE s# IN(
SELECT s# FROM SC
GROUP BY s#
HAVING COUNT(DISTINCT C#)>5)
注意：如果仅是group by，不带having语句，那么针对每个分组仅会产生一条记录，如果使用having语句则会产生1到n条记录，每条记录都必须满足having给定的条件。
--使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
分析：三表c，sc,s
SELECT s#,sn FROM s
WHERE s# IN(
SELECT s# FROM sc WHERE c# IN(
SELECT c# FROM c
WHERE cn = '税收基础'))
-- 使用标准SQL嵌套语句查询选修课程编号为2的学员姓名和所属单位
SELECT s#,sd FROM s
WHERE s# IN(
SELECT s# FROM sc WHERE c# IN(
SELECT c# FROM c
WHERE c# = '2'))
-- 使用标准SQL嵌套语句查询不选修课程编号为’5’的学员姓名和所属单位
SELECT s#,sd FROM s
WHERE s# IN(
SELECT s# FROM sc WHERE c# IN(
SELECT c# FROM c
WHERE c# <> '5'))
--用一条SQL语句查询出每门课都大于80分的学生姓名【常见题目】
分析：涉及选修表，学生表，对sc表里的每个s#，全表扫描他选修的成绩，都要大于80
语义：查找这样的学生，全表扫描他选修的成绩，不存在小于80的
SELECT sn FROM s
WHERE s# IN(
SELECT x.s# FROM sc x
WHERE NOT EXISTS(
SELECT * FROM sc y
WHERE y.s# = x.s# AND g <</span> 80))
3、
问题描述:
已知关系模式:
S1 (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名
C1 (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师
SC1(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
--3.1. 找出没有选修过“李明”老师讲授课程的所有学生姓名【常见题型】
法一：等值连接查询
SELECT SNAME FROM S1,C1, SC1
WHERE S1.SNO = SC1.SNO
AND S1.SNO = C1.CNO
AND C1.CTEACHER <> '李明'
法二:和方法一一样的原理
SELECT SNAME FROM S1
WHERE SNO IN(
SELECT SNO FROM SC1
WHERE CNO IN(
SELECT CNO FROM C1
WHERE CTEACHER <> '李明'))
法三
[错误做法]
SELECT SNAME FROM S1
WHERE NOT EXISTS(
SELECT * FROM C1
WHERE NOT EXISTS(
SELECT * FROM SC1
WHERE CTEACHER = '李明' AND S1.SNO = SC1.SNO AND C1.CNO = SC1.CNO ))
[正解如下]：
语义：查询这样的学生，不存在被李明老师讲且被他选修的课程。
SELECT SNAME FROM S1
WHERE NOT EXISTS(
SELECT * FROM C1
WHERE EXISTS(
SELECT * FROM SC1
WHERE CTEACHER = '李明' AND S1.SNO = SC1.SNO AND C1.CNO = SC1.CNO ))
--查找被李明老师讲且被选修课程
SELECT * FROM C1
WHERE EXISTS(
SELECT * FROM SC1
WHERE CTEACHER = '李明' AND S1.SNO = SC1.SNO AND C1.CNO = SC1.CNO ))
--3.2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩【常见题型】
分析：
语义：查询这样的学生的姓名及学生的平均成绩，选修课程至少有两名门不及格
培养解题习惯-->涉及表:姓名--S1表，成绩--SC1表，关联字段SNO，条件--同一个学生，两门或两门以上不及格，用到函数--VAG()，
条件WHERE，至少要有两门用COUNT,同一个同学，分组GROUP BY
假如这里没有要求查询平均成绩，那么可以如下
SELECT S1.SNAME FROM S1
WHERE EXISTS(
SELECT * FROM SC1 WHERE SC1.SNO = S1.SNO AND SC1.SCGRADE <</span> 60
GROUP BY SC1.SNO HAVING COUNT(*) >= 2);
但是这里还要求平均成绩，我们可以这样，在上一步的基础上，把EXISTS子句中的语句提取出来的结果表给取个别名，然后，两表查询
SELECT S1.SNAME, A.AVGSCORE FROM S1,
(SELECT SNO, AVG(SCGRADE) AVGSCORE FROM SC1
WHERE SCGRADE <</span> 60
GROUP BY SC1.SNO HAVING COUNT(*) >= 2) A
WHERE S1.SNO = A.SNO
--3.3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名【常见题型】
做法1
SELECT SNAME
FROM S1,(
SELECT SNO FROM SC1
WHERE CNO = 1
INTERSECT
SELECT SNO FROM SC1
WHERE CNO = 2
)A
WHERE S1.SNO = A.SNO
做法2：
涉及表：选课表，学生表，从表中查找号课程或者号课程的学生，然后分组取同一个学生，然后选择记录数大于等于选两门的同学，然后再与学生表连接
SELECT SNAME FROM S1
WHERE SNO IN(
SELECT SNO FROM SC1
WHERE CNO = 1 OR CNO = 2
GROUP BY(SNO) HAVING COUNT(*) >= 2)--注意这里要是只写等于，那么要是还选了其他课程的就
会被遗漏
--3.4 列出对同一学生，“1”号课成绩比“2”号课成绩高的所有学生的学号
分析：涉及表SC1，条件：同一个学生，号课程成绩比号课程成绩高，可以为该表起两个别名，然后联合查询，同一学生--》两表SNO相等，相关子查询，给定一个sno，一个cno，全表扫描另一个表，查找满足where的。。
SELECT DISTINCT(X.SNO) FROM SC1 X --注意这里一定要加个DISTINCT
WHERE X.CNO = 1 AND X.SCGRADE > (
SELECT SCGRADE FROM SC1 Y
WHERE Y.SNO = X.SNO AND Y.CNO = 2);
--3.5 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
分析:涉及表一张，先从同一样表中查找出1号课程，与2号课程的成绩，及选修人SNO，然后SNO相等，确定同一个人，然后再比较两表中学生的成绩。再从前面的结果查找学号。。。
SELECT A.SNO, SCORE1, SCORE2 FROM (
SELECT SNO,SCGRADE SCORE1 FROM SC1
WHERE CNO = 1)A,
(SELECT SNO, SCGRADE SCORE2 FROM SC1
WHERE CNO = 2)B
WHERE A.SNO = B.SNO AND A.SCORE1 > B.SCORE2
4、
请查询出在ZD_ks中BM有重复的值及重复的次数，没有的不要列出。如下表：
BM DUPCOUNT
001
002
采用SC1中的数据源
SELECT SNO,COUNT(SNO)- 1 REPEAT FROM SC1
GROUP BY SNO HAVING COUNT(*) > 1
5、
问题描述：
本题用到下面三个关系表：
T_CARD 借书卡 CNO 卡号，NAME 姓名，CLASS 班级
T_BOOKS 图书 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数
T_BORROW 借书记录CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
--5.1．找出借书超过5本的读者,输出借书卡号及所借图书册数。
分析：涉及3表，而且输出字段不在同一表中，这种情况下用如下方式不错
SELECT NAME, CNO, QUANTITY FROM CARD, T_BOOKS, T_BORROW
WHERE CARD.CNO=T_BORROW.CNO AND T_BOOKS.BNO = T_BORROW.BNO
GROUP BY NAME HAVING COUNT(*)>5
--5.2．查询借阅了"水浒"一书的读者，输出姓名及班级。
SELECT NAME, CLASS FROM T_CARD
WHERE CNO IN (
SELECT CNO FROM T_BORROW
WHERE BNO IN (
SELECT BNO FROM T_BOOKS
WHERE BNAME = '水浒'))
--5.3．查询过期未还图书，输出借阅者（卡号）、书号及还书日期。
SELECT * FROM T_BORROW
WHERE REDATE <</span> GETDATE();
--5.4．查询书名包括"网络"关键词的图书，输出书号、书名、作者。
SELECT BNO, BNAME, AUTHOR FROM T_BOOKS
WHERE BNAME LIKE '%网络%';
--5.5．查询现有图书中价格最高的图书，输出书名及作者。
SELECT TOP 1 WITH TIES BNAME, AUTHOR FROM T_BOOKS ORDER BY PRICE DESC;
--或者
SELECT BNAME, AUTHOR FROM T_BOOKS
WHERE PRICE = (SELECT MAX(PRICE) FROM T_BOOKS)
--5.6 查询当前借了"计算方法"但没有借"计算方法导论"的读者，输出其借书卡号，并按卡号降序排序输出。
分析：涉及表，T_BORROW T_BOOKS,条件同一个学生，借了‘计算机方法’，没借计算机方法导论，条件和条件都是同一个表中，考虑起别名
错误做法如下，子查询中是排除了计算方法导论，但是父查询中只要借了BNO的人就会被选出来，而借了BNO的人有可能借了计算方法导论
SELECT CNO FROM T_BORROW
WHERE BNO IN(
SELECT A.BNO FROM T_BOOKS A
WHERE A.BNAME LIKE '计算方法'AND A.BNO NOT IN(
SELECT B.BNO FROM T_BOOKS B
WHERE A.BNAME = '计算方法导论'))
ORDER BY CNO DESC;
一般涉及同一个的情况要么是等值，要么就是EIXSTS或等相关子查询，但这里没T_BOOK表中没有CNO这关联字段；
先保证他借了计算方法，然后再保证他没有再借计算方法导论,怎么保证？给定同一个CNO,再对T_BORROW表中的BNO,对书表进行全表扫描，不存在借计算方法导论的记录
SELECT A.CNO FROM T_BORROW A, T_BOOKS B
WHERE A.BNO = B.BNO AND B.BNAME = '计算方法'
AND NOT EXISTS(
SELECT * FROM T_BORROW A1, T_BOOKS B1
WHERE A.CNO = A1.CNO AND B1.BNO = A1.BNO AND B1.BNAME = '计算方法导论')
ORDER BY CNO DESC;
--5.7 将"1"班同学所借图书的还期都延长一周。
UPDATE T_BORROW SET REDATE = DATEADD(DAY,7,REDATE)--注意函数的用法
WHERE CNO IN (
SELECT CNO FROM T_CARD
WHERE CLASS = 1);
--5.8 从BOOKS表中删除当前无人借阅的图书记录。
分析：对book表中的每个书号，对借书表全表扫描，结果找不到数据
DELETE FROM T_BOOKS WHERE NOT EXISTS(
SELECT * FROM T_BORROW
WHERE T_BOOKS.BNO = T_BORROW.BNO
);
--5.9.如果经常按书名查询图书信息，请建立合适的索引。
CREATE INDEX INDEX_A ON T_BOOKS(BNAME);
--5.10 在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，--就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）。
CREATE TRIGGER TRI_NAME ON T_BORROW
FOR INSERT,UPDATE
AS
DECLARE
@booknum INT
SELECT @booknum = BNO FROM INSERTED --注意这里赋值【SET @变量名=值或SELECT @变量名= 值 FROM 表
IF @booknum = (SELECT BNO FROM T_BOOKS
WHERE BNAME = '数据库技术及应用')
BEGIN
--INSERT INTO BORROW_SVAE SELECT * FROM INSERTED --注意这里这种方式下，表BORROW_SAVE一定要先存在，不然执行的时候会提示对象名无效
SELECT * INTO BORROW_SVAE FROM INSERTED
END;
--检测
INSERT INTO T_CARD VAlUES(3,'JJ',1);
INSERT INTO T_BORROW VALUES(3,5, '2008-12-01');
SELECT * FROM BORROW_SVAE;
发现上面的做法无效
--稍微改动了下
CREATE TRIGGER TR_SAVE ON T_BORROW
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT>0
SELECT * INTO BORROW_SAVE
FROM INSERTED i,T_BOOKS b
WHERE i.BNO=b.BNO
AND b.BNAME='数据库技术及应用'
--检测
DELETE FROM T_BORROW WHERE BNO = 5;
INSERT INTO T_BORROW VALUES(3,5, '2008-12-01');
--提示数据库中已存在名为'BORROW_SVAE' 的对象。
--改正
DROP TRIGGER TR_SAVE
DROP TRIGGER TRI_NAME
CREATE TRIGGER TR_SAVE ON T_BORROW
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT>0
SELECT * INTO BORROW_SAVE
FROM INSERTED i,T_BOOKS b
WHERE i.BNO = b.BNO
AND b.BNAME='数据库技术及应用'
--检测
DELETE FROM T_BORROW WHERE BNO = 5;
INSERT INTO T_BORROW VALUES(3,5, '2008-12-01');
--提示各表中的列名必须唯一。在表'BORROW_SAVE' 中多次指定了列名'BNO'。
--改正
DROP TRIGGER TR_SAVE
CREATE TRIGGER TR_SAVE ON T_BORROW
FOR INSERT,UPDATE
AS
IF @@ROWCOUNT>0
SELECT i.CNO,i.BNO,I.REDATE INTO BORROW_SAVE
FROM INSERTED i,T_BOOKS b
WHERE i.BNO = b.BNO
AND b.BNAME='数据库技术及应用'
--检测
DELETE FROM T_BORROW WHERE BNO = 5;
INSERT INTO T_BORROW VALUES(3,5, '2008-12-01');
SELECT * FROM BORROW_SVAE;
--5.10．建立一个视图，显示1.班学生的借书信息（只要求显示姓名和书名）。
CREATE VIEW V_VIEW
AS
SELECT NAME, BNAME FROM T_BORROW, T_CARD
WHERE T_BORROW.CNO = T_CARD.CNO AND T_CARD.CLASS = 1
--检测
SELECT * FROM V_VIEW
--5.11查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。
分析：涉及表：T_BORROW,T_BOOKS 条件，同时在同一张表选了计算方法和组合数学
--法一.分别查出这两个结果集，然后INTERCEPT取交集
SELECT CNO FROM T_BORROW
WHERE BNO IN (SELECT BNO FROM T_BOOKS--注意这里只能用IN
WHERE BNAME = '计算方法')
INTERSECT
SELECT CNO FROM T_BORROW
WHERE BNO IN (SELECT BNO FROM T_BOOKS
WHERE BNAME = '组合数学')
ORDER BY CNO DESC
--法二.查出一个结果集，然后分组，然后记录大于等于2
SELECT CNO FROM T_BORROW
WHERE BNO IN (SELECT BNO FROM T_BOOKS
WHERE BNAME = '计算方法' OR BNAME = '组合数学')
GROUP BY CNO HAVING COUNT(*)>=2
ORDER BY CNO DESC
--5.12假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。
ALTER TABLE T_BOOKS
ADD CONSTRAINT pk_key2
PRIMARY KEY CLUSTERED(BNO)
--5.15．对CARD表做如下修改：
-- a. 将NAME最大列宽增加到个字符（假定原为个字符）。
ALTER TABLE T_CARD
ALTER COLUMN NAME VARCHAR(10)--注意这里没有SET
-- b. 为该表增加列NAME（系名），可变长，最大个字符。
ALTER TABLE T_CARD
ADD 系名 VARCHAR(20)
--检测结果
SELECT * FROM T_CARD
EXEC SP_HELP T_CARD--查看表信息
6、
学生表如下:
自动编号 学号 姓名课程编号课程名称分数
1 2005001 张三 0001 数学 69
2 2005002 李四 0001 数学 89
3 2005001 张三 0001 数学 69
删除除了自动编号不同,其他都相同的学生冗余信息
分析；分组
DELETE FROM S2
WHERE 自动编号 NOT IN(
SELECT MIN(自动编号) FROM S2 GROUP BY 学号,姓名,课程编号,课程名称,分数)
SELECT * FROM S2;
7、
复制表(复制结构，但是也会复制数据,源表名：a 新表名：b)
SELECT * INTO b FROM a WHERE 1<>1
说明：b可以不必先创建
拷贝表(拷贝数据,源表名：a 目标表名：b)
INSERT INTO a(a, b, c) SELECT d,e,f FROM b;
说明：要先创建a
8、
有两个表A和B，均有keyk和value两个字段，如果B的keyk在A中也有，
就把B的value换为A中对应的value
这道题的SQL语句怎么写？
UPDATE B SET VALUE =(
SELECT VALUE FROM A
WHERE B.KEYK = A.KEYK)
--结果：B中KEYK不在A中的，value就变成了NULL-->原因没对B中的key限制,非常容易忽略
--改正：删除后重新插入上述数据
DELETE FROM B;
UPDATE B SET VALUE =(
SELECT VALUE FROM A
WHERE B.KEYK = A.KEYK)
WHERE B.KEYK IN (
SELECT KEYK FROM A WHERE A.KEYK = B.KEYK)
9、
原表:
courseid coursename score
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
为了便于阅读,查询此表后的结果显式如下(及格分数为):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
写出此查询语句
SELECT COURSEID, COURSENAME, SCORE,
(CASE WHEN SCORE > 60 THEN 'PASS' ELSE 'FAIL' END) AS MARK
FROM T_B
oracle中做法如下(未测试)
select courseid, coursename ,score ,decode(
sign(score-60),-1,'fail','pass') as mark from course
说明：SIGN返回指定表达式的正号(+1)、零(0) 或负号(-1)。
10、
已知表T1、T2和T3的结构和数据如下
T1
NameID CourseID Score
1 1 90
1 2 85
2 1 75
2 2 95
T2
ID Course
1 数学
2 语文
T3
ID Name
1 张三
2 李四
请写出查询的SQL语句，使得查询的结果为:
Name Course Score
——– ——– ——–
张三 数学 90
张三 语文 85
李四 数学 75
李四 语文 95
SELECT NAME,COURSE,SCORE FROM T1, T2, T3
WHERE T1.NAMEID = T3.ID AND T1.COURSEID = T3.ID
GROUP BY NAME,COURSE,SCORE;