Student(S#,Sname,Sage,Ssex) 学生表
Course(C#,Cname,T#) 课程表
SC(S#,C#,score) 成绩表
Teacher(T#,Tname) 教师表
 
create table Student(S# varchar(20),Sname varchar(10),Sage int,Ssex varchar(2))
前面加一列序号：
if
exists(select table_name from information_schema.tables
  where table_name='Temp_Table')
drop table Temp_Table
go
select 排名=identity(int,1,1),* INTO  Temp_Table    from Student
go
select * from Temp_Table
go
 
drop database [ ]  --删除空的没有名字的数据库
问题：
1、查询“”课程比“”课程成绩高的所有学生的学号；
  select a.S# from (select s#,score from SC where C#='001') a,(select s#,score
  from SC where C#='002') 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 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 '李%';
 
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、查询学过“”并且也学过编号“”课程的同学的学号、姓名；
  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 S#,Sname
  from Student
  sql查询语句实例大全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 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 S#,Sname
  from Student
  where S# not in (select Student.S# from Student,SC where S.S#=SC.S# and score>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#) from Course);
 
11、查询至少有一门课与学号为“”的同学所学相同的同学的学号和姓名；
    select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#='1001';
 
12、查询至少学过学号为“”同学所有一门课的其他同学学号和姓名；
    select distinct SC.S#,Sname
    from Student,SC

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。