常熟做网站的公司,禹城网站建设,内蒙古建筑信息平台,html网页首页制作代码一、查询数据
ymysql-面试50题-2-CSDN博客
二、问题
31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
mysql select student.sid,student.sname - from student,sc - where cid01 - and score80 - a…一、查询数据
ymysql-面试50题-2-CSDN博客
二、问题
31.查询课程编号为 01 且课程成绩在 80 分以上的学生的学号和姓名
mysql select student.sid,student.sname - from student,sc - where cid01 - and score80 - and student.sid sc.sid; -------------- | sid | sname | -------------- | 01 | 赵雷 | | 03 | 孙风 | -------------- 2 rows in set (0.00 sec) 32.求每门课程的学生人数
mysql select sc.CId,count(*) as 学生人数 - from sc - GROUP BY sc.CId; -------------------- | CId | 学生人数 | -------------------- | 01 | 6 | | 02 | 6 | | 03 | 6 | -------------------- 3 rows in set (0.00 sec) 33.成绩不重复查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩
mysql select student.*, sc.score, sc.cid from student, teacher, course,sc - where teacher.tid course.tid - and sc.sid student.sid - and sc.cid course.cid - and teacher.tname 张三 - order by score desc - limit 1; ------------------------------------------------------ | SId | Sname | Sage | Ssex | score | cid | ------------------------------------------------------ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 90.0 | 02 | ------------------------------------------------------ 1 row in set (0.01 sec) 34.成绩有重复的情况下查询选修「张三」老师所授课程的学生中成绩最高的学生信息及其成绩
mysql select student.*, sc.score, sc.cid from student, teacher, course,sc - where teacher.tid course.tid - and sc.sid student.sid - and sc.cid course.cid - and teacher.tname 张三 - and sc.score ( - select Max(sc.score) - from sc,student, teacher, course - where teacher.tid course.tid - and sc.sid student.sid - and sc.cid course.cid - and teacher.tname 张三 - ); ------------------------------------------------------ | SId | Sname | Sage | Ssex | score | cid | ------------------------------------------------------ | 01 | 赵雷 | 1990-01-01 00:00:00 | 男 | 90.0 | 02 | ------------------------------------------------------ 1 row in set (0.00 sec) 35.查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
36.查询每门功成绩最好的前两名
37.统计每门课程的学生选修人数超过 5 人的课程才统计。
mysql select sc.cid, count(sid) as cc from sc - group by cid - having cc 5; ---------- | cid | cc | ---------- | 01 | 6 | | 02 | 6 | | 03 | 6 | ---------- 3 rows in set (0.00 sec) 38.检索至少选修两门课程的学生学号
mysql select sid, count(cid) as cc from sc - group by sid - having cc2; ---------- | sid | cc | ---------- | 01 | 3 | | 02 | 3 | | 03 | 3 | | 04 | 3 | | 05 | 2 | | 06 | 2 | | 07 | 2 | ---------- 7 rows in set (0.00 sec) 39.查询选修了全部课程的学生信息
40.查询各学生的年龄只按年份来算 注对于上面的一些题后面补
