SQL上机试题1.doc

一、（12分）问题描述：已知关系模式：S (SNO,SNAME）????????? 学生关系。SNO 为学号，SNAME 为姓名C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师SC(SNO,CNO,SCGRADE)???? 选课关系。SCGRADE 为成绩1. 找出没有选修过“李明”老师讲授课程的所有学生姓名（4分） 2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩（4分） 3. 列出既学过“1”号课程，又学过“2”号课程的所有学生姓名（4分）三、活期存款中，“储户”通过“存取款单”和“储蓄所”发生联系。假定储户包括：账号，姓名，电话，地址，存款额；“储蓄所”包括：储蓄所编号，名称，电话，地址（假定一个储户可以在不同得储蓄所存取款）1、写出设计以上表格的语句（4分）2、创建一个触发器TR1完成下面内容：当向“存取款单”表中插入数据时，如果存取标志＝１则应该更改储户表让存款额加上存取金额，如果存取标志＝0则应该更改储户表让存款额减去存取金额,如果余额不足显示余额不足错误。（5分） create table s(sno int,sName varchar(100)) create table c(cno int,cName varchar(100),CTEACHER varchar(100)) create table sc(sno int,cno int,scGrade int) insert into svalues(1,lfm1)insert into svalues(2,lfm2)insert into svalues(3,lfm3)insert into svalues(4,lfm4)insert into svalues(5,lfm5)insert into svalues(6,lfm6) insert into cvalues(1,ch1,李明)insert into cvalues(2,ch2,王刚)insert into cvalues(3,ch3,ll)insert into cvalues(4,ch4,ff)insert into cvalues(5,ch5,ffd)insert into cvalues(6,ch6,se)insert into cvalues(7,ch7,s) insert into scvalues(1,2,80)insert into scvalues(2,2,50)insert into scvalues(1,1,60)insert into scvalues(1,3,90)insert into scvalues(3,2,55)insert into scvalues(1,6,77)insert into scvalues(3,4,80)insert into scvalues(4,2,70) 1. 找出没有选修过“李明”老师讲授课程的所有学生姓名--实现代码：SELECT SNAME FROM SWHERE sname NOT in(??? SELECT sname FROM SC,C,S??? WHERE SC.CNO=C.CNO ??????? AND cteacher=李明??????? AND S.SNO = SC.SNO) select sname from swhere sno not in(select s.sno from sinner join scon sc.sno=s.snoinner join con c.cno=sc.cnowhere cteacher=李明) 2. 列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩--实现代码：SELECT S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)FROM S,SC,(??? SELECT SNO??? FROM SC??? WHERE SCGRADE60??? GROUP BY SNO??? HAVING COUNT(DISTINCT CNO)=1)A WHERE S.SNO=A.SNO AND SC.SNO=A.SNOGROUP BY S.SNO,S.SNAME select s.sno,s.sname , avg(scGrade) as avg_scgrade from s,scwhere s.sno in(select sc.sno from scinner join con c.cno=sc.cnowhere sc.scGra