-
查询姓"胡"的老师的个数;
SELECT COUNT(1) FROM teacher WHERE tname LIKE '李%';
-
查询每门课程被选修的学生数
SELECT cid,COUNT(1) FROM sc GROUP BY cid;
-
查询平均成绩大于50分的学生的学号、姓名和平均成绩
SELECT st.sid,sname,AVG(score) FROM sc,student st WHERE sc.sid=st.sid GROUP BY st.sid HAVING AVG(score)>50;
-
查询课程名称为"python",且分数低于60的学生学号,姓名和分数
SELECT st.sid,sname,score FROM sc,student st,course c WHERE sc.sid=st.sid AND sc.cid=c.cid AND c.cname='python' AND score<60;
-
查询只选修了一门课程的全部学生的学号和姓名
SELECT st.sid,st.sname FROM sc,student st WHERE sc.sid = st.sid GROUP BY st.sid HAVING COUNT(1)=1;
-
查询同名同性别学生名单,并统计同名同性别人数
SELECT sname,ssex,COUNT(1) FROM student GROUP BY sname,ssex HAVING COUNT(1) >1;
-
1981年出生的学生名单(注:student表中sage列的类型是datetime)
SELECT sid,sname,'1981' age FROM student WHERE DATE_FORMAT(sage,'%Y')='1981';
-
查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列
SELECT cid,AVG(score) avg_score FROM sc GROUP BY cid ORDER BY AVG(score),cid DESC;
-
查询平均成绩大于60分的同学的学号和平均成绩;
SELECT sid,ROUND(AVG(score),2) avg_score FROM sc GROUP BY sid HAVING AVG(score)>60;
-
查询所有同学的学号、姓名、选课数、总成绩;
SELECT st.sid,sname,COUNT(1) num,SUM(score) total_score FROM sc,student st WHERE sc.sid=st.sid GROUP BY st.sid;
-
查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分
SELECT cid 课程ID, MAX(score) 最高分,MIN(score)最低分 FROM sc GROUP BY cid;
-
查询不同老师所教不同课程平均分从高到低显示 要求显示:教师ID,教师姓名,课程ID,课程名称,平均成绩
SELECT MAX(t.tid) "教师ID",MAX(t.tName) "教师姓名",c.cid "课程ID", MAX(c.cname) "课程名称" ,AVG(sc.score) "平均成绩" FROM sc,course c,teacher t WHERE sc.cid = c.cid AND c.tid = t.tid GROUP BY c.tid,c.cid ORDER BY AVG(sc.score) DESC;
-
查询全部学生都选修的课程号
FROM student s,course c, sc WHERE s.sid = sc.sid AND sc.cid = c.cid GROUP BY sc.cid HAVING COUNT(1) = (SELECT COUNT(1) FROM student);
-
查询两门以上(包含两门)不及格课程的同学的学号及其平均成绩
SELECT sid,AVG(score) FROM sc WHERE sid IN (SELECT sid FROM sc WHERE score <60 GROUP BY sid HAVING COUNT(1)>2) GROUP BY sid;
-
查询没学过"叶平"老师讲授的任一门课程的学生姓名
SELECT DISTINCT sname FROM student WHERE sid NOT IN (SELECT sid FROM course,teacher,sc WHERE course.tid=teacher.tid AND sc.cid=course.cid AND tname='叶平');
-
查询所有课程成绩小于60分的同学的学号、姓名;
SELECT sid,sname FROM student st WHERE sid NOT IN (SELECT DISTINCT(sid) FROM sc WHERE score >=60);
-
查询学过"001"并且也学过编号"002"课程的同学的学号、姓名;
-- 这里用到了内连接的特点:不符合要求的全部过滤掉 SELECT st.sid,sname FROM (SELECT sid FROM sc WHERE cid='001') AS s1, (SELECT sid FROM sc WHERE cid='002') AS s2, student st WHERE s1.sid=s2.sid AND s1.sid=st.sid;
-
查询"001"课程比"002"课程成绩高的所有学生的学号;
SELECT s1.sid FROM (SELECT sid,cid,score FROM sc WHERE cid='001') AS s1, (SELECT sid,cid,score FROM sc WHERE cid='002') AS s2 WHERE s1.sid=s2.sid AND s1.score>s2.score;
-
查询没有学全所有课的同学的学号、姓名;
SELECT st.sid,st.sname FROM sc,student st WHERE sc.sid = st.sid GROUP BY st.sid HAVING COUNT(1) < (SELECT COUNT(1) FROM course);
-
查询至少有一门课与学号为"1003"的同学所学相同的同学的学号和姓名;
SELECT DISTINCT(st.sid),st.sname FROM sc,student st WHERE sc.sid = st.sid AND sc.sid !='1003' AND cid IN ( SELECT cid FROM sc WHERE sid ='1003' );
-
按平均成绩从高到低显示所有学生的"python"、"企业管理"、"英语"三门的课程成绩,按如下形式显示: 学生ID,,python,企业管理,英语,有效课程数,有效平均分
SELECT sc1.sid 学生ID, 数据库,企业管理,英语,有效课程数,平均成绩 FROM (SELECT sid,AVG(score) 平均成绩,COUNT(1)有效课程数 FROM sc GROUP BY sid )sc1 LEFT JOIN (SELECT sid,score 数据库 FROM sc,course c WHERE sc.cid = c.cid AND c.cname='数据库') AS a ON sc1.sid =a.sid LEFT JOIN (SELECT sid,score 企业管理 FROM sc,course c WHERE sc.cid =c.cid AND c.cname='企业管理') AS b ON sc1.sid = b.sid LEFT JOIN (SELECT sid,score 英语 FROM sc,course c WHERE sc.cid =c.cid AND c.cname='英语') AS c ON sc1.sid =c.sid ORDER BY 平均成绩 DESC;
-
查询男生、女生人数
SELECT SUM(CASE WHEN ssex='男' THEN 1 ELSE 0 END) '男', SUM(CASE WHEN ssex='女' THEN 1 ELSE 0 END) '女' FROM student;
-
按各科平均成绩从低到高和及格率的百分数从高到低排序
SELECT cid,AVG(score) 平均成绩, 100*SUM(CASE WHEN IFNULL(score,0)>=60 THEN 1 ELSE 0 END)/COUNT(1) 及格率 FROM sc GROUP BY cid ORDER BY 平均成绩,及格率 DESC;
-
统计各科成绩,各分数段人数:课程ID,课程名称,[85,100],[70,85),[60,70),[ <60]
SELECT c.cid 课程ID, c.cname 课程名称, SUM(CASE WHEN score>=85 AND score <=100 THEN 1 ELSE 0 END) AS "[85,100]", SUM(CASE WHEN score>=70 AND score <85 THEN 1 ELSE 0 END) AS "[70,85)", SUM(CASE WHEN score>=60 AND score < 70 THEN 1 ELSE 0 END) AS "[60,70)", SUM(CASE WHEN score<60 THEN 1 ELSE 0 END) AS "<60" FROM sc,course c WHERE sc.cid=c.cid GROUP BY c.cid;
-
查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理(001),马克思(002),OO&java (003),python(004)
SELECT SUM(CASE WHEN cid ='001' THEN score ELSE 0 END)/SUM(CASE cid WHEN '001' THEN 1 ELSE 0 END) AS 企业管理平均分 ,100 * SUM(CASE WHEN cid = '001' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '001' THEN 1 ELSE 0 END) AS 企业管理及格百分数 ,SUM(CASE WHEN cid = '002' THEN score ELSE 0 END)/SUM(CASE cid WHEN '002' THEN 1 ELSE 0 END) AS 马克思平均分 ,100 * SUM(CASE WHEN cid = '002' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '002' THEN 1 ELSE 0 END) AS 马克思及格百分数 ,SUM(CASE WHEN cid = '003' THEN score ELSE 0 END)/SUM(CASE cid WHEN '003' THEN 1 ELSE 0 END) AS UML平均分 ,100 * SUM(CASE WHEN cid = '003' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '003' THEN 1 ELSE 0 END) AS UML及格百分数 ,SUM(CASE WHEN cid = '004' THEN score ELSE 0 END)/SUM(CASE cid WHEN '004' THEN 1 ELSE 0 END) AS 数据库平均分 ,100 * SUM(CASE WHEN cid = '004' AND score >= 60 THEN 1 ELSE 0 END)/SUM(CASE WHEN cid = '004' THEN 1 ELSE 0 END) AS 数据库及格百分数 FROM sc ;
-
查询每门功课成绩最好的前两名(开窗函数)
SELECT sid,cid,score FROM sc t1 WHERE ( SELECT COUNT(1) FROM sc t2 WHERE t1.cid=t2.cid AND t2.score>t1.score ) <2 ORDER BY t1.cid;
-
查询所有学生的选课情况;
SELECT sid,GROUP_CONCAT(cid) FROM sc GROUP BY sid;
-
查询学过"叶平"老师所教的所有课的同学的学号、姓名;
SELECT st.sid,st.sname FROM sc,student st,course c,teacher t WHERE sc.sid =st.sid AND sc.cid = c.cid AND c.tid = t.tid AND tname = '叶平' GROUP BY st.sid HAVING COUNT(1)= (SELECT COUNT(1) FROM teacher t,course c WHERE t.tid=c.tid AND tname ='叶平');
-
查询和"1002"号的同学学习的课程完全相同的其他同学学号和姓名;
SELECT sid FROM sc WHERE sid NOT IN(SELECT DISTINCT(sid) FROM sc WHERE cid NOT IN (SELECT cid FROM sc WHERE sid ='1002')) GROUP BY sid HAVING COUNT(1) = (SELECT COUNT(1) FROM sc WHERE sid ='1002') AND sid !='1002';
-
把"sc"表中"叶平"老师教的课的成绩都更改为此课程的平均成绩;
UPDATE sc,( SELECT AVG(score) avg_score,sc.cid FROM sc,teacher t,course c WHERE sc.cid=c.cid AND c.tid=t.tid AND tname = '叶平' GROUP BY sc.cid ) temp SET sc.score = temp.avg_score WHERE sc.cid =temp.cid;
MySQL经典练习题--30题
最新推荐文章于 2024-08-09 21:25:57 发布