SQL查询练习

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 

可以将查询语句取别名后作为临时表使用！