2023年6月21日发(作者：)
数据库学习打卡第11天SQL查询语句篇（3）数据库打卡第11天,SQL语句题⽬稍难。我也很多天没有来学习打卡了，最近事情其实挺少的，但是每天也学的也挺少的。最后冲刺半个⽉了，加油吧。(1)查询所有学⽣的选课信息-- --通过⾃然连接实现，去掉重复属性列。Select ,Sname,Ssex,Sage,Sdept,,Grade from Student,SCwhere = –(2)(重要)查询包含学⽣201215122同学所选课程的学⽣的学号–通俗点讲,是查询和学号为201215121的学⽣选修了同⼀门课程号的学⽣的学号。存在同⼀学号选择多门课程的情况，所以要消除重复值。–Select distinct Sno from SC where Cno In–(Select Cno from SC where Sno =‘201215122’)–(3)(重要)查询刘晨同学不学课程的课程号和课程名–Select Cno,Cname from Course where Cno Not IN–(Select Cno from Student,SC where = And Sname = ‘刘晨’)–(4)(难度)查询选修的课程数超过(含)2门的学⽣的学号和姓名(使⽤嵌套查询+Group分组⼦句)–Select Sno,Sname from Student where Sno In–(Select Sno from SC Group by Sno Having Count(Sno)>=2)–(5)(难度)查询平均成绩在80分以上(含)的学⽣的学号和姓名(使⽤嵌套查询+Group分组⼦句)–Select Sno,Sname from Student where Sno In–(Select Sno from SC Group by Sno Having AVG(Grade)>=80)–(6)(难度)查询出选修了全部课程的学⽣的姓名/Select Sname from Student where Not Exists(Select * from Course where Not Exists(Select * from SC where = And = ))/–(7)(难度)查询出全部学⽣都选修的课程名称/Select Cname from Course where Exists(Select * from Student where Exists(Select * from SC where = And = ))/–(8)(难度)查询⾄少选修了学⽣"201215122"选了全部课程的学⽣的姓名简⽽⾔之：(查询和学⽣"201215122"选修了同⼀门课程的学⽣的学号和姓名)/Select Sno,Sname from Student where Sno In(Select Sno from SC where Cno In(Select Cno from SC where Sno = ‘201215122’))/ --假如集合内为(2,3)课程–(9)(普通)查询没有选课的学⽣的姓名通过取SC表中没有的学⽣的学号,到S表中找没有选课的学⽣的姓名–Select Sname from Student where Sno Not In–(Select Sno from SC)
2023年6月21日发(作者：)
数据库学习打卡第11天SQL查询语句篇（3）数据库打卡第11天,SQL语句题⽬稍难。我也很多天没有来学习打卡了，最近事情其实挺少的，但是每天也学的也挺少的。最后冲刺半个⽉了，加油吧。(1)查询所有学⽣的选课信息-- --通过⾃然连接实现，去掉重复属性列。Select ,Sname,Ssex,Sage,Sdept,,Grade from Student,SCwhere = –(2)(重要)查询包含学⽣201215122同学所选课程的学⽣的学号–通俗点讲,是查询和学号为201215121的学⽣选修了同⼀门课程号的学⽣的学号。存在同⼀学号选择多门课程的情况，所以要消除重复值。–Select distinct Sno from SC where Cno In–(Select Cno from SC where Sno =‘201215122’)–(3)(重要)查询刘晨同学不学课程的课程号和课程名–Select Cno,Cname from Course where Cno Not IN–(Select Cno from Student,SC where = And Sname = ‘刘晨’)–(4)(难度)查询选修的课程数超过(含)2门的学⽣的学号和姓名(使⽤嵌套查询+Group分组⼦句)–Select Sno,Sname from Student where Sno In–(Select Sno from SC Group by Sno Having Count(Sno)>=2)–(5)(难度)查询平均成绩在80分以上(含)的学⽣的学号和姓名(使⽤嵌套查询+Group分组⼦句)–Select Sno,Sname from Student where Sno In–(Select Sno from SC Group by Sno Having AVG(Grade)>=80)–(6)(难度)查询出选修了全部课程的学⽣的姓名/Select Sname from Student where Not Exists(Select * from Course where Not Exists(Select * from SC where = And = ))/–(7)(难度)查询出全部学⽣都选修的课程名称/Select Cname from Course where Exists(Select * from Student where Exists(Select * from SC where = And = ))/–(8)(难度)查询⾄少选修了学⽣"201215122"选了全部课程的学⽣的姓名简⽽⾔之：(查询和学⽣"201215122"选修了同⼀门课程的学⽣的学号和姓名)/Select Sno,Sname from Student where Sno In(Select Sno from SC where Cno In(Select Cno from SC where Sno = ‘201215122’))/ --假如集合内为(2,3)课程–(9)(普通)查询没有选课的学⽣的姓名通过取SC表中没有的学⽣的学号,到S表中找没有选课的学⽣的姓名–Select Sname from Student where Sno Not In–(Select Sno from SC)
发布评论