sql题精选

落日映苍穹つ 2022-03-25 06:53 152阅读 0赞

学习mysql的时候在网上找了很多sql题，但很多答案是有误的。为了方便日后复习，总结了一份sql题精华，答案都是自己手写的，也亲测过，希望对学习者能起到帮助！

/*
    Student(Sid,Sname,Sage,Ssex) 学生表 
    Course(Cid,Cname,Tid) 课程表 
    SC(Sid,Cid,score) 成绩表 
    Teacher(Tid,Tname) 教师表
    */

-- 01、查询“001”课程比“002”课程成绩高的所有学生的学号；
    select a.sid from (select sid,score from sc where cid = 1)a,(select sid ,score from sc where cid = 2)b where a.score>b.score and a.sid=b.sid;
     
    -- 02、查询平均成绩大于60分的同学的学号和平均成绩； 
    select sid,avg(score) from sc group by sid having avg(score)>60;
     
    -- 03、查询所有同学的学号、姓名、选课数、总成绩；
    select s.sid,s.sname,count(sc.cid),sum(score) from student s left outer join sc on s.sid=sc.sid group by sid;
     
    -- 04、查询姓“李”的老师的个数；
    SELECT COUNT(tid) FROM teacher WHERE tname LIKE "周%";
    SELECT COUNT(DISTINCT(tname)) FROM teacher WHERE tname LIKE "周%";
    
    -- 05、查询没学过“叶平”老师课的同学的学号、姓名；
    select sid ,sname from student where sid not in 
    (select sid from sc,course,teacher where sc.cid=course.cid and course.tid=teacher.tid and tname="叶平");
    
    select sid ,sname from student where sid not in(
    	select sid from sc where cid in(
    		select cid from course where tid =(
    			select tid from teacher where tname ="叶平"))
    );
     
    -- 06、查询学过“001”并且也学过编号“002”课程的同学的学号、姓名；
    select s.sid,s.sname from student s,sc where s.sid=sc.sid and sc.cid =1 and exists (select * from sc sc2 where sc.sid=sc2.sid and sc2.cid=2);
    select sid,sname from student where sid in (select sid from sc where cid=1 and exists (select * from sc sc2 where sc.sid=sc2.sid and sc2.cid=2));
    
    -- 07、查询学过“叶平”老师所教的所有课的同学的学号、姓名；
    select sid,sname from student where sid in(
    	select sid from sc where cid in(
    		select cid from course where tid=(
    			select tid from teacher where tname="叶平")) 
    	group by sid having count(sc.cid)= (select count(cid) from course where tid = (select tid from teacher where tname = "叶平") )  
    ); 
    
    select sid,sname from student where sid in(
    	select sid from sc,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and t.tname="叶平" group by sid 
    		having count(sc.cid)=(select count(cid) from course c,teacher t where c.tid=t.tid and tname="叶平")
    );
    -- 08、查询课程编号“002”的成绩比课程编号“001”课程低的所有同学的学号、姓名；
    select sid,sname from student where sid in(
    	select a.sid from (select sid,score from sc where cid=1)a,(select sid,score from sc where cid=2)b where a.score>b.score and a.sid=b.sid  
    );
    -- 09、查询所有课程成绩小于60分的同学的学号、姓名；-- 转换：只要有一门成绩大于60分即不符合要求，使用not in
    select sid,sname from student where sid not in(
    	select sid from sc where score >60
    );
    -- 10、查询没有学全所有课的同学的学号、姓名； 
    select sid,sname from student where sid not in(
    	select sid from sc group by sid having count(cid)=(select count(cid) from course)
    );
    
    select s.sid,s.sname from student s,sc 
    	where s.sid=sc.sid 
    	group by s.sid 
    	having count(sc.cid)<(select count(cid) from course);
    
    -- 11、查询至少有一门课与学号为“1001”的同学所学相同的同学的学号和姓名；
    select distinct s.sid,s.sname from student s,sc where s.sid=sc.sid and sc.cid in (
    	select cid from sc where sid= 7
    );
    -- 12、查询至少学过学号为“001”同学所学一门课的其他同学学号和姓名；
    select distinct s.sid,s.sname from student s,sc where s.sid=sc.sid and s.sid!=7 and sc.cid in( 
    	select cid from sc where sid=7
    );
    -- 13、把“SC”表中“叶平”老师教的课的成绩都更改为此课程的平均成绩；
    update sc1 set score=(
    	select avg(score) from sc group by cid having cid in( -- “叶平”老师教的课程的平均成绩
    		select cid from course where tid = (select tid from teacher where tname="叶平")-- “叶平”老师教的课程的cid
    	)
    )where
    	 cid in(select cid from course where tid = (select tid from teacher where tname="叶平"));
    
    
    update sc1 set score=(
    	select avg(score) from sc group by cid having cid in(
    		select cid from course c,teacher t where c.tid=t.tid and t.tname="叶平" 
    	)
    )where 
     cid in(select cid from course c,teacher t where c.tid=t.tid and t.tname="叶平");
    
    -- 14、查询和“1002”号的同学学习的课程完全相同的其他同学学号和姓名；
    select s.sid,s.sname from student s,sc 
    	where s.sid=sc.sid and s.sid!=2 and cid in (select cid from sc where sid=2)
    	group by sid,sname 
    	having count(*)=(select count(*) from sc where sid=2);
    
    
    select sid,sname from student where sid in(
    	select sid from sc where cid in (
    		select cid from sc where sid=2
    	)group by sid having count(cid)=(select count(cid) from sc where sid=2)
    );
    
    -- 15、删除学习“叶平”老师课的SC表记录； 
    delete from sc1 where cid in (select cid from course where tid =(select tid from teacher where tname="叶平"));
    delete from sc1 where cid in (select cid from course c,teacher t where c.tid=t.tid and tname="叶平");
    
    -- 16、向SC表中插入一些记录，这些记录要求符合以下条件：没有上过编号“003”课程的同学学号、2号课的平均成绩；
    Insert sc1 select Sid,'002',(Select avg(score)from SC where Cid='002') 
    		from Student where Sid not in (Select Sid from SC where Cid='002'); 
    
    insert sc1 select sid,2,(select avg(score) from sc where cid=2)
    	from student where sid not in (select sid from sc where cid =3)
    
    
    -- 17、按平均成绩从高到低显示所有学生的“数据库”、“企业管理”、“英语”三门的课程成绩，按如下形式显示： 学生ID,,数据库,企业管理,英语,有效课程数,有效平均分 
    SELECT Sid as 学生ID 
            ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid='004') AS 数据库 
            ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid='001') AS 企业管理 
            ,(SELECT score FROM SC WHERE SC.Sid=t.Sid AND Cid='003') AS 英语 
            ,COUNT(*) AS 有效课程数, AVG(t.score) AS 平均成绩 
        FROM SC AS t 
        GROUP BY Sid 
        ORDER BY avg(t.score)DESC
    
    select sid 学生ID,
    		(select score from sc where sc.sid=t.sid and cid=2)数学,
    		(select score from sc where sc.sid=t.sid and cid=1)语文,
    		(select score from sc where sc.sid=t.sid and cid=4)物理,
    		(select score from sc where sc.sid=t.sid and cid=3)英语,
    		count(*) 有效课程数,avg(t.score) 平均成绩
    	from sc t
    	group by sid
    	order by avg(t.score) desc
    
    
    -- 18、查询各科成绩最高和最低的分：以如下形式显示：课程ID，最高分，最低分 
    select cid 课程ID,MAX(score) 最高分,min(score) 最低分 from sc group by cid
       
    -- 19、按各科平均成绩从低到高和及格率的百分数从高到低顺序
     
       
    -- 20、查询如下课程平均成绩和及格率的百分数(用"1行"显示): 企业管理（001），马克思（002），OO&UML （003），数据库（004） 
        
    -- 21、查询不同老师所教不同课程平均分从高到低显示 
     
    -- 22、查询如下课程成绩第 3 名到第 6 名的学生成绩单：企业管理（001），马克思（002），UML （003），数据库（004） 
    --    [学生ID],[学生姓名],企业管理,马克思,UML,数据库,平均成绩 
       
    -- 23、统计列印各科成绩,各分数段人数:课程ID,课程名称,[100-85],[85-70],[70-60],[ <60]  
    SELECT SC.Cid as 课程ID, Cname as 课程名称 
            ,SUM(CASE WHEN score BETWEEN 85 AND 100 THEN 1 ELSE 0 END) AS [100 - 85] 
            ,SUM(CASE WHEN score BETWEEN 70 AND 85 THEN 1 ELSE 0 END) AS [85 - 70] 
            ,SUM(CASE WHEN score BETWEEN 60 AND 70 THEN 1 ELSE 0 END) AS [70 - 60] 
            ,SUM(CASE WHEN score < 60 THEN 1 ELSE 0 END) AS [60 -] 
        FROM SC,Course 
        where SC.Cid=Course.Cid 
        GROUP BY SC.Cid,Cname; 
    
    
    
    select sc.cid 课程ID,cname 课程名称,
    		sum(case when score between 85 and 100 then 1 else 0 end) "[100-85]",
    		sum(case when score between 70 and 85 then 1 else 0 end) "[85-70]",
    		sum(case when score between 60 and 70 then 1 else 0 end) "[70-60]",
    		sum(case when score < 60 then 1 else 0 end) "[60-]"
    	from sc,course c
    	where sc.cid=c.cid
    	group by sc.cid;
    
    -- 24、查询学生平均成绩及其名次
    -- (1)
    select sid,avg(score) from sc group by sid order by avg(score) desc
    
    -- (2)
    select () 名次,学号,平均成绩 
    	from (
    		select sid 学号 ,avg(score) 平均成绩 from sc group by sid )t2
    	order by 平均成绩 desc;
    
    -- (3)
    select (select count(distinct 平均成绩) from (select sid,avg(score) 平均成绩 from sc group by sid)t1 where 平均成绩>t2.平均成绩)+1 名次,sid 学号,平均成绩 
    	from(
    			select sid,avg(score) 平均成绩 from sc group by sid )t2 
    	order by 平均成绩 desc; 
    
          
    -- 25、查询各科成绩前三名的记录:(不考虑成绩并列情况) 
    select * from sc a where (select count(*) from sc where sc.cid=a.cid and sc.score>a.score)<3 order by a.cid,a.score desc
     
    -- 26、查询每门课程被选修的学生数 
    select cid,count(sid) from sc group by cid
      
    -- 27、查询出只选修了一门课程的全部学生的学号和姓名
    select s.sid,s.sname from student s,sc where s.sid=sc.sid group by s.sid having count(cid)=1
     
    -- 28、查询男生、女生人数 
    select count(sid) 男生人数 from student where ssex="男";
    select count(sid) 女生人数 from student where ssex="女";
        
    -- 29、查询姓“张”的学生名单 
    select sname from student where sname like "张%"
     
    -- 30、查询同名同性学生名单，并统计同名人数 
    select sname,count(*) from student group by sname having count(*)>1
      
    -- 31、1981年出生的学生名单(注：Student表中Sage列的类型是datetime)
        
    -- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程号升序排列 
    select cid,avg(score) from sc group by cid order by avg(score) desc,cid 
        
    -- 33、查询平均成绩大于85的所有学生的学号、姓名和平均成绩 
    select s.sid,s.sname,avg(score) from student s,sc where s.sid=sc.sid group by s.sid having avg(score)>85
       
    -- 34、查询课程名称为“数据库”，且分数低于60的学生姓名和分数 
    select s.sname,sc.score from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and c.cname="语文" and score<60
       
    -- 35、查询所有学生的选课情况；
    select s.sid,s.sname,c.cid,c.cname from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid order by sid,cid
    
    -- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
    select s.sname,c.cname,sc.score from student s,sc,course c where s.sid=sc.sid and sc.cid=c.cid and sc.score >70 
        
    -- 37、查询不及格的课程，并按课程号从大到小排列 
    select * from sc where score<60 order by cid desc
        
    -- 38、查询课程编号为003且课程成绩在80分以上的学生的学号和姓名； 
    select s.sid,s.sname from sc,student s where cid=3 and score>80 and s.sid=sc.sid
    select sid,sname from student where sid in(select sid from sc where cid=3 and score>80)
        
    -- 39、求选了课程的学生人数 
    select count(a.sid) from (select sid from sc group by sid) a
    
    -- 40、查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩 
    select s.sname,score from student s,sc where s.sid=sc.sid and cid in(
    	select cid from course c,teacher t where c.tid=t.tid and tname="叶平") order by score desc limit 1;
    
    select s.sname,score from student s,sc sc1,course c,teacher t where s.sid=sc1.sid and sc1.cid=c.cid and c.tid=t.tid and t.tname="叶平" and score=(
    	select MAX(score) from sc where cid=sc1.cid)
       
    -- 41、查询各个课程及相应的选修人数 
    select cid,count(cid) from sc group by cid
        
    -- 42、查询不同课程成绩相同的学生的学号、课程号、学生成绩 
    select distinct a.sid,a.score from sc a,sc b where a.score=b.score and a.cid != b.cid
    select distinct a.sid,a.score from sc a where exists (select * from sc where sc.cid<>a.cid and score=a.score)
     
    -- 43、查询每门功课成绩最好的前两名 
    select sid,cid,score from sc a where( select count(*) from sc where sc.cid=a.cid and sc.score>a.score)<2 order by a.cid,a.score desc
       
    -- 44、统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列  
    select cid 课程号,count(*) 选修人数 from sc group by cid order by count(*) desc,cid asc
         
    -- 45、检索至少选修两门课程的学生学号 
    select sid from sc group by sid having count(cid)>2
       
    -- 46、查询全部学生都选修的课程的课程号和课程名 
    -- 分析：选修这门课程的学生人数等于学生总人数
    select cid,cname from course where cid in(select cid from sc group by cid having count(cid)=(select count(sid) from student) )
         
    -- 47、查询没学过“叶平”老师讲授的任一门课程的学生姓名 
    select sname from student where sid not in (select sc.sid from sc,course c,teacher t where sc.cid=c.cid and c.tid=t.tid and t.tname="叶平");
    
    select sname from student where sid not in (
    	select sid from sc where cid in (select cid from course where tid =(select tid from teacher where tname="叶平") )
    );   
    -- 48、查询两门以上不及格课程的同学的学号及其平均成绩 
    select sid,avg(ifnull(score,0)) from sc where sid in (select sid from sc where score <60 group by sid having count(*)>=2 ) group by sid;
        
    -- 49、检索“004”课程分数小于60，按分数降序排列的同学学号
    select sid from sc where cid=4 and score <60  order by sid desc
        
    -- 50、删除“002”同学的“001”课程的成绩 
    delete from sc1 where sid=2 and cid=1;

-- 关于时间的几个方法
    select NOW();
    select SYSDATE();
    select CURRENT_TIME;
    select CURRENT_TIMESTAMP;
    select REPLACE(unix_timestamp(current_timestamp(3)),'.','');
    -- 测试sql语句执行过程的关键字explain
    explain select * from sc;