MySQL 练习题 答案

二、操作表

1、自行创建测试数据

2、查询“生物”课程比“物理”课程成绩高的所有学生的学号;
    SELECT * FROM
        (SELECT score.student_id,course.cname,score.num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="生物") AS A
     INNER JOIN 
        (SELECT score.student_id,course.cname,score.num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="物理") AS B
        on A.student_id = B.student_id WHERE A.num > B.num;
        
3、查询平均成绩大于60分的同学的学号和平均成绩;
    SELECT B.student_id,student.sname,B.s_num FROM (SELECT student_id,avg(num) AS s_num from score GROUP BY student_id HAVING s_num>60) as B
        LEFT JOIN student on B.student_id = student.sid;  取前三: %ORDER BY s_num desc LIMIT 3;

4、查询所有同学的学号、姓名、选课数、总成绩;
    SELECT score.student_id,student.sname,COUNT(score.course_id) AS courses,SUM(num) as T_Score FROM score 
        LEFT JOIN student on score.student_id=student.sid GROUP BY score.student_id;
        
5、查询姓“李”的老师的个数;
    SELECT * from teacher WHERE tname like "李%";
    
6、查询没学过“李平”老师课的同学的学号、姓名;
    SELECT student.sid,student.sname FROM student WHERE student.sid NOT IN (
        SELECT score.student_id FROM score LEFT JOIN course ON score.course_id = course.cid WHERE score.course_id IN (
            SELECT course.cid FROM course LEFT JOIN teacher ON course.teacher_id = teacher.tid WHERE teacher.tname LIKE "李平%") GROUP BY score.student_id)
    
7、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名;
    SELECT B.sid,B.sname FROM(
        SELECTstudent.sid,score.course_id,student.sname FROM score
            LEFT JOIN student ON score.student_id = student.sid WHERE score.course_id BETWEEN 1 AND 2) AS B
        GROUP BY B.sid HAVING COUNT(B.course_id)=2;

8、查询学过“叶平”老师所教的所有课的同学的学号、姓名;
    SELECT student.sid,student.sname FROM score LEFT JOIN student ON score.student_id=student.sid WHERE score.course_id in 
        (SELECT course.cid FROM course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE tname LIKE "李平%") 
            GROUP BY student.sid HAVING COUNT(student_id)=2;

9、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名;
    SELECT A.sid,A.sname,A.num as score_2,B.num as score_1 FROM
        (SELECT student.sid,student.sname,score.num FROM score LEFT JOIN student on score.student_id=student.sid WHERE score.course_id=2) AS A
        INNER JOIN
        (SELECT student.sid,student.sname,score.num FROM score LEFT JOIN student on score.student_id=student.sid WHERE score.course_id=1) AS B
            ON A.sid = B.sid WHERE A.num<B.num;
    
10、查询有课程成绩小于60分的同学的学号、姓名;
    SELECT student.sid,student.sname FROM student WHERE sid IN
        (SELECT score.student_id FROM score WHERE score.num<60 GROUP BY student_id);

11、查询没有学全所有课的同学的学号、姓名;
    SELECT student.sid,student.sname FROM student WHERE sid in 
        (SELECT student_id FROM score GROUP BY student_id HAVING count(course_id)!=(SELECT COUNT(cid) FROM course));
    
12、查询至少有一门课与学号为“001”的同学所学相同的同学的学号和姓名;
    SELECT student.sid,student.sname FROM student where student.sid in 
        (SELECT score.student_id FROM score WHERE course_id in 
            (SELECT score.course_id FROM score WHERE student_id=1) and student_id !=1 GROUP BY student_id);
    
13、查询至少学过学号为“001”同学所选课程中任意一门课的其他同学学号和姓名;
    SELECT student.sid,student.sname FROM student where student.sid in 
        (SELECT score.student_id FROM score WHERE course_id in 
            (SELECT score.course_id FROM score WHERE student_id=1) and student_id !=1 GROUP BY student_id 
                having count(1) =(SELECT count(score.course_id) FROM score WHERE student_id=1));

14、查询和“002”号的同学学习的课程完全相同的其他同学学号和姓名;
        
    SELECT sid AS 学号,sname AS 姓名 FROM student WHERE sid IN(
    SELECT student_id FROM score WHERE student_id IN (
        SELECT student_id FROM score WHERE student_id!=2 GROUP BY student_id 
            HAVING COUNT(1)=(SELECT COUNT(1) FROM score WHERE student_id=2))
                AND course_id in (SELECT course_id FROM score WHERE student_id=2) 
                    GROUP BY student_id HAVING COUNT(1)=(SELECT COUNT(1) FROM score WHERE student_id=2))

15、删除学习“叶平”老师课的SC表记录;
    DELETE FROM score WHERE course_id IN
        (SELECT cid FROM course LEFT JOIN teacher on course.teacher_id=teacher.tid WHERE tname="李平老师")

16、向SC表中插入一些记录,这些记录要求符合以下条件:①没有上过编号“002”课程的同学学号;②插入“002”号课程的平均成绩; 
    1INSERT into score(student_id,course_id,num) 
            SELECT student_id,2,(SELECT AVG(num) FROM score WHERE course_id=2) FROM score WHERE course_id!=2 group by student_id
            
    --2、SELECT AVG(num) FROM score WHERE course_id=2 --
    
17、按平均成绩从低到高显示所有学生的“语文”、“数学”、“英语”三门的课程成绩,按如下形式显示: 学生ID,语文,数学,英语,有效课程数,有效平均分;
    SELECT student_id AS 学生ID,
        (SELECT num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="语文" AND student_id=s1.student_id) AS 语文,
        (SELECT num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="数学" AND student_id=s1.student_id) AS 数学,
        (SELECT num FROM score LEFT JOIN course on score.course_id=course.cid WHERE course.cname="英语" AND student_id=s1.student_id) AS 英语
            FROM score as s1 GROUP BY student_id;
    
18、查询各科成绩最高和最低的分:以如下形式显示:课程ID,最高分,最低分;
    SELECT score.course_id AS 课程ID,course.cname as 课程名,MAX(num) as 最高分,MIN(num) as 最低分 FROM score 
        LEFT JOIN course ON score.course_id=course.cid GROUP BY course_id;
        

19、按各科平均成绩从低到高和及格率的百分数从高到低顺序;
    
    平均分:
    SELECT score.course_id AS 课程ID,course.cname as 课程名,AVG(num) AS 平均分 FROM score 
        LEFT JOIN course ON score.course_id=course.cid GROUP BY score.course_id ORDER BY 平均分 ASC;
    及格率:
    SELECT A.cid,A.cname,ROUND(B.s/A.ss*100,1)+% AS 及格率 FROM
        (SELECT cid,cname,COUNT(course_id) AS ss FROM score LEFT JOIN course ON score.course_id=course.cid GROUP BY course_id) as A
            INNER JOIN
        (SELECT course_id,COUNT(num) as s FROM score WHERE score.num>60 GROUP BY course_id) as B ON A.cid=B.course_id ORDER BY 及格率 DESC;
    整合:
    SELECT x.课程ID,x.课程名,x.平均分,xx.及格率 FROM
    (SELECT score.course_id AS 课程ID,course.cname as 课程名,AVG(num) AS 平均分 FROM score 
            LEFT JOIN course ON score.course_id=course.cid GROUP BY score.course_id ORDER BY 平均分 ASC) AS x 
    LEFT JOIN
    (SELECT A.cid,A.cname,ROUND(B.s/A.ss*100,1) AS 及格率 FROM
        (SELECT cid,cname,COUNT(course_id) AS ss FROM score LEFT JOIN course ON score.course_id=course.cid GROUP BY course_id) as A
            INNER JOIN
        (SELECT course_id,COUNT(num) as s FROM score WHERE score.num>60 GROUP BY course_id) as B ON A.cid=B.course_id ORDER BY 及格率 DESC) AS xx
ON x.课程ID=xx.cid 
    
    标准:
    SELECT course_id,AVG(num) AS 平均分,ROUND(sum(CASE WHEN num<60 then 0 ELSE 1 END)/sum(1)*100,1) AS 及格率 
        FROM score GROUP BY course_id ORDER BY 平均分 ASC,及格率 DESC;


20、课程平均分从高到低显示(现实任课老师);
    SELECT course.cname,teacher.tname,xxx.平均分 FROM course
        LEFT JOIN teacher ON course.teacher_id=teacher.tid 
        LEFT JOIN (SELECT course_id,AVG(num) AS 平均分 FROM score GROUP BY course_id) AS xxx ON xxx.course_id=course.cid
        ORDER BY xxx.平均分 DESC

21、查询各科成绩前三名的记录:(不考虑成绩并列情况) 
    SELECT s1.cid AS 课程ID,s1.cname AS 课程,
        (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 0,1) AS 第一,
        (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 1,1) AS 第二,
        (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 2,1) AS 第三
    FROM course AS s1    
        
22、查询每门课程被选修的学生数;
    SELECT course.cid AS 课程ID,course.cname AS 课程名,COUNT(1) AS 学生数 FROM score 
        LEFT JOIN course ON score.course_id=course.cid GROUP BY score.course_id; 
23、查询出只选修了一门课程的全部学生的学号和姓名;
    SELECT student.sid AS 学号,student.sname AS 姓名 FROM student
        WHERE sid IN (SELECT student_id FROM score GROUP BY student_id HAVING count(1)=1)    
        
24、查询男生、女生的人数;
    SELECT gender AS 性别,COUNT(1) AS 人数 FROM student GROUP BY gender;

25、查询姓“张”的学生名单;
    SELECT * FROM student WHERE sname LIKE "张%"
    
26、查询同名同姓学生名单,并统计同名人数;
    SELECT sname As 姓名,COUNT(1) AS 人数 FROM student GROUP BY sname; 

27、查询每门课程的平均成绩,结果按平均成绩升序排列,平均成绩相同时,按课程号降序排列;
    SELECT
        course.cid AS 课程ID,
        course.cname AS 课程名,
        AVG(IF(ISNULL(num),0,score.num)) AS 平均分
    FROM score LEFT JOIN course ON score.course_id = course.cid 
        GROUP BY score.course_id ORDER BY 平均分 ASC,课程ID DESC;
    
28、查询平均成绩大于85的所有学生的学号、姓名和平均成绩;
    SELECT student.sid AS 学号,student.sname AS 姓名,AVG(if(isnull(num),0,score.num)) AS 平均分 
    FROM score LEFT JOIN student ON score.student_id=student.sid 
        GROUP BY score.student_id HAVING 平均分>85;
    
29、查询课程名称为“数学”,且分数低于60的学生姓名和分数;
    SELECT student.sid AS 学号,student.sname AS 姓名,score.num AS 成绩 FROM score 
        LEFT JOIN course ON score.course_id=course.cid
        LEFT JOIN student ON score.student_id= student.sid
            WHERE course.cname="数学" AND score.num<6030、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名;
    SELECT student.sid AS 学号,student.sname AS 姓名 FROM student WHERE sid IN 
        (SELECT student_id FROM score WHERE score.course_id =3 AND num > 80) 

31、求选了课程的学生人数
    SELECT COUNT(A.student_id) AS 总人数 
        FROM (SELECT student_id FROM score GROUP BY student_id) AS A

    SELECT COUNT(DISTINCT student_id) AS 总人数 FROM score;
    
32、查询选修“杨艳”老师(这个老师没有,就以张磊老师举例)所授课程的学生中,成绩最高的学生姓名及其成绩;
    SELECT student.sid AS 学号,student.sname AS 姓名,num AS 成绩 FROM score 
        LEFT JOIN course ON score.course_id=course.cid
        LEFT JOIN student ON score.student_id=student.sid
        LEFT JOIN teacher ON course.teacher_id=teacher.tid
            WHERE teacher.tname = "张磊老师" ORDER BY num DESC LIMIT 1;
    
33、查询各个课程及相应的选修人数;
    SELECT course_id AS ID,course.cname AS 课程,count(1) AS 人数 FROM score 
        LEFT JOIN course ON score.course_id=course.cid GROUP BY course_id
34、查询不同课程但成绩相同的学生的学号、课程号、学生成绩;
    SELECT DISTINCT s1.student_id,s1.course_id,s1.num FROM score AS s1,score AS s2
        WHERE s1.student_id    != s2.student_id AND s1.course_id!=s2.course_id AND s1.num=s2.num 

35、查询每门课程成绩最好的前两名;
    SELECT cid AS 课程ID,cname AS 课程,
        (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 0,1) AS 第一,
        (SELECT num FROM score AS s2 WHERE s2.course_id=s1.cid GROUP BY num ORDER BY num DESC LIMIT 1,1) AS 第二
    FROM course AS s1

36、检索至少选修两门课程的学生学号;
    SELECT student_id FROM score GROUP BY student_id HAVING count(course_id)>=2

37、查询全部学生都选修的课程的课程号和课程名;
    SELECT course.cid AS 课程号,course.cname AS 课程名 FROM score 
        LEFT JOIN course ON score.course_id=course.cid GROUP BY score.course_id
            HAVING COUNT(student_id)=(SELECT COUNT(sid) FROM student)

38、查询没学过“李平”老师讲授的任一门课程的学生姓名;
    SELECT student.sid,student.sname FROM student
        WHERE student.sid not IN (SELECT student_id FROM score 
            WHERE course_id IN(SELECT cid FROM course 
                LEFT JOIN teacher ON course.teacher_id=teacher.tid 
                    WHERE teacher.tname="李平老师" ) GROUP BY student_id)
        
39、查询两门以上不及格课程的同学的学号及其平均成绩;
    SELECT score.student_id AS 学号,student.sname AS 姓名,
    (SELECT AVG(if(ISNULL(A.num),0,A.num)) FROM score AS A
        WHERE A.student_id IN (SELECT student_id FROM score WHERE num<60 
            GROUP BY student_id HAVING COUNT(1)>=2)) AS 平均成绩 
    FROM score LEFT JOIN student ON score.student_id=student.sid 
        WHERE num<60 GROUP BY student_id HAVING COUNT(1)>2
    
标准:    
    SELECT student_id AS 学号,sname AS 姓名,AVG(num) AS 平均成绩 FROM score
    LEFT JOIN student ON score.student_id=student.sid
        WHERE student_id IN (SELECT student_id FROM score 
            WHERE num<60 GROUP BY student_id HAVING COUNT(1)>=2) 
                GROUP BY student_id
        
40、检索“004”课程分数小于60,按分数降序排列的同学学号;
    SELECT student_id,num FROM score 
        WHERE score.course_id=4 AND num <60 ORDER BY num ASC

41、删除“002”同学的“001”课程的成绩;
    delete from score where student_id=2 and corse_id=1;

 

posted @ 2017-06-09 17:35  细雨蓝枫  阅读(4495)  评论(0编辑  收藏  举报