如何学习SQL

经典SQL练习题

查询Score表中的最高分的学生学号和课程号。（子查询或者排序）

SELECT sno,cno
  FROM score 
 WHERE degree =(SELECT MAX(degree) FROM score)

 select sno, cno 
 from score 
 order by degree desc 
 limit 1,1;

查询Score表中至少有5名学生选修的并以3开头的课程的平均分数。

SELECT AVG(degree),cno
  FROM score 
 WHERE cno LIKE '3%'
 GROUP BY cno 
HAVING COUNT(*)>=5

SELECT AVG(degree),cno 
  FROM score 
 GROUP BY cno 
HAVING (COUNT(1)>=5 AND cno LIKE '3%')

查询所有学生的Sname、Cno和Degree列。

-- 集合思维（优选）
SELECT a.sname,b.cno,b.degree
  FROM student a
  JOIN score b
    ON a.sno=b.sno
-- 过程思维        
SELECT (SELECT sname FROM student WHERE sno = sc.sno) sname ,cno ,degree 
  FROM score sc

-- My Ans
select sname, cno, degree 
from score 
left join student on student.sno = score.sno 
order by cno;

查询所有学生的Sno、Cname和Degree列。

-- 集合思维（优选）
SELECT b.sno,a.cname,b.degree
  FROM course a
  JOIN score b
    ON a.cno=b.cno

--注意Join和left join的区别，后者会对不匹配的match value返回NULL
select sno, cname, degree 
from score 
join course 
on course.cno=score.cno;

 select sno, cname, degree 
 from score 
 left join course 
 on course.cno=score.cno;

查询所有学生的Sname、Cname和Degree列。

-- 集合思维
select student.sname, course.cname, score.degree 
from score 
left join course on course.cno=score.cno 
join student on student.sno=score.sno;

-- 过程思维    
SELECT (SELECT Sname FROM Student WHERE student.Sno=Score.Sno) sname
      ,(SELECT Cname FROM course WHERE course.Cno=score.Cno) cname
      ,Degree 
  FROM score
  ```

6. 查询“95033”Class (not course)学生的平均分。

-- 集合思维（优选）
SELECT AVG(b.degree),b.cno
FROM student a
JOIN score b
ON a.sno=b.sno
WHERE a.class='95033'
GROUP BY cno

-- 过程思维
SELECT AVG(degree),Cno
FROM score
JOIN student
ON student.sno=score.sno
WHERE cno IN (SELECT cno FROM score WHERE class="95033")
GROUP BY cno

--My Ans
select avg(degree), cno
from score
where sno in (select sno from student where class=95033)
group by cno;

7. ** 假设使用如下命令建立了一个grade表：

create table grade (low int, upp int, rank_name char(1));

现查询所有同学的Sno、Cno和rank列

-- 集合思维（优选）
SELECT a.Sno,a.Cno,b.rank_name
FROM score a
JOIN grade b
ON a.degree>=b.low AND a.degree<=b.upp

-- My Ans
select a.sno, a.cno, a.degree,b.rank_name
from score a, grade b
where a.degree < b.upp and a.degree >= b.low;


8. 查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。

select * from score
where cno='3-105'
and degree > (select degree from score where cno='3-105' and sno=109);


9. ** 查询score中选学多门课程的同学中分数为非最高分成绩的记录。
创建了两个子表

--集合思维（优选）SELECT a.sno,a.degree,a.cno
FROM score a
LEFT JOIN (SELECT MAX(degree) degree,cno
FROM score
GROUP BY cno)b
ON a.cno = b.cno
WHERE a.degree != b.degree
AND a.cno IN(SELECT cno
FROM score
GROUP BY cno
HAVING COUNT(cno)>1)

10. 查询“张旭“教师任的学生成绩。

select sno, degree
from score
where cno in (select cno
from course
where tno = (select tno
from teacher
where tname='张旭'));
```
11. 查询选修某课程的同学人数多于5人的教师姓名。

select tname 
from teacher
join course on course.tno=teacher.tno 
where course.cno in 
(select cno from score group by cno having count(*) > 5);

SELECT tname
  FROM teacher
 WHERE tno=(SELECT tno
          FROM course a
          JOIN (SELECT COUNT(cno),cno
                 FROM score
                GROUP BY cno
               HAVING COUNT(cno)>5) b
                   ON a.cno=b.cno)

SELECT Tname 
  FROM teacher 
 WHERE Tno IN( SELECT Tno 
                 FROM course 
                WHERE Cno IN( SELECT Cno 
                                FROM score 
                               GROUP BY Cno 
                              HAVING COUNT(Cno)>5))

查询存在有85分以上成绩的课程Cno.

select distinct cno from score where degree > 85;

查询出“计算机系“教师所教课程的成绩表。

SELECT sno,cno,degree
  FROM score
 WHERE cno IN(SELECT cno
                FROM course a
                JOIN (SELECT tno FROM teacher WHERE depart = '计算机系') b
                  ON a.tno=b.tno)

-- My Ans
select sno, score.cno, degree 
from score 
join course 
on course.cno=score.cno 
where course.tno in 
(select tno from teacher where depart = "计算机系");

查询所有教师和同学的name、sex和birthday.
如果不改列名就会使用第一个select的列名

select sname as name, ssex as sex, sbirthday as birthday 
from student 
union 
select tname as name, tsex as sex, tbirthday as birthday 
from teacher;

** 查询成绩比该课程平均成绩低的同学的成绩表。

select sno, a.cno, degree 
from score a 
join 
(select cno, avg(degree) as avg_deg 
from score group by cno) b
on b.cno=a.cno 
where degree < b.avg_deg;

-- Amazing Ans
SELECT Sno,Cno,degree 
FROM Score a 
WHERE degree<(SELECT AVG(degree) FROM score b WHERE a.Cno=b.Cno)

查询至少有2名男生的班号。

select class, count(*) 
from student 
where ssex='男' 
group by class 
having count(*) >= 2;

查询不姓王的同学信息

select * from student where sname not like '王%';

根据生日返回年龄

select sname, (year(now()) - year(sbirthday)) age from student;

查询最高分同学的Sno、Cno和Degree列。

-- My Ans
 select c.sname, a.sno, a.cno, a.degree 
 from score a 
 join (select cno, max(degree) as max_deg 
from score group by cno) b 
on b.cno=a.cno 
join student c on c.sno=a.sno 
where a.degree=b.max_deg;

-- Simple Ans
select * from score where degree = (select max(degree) from score)

查询所有选修“计算机导论”课程的“男”同学的成绩表。

select * from score 
join course on course.cno=score.cno 
join student on student.sno=score.sno 
where course.cname='计算机导论' and student.ssex='男';

查询日期使用str_to_date (format)

select * from teacher where tbirthday = str_to_date('1972-05-05','%Y-%m-%d');

** 查询所有同学的学号、姓名、选课数、总成绩
mysql程序不知道outer table的返回值是否是一一对应的，因此不能直接group by sno (因为没有对于sname的group操作，合理的做法应该是同时对sno和sname做group by)

select b.sname, a.sno, count(*) as coursenum, sum(degree) as totaldegree 
from score a 
join student b on b.sno=a.sno 
group by b.sname, a.sno;

查询姓“李”的老师的个数；

select '李', count(*) as count from teacher where tname like '李%';

查询没有学过李诚老师课的同学学号

-- My Ans
select distinct a.sno, b.sname 
from score a 
join student b on b.sno=a.sno 
where a.sno not in 
(select sno from score 
where cno in 
(select cno from course 
where tno = 
(select tno from teacher where tname = '李诚')));

查询[所有]课程成绩小于70分的同学的学号、姓名；
考虑到where语句的判断每次都都是单判断，因此我们可以取反，where选取存在>70分课程的同学，然后排除这些同学

SELECT sno,Sname 
 FROM Student 
WHERE sno NOT IN (SELECT b.sno
                FROM score b 
               WHERE b.degree>60);

查询没有学全所有课的同学的学号、姓名
这个需要注意的是score里头的所有课程都可以在course看到，所以可以直接通过

select sid,count(cid) from sc group by sid having count(cid)<(select count(cid) from course);

如何学习SQL