create table student (
sid int AUTO_INCREMENT not null,
sname VARCHAR(50),
ssex VARCHAR(2),
sage int,
sgp VARCHAR(50),
PRIMARY KEY(sid)
)
CREATE TABLE course(
cid int AUTO_INCREMENT not null,
cname VARCHAR(50),
PRIMARY KEY(cid)
)
CREATE TABLE sc(
scid int AUTO_INCREMENT not null,
sid int not null,
cid int not null,
PRIMARY KEY(scid)
)
#1)写一个SQL语句,查询选修了’计算机原理’的学生学号和姓名(3分钟)
select stu.sid, stu.sname from Student stu ,course c,sc s
where
stu.sid=s.sid and c.cid=s.cid and c.cname='计算原理';
#2)写一个SQL语句,查询’周星驰’同学选修了的课程名字(3分钟)
SELECT cname from course where cid in
(SELECT cid from sc s, student stu
WHERE stu.sid=s.sid and stu.sname='周星驰');
#3)写一个SQL语句,查询选修了5门课程的学生学号和姓名(9分钟)
select stu.sid, stu.sname from student stu
where (select count(*) from sc where sid=stu.sid) = 5;
#4)写出SQL语句,查询选修了所有选修课程的学生;
select stu.sid, stu.sname from student stu
where (select count(*) from sc where sid=stu.sid)=(select count(*) from course);
#5)写出SQL语句,查询选修了至少5门以上的课程的学生?
select stu.sid, stu.sname from student stu
where (select count(*) from sc where sid=stu.sid)>=5;
#6)查询选修课程的学生人数。
SELECT count(*) from student stu WHERE EXISTS (SELECT cid from sc WHERE sid=stu.sid )