create database student
- 格式：doc
- 大小：34.00 KB
- 文档页数：3
create database student
go
use student
create table S(
sno char(6) constraint PK_sno Primary key,
sname varchar(10) not null ,
age int constraint CHK_age check(age between 10 and 40) , GPA real
)
go
create table C(
cno char(4) constraint PK_cno Primary key,
cname varchar(40) not null,
credit int constraint CHK_credit check(credit between 1 and 10), mark int
)
go
Create table SC(
sno char(6) ,
cno char(4),
score int constraint CHK_score check(score between 0 and 100), point real,
desc0 varchar2(40),
constraint FK_sno foreign key(sno) references S(Sno) , constraint PK_sno_cno Primary key(sno,cno),
constraint FK_cno foreign key(cno) references C(Cno)
)
go
insert into S(sno,sname,age) values('S10001','A',10)
insert into S(sno,sname,age) values('S10002','B',20)
insert into S(sno,sname,age) values('S10003','C',30)
insert into S(sno,sname,age) values('S10004','D',40)
GO
insert into C(cno,cname,credit,mark) values('C001','DB',4,1); insert into C(cno,cname,credit,mark) values('C002','OS',3,1);
insert into C(cno,cname,credit,mark) values('C003','C',5,1);
insert into C(cno,cname,credit,mark) values('C004','JAV A',2,0);
GO
insert into SC(sno,cno,score) values('S10001','C001',90);
insert into SC(sno,cno,score) values('S10001','C002',80);
insert into SC(sno,cno,score) values('S10001','C003',70);
insert into SC(sno,cno,score) values('S10002','C001',60);
insert into SC(sno,cno,score) values('S10002','C002',50);
insert into SC(sno,cno,score) values('S10003','C003',40);
insert into SC(sno,cno,score) values('S10003','C004',90);
---成绩定级
update sc set desc0='不及格' where score<60;
update sc set desc0='中' where score between 60 and 79;
update sc set desc0='良好' where score between 80 and 89;
update sc set desc0='优' where score> 90;
update sc set desc0=(select desc0 from map where score between L_Ran and H_Ran); --计算课程积点分
update sc set point=(score-50)/10
where score>=60 and cno in (select cno from c where mark=1);
--计算GPA
create view GPA_v as
select sc.sno,sum(point*credit)/sum(credit)
from c,sc
where o=o and c.mark=1
group by sc.sno
update s set gpa=(select gpa from GPA_V where s.sno=gpa_v.sno);
--查询1号或2号课程的学生姓名
select * from s where sno in (select sno from sc where cno='C001' or cno='C002');
--查询同时选修1 号和2号课程的学生姓名
select * from s where not exists(
select * from c where cno in ('C001','C002','C003') and not exists
(select * from sc where s.sno=sc.sno and o=o)
);
--查询既没有选修1 号又没有选修2号课程的学生姓名
select * from s where sno not in (select sno from sc where cno='C001' or cno='C002');
select * from s where not exists (select * from sc where sc.sno=s.sno and (cno='C001' or cno='C002'));
--查询平均成绩位于前3名的名单
--sql server : select top 3 .....
--oracle: ROWNUM
select sname,avg(score) as avg_score
from s,sc
where s.sno=sc.sno and rownum<=3
order by avg(score) desc
--qu。
