最新sql必须学习案例.docx

《最新sql必须学习案例.docx》由会员分享，可在线阅读，更多相关《最新sql必须学习案例.docx（69页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Four short words sum up what has lifted most successful individuals above the crowd: a little bit more.-author-datesql必须学习案例sql必须学习案例题目1：问题描述：为管理岗位业务培训信息，建立3个表:S (S#,SN,SD,SA) S#,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄C (C#,CN ) C#,CN 分别代表课程编号、课程名称SC ( S#,C#,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩 1. 使用标准SQL嵌套语句查询选

2、修课程名称为税收基础的学员学号和姓名 -实现代码：SELECT SN,SD FROM SWHERE 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,SCWHERE S.S#=SC.S# AND SC.C#=C23. 使用标准SQL嵌套语句查询不选修课程编号为C5的学员姓名和所属单位-实现代码：SELECT SN,SD FROM SWHERE S# NOT IN( SELECT S# FROM SC W

3、HERE C#=C5) 4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位-实现代码：SELECT SN,SD FROM SWHERE S# IN( SELECT S# FROM SC RIGHT JOIN C ON SC.C#=C.C# GROUP BY S# HAVING COUNT(*)=COUNT(S#)5. 查询选修了课程的学员人数-实现代码：SELECT 学员人数=COUNT(DISTINCT S#) FROM SC6. 查询选修课程超过5门的学员学号和所属单位-实现代码：SELECT SN,SD FROM SWHERE S# IN( SELECT S# FROM S

4、C GROUP BY S# HAVING COUNT(DISTINCT C#)5) 题目2问题描述：已知关系模式：S (SNO,SNAME） 学生关系。SNO 为学号，SNAME 为姓名C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩1. 找出没有选修过“李明”老师讲授课程的所有学生姓名-实现代码：SELECT SNAME FROM SWHERE NOT EXISTS( SELECT * FROM SC,C WHERE SC.CNO=C.CNO A

5、ND CNAME=李明 AND SC.SNO=S.SNO) 2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩-实现代码：SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)FROM S,SC,( SELECT SNO FROM SC WHERE SCGRADE=2)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNOGROUP BY S.SNO,S.SNAME 3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名-实现代码：SELECT S.SNO,S.SNAMEFROM S,( SELECT SC.SNO

6、 FROM SC,C WHERE SC.CNO=C.CNO AND C.CNAME IN(1,2) GROUP BY SNO HAVING COUNT(DISTINCT CNO)=2)SC WHERE S.SNO=SC.SNO 4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号-实现代码：SELECT S.SNO,S.SNAMEFROM S,( SELECT SC1.SNO FROM SC SC1,C C1,SC SC2,C C2 WHERE SC1.CNO=C1.CNO AND C1.NAME=1 AND SC2.CNO=C2.CNO AND C2.NAME=2 AND SC

7、1.SCGRADESC2.SCGRADE)SC WHERE S.SNO=SC.SNO 5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩-实现代码：SELECT S.SNO,S.SNAME,SC.1号课成绩,SC.2号课成绩FROM S,( SELECT SC1.SNO,1号课成绩=SC1.SCGRADE,2号课成绩=SC2.SCGRADE FROM SC SC1,C C1,SC SC2,C C2 WHERE SC1.CNO=C1.CNO AND C1.NAME=1 AND SC2.CNO=C2.CNO AND C2.NAME=2 AND SC1.SCG

8、RADESC2.SCGRADE)SC WHERE S.SNO=SC.SNO 题目3问题描述：CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数 BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。要求实现如下15个处理： 1 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束。 2 找出借书超过5本的读者,输出借书卡号及所借图书册数。 3 查询借阅了水浒一

9、书的读者，输出姓名及班级。 4 查询过期未还图书，输出借阅者（卡号）、书号及还书日期。 5 查询书名包括网络关键词的图书，输出书号、书名、作者。 6 查询现有图书中价格最高的图书，输出书名及作者。 7 查询当前借了计算方法但没有借计算方法习题集的读者，输出其借书卡号，并按卡号降序排序输出。 8 将C01班同学所借图书的还期都延长一周。 9 从BOOKS表中删除当前无人借阅的图书记录。 10如果经常按书名查询图书信息，请建立合适的索引。 11在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是数据库技术及应用，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_S

10、AVE表结构同BORROW表）。 12建立一个视图，显示力01班学生的借书信息（只要求显示姓名和书名）。 13查询当前同时借有计算方法和组合数学两本书的读者，输出其借书卡号，并按卡号升序排序输出。 14假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句。 15对CARD表做如下修改： a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。 b. 为该表增加1列NAME（系名），可变长，最大20个字符。1. 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束-实现代码：CREATE TABLE BORROW( CNO int FOREIGN

11、KEY REFERENCES CARD(CNO), BNO int FOREIGN KEY REFERENCES BOOKS(BNO), RDATE datetime, PRIMARY KEY(CNO,BNO) 2. 找出借书超过5本的读者,输出借书卡号及所借图书册数-实现代码：SELECT CNO,借图书册数=COUNT(*)FROM BORROWGROUP BY CNOHAVING COUNT(*)53. 查询借阅了水浒一书的读者，输出姓名及班级-实现代码：SELECT * FROM CARD cWHERE EXISTS( SELECT * FROM BORROW a,BOOKS b WH

12、ERE a.BNO=b.BNO AND b.BNAME=N水浒 AND a.CNO=c.CNO) 4. 查询过期未还图书，输出借阅者（卡号）、书号及还书日期-实现代码：SELECT * FROM BORROW WHERE RDATE0INSERT BORROW_SAVE SELECT i.*FROM INSERTED i,BOOKS bWHERE i.BNO=b.BNO AND b.BNAME=数据库技术及应用 12. 建立一个视图，显示力01班学生的借书信息（只要求显示姓名和书名）-实现代码：CREATE VIEW V_VIEWASSELECT a.NAME,b.BNAMEFROM BORR

13、OW ab,CARD a,BOOKS bWHERE ab.CNO=a.CNO AND ab.BNO=b.BNO AND a.CLASS=N力0113. 查询当前同时借有计算方法和组合数学两本书的读者，输出其借书卡号，并按卡号升序排序输出-实现代码：SELECT a.CNOFROM BORROW a,BOOKS bWHERE a.BNO=b.BNO AND b.BNAME IN(N计算方法,N组合数学)GROUP BY a.CNOHAVING COUNT(*)=2ORDER BY a.CNO DESC 14. 假定在建BOOKS表时没有定义主码，写出为BOOKS表追加定义主码的语句-实现代码：A

14、LTER TABLE BOOKS ADD PRIMARY KEY(BNO) 15.1 将NAME最大列宽增加到10个字符（假定原为6个字符）-实现代码：ALTER TABLE CARD ALTER COLUMN NAME varchar(10) 15.2 为该表增加1列NAME（系名），可变长，最大20个字符-实现代码：ALTER TABLE CARD ADD 系名 varchar(20)sql面试题（一）2009-05-23 09:05Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Tea

15、cher(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.scoreb.score and a.s#=b.s#; 2、查询平均成绩大于60分的同学的学号和平均成绩； select S#,avg(score) from sc group by S# having avg(score) 60; 3、查询所有同学的学号、姓名、选课数、总成绩

16、； 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

17、 (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#=001and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and

18、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=叶平);

19、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 60); 10、查询没有学全所有课的同学的学号、姓名； select Student.S#,Student

20、.Sname from Student,SC where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) =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行显示):

21、 企业管理（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 E

22、LSE 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#

23、= 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

24、数据库及格百分数 FROM SC21、查询不同老师所教不同课程平均分从高到低显示 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 （0

25、03），数据库（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

26、 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

27、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 O

28、N 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 WHE

29、N 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 T2.平均成绩) as 名次, S# as 学生学号,平均成绩 FROM (SELECT S#,AVG(score) 平均成绩 FROM SC GROUP BY S# ) AS T2 ORDER BY 平均成绩 desc; 25、查询各科成绩前三名的记录:(不考虑成绩并列情况) SELECT t1.S# a

30、s 学生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