TSQL面试题含建表语句.docx

1、TSQL面试题含建表语句Student(S#,Sname,Sage,Ssex) 学生表 Course(C#,Cname,T#) 课程表 SC(S#,C#,score) 成绩表 Teacher(T#,Tname) 教师表建表语句 Create table Student(S# number(4),Sname varchar2(9) not null,Ssex varchar2(3),Sage date,constraint Student_S#_Pk primary key(S#);insert into Student values(1001,李志国,男,to_date(26-9-1985,d

2、d-mm-yyyy);insert into Student values(1002,李甜甜,女,to_date(6-2-1987,dd-mm-yyyy);insert into Student values(1003,张小燕,女,to_date(18-11-1984,dd-mm-yyyy);insert into Student values(1004,王菲,男,to_date(3-6-1985,dd-mm-yyyy);insert into Student values(1005,杜宇,女,to_date(25-4-1986,dd-mm-yyyy);insert into Student

3、values(1006,彭大生,男,to_date(28-12-1988,dd-mm-yyyy);insert into Student values(1007,王亮,男,to_date(1-8-1983,dd-mm-yyyy);insert into Student values(1008,赵婷婷,女,to_date(2-2-1984,dd-mm-yyyy);Create table Teacher(T# number(4),Tname varchar2(9) not null,constraint Teacher_T#_Pk primary key(T#);insert into Teac

4、her values(2001,李华);insert into Teacher values(2002,王力宏);insert into Teacher values(2003,叶平);insert into Teacher values(2004,王倩倩);insert into Teacher values(2005,李莉莉);Create table Course(C# number(3),Cname varchar2(12) not null,T# number(4),constraint Course_C#_Pk primary key(C#),constraint Course_T

5、#_Fk foreign key (T#) references Teacher(T#);insert into Course values(001,企业管理,2003);insert into Course values(002,马克思,2001);insert into Course values(003,OO|&|UML,2002);insert into Course values(004,数据库,2004);insert into Course values(005,英语,2005);insert into Course values(006,大学语文,2005);insert in

6、to Course values(007,现代企业,2003);Create table SC(S# number(4),C# number(4),score number(3),constraint SC_S#_C#_Pk primary key(S#,C#),constraint Sc_S#_Fk foreign key (S#) references Student(S#),constraint Sc_C#_Fk foreign key (C#) references Course(C#);insert into SC values(1001,1,87);insert into SC v

7、alues(1001,3,66);insert into SC values(1001,2,77);insert into SC values(1001,5,45);insert into SC values(1001,7,67);insert into SC values(1002,4,26);insert into SC values(1002,1,86);insert into SC values(1003,3,67);insert into SC values(1003,2,97);insert into SC values(1003,5,67);insert into SC valu

8、es(1004,5,78);insert into SC values(1004,3,67);insert into SC values(1004,4,49);insert into SC values(1005,4,76);insert into SC values(1005,2,84);insert into SC values(1005,3,35);insert into SC values(1006,3,54);insert into SC values(1006,2,76);insert into SC values(1006,1,56);insert into SC values(

9、1007,1,56);insert into SC values(1007,7,88);insert into SC values(1007,2,89);insert into SC values(1007,3,57);insert into SC values(1007,4,99);insert into SC values(1007,5,85);insert into SC values(1008,1,76);insert into SC values(1008,2,44);insert into SC values(1008,4,87);insert into SC values(100

10、8,5,33);insert into SC values(1002,6,65);insert into SC values(1002,7,90);insert into SC values(1006,6,67);问题： 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分的同学的学号和平均

11、成绩； select S#,avg(score) from sc group by S# having avg(score) 60; 3、查询所有同学的学号、姓名、选课数、总成绩； 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

12、李%; 5、查询没学过“叶平”老师课的同学的学号、姓名； select Student.S#,Student.Sname from Student where S# not in (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 St

13、udent.S#=SC.S# and SC.C#=001and exists( Select * from SC SC_2 where SC_2.S#=SC.S# and SC_2.C#=002); -exists 是Oracle sql中的一个函数。表示是否存在符合某种条件的记录。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 T

14、eacher.Tname=叶平 group by S# having count(SC.C#)=(select count(C#) from Course,Teacher where Teacher.T#=Course.T# and Tname=叶平); 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.Sname from Student,SC where Student.S#=SC.S# group by Student.S#,Student.Sname having count(C#) (select count(C#) fr