SQLServer经典面试题.docVIP

题目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. 找出没有选修过“”老师讲授课程的所有学生姓名SELECT SNAME FROM S WHERE SNO NOT IN(SELECT SNO FROM SC WHERE CNO IN(SELECT CNO FROM C WHERE CTEACHER = )); 2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩SELECT S.SNO,S.SNAME,平均成绩=AVG(SC.SCGRADE)FROM S,SC,(SELECT SNO FROM SC WHERE SCGRADE 60 GROUP BY SNO HAVING COUNT(DISTINCT CNO)=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.CNO?IN(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 SC