sql 最基本联合查询

use tt
select * from student
select * from score
select * from course
select sid,score from score where cid='1'
select sid ,score from score where cid='2'
--查询1课程比2课程成绩高的所有学生学号
select a.sid from (select sid,score from score where cid='1') a,(select sid ,score from score where cid='2') b where a.score>b.score and a.sid=b.sid
---查询平均成绩大于60分的学生学号
select * from score
select sid 学号,avg(score)as average from score group by sid
having avg(score)>60
----------------------
--查询学生学号，姓名，总选课数，总成绩
select student.id 学号,Sname 姓名,count(score.cid) as 选课总数,sum(score) 总成绩
from student
inner join score
on student.id=score.sid
group by
student.id,Sname
----------------
select student.id,student.sname,count(score.cid),sum(score) from student
left join score
on student.id=score.sid
group by student.id,Sname
--查询姓李的老师有多少个,,
select count(distinct(tname)) from teacher where tname like '李%' as a
--------------------
--查询没有选修过叶平老师课程的学生学号和姓名
select student.id,student.Sname from student where
student.id not in(select distinct(score.sid)
from score,Course,Teacher
where  score.cid=Course.id and Teacher.id=Course.tid and Teacher.Tname='叶平')

-----------------------------------------
--查询既选修了1号课程，又选修了2号课程的同学
select Student.id,Student.Sname
from Student,Score where Student.id=score.sid and
score.cid='001'and
exists( Select * from score as SC_2 where SC_2.sid=score.sid and SC_2.cid='002');
-----------------------
--选修了叶平老师所有课程的同学学号，姓名
-----------------------
select student.id,Sname from Student
where student.id in
(select Sid  from SCore ,Course ,Teacher
where SCore.Cid=Course.id and Teacher.id=Course.tid
 and Teacher.Tname='叶平' group by sid
having count(SCore.cid)=
(select count(Course.id) from Course,Teacher  
where Teacher.id=Course.Tid and Tname='叶平'));

------------------------------------
--查询所有课程成绩小于60分的同学的学号、姓名；
--------------------------------------
select student.id,sname from student
where  student.id not in(
select student.id from student
inner join
score
on score.sid=student.id
where score>60)
------------------------------------------
--查询所有课程成绩小于60分的同学的学号、姓名；
  select Student.id,Sname
  from Student
  where Student.id not in (select Student.id from Student,SCore
 where Student.id=SCore.Sid and score>60);
-------------------------------------
--查询没有学全所有课程的学生
select student.id,sname
from student ,score
where student.id=score.sid
group by student.id,sname
having count(score.cid)<(select count(*) from course)
------------------------
--至少有一门课与学号1的学生选修的课程一样的其他同学的学号，姓名
-----------------------
select distinct score.sid,sname
from student,score
where student.id=score.sid
and score.cid in (select cid from score where sid='1' )
--查询学习两门课程的人数之差
select distinct
(select count(sid) as c from score where cid='1')-
(select count(sid) as d  from score where cid='3') as betweens  from score
--
--选修2课程也选修1课程的同学序号，姓名
select student.id ,sname from student where id in(
select a.sid from (select sid from score where cid='1') a,
(select sid from score where cid='2') b
where a.sid=b.sid)
--选修2课程也选修1课程的同学序号，姓名
select student.id,sname from student,score
where student.id=score.sid and score.cid='1'
and exists(select * from score as sc where sc.sid=score.sid and sc.cid='2')
--选修了叶平老师所有课程的学生学号，姓名
select student.id,sname from student
where id in (select sid from score,course,teacher
where score.cid=course.id and course.tid=teacher.id and
teacher.tname='叶平' group by score.sid having count(score.cid)
=(select count(course.id) from course ,teacher
where course.tid=teacher.id and teacher.tname='叶平'))
--选修2号课程比一号课程低的同学学号，姓名
select student.id,sname from student
where id in (select a.sid from  (select * from score where cid='2') a,
(select * from score where cid='1') b
where a.sid=b.sid and a.score<b.score)
--所有课程成绩小于60分的同学
select id,sname from student
where id not in(select id from student ,score
where score.sid=student.id and score.SCORE>60)
--查询没有学全所有课程的同学
select student.id,student.sname from student,score
where student.id=score.sid group by student.id,student.sname
having count(cid)<(select count(*) from course)
--选修了至少一门1号学员课程的其他同学
select distinct student.id,sname
from student,score
where student.id=score.sid
and score.cid in (select cid from score where sid='1' )
--
select * from score
select * from course
select * from teacher
select * from score where id
------叶平老师教受的没门课程的平均成绩
select cid ,avg(score) from teacher,score ,course where teacher.id=course.tid
and course.id=score.cid and teacher.tname='叶平'
group by cid
having cid=7
--case when 用法对其中某一列进行评价或标识
select sid ,cid,score=(case when (score<60) then '不及格'
                        when (score <70 and score >60) then '良好'
                        when (score <80 and score >70) then '优越'
                        when (score <90 and score >80) then '棒'
                        else  '胜利' end),
(select count(*) from score) as totals from score
---------------
--查询和2号学院学习课程完全一样的学生

----行列转换sql-----------
http://blog.csdn.net/xys_777/article/details/5685953
http://blog.sina.com.cn/s/blog_4cca663f0100abfv.html