一点sql题的实现

//学生表
create TABLE student(
sno int(10) primary key,
sname varchar(10),
sdept varchar(10),
sage int(3)
)
inSERT INTO student VALUES(1,'aa','yw',19),(2,'bb','sx',20),(3,'cc','yy',22),(4,'dd','yw',17)
inSERT INTO student VALUES(5,'ee','yw',19),(6,'xx','sx',20),(7,'gg','yy',22)
//课程表
CREATE TABLE coures(
cno int(10) primary key,
cname varchar(10)
)
inSERT INTO coures VALUES(1,'yuwen'),(2,'shuxue')
//选课表
CREATE TABLE sc(
sno int(10) references student(sno),
cno int(10) references coures(cno),
grade int(3),
  primary key(sno,cno)
)
inSERT INTO sc VALUES(1,1,60),(2,1,76),(4,1,79),(1,2,90),(3,2,80)
inSERT INTO sc VALUES(2,2,86)

//1 查询选修课名称为'shuxue'的学员的学号和姓名
SELECT sno,sname FROM student
WHERE sno IN (SELECT sno FROM coures,sc WHERE coures.cno = sc.cno AND cname='shuxue');
//2 查询课程号为2的学员的姓名和所属单位
SELECT sno,sname,sdept FROM student
WHERE sno IN (SELECT sno FROM sc WHERE sc.cno=2)
SELECT student.sno,sname,sdept FROM student,sc
WHERE sc.cno=2 AND student.sno=sc.sno
//3 查找没有选修'shuxue'的学员的姓名和单位
SELECT sno,sname,sdept FROM student
WHERE sno NOT IN (SELECT sno FROM sc,coures WHERE sc.cno = coures.cno AND coures.cname='shuxue')
SELECT sno,sname,sdept FROM student WHERE sno NOT IN(SELECT sno FROM sc WHERE cno=2)
//4 查找出两门课程都选修的学员
SELECT sno,sname,sdept FROM student
WHERE sno in (SELECT sno FROM sc
     right join coures
     on sc.cno=coures.cno
     GROUP BY sno
     HAVING count(*)=(SELECT count(*) FROM coures)
     )
    
    //得到选修了所有课程的学生号
   SELECT sno FROM sc
        right join coures
        on sc.cno=coures.cno
        GROUP BY sno
        HAVING count(*)=(SELECT count(*) FROM coures)
//5 查询选修课的人数
SELECT count(distinct sno) FROM sc
//6 选修课超过两门的学员（包括两门）
SELECT sno,sname,sdept FROM student
WHERE sno in (SELECT sno FROM sc GROUP BY sno HAVING count(distinct cno)>=2)
************************************************************************************
CREATE TABLE coures2(
cno int(10),
cname varchar(10),
cteacher varchar(10)
)
INSERT INTO coures2 VALUES(1,'yuwen','zhangsan'),(2,'shuxue','lisi')
CREATE TABLE cs2(
sno int(10) references student(sno),
cno int(10) references coures2(cno),
grade int(3)
)
inSERT INTO cs2 VALUES(1,1,60),(1,2,90),
       (4,1,79),
       (3,2,80)
inSERT INTO cs2 VALUES(2,1,76),(2,2,86)
inSERT INTO cs2 VALUES(5,1,54),(5,2,47),(6,1,86),(6,2,16)
inSERT INTO cs2 VALUES(7,1,16)
//1 查找没有选修'lisi'老师课的学员
   SELECT cno FROM coures2 WHERE cteacher!='lisi'
   SELECT sno FROM cs2 WHERE cno IN (SELECT cno FROM coures2 WHERE cteacher='lisi')
   
SELECT student.sno,sname,sdept FROM student,cs2
WHERE student.sno=cs2.sno AND cs2.sno NOT IN(SELECT sno FROM cs2 WHERE cno IN (SELECT cno FROM coures2 WHERE cteacher='lisi'))
//2 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩
SELECT s.sno,s.sname,s.sdept,avg(cs2.grade)
FROM student s,cs2,(SELECT sno FROM cs2 WHERE grade<60 GROUP BY sno HAVING count(distinct cno)>=2) a
WHERE s.sno=a.sno AND cs2.sno=a.sno
GROUP BY s.sno,s.sname
SELECT sno FROM cs2 WHERE grade<60 GROUP BY sno HAVING count(distinct cno)>=2
//3 列出既学过'shuxue'，又学过'yuwen'的所有学生姓名
SELECT student.sno,student.sname,student.sdept
FROM student,(SELECT cs2.sno FROM cs2,coures c WHERE cs2.cno=c.cno AND c.cname IN ('shuxue','yuwen') GROUP BY sno HAVING count(distinct cs2.cno)=2) a
WHERE student.sno = a.sno
SELECT cs2.sno FROM cs2,coures c WHERE cs2.cno=c.cno AND c.cname IN ('shuxue','yuwen') GROUP BY sno HAVING count(distinct cs2.cno)=2
//4 列出'shuxue'成绩比'yuwen'高的学生
SELECT cs21.sno sno,cs21.cno cno,c1.cname cname,cs21.grade grade
FROM cs2 cs21,coures2 c1
WHERE (cs21.cno=c1.cno AND c1.cname='yuwen')

SELECT cs22.sno sno,cs22.cno con,c2.cname cname,cs22.grade  grade
FROM cs2 cs22,coures2 c2
WHERE (cs22.cno=c2.cno AND c2.cname='shuxue')

SELECT a.sno,a.grade as "yumen",b.grade as "shuxue"
FROM (SELECT cs21.sno sno,cs21.cno cno,c1.cname cname,cs21.grade grade
  FROM cs2 cs21,coures2 c1
  WHERE (cs21.cno=c1.cno AND c1.cname='yuwen') ) a,
(SELECT cs22.sno sno,cs22.cno con,c2.cname cname,cs22.grade  grade
  FROM cs2 cs22,coures2 c2
  WHERE (cs22.cno=c2.cno AND c2.cname='shuxue') ) b
WHERE a.grade<b.grade AND a.sno=b.sno
**********************************************************
//将对应id的age值赋给grade
CREATE TABLE a(id int(3), grade int(3))
CREATE TABLE b(id int(3), age int(3))

UPDATE a,b SET a.grade=(SELECT age FROM b WHERE a.id=b.id)