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 )
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 )