create table student(sid int primary key,sname varchar(20),age int,sex varchar(4))create table st_co(sid int,cid varchar(20))
create table course(cid varchar(20), cname varchar(20))insert into student values('小张',20,'boy')insert into student values('小王',21,'girl')insert into student values('小李',20,'boy')insert into student values('小红',21,'girl')select * from studentinsert into course values ('c001','C语言')insert into course values ('c002','JAVA')insert into course values ('c003','STRUTS')insert into course values ('c004','Spring')select * from course
insert into st_co values(1,'c001')insert into st_co values(1,'c002')insert into st_co values(1,'c003')insert into st_co values(1,'c004')insert into st_co values(2,'c001')insert into st_co values(2,'c003')insert into st_co values(3,'c001')insert into st_co values(3,'c004')
select * from student
select * from course
select * from st_co/*1.查询选修了所有课程的学生的姓名(例如小张)*/
/*2.查询没有选修所有课程的学生的姓名*//*3.查询一门课程都没有选修的学生的姓名*/
/*4.查询所有学生的编号,并按所选课程数的降序排列显示,没有选修课程的学生不显示其编号*/
create table course(cid varchar(20), cname varchar(20))insert into student values('小张',20,'boy')insert into student values('小王',21,'girl')insert into student values('小李',20,'boy')insert into student values('小红',21,'girl')select * from studentinsert into course values ('c001','C语言')insert into course values ('c002','JAVA')insert into course values ('c003','STRUTS')insert into course values ('c004','Spring')select * from course
insert into st_co values(1,'c001')insert into st_co values(1,'c002')insert into st_co values(1,'c003')insert into st_co values(1,'c004')insert into st_co values(2,'c001')insert into st_co values(2,'c003')insert into st_co values(3,'c001')insert into st_co values(3,'c004')
select * from student
select * from course
select * from st_co/*1.查询选修了所有课程的学生的姓名(例如小张)*/
/*2.查询没有选修所有课程的学生的姓名*//*3.查询一门课程都没有选修的学生的姓名*/
/*4.查询所有学生的编号,并按所选课程数的降序排列显示,没有选修课程的学生不显示其编号*/
/*1.查询选修了所有课程的学生的姓名(例如小张)*/ select sname from student where sid in (
select sid from st_co
group by sid
having count(1) = (select count(1) from course)
)
select * from student a where not exists(select 1 from course b where not exists(select 1 from st_co where sid=a.sid and cid=b.cid))
2、
select * from student a where exists(select 1 from course b where not exists(select 1 from st_co where sid=a.sid and cid=b.cid))
3、
select * from student a where not exists(select 1 from st_co where sid=a.sid)
4、
select a.*,b.con from student a join (select sid,count(cid) as Con from st_co group by sid) b on a.sid=b.sid order by b.con
select a.*,b.con from student a join (select sid,count(cid) as Con from st_co group by sid) b on a.sid=b.sid order by b.con desc--倒序
select sid from st_co
group by sid
having count(1) = (select count(1) from course)
)
/*3.查询一门课程都没有选修的学生的姓名*/
select A.sname from student A left join st_co B on A.sid = B.sid where B.sid is null
group by sid
order by px desc