MySQL 笔试题

表和表间关系图：

题目及答案：（未完，待续。。。）

-- 1、课程1成绩大于课程2成绩的学生学号
select S,score from sc where c=1;
select S,score from sc where c=2;

select a.s
from 
(select S,score from sc where c=1) a,
(select S,score from sc where c=2) b
where a.score > b.score and a.s=b.s;
-- 2、查询平均成绩大于80 学生学号和平均成绩
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 JOIN sc on student.S=sc.S
group by student.s;
-- 4、查询姓“李”的老师的个数; 
select count(distinct(Tname))
from teacher
where Tname like '%李%';
-- 5、查询没学过“陈”老师课的同学的学号、姓名; 
select SC.s from teacher,course,sc where Tname like '%陈%' and teacher.T=course.T and course.c=sc.c;

select student.s,student.sname
from student
where student.s not in (select distinct(SC.s) from teacher,course,sc where Tname like '%陈%' and teacher.T=course.T and course.c=sc.c);

-- 6、查询学过“1”并且也学过编号“3”课程的同学的学号、姓名; 
-- 方法1
select student.s,student.sname
from student
where student.s in (
select sc_2.s as SSS
from sc,(select s from sc where sc.C=3) sc_2
where sc.s=sc_2.s and sc.c=1);
-- 方法2
select Student.S,Student.Sname 
from Student,SC 
where Student.S=SC.S and SC.C=1 and exists( select * from SC as SC_2 where SC_2.S=SC.S and SC_2.C=3);
-- exists 在乎的是是否有结果集返回
--  not exists


-- 7、查询学过“4宁老师”老师所教的所有课的同学的学号、姓名; 
select sc.s from sc,teacher,course where teacher.Tname='4宁老师' and course.T=teacher.T and sc.C=course.c;
-- 方法1
select student.s,student.sname
from student
where student.s in (select sc.s from sc,teacher,course where teacher.Tname='4宁老师' and course.T=teacher.T and sc.C=course.c);
-- 方法2
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='4宁老师' group by S having count(SC.C)=(select count(C) from Course,Teacher where Teacher.T=Course.T and Tname='4宁老师'));

-- 8、查询课程编号“2”的成绩比课程编号“1”课程低的所有同学的学号、姓名; 
select s,score from sc where sc.c=1;
select s,score from sc where sc.c=2;
-- 方法1
select s,sname
from student
where student.s in(
select s1.s
from (select s,score from sc where sc.c=1) s1,(select s,score from sc where sc.c=2) s2
where s1.s=s2.s and s1.score > s2.score );

-- 方法2
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=2) score2 from Student,SC where Student.S=SC.S and C=1) s_2 
where score2 < score;

-- 9、查询所有课程成绩小于60分的同学的学号、姓名; 
select distinct(s) from sc where score <= 60; 

select S,Sname
from student
where student.S in (select distinct(S) from sc where score < 60);


-- 10、查询没有学全所有课的同学的学号、姓名; 
select count(c) from course ;
select s from sc GROUP BY s having count(c) < (select count(c) from course );
-- 方法1
select s,sname
from student
where student.s in (select s from sc GROUP BY s having count(c) < (select count(c) from course ));
-- 方法2
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);