一，简介
本博客收录了，一些常见的sql语句的写法，值得借鉴，学习，其中有些是比较复杂的，
1.sql数据
有关的sql测试数据在以下链接
链接：http://download.csdn.net/detail/k_122/9923538，免费的
2.题目以及答案
*1、查询课程1的成绩 比 课程2的成绩 高 的所有学生的学号.
SELECT a.sno ,a.score,b.score from (SELECT sno ,score from j_score where cno = '1') a ,
(SELECT sno ,score from j_score where cno = '2') b
WHERE a.score > b.score and a.sno = b.sno
2、查询平均成绩大于60分的同学的学号和平均成绩
SELECT sno,AVG(score) FROM j_score GROUP BY sno HAVING AVG(score)>60
3、查询所有同学的学号、姓名、选课数、总成绩
SELECT s.sno,s.sname,COUNT(sc.cno),SUM(sc.score) FROM j_student s LEFT OUTER JOIN
j_score sc ON s.sno=sc.sno GROUP BY s.sno,s.sname
4、查询姓“李”的学生的个数；
SELECT COUNT(*) FROM j_student WHERE sname LIKE "李%";
*5、查询没学过“叶平”老师课的同学的学号、姓名；
SELECT sno,sname FROM j_student WHERE sno NOT IN(
SELECT DISTINCT sno FROM j_score WHERE cno IN
(SELECT cno FROM j_course WHERE tno IN
(SELECT tno FROM j_teacher WHERE tname='叶平'))
)
*6、查询同时学过课程1和课程2的同学的学号、姓名
select s.sno, s.sname from j_student s
where s.sno in (select sc.sno from j_score sc
where sc.cno = 1)
and s.sno in (select sno from j_score where cno = 2)
7、查询学过“叶凡”老师所教所有课程的所有同学的学号、姓名
select s.sno,s.sname from j_student s
where s.sno in (select distinct(sc.sno) from
j_score sc,j_course c, j_teacher t
where sc.cno=c.cno and t.tno=c.tno
and t.tname='叶平')
8、查询 课程编号1的成绩 比 课程编号2的成绩 高的所有同学的学号、姓名
SELECT a.sno, STU.sname,a.score,b.score from j_student stu, (SELECT sno ,score from j_score where cno = '1') a ,
(SELECT sno ,score from j_score where cno = '2') b
WHERE a.score > b.score and a.sno = b.sno and stu.sno = b.sno
9、查询所有课程成绩小于60分的同学的学号、姓名
SELECT s.sno,s.sname FROM j_student s WHERE s.sno NOT IN
(SELECT s.sno FROM j_student s LEFT JOIN j_score sc ON s.sno=sc.sno WHERE sc.score>60 OR sc.score IS NULL)
10、查询所有课程成绩大于60分的同学的学号、姓名
SELECT s.sno,s.sname FROM j_student s WHERE s.sno NOT IN
(SELECT s.sno FROM j_student s LEFT JOIN j_score sc ON s.sno=sc.sno WHERE sc.score<60 OR sc.score IS NULL)
11、查询没有学全所有课的同学的学号、姓名
SELECT s1.sno FROM (SELECT sc.sno,sc.cno AS course1 FROM j_score sc GROUP BY sc.sno) s1,
(SELECT COUNT(*) AS course2 FROM j_course) s2
WHERE course1!=course2
12、查询至少有一门课程 与 学号为1的同学所学课程 相同的同学的学号和姓名
SELECT DISTINCT s.sno,s.sname from j_student s ,j_score sc
WHERE s.sno <> 1 and s.sno = sc.sno and sc.cno in (SELECT cno from j_score where sno = 1)
13、查询和2号同学学习的课程完全相同的其他同学学号和姓名
SELECT s.sno ,s.sname from j_score sc,j_student s
WHERE s.sno <> 2 and s.sno = sc.sno
GROUP BY s.sno ,s.sname
HAVING SUM(sc.cno) = (SELECT SUM(cno) from j_score where sno =2)
15、查询各科成绩最高分和最低分：
以如下形式显示：课程号，最高分，最低分
select cno as 课程号, max(score) as 最高分, min(score) 最低分
from j_score group by cno
16、查询每门课程被选修的学生数
SELECT c.cname,COUNT(sc.sno) from j_course c LEFT JOIN j_score sc
on c.cno = sc.cno GROUP BY c.cname
17、查询出只选修了一门课程的全部学生的学号和姓名
SELECT s.sno ,s.sname,COUNT(sc.cno) from j_student s LEFT JOIN j_score sc
on s.sno = sc.sno GROUP BY s.sno,s.sname HAVING COUNT(sc.cno) = 1
18、查询同名同性学生名单，并统计同名人数
SELECT s.sname ,COUNT(s.sname) from j_student s ,
(SELECT sname,ssex from j_student )t
WHERE s.sname = t.sname and s.ssex = t.ssex
GROUP BY s.sname
19、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
select s.sname,cname, score
from j_student s,j_score sc,j_course c,j_teacher t
where s.sno=sc.sno and sc.cno=c.cno and c.tno=t.tno
and t.tname ='叶平'
and sc.score=(select max(score)from j_score where cno = C.cno)
20、查询不同课程成绩相同的学生的学号、课程号、学生成绩
select distinct A.sno, A.cno,B.score
from j_score A ,j_score B
where A.Score=B.Score and A.cno <>B.cno
order by B.score
21、查询每门课程成绩最好的前两名的学生ID
--以科目，按成绩给记录编号，然后取出编号<=2的，就是每科前两名
SELECT *
FROM j_score A
WHERE (SELECT COUNT(*)
FROM j_score B
WHERE B.cno = A.cno
AND B.score >= A.score) <= 2
ORDER BY A.cno, A.score DESC
22、检索至少选修了5门课程的学生学号
select sno from j_score group by sno having count(1) >= 5
23、查询没学过“叶平”老师讲授的任一门课程的学生姓名
select sno, sname from j_student
where sno not in(
select sno from j_score where cno in
(select a.cno from j_course a, j_teacher b where a.tno = b.tno and b.tname = '叶平'))
24、查询两门以上不及格课程的同学的学号及其平均成绩
select
sno,avg(score) 平均分,COUNT(sno) as 不及格课程
from j_score
where score<60
group by sno
having COUNT(sno)>2
25、查询最受欢迎的老师(选修学生最多的老师)
select cno 课程ID, count(cno) 选修人数
from j_score group by cno
having count(cno) in (
SELECT t.num from (select count(cno) num from j_score group by cno order by count(cno) desc LIMIT 0,1)t
)
order by 选修人数 desc