insert into sc (sid,cid,score) values (select student.sid from student
where student.sid in (select sc.sid from sc where sc.cid !='002'),
'001',select avg(sc.score) from sc where sc.cid='002'
group by sc.cid)
sc 是学生成绩表 条件是 --16、向SC表中插入一些记录,这些记录要求符合以下条件:
--(1)没有上过编号“002”课程的同学学号、
--(2)插入2号课的平均成绩;
------解决思路----------------------
insert into sc (sid,cid,score)
SELECT s.sid,'002',T.score
FROM student s
LEFT JOIN sc ON s.sid=sc.sid AND sc.cid='002'
CROSS JOIN(SELECT AVG(score)score FROM sc WHERE cid='002')T
WHERE sc.sid IS NULL