实用操作

https://blog.csdn.net/u012453843/article/details/53330926

1、查询“001”课程比“002”课程成绩高的所有学生的学号;

分析：先查询001课程学生学号和成绩 为a 表

再查询002课程学生学号和成绩 为b表

再查询a表中成绩比b表中成绩好的同一位同学

 

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、查询平均成绩大于60分的同学的学号和平均成绩; 

分析：平均成绩用函数avg();

按照学生进行汇总后计算平均成绩，分类聚合操作 group by snum

对分类聚合后的平均成绩结果进行条件筛选。having avg(score)>60

SELECT snum ,avg(score)

from tbl_score

GROUP BY snum

having avg(score)>60;

 

3、查询所有同学的学号、姓名、选课数、总成绩; 

分析：左连接：需要获取学号所对应的姓名，通过外连接来获取

select s.snum, s.sname, count(sc.cnum), sum(sc.score)

from tbl_student s

LEFT OUTER JOIN tbl_score sc

on s.snum = sc.snum

GROUP BY s.snum,s.sname;　　

 

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、查询姓“李”的老师的个数; 

分析：表：tbl_teacher

姓李：like '李%'

select count(tname)

from tbl_teacher

where tname

like '李%';

 

5、查询没学过“张小兵”老师课的同学的学号、姓名; 

分析：表:tbl_student tbl_score tbl_teacher

先查询学过张小兵老师课的学生的学号

再查询在表tbl_student中不包括以上学号的学生姓名和学号

select s.sname,s.snum

from tbl_student s

where s.snum

not in (

select sc.snum

from tbl_score sc, tbl_teacher t

where sc.cnum = t.tnum

and t.tname = '张小兵');

 

6、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名; 

分析:表：tbl_score tbl_student

先查出tbl_score中所有学过002课程的信息作为exists的条件语句

SELECT s.sname,s.snum

from tbl_student s, tbl_score sc

where s.snum= sc.snum

and sc.cnum='001'

and exists (

select *

from tbl_score as sc_2

where sc_2.cnum = '002'

);

 

SELECT s.sname,s.snum

from tbl_student s, tbl_score sc

where s.snum= sc.snum

and sc.cnum='001'

and sc.snum in (

select sc_2.snum

from tbl_score as sc_2

where sc_2.cnum = '002'

);

 

　11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名; 

　select S#,Sname from Student,SC

where Student.S#=SC.S#

and C# in （select C# from SC where S#='1001'）;

 

select DISTINCT s.snum,s.sname

from tbl_student s,tbl_score sc

where s.snum=sc.snum

and cnum in(

select sc.cnum

from tbl_score sc

where sc.snum='12250404');

 

10、查询没有学全所有课的同学的学号、姓名; 

分析：表：tbl_student tbl_score tbl_courses

按同一同学进行分类聚合操作，过滤出所学课程数小于总课程数的学生

查询出总的课程数

 

select s.snum,s.sname

from tbl_student s, tbl_score sc

where s.snum=sc.snum

GROUP BY s.snum,s.sname

HAVING count(cnum)<(

 

select count(cnum)

from tbl_courses);

 

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);是，

 

 

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

分析：找到张平老师教的课程的平均成绩

update xx set where xxxxx 进行更新

select avg(score)

from tbl_score sc ,tbl_courses c,tbl_teacher t

where c.technum=t.tnum

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

 

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

select s.snum

from tbl_student s,tbl_score sc

where sc.cnum

in (

select cnum

from tbl_score

where snum='12250404')

GROUP BY s.snum

having count(*)=(

select count(*)

from tbl_score

where snum = '12250404');

 

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

 

查询两门以上不及格课程的同学的学号及其平均成绩 

select S#,avg(isnull(score,0))

from SC

where S# in (select S#

from SC

where score <60

group by S#

having count(*)>2)

group by S#;