SQL语句练习

---------------------------------------------
---------------------------------------------
--1． 选修了java课程的所有2001级的学生姓名
select sname
from students
where grade = 2001 and sid in (
 select b.sid
 from choices,students b,courses
 where choices.cid = courses.cid and courses.cname = 'java' and choices.sid = b.sid
)
---------------------------------------------
---------------------------------------------
---2 请列出每门课程的平均分
select avg(score)
from choices
group by cid
---------------------------------------------
---------------------------------------------
--3． 教授java课程的老师中工资最高的老师姓名
select tname
from teachers
where salary = (
 select max(salary)
 from teachers
 where tid in (
  select tid
  from choices,courses
  where courses.cname = 'java' and choices.cid = courses.cid
 )
)
---------------------------------------------
---------------------------------------------
--4． 找出有30个同一年级学生选修的课程名，不显示重复的课程名
select distinct cname
from courses
where cid in (
 select cid
 from choices join students on choices.sid = students.sid
 group by cid ,grade
 having count(*) = 30
)
---------------------------------------------
---------------------------------------------
--5． 找出给所有年级都上过课的老师姓名
select tname

from teachers as t

where not exists (
 select *
 from students as s1
 where not exists (
  select *
  from choices c ,students s2
  where c.tid = t.tid and c.sid = s2.sid and s2.grade = s1.grade
 )
)
---------------------------------------------
---------------------------------------------
--6． 找出选修课程最多的学生姓名
select sname
from students
where sid in (
 select sid
 from choices
 group by sid
 having count(*)  >= all (
  select count(*)
  from choices
  group by sid
 )
)
---------------------------------------------
---------------------------------------------
--7． 找出没有选修任何课程的学生姓名
select sname
from students
where sid not in (
 select sid
 from choices
)
---------------------------------------------
---------------------------------------------
--8． 请将教授java课程的老师的工资增加10元。
update teachers
set salary = salary + 10
where tid in (
 select choices.tid
 from choices,courses
 where choices.cid = courses.cid and courses.cname = 'java'
)
---------------------------------------------
---------------------------------------------
--9． 请将2001级选修c++课程的学生成绩置为空
update choices
set score = null
where sid in (
 select a.sid
 from choices a ,students,courses
 where a.sid = students.sid and students.grade = 2001 and courses.cname = 'c++'
   and a.cid = courses.cid and a.cid = choices.cid
)
---------------------------------------------
---------------------------------------------
--10． 请找出又教授java又教授c++的老师姓名。
select tname
from teachers
where tid in (
 select a.tid
 from choices a,choices b,courses x,courses y
 where x.cname = 'java' and a.cid = x.cid and y.cname = 'c++' and b.cid = y.cid
  and a.tid = b.tid
)
---------------------------------------------
---------------------------------------------
--11． 找出只教了一门课的老师姓名
select tname
from teachers
where not exists (
 select *
 from choices a ,choices b
 where teachers.tid= a.tid and a.tid = b.tid and a.cid != b.cid

)
---------------------------------------------
---------------------------------------------
---12 请找出没有选修java的学生姓名
select sname
from students
where sid not in (
 select  choices.sid
 from choices ,courses
 where courses.cname = 'java' and choices.cid = courses.cid
)
---------------------------------------------
---------------------------------------------
---13 请找出没有成绩的学生姓名和课程名
select sname ,cname,score
from students,courses,choices a
where a.score is null and a.sid = students.sid and a.cid = courses.cid
---------------------------------------------
---------------------------------------------

（转自MCS知识社区  longt）