数据库技术_查询练习题

1.查询选修了和王名一样学分数的其他同学姓名。

select sname
from student
where sno in(select sno 
from sc,course 
where sc.cno =course.cno 
group by sno 
having sum(ccredit)=(select sum(ccredit) 
from sc,course 
where sc.cno =course.cno  
and sno in(select sno 
from student 
where sname='王名') )
and sname<>'王名')

2.查询选修了1号课并且成绩比全班1号课平均成绩低的同学学号及成绩。

select sno,grade
from sc
where cno='1' 
and grade<(select avg(grade) 
from sc 
where cno='1')

3.分别按每年统计出生在1988-1990年三年间的学生人数，平均成绩，（学生人数，平均成绩表示）。

select count(student.sno) as 学生人数,avg(grade) as 平均成绩 
from student,sc 
where student.sno=sc.sno 
group by year(csrq) 
having year(csrq)=1988 
or year(csrq)=1989 
or year(csrq)=1990

4.查询个人平均成绩比王名同学成绩的最高成绩还要高的同学的账户名和密码。

select  user1,password1 
from mm 
where sno in(select sno 
from sc 
group by sno 
having avg(grade)>(select max(grade) 
from sc 
where sno in(select sno 
from student 
where sname='王名')))

5.查询选过课的但没有在MM表中注册的同学姓名。

select sname
from student
where sno in(select sno from sc,course where sc.cno=course.cno) 
and sno not in (select sno from mm )

6.查询选修的学分数超过8个学分的同学学号及学分数。

select sname
from student
where sno in(select sno 
from sc,course 
where sc.cno= course.cno 
group by sno 
having sum(ccredit)>8)

7.查询李明，刘晨两位同学的平均成绩，并用学号和平均成绩表示出来。

select sc.sno,avg(grade) as 平均成绩 
from sc,student 
where sc.sno = student.sno 
and sname in('李明','刘晨') 
group by sc.sno

8.查询和李明同一个系，年龄比他大的同学姓名(当前年2010)。

select sname 
 from student 
 where sdept in(select sdept 
 from student 
 where sname='李明') 
 and 2010-year(csrq)>all(select 2010-year(csrq) 
 from student 
 where sname='李明')

9.查询数据库和数学这两门课的选课人数，并用课程号和选课人数表示出来。

select course.cno as 课程号, count(sc.sno) as 选课人数  
from sc,course 
where sc.cno = course.cno 
and (cname = '数据库' or cname = '数学') 
group by course.cno

10.查询同时选修了1,2,3号课的同学的姓名及系。

select sname,sdept
from student
where sno in (select sno 
from sc 
where sno in(select sno 
from sc 
where sno in(select sno 
from sc 
where cno='1'  ) 
and cno='2') 
and cno='3')