SQL实验九答案.docVIP

实验九SQL语言的应用之二答案 一、1、select * from jsqk where 教授$ zc and xbbh=(select xbbh from xb where xbmc=计算机系) select * from jsqk,xb where jsqk.xbbh=xb.xbbh and xbmc=计算机 and zc like %教授 2、select xm,jcgz from jsqk,gz where jsqk.jsbh=gz.jsbh and jcgz1000 3、select xm from jsqk where jsbh not in (select jsbh from kyqk) 4、select xbmc from xb where xbbh not in (select xbbh from jsqk, kyqk where kyqk.jsbh=jsqk.jsbh) sele xbmc from xb,jsqk where xb.xbbh=jsqk.xbbh and jsbh not in(select jsbh from kyqk) 5、select sum(jcgz) from gz 6、select top 1 xbbh,count(cgmc) 总数 from jsqk, kyqk where； jsqk.jsbh=kyqk.jsbh group by xbbh order by 总数 desc 7、select xbbh,count(*) from jsqk,gz where jsqk.jsbh=gz.jsbh and jcgz2000 group by xbbh 8、select xbbh,sum(jcgz+zwbt+zjjt) from jsqk,gz where jsqk.jsbh=gz.jsbh group by xbbh 二、1、select * from student where sex=男 and dno =d01 2、select sname,grade from student,sc where student.sno=sc.sno and cno=C001 order by grade desc 3、select sname from student where sex=女 and birthday between{^1979-1-1} and {^1980-12-31} 4、select sc.sno,sname,grade from sc join student on sc.sno= student.sno join course on sc.cno=course.cno where cname=”计算机基础” and grade60 select sc.sno, sname, grade from student,sc,course where student.sno=sc.sno and course.cno=sc.cno and cname=计算机基础and grade60 5、sele sno from sc where cno=C001 and sno in (sele sno from sc where cno=C002) 6、sele sno from sc where cno=C001and sno not in (sele sno from sc where cno=C002) 7、sele distinct sno from sc where cno=C001or sno in (sele sno from sc where cno=C002) 8、select count(sno) from student where sex=”女” 9、select count(sno) from sc join course on sc.cno=course.cno where cname=计算机基础 and grade60 select count(sno) from sc,course where sc.cno=course.cno and cname=计算机基础and grade60 10、select dno,count(*) from student group by dno 11、select sno, sum(credit) from sc,course where sc.cno=course.cno and grade60 group by sno 12、select sno from sc,course where sc.cno=course.cno and grade60 group by sno having sum(credi