SQLServer经典面试题.doc

题目1：为管理岗位业务培训信息，建立3个表S (SNO,SNAME,SDD,SAGE) SNO,SNAME,SDD,SAGE 分别代表学号、学员姓名、所属单位、学员年龄C (CNO,CNAME ) CNO,CNAME 分别代表课程编号、课程名称SC(SNO,CNO,SCORE ) SNO,CNO,SCORE 分别代表学号、所选修的课程编号、学习成绩1. 使用标准SQL嵌套语句查询选修课程名称为税收基础的学员学号和姓名 SELECT SNO,SDD FROM SWHERE SNO IN(SELECT SNO FROM C,SC WHERE C.CNO=SC.CNO AND CNAME=税收基础);2. 使用标准SQL嵌套语句查询选修课程编号为C2的学员姓名和所属单位SELECT S.SNO,S.SDD FROM S,SC WHERE S.NOSNO=SC.SNO AND SC.CNO=C2;3. 使用标准SQL嵌套语句查询不选修课程编号为C5的学员姓名和所属单位SELECT SNO,SDD FROM S WHERE SNO NOT IN(SELECT SNO FROM SC WHERE CNO=C5) ;4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位SELECT SNO,SDD FROM SWHERE SNO IN(SELECT SNO FROM SC RIGHT JOIN C ON SC.CNO=C.CNO GROUP BY SNOHAVING COUNT(*)=COUNT(SNO);5. 查询选修了课程的学员人数SELECT 学员人数=COUNT(DISTINCT SNO) FROM SC;6. 查询选修课程超过5门的学员学号和所属单位SELECT SN,SD FROM SWHERE SNO IN(SELECT SNO FROM SC GROUP BY SNOHAVING COUNT(DISTINCT CNO)5) ;题目2：已知关系模式S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩1. 找出没有选修过“CC”老师讲授课程的所有学生姓名SELECT SNAME FROM S WHERE SNO NOT IN(SELECT SNO FROM SC WHERE CNO IN(SELECT CNO FROM C WHERE CTEACHER = CC);2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩SELECT S.SNO,S.SNAME,平均成绩=AVG(SC.SCGRADE)FROM S,SC,(SELECT SNO FROM SC WHERE SCGRADE =2) as 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 FROM SC,C WHERE SC.CNO=C.CNO AND C.CNOIN(1,2)GROUP BY SC.SNO HAVING COUNT(DISTINCT SC.CNO)=2) SC WHERE S.SNO=SC.SNO;4. 列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号SELECT S.SNO,S.SNAMEFROM S,SC AS SC1,SC AS SC2 WHERE SC1.CNO=1 AND SC2.SNO=2 AND SC1.CNO=SC2.CNO AND SC1.SCGRADE SC2.SCGRADE AND SC1.SNO=S.SNO;5. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩SELECT SC1.SNO,1号课成绩=SC1.SCGRADE,2号课成绩=SC2.SCGRADEFROM SC SC1,SC SC2WHERE SC1.CNO=1 AND SC2.CNO=2 AND SC1.SNO= SC2.SNO AND SC1.SCGRADE SC2.SCGRADE;题目3：本题用到下面三个关系表CARD 借书卡。 CNO 卡号，NAME 姓名，CLASS 班级BOOKS 图书。 BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数 BORROW 借书记录。 CNO 借书卡号，BNO 书号，RDATE 还书日期备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。要求实现如下15个处理： 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对CARD表做如下修改： a. 将NAME最大列宽增加到10个字符（假定原为6个字符）。 b. 为该表增加1列NAME（系名），可变长，最大20个字符。1. 写出建立BORROW表的SQL语句，要求定义主码完整性约束和引用完整性约束CREATE TABLE BORROW (CNO CHAR(20), BNO CHAR(20), RDATE DATETIME); ALTER TABLE BORROW ADD CONSTRAINT BORROW_CNO_BNO_PK PRIMARY KEY (CNO,BNO); ALTER TABLE BORROW ADD CONSTRAINT BORROW_CNO_FK FOREIGN KEY(CNO) REFERENCES CARD(CNO);ALTER TABLE BORROW ADD CONSTRAINT BORROW_BNO_FK FOREIGN KEY(BNO) REFERENCES BOOKS(BNO); 2. 找出借书超过5本的读者,输出借书卡号及所借图书册数SELECT CNO,借图书册数=COUNT(*) FROM BORROW GROUP BY CNO HAVING COUNT(*)5;3. 查询借阅了水浒一书的读者，输出姓名及班级SELECT A.NAME,A.CLASS FROM CARD A,BOOKS B ,BORROW C WHERE A.CNO=C.CNOAND B.BNO=C.BNO AND B.BNAME=水浒; 4. 查询过期未还图书，输出借阅者（卡号）、书号及还书日期ORACLE:SELECT * FROM BORROW C WHERE RDATE SYSDATE;SQL SERVER:SELECT * FROM BORROW C WHERE RDATEGETDATE() ;5. 查询书名包括网络关键词的图书，输出书号、书名、作者SELECT B.BNO,B.BNAME,B.AUTHOR FROM BOOKS B WHERE B.BNAME LIKE %网络%;6. 查询现有图书中价格最高的图书，输出书名及作者SELECT * FROM BOOKS B WHERE B.PRICE IN (SELECT MAX(PRICE) FROM BOOKS); 7. 查询当前借了计算方法但没有借计算方法习题集的读者，输出其借书卡号，并按卡号降序排序输出SELECT C.CNO FROM BOOKS B, BORROW C WHERE B.BNO=C.CNO AND B.BNAME LIKE 计算方法 AND B.BNAME NOT LIKE 计算方法习题集 ORDER BY C.CNO DESC;8. 将C01班同学所借图书的还期都延长一周UPDATE BORROW SET RDATE = DATEADD(WEEK,1,RDATE) WHERE CNO IN(SELECT CNO FROM CARD WHERE CLASS = C01) 或者UPDATE BORROW SET RDATE= (SELECT C.RDATE+7 FROM CARD A ,BORROW C WHERE C.BNO=A.BNO AND A.CLASS=C01);9. 从BOOKS表中删除当前无人借阅的图书记录DELETE FROM Books WHERE Books.BNO NOT IN (SELECT DISTINCT C.BNO from BORROW C );10. 如果经常按书名查询图书信息，请建立合适的索引CREATE INDEX BOOKS_INDEX on BOOKS(BNAME);11. 在BORROW表上建立一个触发器，完成如下功能：如果读者借阅的书名是数据库技术及应用，就将该读者的借阅记录保存在BORROW_SAVE表中（注ORROW_SAVE表结构同BORROW表）CREATE OR REPLACE TRIGGER BORROW_Insert_Trigs BEFORE INSERT ON BORROW REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE V_BAME BOOKS.BNAME%TYPE; BEGIN SELECT BNAME INTO V_BNAME FROM BOOKS WHERE BNO=:NEW.BNO; IF V_NAME=数据库技术及应用 THENINSERT INTO ORROW_SAVE VALUES(:NEW.BNO，:NEW.BNAME,:NEW.AUTHOR，:NEW.PRICE，:NEW.QUANTITY) END; 12. 建立一个视图，显示力01班学生的借书信息（只要求显示姓名和书名）CREATE OR REPLACE VIEW ABC_VIEW AS SELECT A.NAME,B.BNAMEFROM CARD A,BOOKS B,BORROW C WHERE A.CNO=C.CNO AND B.BNO=C.BNO AND A.CLASS=C01);13. 查询当前同时借有计算方法和组合数学两本书的读者，输出其借书卡号，并按卡号升序排序输出SELECT A.CNO FROM CARD A WHERE A.CNO IN (SELECT DISTINCT C.CNO FROM BORROW C,BOOKS B WHERE C.BNO=B.BNO AND B.BNAME IN(计算方法,组合数学) ) ORDE