TSQL面试题含建表语句.docx
《TSQL面试题含建表语句.docx》由会员分享,可在线阅读,更多相关《TSQL面试题含建表语句.docx(13页珍藏版)》请在冰豆网上搜索。
TSQL面试题含建表语句
Student(S#,Sname,Sage,Ssex)学生表
Course(C#,Cname,T#)课程表
SC(S#,C#,score)成绩表
Teacher(T#,Tname)教师表
建表语句
CreatetableStudent
(
S#number(4),
Snamevarchar2(9)notnull,
Ssexvarchar2(3),
Sagedate,
constraintStudent_S#_Pkprimarykey(S#)
);
insertintoStudentvalues(1001,'李志国','男',to_date('26-9-1985','dd-mm-yyyy'));
insertintoStudentvalues(1002,'李甜甜','女',to_date('6-2-1987','dd-mm-yyyy'));
insertintoStudentvalues(1003,'张小燕','女',to_date('18-11-1984','dd-mm-yyyy'));
insertintoStudentvalues(1004,'王菲','男',to_date('3-6-1985','dd-mm-yyyy'));
insertintoStudentvalues(1005,'杜宇','女',to_date('25-4-1986','dd-mm-yyyy'));
insertintoStudentvalues(1006,'彭大生','男',to_date('28-12-1988','dd-mm-yyyy'));
insertintoStudentvalues(1007,'王亮','男',to_date('1-8-1983','dd-mm-yyyy'));
insertintoStudentvalues(1008,'赵婷婷','女',to_date('2-2-1984','dd-mm-yyyy'));
CreatetableTeacher
(
T#number(4),
Tnamevarchar2(9)notnull,
constraintTeacher_T#_Pkprimarykey(T#)
);
insertintoTeachervalues(2001,'李华');
insertintoTeachervalues(2002,'王力宏');
insertintoTeachervalues(2003,'叶平');
insertintoTeachervalues(2004,'王倩倩');
insertintoTeachervalues(2005,'李莉莉');
CreatetableCourse
(
C#number(3),
Cnamevarchar2(12)notnull,
T#number(4),
constraintCourse_C#_Pkprimarykey(C#),
constraintCourse_T#_Fkforeignkey(T#)referencesTeacher(T#)
);
insertintoCoursevalues(001,'企业管理',2003);
insertintoCoursevalues(002,'马克思',2001);
insertintoCoursevalues(003,'OO'||'&'||'UML',2002);
insertintoCoursevalues(004,'数据库',2004);
insertintoCoursevalues(005,'英语',2005);
insertintoCoursevalues(006,'大学语文',2005);
insertintoCoursevalues(007,'现代企业',2003);
CreatetableSC
(
S#number(4),
C#number(4),
scorenumber(3),
constraintSC__S#_C#_Pkprimarykey(S#,C#),
constraintSc_S#_Fkforeignkey(S#)referencesStudent(S#),
constraintSc_C#_Fkforeignkey(C#)referencesCourse(C#)
);
insertintoSCvalues(1001,1,87);
insertintoSCvalues(1001,3,66);
insertintoSCvalues(1001,2,77);
insertintoSCvalues(1001,5,45);
insertintoSCvalues(1001,7,67);
insertintoSCvalues(1002,4,26);
insertintoSCvalues(1002,1,86);
insertintoSCvalues(1003,3,67);
insertintoSCvalues(1003,2,97);
insertintoSCvalues(1003,5,67);
insertintoSCvalues(1004,5,78);
insertintoSCvalues(1004,3,67);
insertintoSCvalues(1004,4,49);
insertintoSCvalues(1005,4,76);
insertintoSCvalues(1005,2,84);
insertintoSCvalues(1005,3,35);
insertintoSCvalues(1006,3,54);
insertintoSCvalues(1006,2,76);
insertintoSCvalues(1006,1,56);
insertintoSCvalues(1007,1,56);
insertintoSCvalues(1007,7,88);
insertintoSCvalues(1007,2,89);
insertintoSCvalues(1007,3,57);
insertintoSCvalues(1007,4,99);
insertintoSCvalues(1007,5,85);
insertintoSCvalues(1008,1,76);
insertintoSCvalues(1008,2,44);
insertintoSCvalues(1008,4,87);
insertintoSCvalues(1008,5,33);
insertintoSCvalues(1002,6,65);
insertintoSCvalues(1002,7,90);
insertintoSCvalues(1006,6,67);
问题:
1、查询“001”课程比“002”课程成绩高的所有学生的学号;
selecta.S#from(selects#,scorefromSCwhereC#='001')a,(selects#,score
fromSCwhereC#='002')b
wherea.score>b.scoreanda.s#=b.s#;
2、查询平均成绩大于60分的同学的学号和平均成绩;
selectS#,avg(score)
fromsc
groupbyS#havingavg(score)>60;
3、查询所有同学的学号、姓名、选课数、总成绩;
selectStudent.S#,Student.Sname,count(SC.C#),sum(score)
fromStudentleftOuterjoinSConStudent.S#=SC.S#
groupbyStudent.S#,Sname
4、查询姓“李”的老师的个数;
selectcount(distinct(Tname))
fromTeacher
whereTnamelike'李%';
5、查询没学过“叶平”老师课的同学的学号、姓名;
selectStudent.S#,Student.Sname
fromStudent
whereS#notin(selectdistinct(SC.S#)fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname='叶平');
6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
selectStudent.S#,Student.SnamefromStudent,SCwhereStudent.S#=SC.S#andSC.C#='001'andexists(Select*fromSCSC_2whereSC_2.S#=SC.S#andSC_2.C#='002');
--exists是Oraclesql中的一个函数。
表示是否存在符合某种条件的记录。
7、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
selectS#,Sname
fromStudent
whereS#in(selectS#fromSC,Course,TeacherwhereSC.C#=Course.C#andTeacher.T#=Course.T#andTeacher.Tname='叶平'groupbyS#havingcount(SC.C#)=(selectcount(C#)fromCourse,TeacherwhereTeacher.T#=Course.T#andTname='叶平'));
8、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
SelectS#,Snamefrom(selectStudent.S#,Student.Sname,score,(selectscorefromSCSC_2whereSC_2.S#=Student.S#andSC_2.C#='002')score2
fromStudent,SCwhereStudent.S#=SC.S#andC#='001')S_2wherescore29、查询所有课程成绩小于60分的同学的学号、姓名;
selectS#,Sname
fromStudent
whereS#notin(selectStudent.S#fromStudent,SCwhereS.S#=SC.S#andscore>60);
10、查询没有学全所有课的同学的学号、姓名;
selectStudent.S#,Student.Sname
fromStudent,SC
whereStudent.S#=SC.S#groupbyStudent.S#,Student.Snamehavingcount(C#)<(selectcount(C#)fr