sql面试题一 学生成绩

原帖链接:http://topic.csdn.net/u/20081020/15/1ABF54D0-F401-42AB-A75E-DF90027CEBA0.html

表架构

Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表

建表语句 

CREATE TABLE student 
  ( 
     s#    INT, 
     sname nvarchar(32), 
     sage  INT, 
     ssex  nvarchar(8) 
  ) 

CREATE TABLE course 
  ( 
     c#    INT, 
     cname nvarchar(32), 
     t#    INT 
  ) 

CREATE TABLE sc 
  ( 
     s#    INT, 
     c#    INT, 
     score INT 
  ) 

CREATE TABLE teacher 
  ( 
     t#    INT, 
     tname nvarchar(16) 
  ) 

插入测试数据语句 

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 into SC 
 select 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

问题

1.查询“语文”课程比“数学”课程成绩高的所有学生的学号；

2.查询平均分大于60的学生的学号和平均分

3.查询所有学生的学号，姓名，选课数，总分

4.查询姓叶的老师的个数

5.查询没学过“叶平”老师课的同学的学号、姓名；

6.查询学过“语文”并且也学过“数学”课程的同学的学号、姓名；

7.查询学过“叶平”老师所教的所有课的同学的学号、姓名；
8.查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名；
9.查询所有课程成绩小于60分的同学的学号、姓名；
10.查询没有学全所有课的同学的学号、姓名；
11.查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
12.查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名；
13.把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
14.查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
15.删除学习“叶平”老师课的SC表记录；
16.向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2、课的平均成绩；
17.按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分
18.查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分
19.按各科平均成绩从低到高和及格率的百分数从高到低顺序
20.查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004）

答案

 

--1.
select a.S# from 
(select s#,score from SC where C#=(select C# from Course where Cname = '语文')) a,
(select s#,score from SC where C#=(select C# from Course where Cname = '数学')) b 
 where a.score>b.score and a.s#=b.s#; 
--2.
select S#, avg(score) 平均分 from  SC group by S# having avg(score) > 60
--3.
select sc.S# 学号,s.Sname 姓名,Count(C#) 课程数,Sum(score) 总成绩 from SC sc  left join Student s on s.S# = sc.S# group by sc.S#,s.Sname order by sc.S#
--4.
select count(*) from teacher where Tname like '叶%'
--5.
select s.S#,s.Sname from Student s where s.S# not in (select S# from SC where C# = (select C# from Course where T# = (select T# from Teacher where Tname = '叶平')))   
--或者
select s.S#,s.Sname from Student s where s.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
select Student.S#,Student.Sname from Student,SC where 
  Student.S#=SC.S# and SC.C#='001' and exists( Select * from SC as SC_2 where SC_2.S#=SC.S# and SC_2.C#='002'); 
--7.  自己写的
select Student.S#,Student.Sname from Student where 
   (select count(C#) from SC where SC.S# = Student.S# and SC.C# in  (select C# from Course,Teacher where Course.T# = Teacher.T# and Tname = '叶平'))
 = (select count(C#) from Course,Teacher where Course.T# = Teacher.T# and Tname = '叶平')
--参考答案
  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 Teacher.Tname='叶平' 
     group by S# having count(SC.C#)=(select count(C#) from Course,Teacher  where Teacher.T#=Course.T# and Tname='叶平'))
--8.自己写的
select Student.S#,Student.Sname from Student where (select score from SC where SC.S# = Student.S# and C# = 2)  >  (select score from SC where SC.S# = Student.S# and C# = 1) 
--参考答案
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 <score; 
--9.自己写的
select Student.S#,Student.Sname from Student where (select count(*) from SC where SC.S# = tudent.S# and SC.score > 80)  = 0
--参考答案
select S#,Sname from Student where S# not in (select Student.S# from Student,SC where student.S#=SC.S# and score>80); 
--10自己写的
select Student.S#,Student.Sname from Student  where (select count(*) from SC where SC.S# = Student.S#) <  (select count(*) from Course)
--参考答案
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#) from Course); 
--11.
select Student.S#,Student.Sname from Student  where select count(*) from SC where SC.S# = Student.S# and SC.C# in (select C# from SC where S# = 1) ) > 1 and Student.S# <>1;
--12.
select Student.S#,Student.Sname from Student  where (select count(*) from SC where SC.S# = Student.S# and SC.C# in (select C# from SC where S# = 1) ) >= (select count(C#) from SC where S# = 1) and Student.S# <> 1
--13.自己写
update SC set score = (select avg(score) from SC where C# = (select  C# from Course where T# = (select T# from Teacher where Tname = '叶平')) ) 
  where C# = (select  C# from Course where T# = (select T# from Teacher where Tname = '叶平'))
--参考答案
 update SC set score=(select avg(SC_2.score) from SC SC_2 where SC_2.C#=SC.C# )
 from Course,Teacher where Course.C#=SC.C# and Course.T#=Teacher.T# and Teacher.Tname='叶平' 
--18
select C#,Max(score),min(score) from SC group by C#