sql嵌套查询语句.docVIP

sql嵌套查询语句 篇一：sql嵌套查询语句 select COUNT(*) as “人数”,SGender 性别 from Students where ssdept=“计算机科学与技术” group by SGender--查询每个系男女生人数select COUNT(*) as “人数”,SSdept as “系”,SGender as “性别” from students group by ssdept,SGender--查询男生人数超过1的系select ssdept as “所在系”,COUNT(*) as “人数”from Students where SGender=“Male” group by ssdept having COUNT(*) 2--查询和张三同一系的学生学号、姓名select SId as “学号” ,SName as “姓名”from Students where SSdept=(select ssdept from Students where SName=“张三”) and SName “张三”--查询比张三年纪大的学生的姓名、性别select SName as “姓名”,SGender as “性别” from Students where SAge (select sage from students where sname=“张三”)--查询张三的学号和其选修的课程号和成绩select sc.SCCNo as “课程号”,sc.SCGrade as “成绩”from students as s,Course c,StudentCourse scwhere s.SId=sc.SCId and sc.SCCNo=c.CNo and s.SName=“张三”--查询张三选修高等数学上这门课的成绩select sc.SCCNo as “课程号”,c.CName as “课程名”,sc.SCGrade as “成绩”from students as s,Course c,StudentCourse scwhere s.SId=sc.SCId and sc.SCCNo=c.CNo and c.CName=“高等数学上” and s.SName=“张三”--查询与张三一样大的学生姓名，性别，年龄。select SName as “姓名”,SGender as “性别”,SAge as “年龄” from Studentswhere SAge=(select SAge from Students where SName=“张三”) and SName “张三”--查询选修了高等数学上的学生的学号、姓名select s.SId as “学号”,s.SName as “姓名”from students as s,Course c,StudentCourse scwhere s.SId=sc.SCId and sc.SCCNo=c.CNo and c.CName=“高等数学上”--查询张三选修的所有课程号、课程名称、成绩select sc.SCCNo as “课程号”,c.CName as “课程名”,sc.SCGrade as “成绩”from students as s,Course c,StudentCourse scwhere s.SId=sc.SCId and sc.SCCNo=c.CNo and s.SName=“张三”--查询学习了张三选修某门课程的学生学号、姓名、年龄select * from Students s1,StudentCourse sc1 where sc1.SCCNo in select sc.SCCNo from Students s,StudentCourse sc where sc.SCId=s.SId and s.SName=“张三” and sc1.SCId=s1.SId--查询张三选修的所有课程的课程号、课程名称select sc.SCCNo as “课程号”,c.CName as “课程名” from Students s,Course c,StudentCourse scwhere s.SId=sc.SCId and sc.SCCNo=c.CNo and s.SName=“张三”--查询比张三年龄大的学生学号、姓名select SId as “学号”,SName as “姓名” from Students where SAge (select SAge from Students where SName=“张三”)--查询选修每门课程中成绩小于其平均成绩的学生学号select sc1.SCId as “学生学号” from StudentCours