经典SQL语句大全以及50个常用的sql语句 (7)

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、查询课程编号“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 <score;

9、查询所有课程成绩小于60分的同学的学号、姓名;

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、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名;

select S#,Sname from Student,SC where Student.S#=SC.S# and C# in select C# from SC where S#=\'1001\';

12、查询至少学过学号为“001”同学所有一门课的其他同学学号和姓名;

select distinct SC.S#,Sname

from Student,SC

where Student.S#=SC.S# and C# in (select C# from SC where S#=\'001\');

13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩;

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=\'叶平\');

14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名;

select S# from SC where C# in (select C# from SC where S#=\'1002\')

group by S# having count(*)=(select count(*) from SC where S#=\'1002\');

15、删除学习“叶平”老师课的SC表记录;

Delect SC

from course ,Teacher 

where Course.C#=SC.C# and Course.T#= Teacher.T# and Tname=\'叶平\';

16、向SC表中插入一些记录,这些记录要求符合以下条件:没有上过编号“003”课程的同学学号、2

号课的平均成绩;

Insert SC select S#,\'002\',(Select avg(score)

from SC where C#=\'002\') from Student where S# not in (Select S# from SC where C#=\'002\');

内容版权声明:除非注明,否则皆为本站原创文章。

转载注明出处:https://www.heiqu.com/zggszd.html