MySQL 练习题4 参考答案

最新推荐文章于 2022-07-31 07:09:42 发布

aaronthon

最新推荐文章于 2022-07-31 07:09:42 发布

阅读量403

点赞数 1

本文链接：https://blog.csdn.net/aaronthon/article/details/107439418

版权

1.表结构如下:

数据脚本

2.查询:

查询学习课程"python"比课程 "java" 成绩高的学生的学号;

 
        
             #先查询 
             "python" 
             课程和 
             "java" 
             课程的学生成绩,临时表 
            
 
             #让两个临时表进行比较 
            
 
             -- select * from course c,score s where c.c_id = s.c_id and c.c_name='python'; 
            
 
             -- select * from course c,score s where c.c_id = s.c_id and c.c_name='java'; 
            

                
            
 
             select  
             A.s_id  
             from 
            
 
             ( 
             select  
             s.s_id,s.num  
             from  
             course c,score s  
             where  
             c.c_id = s.c_id  
             and  
             c.c_name= 
             'python' 
             )  
             AS  
             A , 
            
 
               
             ( 
             select  
             s.s_id,s.num  
             from  
             course c,score s  
             where  
             c.c_id = s.c_id  
             and  
             c.c_name= 
             'java' 
             )  
             AS  
             B 
            
 
             where  
             A.s_id = B.s_id  
             and  
             A.num > B.num; 
            
 
      

查询平均成绩大于65分的同学的姓名和平均成绩(保留两位小数);　

 
        
             select  
             round( 
             avg 
             (num),2)  
             as  
             num,student.s_name  
             from  
             score s 
            
 
             LEFT   
             JOIN  
             student  
             ON  
             s.s_id = student.s_id  
             group  
             by  
             s.s_id  
             having  
             num > 65; 
            
 
      

查询所有同学的姓名、选课数、总成绩；

 
        
             #先来分析需要哪些表:学生表/成绩表 
            
 
             #然后进行多表查询即可 
            

                
            
 
             select  
             s_name, 
             count 
             (*)  
             '选课数' 
             , 
             sum 
             (num) 
             as  
             '总成绩'  
             from  
             student st,score s  
             where  
             st.s_id = s.s_id  
             GROUP  
             BY  
             s.s_id; 
            
 
      

查询所有的课程的名称以及对应的任课老师姓名;

 
             select  
             c_name,t_name  
             from  
             course,teacher  
             where  
             course.t_id = teacher.t_id;

查询没学过“alex”老师课的同学的姓名;

 
             #先看看alex教什么课程 
            
             #看看谁学了alex的课程 
            
             #最后把学了的人过滤掉就是没学过的学生 
            
             -- select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex'; 
            
             -- select s_id from score where c_id in(2,4); 
            
             select  
             s_name  
             from  
             student  
            
             where  
             s_id  
             not  
             in 
             ( 
             select  
             s_id  
             from  
             score  
             where  
             c_id  
             in 
             (2,4));

查询学过'python'并且也学过编号'java'课程的同学的姓名;

 
             -- select * from score where score.c_id='1' and score.c_id='2' 
            
             #查询python和java课程号 
            
             -- select c_id from course where course.c_name in('python','java'); 
            
             SELECT  
             st.s_name  
             from  
             score s ,student st 
            
             where  
             s.s_id = st.s_id  
             AND  
             s.c_id  
             in 
             (1,2)  
             GROUP  
             BY  
             s.s_id  
             HAVING  
             COUNT 
             (*) = 2;

查询学过“alex”老师所教的全部课程的同学的姓名；

 
        
             #先知道alex老师教什么课程 
            
 
             #然后来看看学了alex课程的学生有哪些人 
            
 
             #按学生分组,看看谁学的课程数 = alex老师教授的课程数 
            

                
            
 
             -- select c_id from teacher t, course c where t.t_id = c.t_id and t.t_name ='alex'; 
            

                
            
 
             select  
             student.s_name  
             from  
             score,student  
             where  
             score.s_id =student.s_id  
             and  
             score.c_id  
             in 
            
 
                  
             ( 
             select  
             c_id  
             from  
             teacher t, course c  
             where  
             t.t_id = c.t_id  
             and  
             t.t_name = 
             'alex' 
             )  
             GROUP  
             BY  
             score.s_id 
            

                
            
 
             HAVING  
             count 
             (*) = ( 
             select  
             count 
             (*)  
             from  
             teacher t, course c  
             where  
             t.t_id = c.t_id  
             and  
             t.t_name = 
             'alex' 
             ); 
            
 
      

查询挂科超过两门(包括两门)的学生姓名;

 
        
             SELECT  
             student.s_name  
             from  
             score,student 
            
 
             where  
             score.s_id = student.s_id  
             and  
             score.num <60  
             GROUP  
             BY  
             student.s_id  
             HAVING  
             count 
             (*)>=2; 
            
 
      

查询有课程成绩小于60分的同学的姓名;

 
             SELECT  
             DISTINCT  
             student.s_name  
             from  
             score,student 
            
             where  
             score.s_id = student.s_id  
             and  
             score.num <60;

查询选修了全部课程的学生姓名;

 
        
             -- select count(*) from course; 
            

                
            
 
             select  
             student.s_name  
             from  
             score,student 
            
 
             where  
             score.s_id = student.s_id  
             GROUP  
             BY  
             score.s_id  
             HAVING  
             count 
             (*) = ( 
             select  
             count 
             (*)  
             from  
             course) 
            
 
      

查询至少有一门课程与“貂蝉”同学所学课程相同的同学姓名；

 
        
             -- SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉' 
            
 
                
            
 
             select  
             student.s_name  
             from  
             score,student   
             where  
             score.s_id = student.s_id  
             and 
            
 
             　　score.c_id  
             in 
             ( 
             SELECT  
             c_id  
             from  
             score,student  
             where  
             score.s_id =student.s_id  
             and  
             student.s_name= 
             '貂蝉' 
             ) 
            

                
            
 
             and  
             student.s_name <>  
             '貂蝉'  
             GROUP  
             BY  
             student.s_id; 
            
 
      

查询学过'貂蝉'同学全部课程的其他同学姓名；

 
        
             -- SELECT c_id from score,student where score.s_id =student.s_id and student.s_name='貂蝉' 
            

                
            
 
             select  
             student.s_name, 
             count 
             (*)  
             from  
             score,student   
             where  
             score.s_id = student.s_id 
            
 
             and   
             score.c_id  
             in 
             ( 
             SELECT  
             c_id  
             from  
             score,student  
             where  
             score.s_id =student.s_id  
             and  
             student.s_name= 
             '貂蝉' 
             ) 
            
 
             and  
             student.s_name <>  
             '貂蝉'  
             GROUP  
             BY  
             student.s_id 
            
 
             HAVING  
             count 
             (*) = ( 
             SELECT  
             count 
             (*)  
             from  
             score,student  
             where  
             score.s_id =student.s_id  
             and  
             student.s_name= 
             '貂蝉' 
             ); 
            
 
      

查询和'貂蝉'同学学习的课程完全相同的,其他同学姓名；

 
        
             解题思路: 
            
 
             #1. 找出与 
             '貂蝉' 
             学习课程数 相同的学生s_id (你学两门,我也学两门)　 
            
 
             #2. 再找出学过 
             '貂蝉' 
             课程的学生,剩下的一定是至少学过一门 
             '貂蝉' 
             课程的学生 
            
 
             #3. 再根据学生ID进行分组,剩下学生数 
             count 
             (1) = 貂蝉学生所学课程数 
            

                
            
 
             #1.找出与 
             '貂蝉' 
             学习课程数 相同的学生s_id (你学两门,我也学两门) 
            
 
             select  
             *  
             FROM  
             score  
             where  
             score.s_id  
             in 
             ( 
            
 
                  
             select  
             s_id  
             from  
             score  
             GROUP  
             BY  
             score.s_id  
             HAVING  
             count 
             (*) = ( 
            
 
                      
             select  
             count 
             (*)  
             from  
             student,score  
             where  
             student.s_id = score.s_id  
             and  
             student.s_name=  
             '貂蝉' 
            
 
                  
             ) 
            
 
             ) 
            

                
            
 
             #2.然后再找出学过 
             '貂蝉' 
             课程的学生,剩下的一定是至少学过一门 
             '貂蝉' 
             课程的学生 
            
 
             select  
             *  
             FROM  
             score  
             where  
             score.s_id  
             in 
             ( 
            
 
                  
             select  
             s_id  
             from  
             score  
             GROUP  
             BY  
             score.s_id  
             HAVING  
             count 
             (*) = ( 
            
 
                      
             select  
             count 
             (*)  
             from  
             student,score  
             where  
             student.s_id = score.s_id  
             and  
             student.s_name=  
             '貂蝉' 
            
 
                  
             ) 
            
 
             ) 
            
 
             and  
             score.c_id  
             in 
             ( 
             select  
             c_id  
             from  
             student,score  
             where  
             student.s_id = score.s_id  
             and  
             student.s_name=  
             '貂蝉' 
             ) 
            

                
            
 
             #3.再根据学生ID进行分组,剩下学生数 
             count 
             (1) = 貂蝉学生所学课程数 
            
 
             select  
             *  
             FROM  
             score  
             where  
             score.s_id  
             in 
             ( 
            
 
                  
             select  
             s_id  
             from  
             score  
             GROUP  
             BY  
             score.s_id  
             HAVING  
             count 
             (*) = ( 
            
 
                      
             select  
             count 
             (*)  
             from  
             student,score  
             where  
             student.s_id = score.s_id  
             and  
             student.s_name=  
             '貂蝉' 
            
 
                  
             ) 
            
 
             ) 
            
 
             and  
             score.c_id  
             in 
             ( 
             select  
             c_id  
             from  
             student,score  
             where  
             student.s_id = score.s_id  
             and  
             student.s_name=  
             '貂蝉' 
             ) 
            

                
            
 
             GROUP  
             BY  
             score.s_id  
             HAVING  
             count 
             (*) =( 
             select  
             count 
             (*)  
             from  
             student,score  
             where  
             student.s_id = score.s_id  
             and  
             student.s_name=  
             '貂蝉' 
             ) 
            
 
             and  
             score.s_id !=2; 
            
 
      

按平均成绩倒序显示所有学生的“python”、“java”、“linux”三门的课程成绩，按如下形式显示：学生ID,python,java,linux,课程数,平均分

 
        
             #1.先查询单一学生的python课程分数 
            
 
             select  
             num  
             from  
             score,course  
             where  
             score.c_id = course.c_id   
             AND  
             course.c_name = 
             'python'  
             and  
             score.s_id = 1; 
            

                
            
 
             #2.将上面查询的结果作为 列字段使用 
            
 
             select  
             s.s_id, 
            
 
             ( 
             select  
             num  
             from  
             score,course  
             where  
             score.c_id = course.c_id  
             AND  
             course.c_name = 
             'python'  
             and  
             score.s_id = s.s_id )  
             as  
             'python' 
             , 
            
 
             ( 
             select  
             num  
             from  
             score,course  
             where  
             score.c_id = course.c_id  
             AND  
             course.c_name = 
             'java'  
             and  
             score.s_id = s.s_id )  
             as  
             'java' 
             , 
            
 
             ( 
             select  
             num  
             from  
             score,course  
             where  
             score.c_id = course.c_id  
             AND  
             course.c_name = 
             'linux'  
             and  
             score.s_id = s.s_id )  
             as  
             'linux' 
             , 
            
 
               
             count 
             (c_id) 
             as  
             '课程数' 
             , 
            
 
               
             avg 
             (num)  
             as  
             '平均分' 
            

                
            
 
             from  
             score s  
             GROUP  
             BY  
             s.s_id; 
            
 
      

统计各科各分数段人数.显示格式:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]

 
        
             select  
             score.c_id,course.c_name, 
            
 
             　　 
             sum 
             ( 
             CASE  
             WHEN  
             num  
             BETWEEN  
             85  
             and  
             100  
             THEN  
             1  
             ELSE  
             0  
             END 
             )  
             as  
             '[100-85]' 
             , 
            
 
             　　 
             sum 
             ( 
             CASE  
             WHEN  
             num  
             BETWEEN  
             70  
             and  
             85  
             THEN  
             1  
             ELSE  
             0  
             END 
             )  
             as  
             '[85-70]' 
             , 
            
 
             　　 
             sum 
             ( 
             CASE  
             WHEN  
             num  
             BETWEEN  
             60  
             and  
             70  
             THEN  
             1  
             ELSE  
             0  
             END 
             )  
             as  
             '[70-60]' 
             , 
            
 
             　　 
             sum 
             ( 
             CASE  
             WHEN  
             num < 60  
             THEN  
             1  
             ELSE  
             0  
             END 
             )  
             as  
             '[ <60]' 
            
 
             from  
             score,course  
             where  
             score.c_id=course.c_id  
             GROUP  
             BY  
             score.c_id; 
            
 
      

查询每门课程被选修的次数

 
        
             select  
             c_name, 
             count 
             (*)  
             from  
             course,score  
             where  
             course.c_id = score.c_id  
             GROUP  
             BY  
             score.c_id; 
            
 
      

查询出只选修了一门课程的学生的学号和姓名

 
        
             select  
             student.s_id,student.s_name  
             from  
             student,score  
             where 
            
 
             student.s_id = score.s_id  
             GROUP  
             BY  
             score.s_id  
             HAVING  
             count 
             (*)=1 
            
 
      

查询学生表中男生、女生各有多少人

查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列

 
        
             select  
             course.c_name, 
             avg 
             (num)  
             as  
             '平均成绩'  
             from  
             course,score 
            
 
             where  
             course.c_id = score.c_id  
             GROUP  
             BY  
             score.c_id  
             ORDER  
             BY  
             avg 
             (num),score.c_id  
             desc 
             ; 
            
 
      

查询课程名称为“python”，且分数低于60的学生姓名和分数

 
        
             select  
             student.s_name,score.num  
             from  
             score,course,student 
            
 
             where  
             score.c_id = course.c_id  
             and  
             student.s_id = score.s_id  
             and  
             course.c_name =  
             'python'  
             and  
             score.num < 67 
            
 
      

aaronthon

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 练习题4 参考答案

1.表结构如下:数据脚本2.查询:查询学习课程"python"比课程 "java" 成绩高的学生的学号;123456789#先查询"python"课程和"java"课程的学生成绩,临时表#让两个临时表进行比较-- select * from course c,score s w...
复制链接

扫一扫