mysql面试题 学生成绩表_sql面试题（学生表_课程表_成绩表_教师表）

原帖链接:http://bbs.csdn.net/topics/280002741

表架构

Student(S#,Sname,Sage,Ssex)   学生表

Course(C#,Cname,T#) 　　　　课程表

SC(S#,C#,score) 　　　　　　  成绩表

Teacher(T#,Tname) 　　　　　 教师表

建表语句

CREATE TABLEstudent

(

s#INT,

snamenvarchar(32),

sageINT,

ssexnvarchar(8)

)CREATE TABLEcourse

(

c#INT,

cnamenvarchar(32),

t#INT)CREATE TABLEsc

(

s#INT,

c#INT,

scoreINT)CREATE TABLEteacher

(

t#INT,

tnamenvarchar(16)

)

View Code

插入测试数据语句

insert into Student select 1,N'刘一',18,N'男' union all

select 2,N'钱二',19,N'女' union all

select 3,N'张三',17,N'男' union all

select 4,N'李四',18,N'女' union all

select 5,N'王五',17,N'男' union all

select 6,N'赵六',19,N'女'

insert into Teacher select 1,N'叶平' union all

select 2,N'贺高' union all

select 3,N'杨艳' union all

select 4,N'周磊'

insert into Course select 1,N'语文',1 union all

select 2,N'数学',2 union all

select 3,N'英语',3 union all

select 4,N'物理',4

insert intoSCselect 1,1,56 union all

select 1,2,78 union all

select 1,3,67 union all

select 1,4,58 union all

select 2,1,79 union all

select 2,2,81 union all

select 2,3,92 union all

select 2,4,68 union all

select 3,1,91 union all

select 3,2,47 union all

select 3,3,88 union all

select 3,4,56 union all

select 4,2,88 union all

select 4,3,90 union all

select 4,4,93 union all

select 5,1,46 union all

select 5,3,78 union all

select 5,4,53 union all

select 6,1,35 union all

select 6,2,68 union all

select 6,4,71

View Code

问题

问题：1、查询“001”课程比“002”课程成绩高的所有学生的学号；select a.S# from (select s#,score from SC where C#='001') a,(selects#,scorefrom SC where C#='002') bwhere a.score>b.score and a.s#=b.s#;2、查询平均成绩大于60分的同学的学号和平均成绩；select S#,avg(score)fromscgroup 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 byStudent.S#,Sname4、查询姓“李”的老师的个数；select count(distinct(Tname))fromTeacherwhere Tname like '李%';5、查询没学过“叶平”老师课的同学的学号、姓名；selectStudent.S#,Student.SnamefromStudentwhere S# not in (select distinct( SC.S#) from SC,Course,Teacher where S