select * from STUDENT055 where SNO in (select SNO from SCT055 where CNO in (select CNO from COURSE055 where CNAME = '数据库' ) );
select * from SCT055 where SNO in (select SNO from STUDENT055 where SDEPT in (select SDEPT from STUDENT055 where SNAME = '李维' ) );
select SNO,CNO,GRADE from SCT055 where GRADE in (select MAX(GRADE) from SCT055 group by SNO )
select * from TEACHER055 where TITLE in (select TITLE from TEACHER055 where TNAME = '王丽' ) and TSEX = '女';
select distinct TNO,CNO from SCT055 where TNO in (select TNO from TEACHER055 where TNAME = '周小平' or TNAME = '王建宁' ) order by TNO;
select * from STUDENT055 where SNO in ( select distinct SNO from SCT055 STU1 where not exists (select * from SCT055 STU2 where STU2.SNO in (select SNO from STUDENT055 where SNAME = '李维' ) and not exists (select * from SCT055 STU3 where STU3.SNO = STU1.SNO and STU3.CNO = STU2.CNO)) );
intersect取交集
select * from STUDENT055 where SNO in( select SNO from SCT055 where CNO in( select CNO from COURSE055 where CNAME = '数据库' ) ) intersect select * from STUDENT055 where SNO in( select SNO from SCT055 where CNO in( select CNO from COURSE055 where CNAME = 'C语言' ) )
union求并集
select * from STUDENT055 where SSEX = '女' and SDEPT = '计算机' union select * from STUDENT055 where SSEX = '女' and SDEPT = '电子'
差集
select * from STUDENT055 where SNO in( select SNO from SCT055 where CNO in( select CNO from COURSE055 where CNAME = 'C语言' ) ) except select * from STUDENT055 where SNO in( select SNO from SCT055 where CNO in( select CNO from COURSE055 where CNAME = '数据库' ) )
select * from COURSE055 where CREDIT = '3' union select * from COURSE055 where CREDIT = '4'
select * from COURSE055 where CNO in( select CNO from COURSE055 ) except select * from COURSE055 where CNO in( select distinct CNO from SCT055 )
(以下题目不限制查询方法)
按照逻辑应该是top5减去top2,但是这样在SQL server2019里得不到正确答案,必须用top5减去top3才能得到第3到第5名。
没有排序order by时 返回记录默认从高到低,由于GRADE的index是降序,所以order by GRADE直接得到降序输出
select top 5 SNAME,GRADE from STUDENT055,SCT055 where CNO = 'CS-001' and SCT055.SNO = STUDENT055.SNO except select top 3 SNAME,GRADE from STUDENT055,SCT055 where CNO = 'CS-001' and SCT055.SNO = STUDENT055.SNO order by GRADE
convert转换成浮点数相除,concat函数加上百分号,没有学生选修的课程也要考虑所以使用左外连接。
select CNAME,concat(convert(float,count(SCT055.SNO))/( select count(*) from STUDENT055 )*100,'%') as '选课人数占比' from COURSE055 left join SCT055 on COURSE055.CNO = SCT055.CNO group by COURSE055.CNAME order by count (*) desc
select SNAME from STUDENT055 where SNO in( select X.SNO from STUDENT055 X,SCT055 where X.SNO = SCT055.SNO group by X.SNO having count(CNO) not in( select count(CNO) from STUDENT055 Y,SCT055 where Y.SNO = SCT055.SNO and Y.SNO !=X.SNO group by Y.SNO ) )