数据库第三次作业
SQL-查询语句作业
实验指导书p71(1)
提交word文档,命名“学号_姓名_作业3”
(1)基于“教学管理”数据库jxgl,试用SQL的查询语句表达下列查询
SELECT SNO,SNAME FROM STUDENT WHERE SAGE > 23 AND SSEX = '男';
查询结果:
2.检索至少选修一门课程的女学生姓名
检索代码:
Select SNAME From STUDENT,SC Where SSEX='女'And STUDENT.SNO=SC.SNO GROUP BY STUDENT.SNAME HAVING COUNT (CNO)>=1;
查询结果:
3.检索王同学不学的课程的课程号
检索代码:
Select CNO From COURSE Where CNO Not IN( Select SC.CNO From COURSE,STUDENT,SC WHERE COURSE.CNO =SC.CNO AND STUDENT.SNO = SC.SNO And SNAME LIKE '王%' );
查询结果:
SELECT STUDENT.SNO FROM SC,STUDENT WHERE STUDENT.SNO=SC.SNO GROUP BY STUDENT.SNO HAVING COUNT(CNO)>=2;
查询结果:
SELECT CNO,CNAME FROM COURSE WHERE NOT EXISTS ( SELECT * FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM SC WHERE STUDENT.SNO = SC.SNO AND SC.CNO=COURSE.CNO ) );
查询结果:
SELECT SNO FROM STUDENT WHERE NOT EXISTS( SELECT * FROM COURSE WHERE COURSE.CCREDIT=3 AND NOT EXISTS( SELECT * FROM SC WHERE SC.SNO=STUDENT.SNO AND SC.CNO=COURSE.CNO ));
查询结果:
做题总结:检索语法还需加强练习,熟练掌握并灵活运用