– 1.求全体学生的学号，姓名和证件号码
select sid 学号,sname 姓名,sidcard 证件号码 from student;
– 2.求选修了课程的学生学号
select DISTINCT sid 学号 from sc;
– 3.求全体学生的学号，姓名和出生年份–求全体学生的学号，姓名和证件号码
select sid 学号,sname 姓名,sidcard 证件号码 from student;
– 4.求选修了课程的学生学号
select DISTINCT sid 学号 from sc;
– 5.求全体学生的学号，姓名和出生年份
select sid,sname,substring(sidcard,7,4) from student;
– 6.求学生的学号和出生年份，显示时使用别名‘学号’和‘出生年份’
select sid 学号,substring(sidcard,7,4) 出生年份 from student;
– 7.求在90后学生的姓名和年龄
select sname,left(now(),4)-substring(sidcard,7,4) from student where substring(sidcard,7,4)>=1990;
– 8.求1990-1988年之间的学生学号和证件号码
select sid,sidcard from student where substring(sidcard,7,4)>=1988 and substring(sidcard,7,4)<=1990;
– 9.求不在1990-1988年之间的学生学号和证件号码
select sid,sidcard from student where substring(sidcard,7,4) not between 1988 and 1990;
– 10.求姓名中含有‘h’的学生
select * from student where sname like ‘%h%’;
– 11.求选修100或200课程的学生学号，课程号，分数和课程名
select sid,c.cid,score,cname from sc join course c on sc.cid = c.cid
where c.cid = 100 or c.cid=200;
select sid,course.cid,score,cname from sc join course on sc.cid = course.cid
where course.cid = 100 or course.cid=200;
– 12.求学习马克思课程学生的平均成绩
select avg(score) from sc join course on sc.cid = course.cid where cname=‘msc’;
– 13.求选修课程300的最高，最低与平均成绩
select max(score),min(score),avg(score) from sc where cid=300;
– 14.求全体学生的详细信息
select * from student join sc on student.sid = sc.sid join course on course.cid=sc.cid join teacher on course.tid=teacher.tid;
– 15.1981年出生的学生名单
select sname from student where substring(sidcard,7,4)=1981;
– 16.求每位老师课下学生总人数
select count(sid), tid from sc join course on sc.cid = course.cid group by tid;
– 17.求选修了WangFeng老师课程的学生信息
select * from student join sc on student.sid = sc.sid join course on course.cid=sc.cid join teacher on course.tid=teacher.tid where tname=‘WangFeng’;
– 18.求没有选修了WangFeng老师课程的学生信息
select * from student join sc on student.sid = sc.sid join course on course.cid=sc.cid join teacher on course.tid=teacher.tid where not tname=‘WangFeng’;
– 19.删除1002同学的100课程的成绩
update sc set scire = 0 where sid=1002 and cid=100;
– 20.查询所有同学的学号，姓名，选课数，总成绩
select student.sid 学号,student.sname 姓名,count(sc.sid),sum(sc.score) from student join sc on student.sid = sc.sid group by student.sid,student.sname;
– 21.查询学过100并且也学过编号200课程的同学的学号，姓名
select student.sid, student.sname from student join sc on student.sid = sc.sid join course on course.cid=sc.cid where course.cid=100 and sc.sid in(select sc.sid from sc where sc.cid=200);
– 22.查询每门课程被选修的学生数
select count(sid),cid from sc group by cid;
– 23.查询只选修了一门课程的全部学生的学号和姓名
select student.sid 学号,student.sname 姓名 from student join sc on student.sid = sc.sid group by student.sid,student.sname having count()=1;
– 24.查询男生和女生的人数
select count() from student GROUP BY sgender;
– 25.查询平均成绩大于85的所有学生的学号，姓名和平均成绩
select student.sid,student.sname,avg(score) from student join sc on student.sid = sc.sid GROUP BY sid having avg(score)>85;
– 26.查询课程名称为‘数据库‘，且分数低于60的学生姓名和分数
select sname 姓名,score 分数 from student join sc on student.sid = sc.sid join course on course.cid = sc.cid where score<60 and cname=‘sjk’;
– 27.查询100课程比200课程成绩高的所有学生的学号。
select student.sid, student.sname from student join sc on student.sid = sc.sid join course on course.cid=sc.cid where course.cid=100 and sc.sid in(select sc.sid from sc where sc.cid=200);
– 28.查询平均成绩大于60分的同学的学号和平均成绩。
select sid,avg(score) from sc join course on sc.cid = course.cid where avg(score)>60;