目录
create table LYL_116_week8student(SNO int primary key, SNAME char(8) not null unique,SEX char(2), DEPTNO int foreign key references LYL_116_week8dept(DEPTNO))
create table LYL_116_week8course(CNO int, CNAME char(20) not null,TNO int,CREDIT int, primary key(CNO,TNO), foreign key (TNO) references LYL_116_week8teacher(TNO))
create table LYL_116_week8sc(SNO int,CNO int,GRADE int, primary key(SNO,CNO), foreign key (SNO) references LYL_116_week8student(SNO), foreign key (CNO) references LYL_116_week8course(CNO))
create table LYL_116_week8teacher(TNO int primary key, TNAME char(8) not null, DEPTNO int foreign key references LYL_116_week8dept(DEPTNO))
create table LYL_116_week8dept(DEPTNO int primary key, DEPTNAME char(20) not null)
alter table LYL_116_week8student add SAGE int
alter table LYL_116_week8student alter column SAGE smallint
alter table LYL_116_week8student add unique(SNO)
alter table LYL_116_week8course add unique(CNO desc)
insert into LYL_116_week8student values(1001,'张天','男',10,20) insert into LYL_116_week8student values(1002,'李兰','女',10,21) insert into LYL_116_week8student values(1003,'陈铭','男',10,21) insert into LYL_116_week8student values(1004,'刘茜','女',20,21) insert into LYL_116_week8student values(1005,'马朝阳','男',20,22)
insert into LYL_116_week8course values(1,'数据结构',101,4) insert into LYL_116_week8course values(2,'数据库',102,4) insert into LYL_116_week8course values(3,'离散数学',103,4) insert into LYL_116_week8course values(4,'C语言程序设计',101,2)
insert into LYL_116_week8sc values(1001,1,80) insert into LYL_116_week8sc values(1001,2,85) insert into LYL_116_week8sc values(1001,3,78) insert into LYL_116_week8sc values(1002,1,78) insert into LYL_116_week8sc values(1002,2,82) insert into LYL_116_week8sc values(1003,1,92) insert into LYL_116_week8sc values(1004,1,87) insert into LYL_116_week8sc values(1004,4,90) insert into LYL_116_week8sc values(1005,1,85) insert into LYL_116_week8sc values(1005,4,92)
insert into LYL_116_week8teacher values(101,'张星',10) insert into LYL_116_week8teacher values(102,'李珊',10) insert into LYL_116_week8teacher values(103,'赵天应',10) insert into LYL_116_week8teacher values(104,'李田',20)
insert into LYL_116_week8dept values(10,'计算机') insert into LYL_116_week8dept values(20,'信息')
select s.SNAME'姓名',d.DEPTNAME'所在系名' from LYL_116_week8student s, LYL_116_week8dept d where s.DEPTNO = d.DEPTNO
select avg(sc.GRADE)'数据结构的平均成绩' from LYL_116_week8sc sc,LYL_116_week8course c where sc.CNO = c.CNO and c.CNAME='数据结构' group by sc.CNO
create view CS_STUDENT as select s.SNO,s.SNAME,s.SEX,s.DEPTNO,d.DEPTNAME from LYL_116_week8student s,LYL_116_week8dept d where s.DEPTNO=d.DEPTNO and d.DEPTNAME='计算机'
select cs.SNAME,c.CNAME,sc.GRADE from CS_STUDENT cs,LYL_116_week8sc sc,LYL_116_week8course c where c.CNO=sc.CNO and cs.SNO = sc.SNO
select s.SNAME from LYL_116_week8student s where s.SEX='女'
select * from LYL_116_week8sc sc where sc.GRADE between 80 and 89 order by sc.GRADE desc
select count(s.DEPTNO)'人数',d.DEPTNAME'系名' from LYL_116_week8student s,LYL_116_week8dept d where s.DEPTNO=d.DEPTNO group by s.DEPTNO,d.DEPTNAME
select s.SNAME,s.SAGE from LYL_116_week8dept d,LYL_116_week8student s where d.DEPTNAME='信息' and s.SEX='女' and s.SAGE<=21
select s.SNAME'选修总学分在10分以上' from LYL_116_week8course c,LYL_116_week8sc sc,LYL_116_week8student s where c.CNO=sc.CNO and s.SNO=sc.SNO group by s.SNAME having sum(c.CREDIT)>10
select m.CNO,s.SNAME,m.maxG from LYL_116_week8sc sc2,LYL_116_week8student s, (select sc1.CNO,max(sc1.GRADE)'maxG' from LYL_116_week8sc sc1 group by sc1.CNO)m where m.CNO=sc2.CNO and sc2.GRADE=m.maxG and sc2.SNO=s.SNO
select distinct SNO from LYL_116_week8sc sc1 where not exists( select * from LYL_116_week8sc sc2 where sc2.SNO='1002' and not exists( select * from LYL_116_week8sc sc3 where sc3.SNO = sc1.SNO and sc3.CNO = sc2.CNO))
select s.SNAME from LYL_116_week8student s where not exists ( select * from LYL_116_week8course c where c.TNO in( select t.TNO from LYL_116_week8teacher t where t.TNAME='张星' and not exists( select * from LYL_116_week8sc sc where sc.SNO=s.SNO and sc.CNO=c.CNO)) )
select s.SNAME,sc1.GRADE from LYL_116_week8sc sc1,LYL_116_week8student s where sc1.CNO in (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and sc1.SNO = s.SNO
update LYL_116_week8sc set GRADE = GRADE+2 where CNO in (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and SNO in ( select sc1.SNO from LYL_116_week8sc sc1,LYL_116_week8student s where sc1.CNO in (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and sc1.SNO = s.SNO)
select s.SNAME,sc1.GRADE from LYL_116_week8sc sc1,LYL_116_week8student s where sc1.CNO in (select c.CNO from LYL_116_week8teacher t,LYL_116_week8course c where t.TNAME='张星' and t.TNO=c.TNO and c.CNAME='数据结构') and sc1.SNO = s.SNO
select * from LYL_116_week8student s,LYL_116_week8sc sc where s.SNAME='马朝阳' and s.SNO=sc.SNO
delete from LYL_116_week8sc where SNO in (select SNO from LYL_116_week8student where SNAME='马朝阳')
select * from LYL_116_week8student s,LYL_116_week8sc sc where s.SNAME='马朝阳' and s.SNO=sc.SNO