SQL查询练习
student(学生表)
sid | sname |
---|
sbject(课程表)
kid | kname |
---|
select(选修课程表)
skid | kid | sid |
---|
1.查询没有被任何一个同学选修的课程信息
2.查询每个学生选修了哪些课程,需要详细的学生信息与课程信息
3.查询选修化学课程的学生信息
4.查询选修了三门课程的学生信息
1.select kname from subject where kid exists
2.select sid,sname,kid,kname from student a,sbject b,`select` c where a.sid = c.sid and b.kid = c.kid;
3.
-- 子查询
select sname from student where sid in(select sid from `select` where jid in(select jid from subject where jn ='化学'));
-- 关联查询
select sname from select, subject, student where select.jid = subject.jid and student.sid = select.sid and subject.jn = '化学';
4.
--第一种方式
select sname from student where sid in (select sid from `select` c group by c.sid having count(*) =3);
-- 第二种方式
select sname from student where sid in
(select sid from (select sid, count(*) as cnt from `select` group by sid) a where cnt >= 3);
-- 第三种方式 将查询语句取别名可作为临时表使用
select student.* from student,
(select sid, count(*) as cnt from select group by sid) a where student.sid = a.sid and a.cnt >= 3
可以将查询语句取别名后作为临时表使用!