sql题目和答案全解
㈠ 数据库SQ习题
最佳答案：
习题
一、选择题
1．关于查询语句中ORDER BY子句使用正确的是( D )。
A．如果未指定排序字段，则默认按递增排序
B．表的字段都可用于排序
C．如果在SELECT子句中使用了DISTINCT关键字，则排序字段必须出现在查询结果中
D．联合查询不允许使用ORDER BY子句
第2～4题使用7．7节“示例分析”中数据表。
2．使用查询语句：
SELECT STUDENT.学号,STUDENT.姓名,SUM(分数)
FROM STUDENT,GRADE
WHERE STUDENT.学号=GRADE.学号
GROUP BY STUDENT.学号,STUDENT.姓名
查询结果是(A )。
A．按学号分类的每个学生所有课程成绩的总分 B．按学号分类的每个学生各课程成绩
C．全体学生的按各课程分类的成绩总分 D．所有学生所有课程成绩总分
3．使用查询语句：
SELECT MAX(分数) AS 最高分
FROM STUDENT,curriculum,GRADE
WHERE STUDENT.学号=GRADE.学号
AND curriculum.课程编号=GRADE.课程编号
AND 课程名称='数据库技术及应用'
查询的结果是( )。
A．87 B．91 C．82 D．90
很遗憾！不知道表中的记录数据，无法判断是哪个选项，但是题意是检索出：选修课程'数据库技术及应用'的学生中成绩最高的。看一下表就知道了。
4．查询选修了课程编号为“0002”的学生的学号和姓名，以下( A )语句是错误的。
A．
SELECT 学号,姓名FROM STUDENT
WHERE 学号=(SELECT 学号FROM GRADE WHERE 课程编号='0002')
B．
SELECT STUDENT.学号,STUDENT.姓名
FROM STUDENT,GRADE
WHERE STUDENT.学号=GRADE.学号AND 课程编号='0002'
C．
SELECT STUDENT.学号，STUDENT.姓名
FROM STUDENT JOIN GRADE ON STUDENT.学号=GRADE.学号
WHERE GRADE.课程编号='0002'
D．
SELECT 学号,姓名FROM STUDENT
WHERE 学号IN (SELECT 学号FROM GRADE WHERE 课程编号='0002')
5．下列关于查询结果错误的是( D )。
A．查询结果可以显示在表格中
B．查询结果可以按文本方式显示
C．以文本和表格显示的查询结果在保存时，其文件格式不同
D．不管以哪种方式查看，查询结果都会显示在查询结果窗口中
6．在T-sql语句中，与表达式“仓库号NOT IN ('wh1','wh2')”功能相同的表达式是( D )。
A．仓库号='wh1' AND 仓库号='wh2' B．仓库号!='wh1' OR 仓库号# 'wh2'
C．仓库号<>'wh1' OR 仓库号!='wh2' D．仓库号!='wh1' AND 仓库号!='wh2'
7．在T-SQL 的SELECT语句中用于实现关系的选择运算的短语是( C )。
A．FOR B．WHILE C．WHERE D．CONDITION
8．使用SQL语句进行分组检索时，为了去掉不满足条件的分组，应当( B )。
A．使用WHERE子句 B．在GROUP BY后面使用HAVING子句
C．先使用WHERE子句，再使用HAVING子句 D．先使用HAVING子句，再使用WHERE子句
二、填空题
1．在查询语句中，应在__SELECT____子句中指定输出字段。
2．如果要使用SELECT语句返回指定条数的记录，则应使用_TOP__关键字来限定输出字段。
3．当一个子SELECT的结果作为查询的条件，即在一个SELECT命令的WHERE子句中出现另一个SELECT命令，这种查询称为__嵌套____查询。
4．连接查询可分为3种类型：_内连接___、__外连接__和交叉连接。
5．若要把查询结果存放到一个新建的表中，可使用__into tabale 或into dbf____子句。
三、判断题
1．在关系数据库SQL Server中，用于检索数据的语句是T-SQL的定义语言。 (× )
2．逻辑运算符(AND、NOT、OR)的运算顺序是AND→OR→NOT。 ( × )
3．用于WHERE子句的查询条件表达式可用的比较运算符为：=(等于)、!=或<>(不等于)、>(大于)、>=(大于等于)、<(小于)、<=(小于等于)。 ( √ )
4．SELECT语句的DISTINCT参数表示输出无重复结果的记录。 ( √ )
5．如果要使SELECT的查询结果有序输出，需要用GROUP BY子句配合。 ( × )
㈡ 鍏充簬SQL镄勫嚑阆揿皬棰樿﹁В
棰樼洰涓锛氩备笅琛ㄦ墍绀猴纴鐜伴渶瑕佹寜镦ф敹娆惧憳缁熻℃敹娆惧拰阃娆惧悎璁￠噾棰濄
瀹炵幇缁撴灉闇濡备笅鏄剧ず锛
鍒嗘瀽锛氭兂瑕佺殑缁撴灉锛堣颁负琛˙锛夊拰婧愭暟鎹锛堣颁负琛ˋ锛夌浉姣旓纴链夊叡钖岀殑鍒楋纸鏀舵惧憳锛夛纴涓嶅悓镄勬槸琛ˋ镄勯噾棰濇牴鎹镙囱板拰鏀舵惧憳鍒嗘垚浜嗕袱鍒楋纴镓浠ヨ繖涓闇姹傚彲浠ョ敤璇瑷琛ㄨ堪涓涓嬶细棣栧厛镙规嵁鏀舵惧憳鍒嗙粍锛坓roup by锛,铹跺悗褰撴爣璁颁负钬沧敹钬濇椂锛岄噾棰濊″叆鏀舵惧悎璁★纸sum锛夛绂褰撴爣璁颁负钬滈钬濇椂锛岄噾棰濊″叆阃娆惧悎璁★纸sum锛夈傚綋??镞??锛岃繖涓嶅氨鏄疭QL镄勬浔浠跺垽鏂鍢涳纻鐩樼偣SQL镄勬浔浠惰鍙ヤ笉澶氾纴if??else??鍜宑ase??when??then??else??end銆傝繖镙烽梾棰桦氨杩庡垉钥岃В浜嗐
瑙ｅ喅鏂规埚备笅锛
with ta as
(select '鏀' as 镙囱,'100' as 鏀舵惧憳,150 as 閲戦
union
select '鏀','100',375
union
select '阃','100',78
union select '鏀','200',74
)
select 鏀舵惧憳,sum(case when 镙囱='鏀' then 閲戦 else 0 end) as 鏀舵惧悎璁,
sum(case when 镙囱='阃' then 閲戦 else 0 end) as 阃娆惧悎璁 from ta
group by 鏀舵惧憳
棰樼洰浜岋细濡备笅琛ˋ锛埚乏杈癸级镵屽憳淇℃伅琛锛屽叾涓琏D涓鸿亴锻桦伐鍙凤纴name涓鸿亴锻桦揿悕锛涜〃B锛埚彸杈癸级涓鸿亴锻树换锷″垎閰嶈〃锛屽叾涓琏D涓鸿亴锻桦伐鍙凤纸鍜岃〃A涓琏D瀵瑰簲锛夛纴Task涓轰换锷＄紪鍙枫
鐜伴渶姹傛疮涓镵屽憳镄勪换锷℃暟銆傜粨鏋滃备笅鏄剧ず锛
鍏跺疄铡熼樻槸杩欐牱镄勶细鍙链変竴寮犺〃B锛屾眰姹傛疮涓镵屽憳镄勪换锷℃暟銆傛病链夋垒鍒版瘆杈冨ソ镄勬柟娉曞疄鐜帮纴涓嶅仛璁ㄨ猴纴娆㈣繋楂树汉鎸囩偣銆
鍒嗘瀽锛氭ら樼殑闅剧偣鍦ㄤ簬琛˙涓镄処D澶嶆潅琛ㄧず锛屽叾瀹炶繖镙锋湁鎭栦簬鏁版嵁搴撶殑璁捐″师鍒欙纴鐞嗗簲琛ˋ鍜岃〃B镄処D涓涓瀵瑰簲銆傛棦铹舵槸棰桡纴鎴戜滑鍙鑳戒粠褰揿墠镄勬浔浠跺叆镓嬩简锛屾敾镰撮毦镣圭殑鍏抽敭鍦ㄤ簬鍒ゆ柇A涓琏D鍦˙涓琏D鍑虹幇涓庡惁锛屽傛灉鍑虹幇闾ｄ箞濡备綍缁熻″嚭鐜扮殑娆℃暟銆傚垽鏂鍑虹幇涓庡惁闇瑕佺敤鍒板嚱鏁癈HARINDEX銆
瑙ｅ喅鏂规埚备笅锛
--鍒涘缓娴嬭瘯鏁版嵁
WITH TA
AS
(SELECT '1,2' AS ID,'job1' AS task
UNION SELECT '1,2,3','job3'
UNION SELECT '2,3','job2'
UNION SELECT '3,4,5','job4')
,TB AS
(SELECT '1' AS ID,'寮犱笁' as name
UNION SELECT '2','鐜嬩簩'
UNION SELECT '3','𨱒庡洓'
UNION SELECT '4','𨱒庢槑'
UNION SELECT '5','鐜嬩簲')
SELECT B.ID,B.name,COUNT(1) AS TASKS
FROM TA A,TB B
WHERE CHARINDEX(B.ID,A.ID)>0
GROUP BY B.ID,B.name
order by B.ID
棰樼洰涓夛细铡熼桦弬瑙佸崥瀹锛歨ttp://www.cnblogs.com/Lumia1020/p/4571301.html
濡备笅琛–ity镓绀猴纴code涓鸿屾敛鍖哄烟镰侊纸鍏浣嶆暟瀛楋纴鍓崭袱浠ｈ〃鐪佺骇锛屼腑闂翠袱浣崭唬琛ㄥ竞绾э纴链钖庝袱浣崭唬琛ㄥ幙绾э纴涓嶈冭槛xx00xx𨱍呭喌锛夛纴city涓哄煄甯傚悕绉帮纴CCode涓鸿ュ煄甯傛墍灞炵殑鐪佺骇鎴栬呭竞绾ц屾敛鍖哄烟镰併
鐜伴渶姹傚备笅缁撴灉锛
鍒嗘瀽锛氩垎鏋愯〃city锛宑ode镄勫惈涔夊崄鍒嗘槑鏄撅纴镓闇瑕佺殑缁撴灉涔熷緢鏄庢樉锛屽傛灉鏄鐪佸氨鏄鏄剧ず鐪佷唤锛涙槸甯傚垯鏄剧ず涓烘墍灞炵渷绾+甯傜骇锛涙槸铡跨骇鍒欐樉绀轰负镓灞炵渷绾+镓灞炲竞绾+铡跨骇銆傝矊似鍙浠ョ敤棰樼洰涓鍒嗘瀽涓鎻愬埌镄凷QL𨱒′欢璇鍙ュ疄鐜帮纴浣嗘槸杞蹇典竴𨱍筹纴杩樻槸链夊樊鍒锛岃繖閲岄渶瑕佸厛鍒ゆ柇city灞炰簬鐪佺骇锛熷竞绾э纻铡跨骇锛熺劧钖庡湪瀵瑰簲璧锋潵镄勶纴杩欐牱杩桦缑链夊弬镦ц〃锛屽嶆潅浜嗐傚洖鍒扮粨鏋滆〃涓𨱒ヨ繘琛屽垎鏋愶纴鍏跺疄鍒ゅ畾city灞炰簬鐪佸竞铡跨殑闂棰桦苟涓嶉毦锛宑ode镄勫惈涔夊凡缁忚存槑浜嗭纴鍙瑕佽浆鎹㈣〃杩帮细鍦ㄨ〃City涓锛屽綋code镄勫悗锲涗綅涓衡0000钬濇椂锛岃偗瀹氭槸鐪佺骇锛涘綋code镄勫悗涓や綅涓衡00钬濓纴骞朵笖钖庡洓浣崭笉涓衡0000钬濇椂锛岃偗瀹氭槸甯傜骇锛涘綋code钖庝袱浣崭笉涓衡00钬濇椂锛屼负铡跨骇銆傝繖镙风渷甯傚幙镄勫垽瀹氩氨涓鐩浜嗙劧浜嗭纴铹跺悗锛屾牴鎹甯傜骇缂栫爜杩芥湐镓灞炵殑鐪佺骇锛屽苟寰楀嚭镓灞炵渷绾+甯傜骇锛屽幙绾ц拷链旀墍灞炵殑甯傜骇锛屽缑鍑烘墍灞炵渷绾+镓灞炲竞绾+镓灞炲幙绾э纴阃氲繃杩愮敤杩欑岖亩鍗旷殑阃掑綊镐濇兂锛岃В鍐虫柟妗堜究璺幂劧绾镐笂浜嗐
瑙ｅ喅鏂规埚备笅锛
--娴嬭瘯鏁版嵁
with ta as
(select '110000' as code, '鍖椾含甯' city, '110000' Ccode
union
select N'110200', N'瑗垮煄鍖', N'110200'
union
select N'110300', N'宕囨枃鍖', N'110300'
union
select N'430000', N'婀栧崡鐪', N'430000'
union
select N'430100', N'闀挎矙甯', N'430100'
union
select N'430101', N'链涘煄铡', N'430100')
select * into City from ta
select * from City;
--瑙ｅ喅鏂规
with ta
as(
--鐪佺骇
select code,city,Ccode,city content from City where right(code,4)='0000'),
tb as(
--甯傜骇
select b.code,b.city,b.Ccode,a.city+','+b.city as content from ta a,City b where left(a.Ccode,2)=left(b.Ccode,2)
and right(b.code,2)='00' and right(b.code,4)<>'0000'),
tc as(
select c.code,c.city,c.Ccode,b.content+','+c.city content from tb b,City c where left(b.Ccode,4)=left(c.Ccode,4)
and right(c.code,2)<>'00')
select * from ta
union
select * from tb
union
select * from tc
阃氲繃涓婅堪鍑犻亾灏忛桡纴甯告濆父鏂帮纴娓╂晠浜哠QL镄勯儴鍒嗙煡璇嗭纴褰撶劧鏂规硶寰埚氾纴鍙桦纺寰埚氾纴濡傞樼洰浜岀粺璁¤〃B涓姣忎釜Task镄勪汉鏁扮瓑銆备笉瓒充箣澶勶纴娆㈣繋钖勪綅鎸囩偣锛
鍏充簬SQL镄勫嚑阆揿皬棰樿﹁В
镙囩撅细sql闱㈣瘯棰
㈢ SQL多项选择题，急求答案，最好有解析
1.答案A,D,E DESC是降序,省略ASC和DESC，默认为升序。
2.答案A,C,D,E 创建视图时不允许在所用SELECT语句中使用ORDER BY、COMPUTE子句
3.C,D,E 执行存储过程时可用WITH RECOMPLE选项进行重新编译;只有当执行存储过程的语句是批处理中的第一个语句，才可以直接通过名称来调用存储过程
4.C,E 使用SQL Server Management Studio不能运行命令行实用程序sqlcmd
5.C,D,F 一个局部变量只能在一个语句批中使用,使用SET语句只能对一个局部变量赋值,刚定义的局部变量的初值为空值
6.B 用户定义函数可以有输入参数和返回值
㈣ 鍑犻亾sql棰樻眰绛旀
1锛宻elect * from 琛ㄥ悕 order by 濮揿悕
2锛宻elect * from 琛ㄩ溃 where 璇剧▼=钬沧暟鎹缁撴瀯钬 and 濮揿悕 like 钬%𨱒%钬
3锛宻elect max(鎴愮哗锛夛纴min(鎴愮哗)锛宎vg(鎴愮哗) from 琛ㄥ悕 group by 鎴愮哗
4,select into 鐢峰︾敓琛 from 琛ㄥ悕 where 镐у埆=钬灭敺钬
5,select * from 琛ㄥ悕 where 镐у埆=钬滃コ钬 and 骞撮缎>20
6,create uniwue clustered index i_name on 琛ㄥ悕锛坰_name锛
7,create nonclustered index i_score on 琛ㄥ悕(score)
8,create view v_nopass as select 瀛﹀彿锛屽揿悕锛岀戠洰锛屾垚缁 where 鎴愮哗<60
㈤ SQL查询面试题与答案
SQL查询面试题与答案
SQL语言是一种数据库查询和程序设计语言，用于存取数据以及查询、更新和管理关系数据库系统;同时也是数据库脚本文件的扩展名。下面是我搜集的SQL查询面试题与答案，欢迎大家阅读。
SQL查询面试题与答案一
1.一道SQL语句面试题，关于group by表内容：
2005-05-09 胜
2005-05-09 胜
2005-05-09 负
2005-05-09 负
2005-05-10 胜
2005-05-10 负
2005-05-10 负
如果要生成下列结果, 该如何写sql语句?
胜 负
2005-05-09 2 2
2005-05-10 1 2
------------------------------------------
create table #tmp(rq varchar(10),shengfu nchar(1))
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','胜')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-09','负')
insert into #tmp values('2005-05-10','胜')
insert into #tmp values('2005-05-10','负')
insert into #tmp values('2005-05-10','负')
1)select rq, sum(case when shengfu='胜' then 1 else 0 end)'胜',sum(case when shengfu='负' then 1 else 0 end)'负' from #tmp group by rq
2) select N.rq,N.胜,M.负 from (
select rq,胜=count(*) from #tmp where shengfu='胜'group by rq)N inner join
(select rq,负=count(*) from #tmp where shengfu='负'group by rq)M on N.rq=M.rq
3)select a.col001,a.a1 胜,b.b1 负 from
(select col001,count(col001) a1 from temp1 where col002='胜' group by col001) a,
(select col001,count(col001) b1 from temp1 where col002='负' group by col001) b
where a.col001=b.col001
2.请教一个面试中遇到的SQL语句的查询问题
表中有A B C三列,用SQL语句实现：当A列大于B列时选择A列否则选择B列，当B列大于C列时选择B列否则选择C列。
------------------------------------------
select (case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name
3.面试题：一个日期判断的sql语句?
请取出tb_send表中日期(SendTime字段)为当天的所有记录?(SendTime字段为datetime型，包含日期与时间)
------------------------------------------
select * from tb where datediff(dd,SendTime,getdate())=0
4.有一张表，里面有3个字段：语文，数学，英语。其中有3条记录分别表示语文70分，数学80分，英语58分，请用一条sql语句查询出这三条记录并按以下条件显示出来(并写出您的思路)：
大于或等于80表示优秀，大于或等于60表示及格，小于60分表示不及格。
显示格式：
语文 数学 英语
及格 优秀 不及格
------------------------------------------
select
(case when 语文>=80 then '优秀'
when 语文>=60 then '及格'
else '不及格') as 语文,
(case when 数学>=80 then '优秀'
when 数学>=60 then '及格'
else '不及格') as 数学,
(case when 英语>=80 then '优秀'
when 英语>=60 then '及格'
else '不及格') as 英语,
from table
5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
------------------------------------------
用户临时表:create table #xx(ID int, IDValues int)
系统临时表:create table ##xx(ID int, IDValues int)
区别:
用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.
当创建它的进程消失时这个临时表就自动删除.
全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.
6.sqlserver2000是一种大型数据库，他的`存储容量只受存储介质的限制，请问它是通过什么方式实现这种无限容量机制的。
------------------------------------------
它的所有数据都存储在数据文件中(*.dbf),所以只要文件够大,SQL Server的存储容量是可以扩大的.
SQL Server 2000 数据库有三种类型的文件：
主要数据文件
主要数据文件是数据库的起点，指向数据库中文件的其它部分。每个数据库都有一个主要数据文件。主要数据文件的推荐文件扩展名是 .mdf。
次要数据文件
次要数据文件包含除主要数据文件外的所有数据文件。有些数据库可能没有次要数据文件，而有些数据库则有多个次要数据文件。次要数据文件的推荐文件扩展名是 .ndf。
日志文件
日志文件包含恢复数据库所需的所有日志信息。每个数据库必须至少有一个日志文件，但可以不止一个。日志文件的推荐文件扩展名是 .ldf。
7.请用一个sql语句得出结果
从table1,table2中取出如table3所列格式数据，注意提供的数据及结果不准确，只是作为一个格式向大家请教。
如使用存储过程也可以。
table1
月份mon 部门dep 业绩yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
table2
部门dep 部门名称dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
table3 (result)
部门dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 null 5 8
04 null null 9
------------------------------------------
1)
select a.部门名称dname,b.业绩yj as '一月份',c.业绩yj as '二月份',d.业绩yj as '三月份'
from table1 a,table2 b,table2 c,table2 d
where a.部门dep = b.部门dep and b.月份mon = '一月份' and
a.部门dep = c.部门dep and c.月份mon = '二月份' and
a.部门dep = d.部门dep and d.月份mon = '三月份' and
2)
select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep
8.华为一道面试题
一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
------------------------------------------
select id, Count(*) from tb group by id having count(*)>1
select * from(select count(ID) as count from table group by ID)T where T.count>1
SQL查询面试题与答案二
1、查询不同老师所教不同课程平均分从高到低显示
SELECT max(Z.T#) AS 教师ID,MAX(Z.Tname) AS 教师姓名,C.C# AS 课程ID,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
2、查询如下课程成绩第 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);
3、统计打印各科成绩,各分数段人数:课程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;
4、查询学生平均成绩及其名次
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;
5、查询各科成绩前三名的记录:(不考虑成绩并列情况)
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#;
6、查询每门课程被选修的学生数
select c#,count(S#) from sc group by C#;
7、查询出只选修了一门课程的全部学生的学号和姓名
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;
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
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
;