关于SQL语句的例子

题目 操作步骤或代码 效果截图
1.查询学生的基本信息 Select * from student

2.查询‘CS’系学生的基本信息 select * from student
where Sdept=‘CS’;

3.查询’CS’系学生年龄不在19到21之间的学生的学号、姓名 select Sno,Sname from student
where Age <19 or Age >21;

4.找出最大年龄 select max(Age) as maxAge from student;

5．找出‘CS’系年龄最大的学生，显示其学号、姓名 select Sno,Sname from student
where Sdept = ‘CS’ and Age in (select max(Age) from student);

6.查询所有姓王的学生的学号、姓名、所在系 SELECT Sno,Sname,Sdept FROM student WHERE LEFT(Sname,1)=‘王’;

7.统计‘CS’系学生人数 select count(Sno) as number from student
where Sdept=‘CS’;

8.统计各系学生的人数，结果按降序排列 select count(Sno) as number,Sdept from student
group by Sdept
order by count(Sno);

9.按系统计各系学生的平均年龄，结果按降序排列 select avg(Age) as avgAge from student
group by Sdept
order by avg(Age) desc;

10.查询每门课程的课程名 select Cname from course;

11.统计无先修课的课程的学分总数 select sum(Credit) from course
where Cpno is NULL;

12.查询选修了‘1’或‘2’号课程的学生学号和姓名。 select Sno , Sname from student
where Sno in (select Sno from sc where Cno = 1 or Cno = 2 );

13.查询选修了‘1’和‘2’号课程的学生学号和姓名。 select Sno,Sname from student
where Sno in (select Sno from sc p1 where Cno = 1 and (select Sno from sc p2 where Cno = 2 and p1.Sno=p2.Sno));

14.查询选修了课程名为“数据库”且成绩在60分以下的学生的学号、姓名和成绩 select student.Sno,Sname,Grade from student,sc,course
where Cname=‘数据库’ and Grade < 60 and student.Sno = sc.Sno and course.Cno = sc.Cno;

15.查询每位选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩） select sc.Sno,Sname,sc.Cno,Cname,Grade from sc,student,course
where sc.Sno=student.Sno and sc.Cno = course.Cno;

16.查询每位选修了课程的学生信息（显示：学号，姓名，课程号，课程名，成绩，包括没选课的学生） select student.Sno,Sname,sc.Cno,Cname,Grade
from student left outer join
(sc left outer join course on(sc.Cno = course.Cno)) on student.Sno = sc.Sno ;

17.查询没有选修课程的学生的基本信息 select * from student
where Sno not in(select Sno from sc);

18.查询各系年龄最大的学生，显示其学号、姓名 select Sno,Sname from student
where Age in (select max(Age) from student
group by Sdept)

19.查询选修了3门以上课程的学生学号 select Sno from sc
group by Sno
having count(*) >= 3;

20.查询选修课程成绩至少有一门在80分以上的学生学号 use scdb;
select Sno from sc
where Sno in
(select Sno from sc where Grade >=80 group by Sno)
group by Sno
having count(Cno)>=1

21.查询选修课程成绩均在80分以上的学生学号 select Sno from sc
where Sno not in
(select Sno from sc where Grade < 80 group by Sno)
group by Sno;

22查询选修课程平均成绩在80分以上的学生学号 SELECT sno from sc GROUP BY sno HAVING avg(grade)>80

23.统计每位学生选修课程的门数、学分及其平均成绩 select student.Sno,count(course.Cno),sum(Credit),avg(Grade)
from student left outer join (sc left outer join course on sc.Cno = course.Cno) on student.Sno = sc.Sno
group by student.Sno;

24统计选修每门课程的学生人数，及各门课程的平均分 Select Cno,count(Sno) as SnoNum,avg(Grade) from sc
group by Cno;

25.找出平均分在85分以上的学生，结果按系分组，并按平均成绩的升序排列 select Student.Sno,Sname,Sdept,avg(Grade) as avgGrade
from student,sc,course
where student.Sno = sc.Sno and sc.Cno = course.Cno
group by Sno
having avg(Grade) > 85
order by avg(Grade);