查询同时选了1,2号课程的学生姓名,学号
select sname,sno
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') );
3007 查询李明,刘晨两位同学的平均成绩,并用学号和平均成绩表示出来
select sc.sno,avg(grade) as 平均成绩
from sc,student
where sc.sno = student.sno
and sname in('李明','刘晨')
group by sc.sno;
3008 查询和李明同一个系,年龄比他大的同学姓名(当前年 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='李明');
4006 创建一个名字为grade_view视图,将计算机系和管理系,平均成绩及格的同学的学号,姓名及平均成绩放入该视图中(平均成绩要用pjgrade表示)
creat view grade_view