数据库24个查询代码
--1. 查询学生的基本信息; select * from student go
--2. 查询“CS ”系学生的基本信息; select * from student where sdept='cs' go
--3. 查询“CS ”系学生年龄不在19到21之间的学生的学号、姓名; select sno,sname from student
where sdept='cs' and sage not between 19 and 21 go
--4. 找出最大年龄; select max(sage) from student go
--5. 找出“CS ”系年龄最大的学生,显示其学号、姓名; select sno,sname from student
where sdept='cs' and sage=(select max(sage) from student where sdept='cs' ) go --6. 找出各系年龄最大的学生,显示其学号、姓名;
select sno,sname,sdept from student s1
where sage=(select max(sage) from student
where sdept=s1.sdept ) go
--7. 统计“CS ”系学生的人数; select count(*) from student where sdept='cs' go
--8. 统计各系学生的人数,结果按升序排列; select sdept,count(sno) from student group by sdept order by count(sno) go
--9. 按系统计各系学生的平均年龄,结果按降序排列;
select sdept,avg(sage) as sm from student group by sdept order by sm desc go
--10. 查询每门课程的课程名; select cno,cname from course go
--11. 查询无先修课的课程的课程名和学分; select cname,ccredit from course
where cpno is null go
--12. 统计无先修课的课程的学分总数;select * from course
select sum(ccredit) from course
where cpno is null go
--13. 统计每位学生选修课程的门数、学分及其平均成绩;
select sno,count(sc.cno),sum(ccredit),avg(grade) from sc,course
where sc.cno=course.cno group by sno go
--14. 统计选修每门课程的学生人数及各门课程的平均成绩;
select cno,count(sno),avg(grade)
from sc from sc select distinct sno group by cno from sc go --15. 找出平均成绩在85分以上的学生,结果
按系分组,并按平均成绩的升序排列; select sdept,sc.sno,avg(scores) from sc,student where sc.sno=s.sno group by sc.sno,sdept having avg(scores)>85 go --16. 查询选修了“1”或“2”号课程的学生学号和姓名; select sc.sno,sname from sc,student where sc.sno=student.sno and (sc.cno='1' or sc.cno='2') go --17. 查询选修了“1”和“2”号课程的学生
学号和姓名;
select sc.sno,sname from sc,student where sc.sno=student.sno and sc.cno='1' and sc.sno in(select sno where cno='2') go --18. 查询选修了课程名为“数据库系统”且成绩在60分以下的学生的学号、姓名和成绩; select sc.sno,sname,grade from sc,student,course where sc.sno=student.sno and sc.cno=course.cno and course.cname='数据库系统' and grade
示:学号,姓名,课程号,课程名,成绩); --20 查询没有选修课程的学生的基本信息; select sno,sname,sage from s where sno not in(select distinct sno from sc) go --21. 查询选修了3门及以上课程的学生学号;
select sno from sc group by sno having count(cno)>=3 go --22. 查询选修课程成绩至少有一门在80分以上的学生学号;
where grade>80
go --23. 查询选修课程成绩均在80分以上的学生学号; select distinct sno from sc where sno not in(select distinct sno from sc where grade
go --24. 查询选修课程平均成绩在80分以上的学生学号; select sno from sc group by sno having avg(grade)>80 go