sql 语句练习

数据库题目练习记录

csdn： https://blog.csdn.net/qq_41196190/article/details/101428660

1. 数据库中 union 和 union all 的区别

   • union：对两个结果集进行并集操作，不包括重复行，同时默认规则排序

   • union all：对两个结果集进行并集操作，包括重复行，不进行排序，

   总的来说，两者都是取并集，但是 union all 重复的行会全部显示出来而不是只显示一个

2. S(sno,sname) 学生表，sno 为学号，sname 为姓名

   C(cno,cname,cteacher) 课程表。cno 为课程号，cname 为课程名，cteacher 为任课教师

   SC(snc,cno,sgrade) 选课关系表，sgrade 为成绩

   2.1 找出没有选修过“李明”老师讲授课程的所有学生姓名

   
    select sname
   
    from s
   
    where sno not in(select distinct(sno) from sc
   
   
   
                where cno in(
   
                    select cno from c 
   
                    where cteacher="李明"
   
                ))
   
   
   
   或者
   
   select sname
   
   from s
   
   where not exists(select * from S,C,SC
   
                   where S.sno = SC.sno and C.cno = Sc.cno and c.cteacher = "李明"
   
   
   
                   )
   
   

   2.2 列出有两门以上不及格课程的学生姓名及其平均成绩

   
   select s.sname,ave(scgrade)
   
   from s,sc, (select cno from sc where scgrade<60 group by sno having count(distinct cno)>=2) A
   
   where s.sno=a.sno and sc.sno=a.sno
   
   group by s.sno,s.sname
   
   
   
   

   2.3 列出既学过课程名为”1“的课程，又学过课程名为”2“的课程的所有学生姓名

   
   select A.sno,A.sname
   
   from (select sno,sname from sc where cno =1) a,
   
        (select sno,sname from sc where cno =2) b
   
   where a.sno=b.sno
   
   或者
   
   select s.sno,s.sname
   
   from s,(select sc.sno from sc,c 
   
           where sc.cno=c.cno and c.cname in(1,2)
   
           group by sno
   
           having count(distinct CNO)=2
   
   )sc
   
   where s.sno=sc.sno
   
   
   
   

   2.4 列出 1 号课程成绩比 2 号课程成绩高的学号及其 1 号课程和 2 号程的成绩

   
   select sc1.sno,sc1.scgrade as "1号课程成绩",sc2.scgrade as "2号课程成绩"
   
   from sc sc1,sc sc2
   
   where sc1.cno=1 and sc2.cno=2 and sc1.sno=sc2.sno
   
         and sc1.scgrade>sc2.scgrade
   
   

如果有错误，请大佬提出