SqlServer常见SQL笔试题之语句操作题详解
测试数据库
CREATEDATABASE handWriting
ONPRIMARY
(
name='bishi',
fileName='E:\数据库\bishi.mbf',
size= 5MB,
maxSize= 30MB,
fileGrowth= 10%
)
LOGON
(
name='bishilog',
fileName='E:\数据库\bishilog.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指定的列返回查询结果。
SELECTname,employee.num FROM employee, wage, attend
WHEREemployee.num = attend.num and attend.ID = wage.ID and wage.amount = 8000
--写一个SQL语句，查询职工张三的出勤率
分析:只涉及到两张表。。如下
SELECT attendance FROM attend, employee
WHEREattend.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重复。。。。
SELECTname, num FROM employee
WHEREEXISTS(
SELECT num FROM attend
WHEREemployee.num = attend.num and attendance = 0
GROUPBY num HAVINGCOUNT(*)= 3)
--写一个SQL语句，查询出勤率为10并且工资金额小于2500的职工信息。[常见题目]
分析：不可行的思路：职工信息在表employee,工资在表wage，出勤率在表attend中，工资表只和出勤表相关联，先从表attend和表wage选出满足条件的结果集，再通过num和表employee相关联
SELECT*FROM employee,(
SELECT*FROM attend, wage
WHEREattendance = 10 AND attend.ID = wage.ID and amount <</span> 2500) A
WHEREemployee.num = A.num
说明：多次为'A'指定了列'ID' ，这种方法不可行，字段太多，会同名，要全部写出来。。
思路二:多表连接，【一般这种做法错不了，具有普遍性】，num的选择，来自attend中num集合，ID来自满足条件的集合
SELECT*FROM employee
WHEREnum IN(
SELECT num FROM attend
WHEREattendance = 10 and ID IN(
SELECT ID FROM wage
WHEREamount <</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
WHEREEXISTS(
SELECT*FROM c
WHEREEXISTS(
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
WHEREs# IN(
SELECT s# FROM SC
GROUPBY s#
HAVINGCOUNT(DISTINCT C#)>5)
注意：如果仅是group by，不带having语句，那么针对每个分组仅会产生一条记录，如果使用having语句则会产生1到n条记录，每条记录都必须满足having给定的条件。
--使用标准SQL嵌套语句查询选修课程名称为’税收基础’的学员学号和姓名
分析：三表c，sc,s
SELECT s#,sn FROM s
WHEREs# IN(
SELECT s# FROM sc WHERE c# IN(
SELECT c# FROM c
WHEREcn ='税收基础'))
--使用标准SQL嵌套语句查询选修课程编号为2的学员姓名和所属单位
SELECT s#,sd FROM s
WHEREs# IN(
SELECT s# FROM sc WHERE c# IN(
SELECT c# FROM c
WHEREc# ='2'))
--使用标准SQL嵌套语句查询不选修课程编号为’5’的学员姓名和所属单位
SELECT s#,sd FROM s
WHEREs# IN(
SELECT s# FROM sc WHERE c# IN(
SELECT c# FROM c
WHEREc# <>'5'))
--用一条SQL语句查询出每门课都大于80分的学生姓名【常见题目】
分析：涉及选修表，学生表，对sc表里的每个s#，全表扫描他选修的成绩，都要大于80
语义：查找这样的学生，全表扫描他选修的成绩，不存在小于80的
SELECT sn FROM s
WHEREs# IN(
SELECT x.s# FROM sc x
WHERENOTEXISTS(
SELECT*FROM sc y
WHEREy.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
WHERENOTEXISTS(
SELECT *FROM C1
WHERENOTEXISTS(
SELECT *FROM SC1
WHERE CTEACHER ='李明'AND S1.SNO = SC1.SNO AND C1.CNO = SC1.CNO ))
[正解如下]：
语义：查询这样的学生，不存在被李明老师讲且被他选修的课程。
SELECT SNAME FROM S1
WHERE NOTEXISTS(
SELECT *FROM C1
WHEREEXISTS(
SELECT *FROM SC1
WHERE CTEACHER ='李明'AND S1.SNO = SC1.SNO AND C1.CNO = SC1.CNO ))
--查找被李明老师讲且被选修课程
SELECT *FROM C1
WHEREEXISTS(
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
WHEREEXISTS(
SELECT*FROM SC1 WHERE SC1.SNO = S1.SNO AND SC1.SCGRADE <</span> 60
GROUPBY SC1.SNO HAVINGCOUNT(*)>= 2);
但是这里还要求平均成绩，我们可以这样，在上一步的基础上，把EXISTS子句中的语句提取出来的结果表给取个别名，然后，两表查询
SELECT S1.SNAME, A.AVGSCORE FROM S1,
(SELECT SNO,AVG(SCGRADE) AVGSCORE FROM SC1
WHERESCGRADE <</span> 60
GROUPBY SC1.SNO HAVINGCOUNT(*)>= 2) A
WHERES1.SNO = A.SNO
--3.3.列出既学过“1”号课程，又学过“2”号课程的所有学生姓名【常见题型】
做法1
SELECT SNAME
FROM S1,(
SELECT SNO FROM SC1
WHERECNO = 1
INTERSECT
SELECT SNO FROM SC1
WHERECNO = 2
)A
WHERES1.SNO = A.SNO
