多表关联查询练习

第一题

–1、查询名人表中性别是男的作者
select sname from celebrity where sex = ‘男’;

–2、查询静夜思的作者
select c.sname from celebrity c,works w,summary s where s.sid = c.sid and s.wid = w.wid and w.work = ‘静夜思’;

select c.sname From summary s INNER JOIN celebrity c on s.sid = c.sid INNER JOIN works w on s.wid = w.wid where w.work = ‘静夜思’;

–3、查询李白和杜甫的年纪
select sname,sage from celebrity where sname in(‘李白’,‘杜甫’);

–4、查询名人表中的男女人数
select count(*),sex from celebrity group by sex;

–5、查询白居易写的作品名称和对应的名句
select c.sname,w.WORk,s.sassess from celebrity c,works w,summary s where c.sid = s.sid and w.wid = s.wid and c.sname = ‘白居易’;

select c.sname,w.work,s.sassess from summary s INNER JOIN celebrity c on s.sid = c.sid INNER JOIN works w on s.wid = w.wid where c.sname = ‘白居易’;

–6、查询年纪在25-30岁之间的个数
select count(*) from celebrity where sage between 25 and 30;

–7、查询名人表中年纪最小两条的数据信息
select * from celebrity ORDER BY sage limit 0,2;

–8、查询李白的作品名称、名句和年纪
select c.sname,w.work,s.sassess,c.sage from celebrity c,works w,summary s where s.sid = c.sid and s.wid = w.wid and c.sname = ‘李白’;

–9、将作品为望岳的朝代修改为北宋
update works set dynasty = ‘北宋’ where work=‘望岳’;

–10、新增名人表中的一个作者王维，年纪25，性别男
insert into celebrity values(7,‘王维’,25,‘男’);

---

第二题

–1、查询sc表对应何昊老师授课的女生信息
select s.* from student_2 s,course c,studentcourse sc where s.sno = sc.sno and c.cno = sc.cno and c.cteacher = ‘何昊’ and s.sex = ‘女’;

–2、找出没有选修何老师课程的所有学生姓名
–首先查出选了何老师课程的所有学生
select s.sno,s.sname from student_2 s,course c,studentcourse sc where s.sno = sc.sno and c.cno = sc.cno and c.cteacher = ‘何昊’;
–使用子查询排除选了何老师课程的学生
select * from student_2 where sno not in (select s.sno from student_2 s,course c,studentcourse sc where s.sno = sc.sno and c.cno = sc.cno and c.cteacher = ‘何昊’);

–3、列出有不及格课程（成绩小于60）的学生姓名
select s.sname,c.cname,sc.scgrade from student_2 s,course c,studentcourse sc where s.sno = sc.sno and c.cno = sc.cno and sc.scgrade < 60

---

第三题

–1、查询刘良誉老师带的学生的姓名和对应学科
select s.name,t.class from teacher t,student s where t.stu_id = s.id and t.name = ‘刘良誉’;

select s.name,t.class from teacher t inner join student s on t.stu_id = s.id where t.name = ‘刘良誉’;

–2、查询刘良誉老师带的学生，成绩及格的学生姓名
select s.name from teacher t,student s where t.stu_id = s.id and t.name = ‘刘良誉’ and s.score > 60;

select s.name from teacher t INNER JOIN student s on t.stu_id = s.id where t.name = ‘刘良誉’ and s.score > 60;

–子查询
select name from student where id in (select stu_id from teacher where name = ‘刘良誉’) and score > 60;

–3、查询赵六学生的老师姓名和对应的学科名称
select s.name,t.name,t.class from student s,teacher t where s.id = t.stu_id and s.name = ‘赵六’;

select s.name,t.name,t.class from student s inner join teacher t on s.id = t.stu_id where s.name = ‘赵六’;