-- 一题create table s--学生表(sno char(8) primary key,--学号sname char(10) ,--姓名sage int ,--年龄ssex char(2),--性别sdept char(20))--所在系create table c--课程表(cno char(4) primary key,--课程号cname char(20),--课程名c_dept 展开
-- 一题create table s--学生表(sno char(8) primary key,--学号sname char(10) ,--姓名sage int ,--年龄ssex char(2),--性别sdept char(20))--所在系create table c--课程表(cno char(4) primary key,--课程号cname char(20),--课程名c_dept char(20),--开课系teacher char(10))--老师create table sc--成绩表(sno char(8) not null,--学号cno char(4) not null,--课程号grade int ,--成绩constraint PK_sc primary key(sno,cno),--主键constraint PK_s_sno foreign key(sno) references s(sno),--外键constraint FK_c_cno foreign key(cno) references c(cno))--外键--二题--1select cno,cnamefrom cwhere teacher='刘'--2select snamefrom swhere ssex='女'and sno in (select snofrom scwhere cno in(select cnofrom cwhere teacher='刘'))--3select cnofrom cwhere not exists(select * from s,sc where s.sno=sc.sno and sc.cno=c.cno and sname='王乐')--4select count(distinct Cno) as 课程门数from sc--5select avg(grade)from scwhere cno='c4'--6select c.cno,avg(grade) as avg_gradefrom sc,cwhere sc.cno=c.cno and teacher='刘'group by c.cno--7select sname,sagefrom swhere sname like'王%'--8select sname,sagefrom swhere ssex='男' and sage>all(select sagefrom swhere ssex='女')--9insert into s(sno,sname,sage)values('009','吴',18)--10delete from scwhere grade is null--11update scset grade=0where cno in (select cnofrom cwhere cname='数据库')and grade<60--12update scset grade=grade*1.05where sno in(select sno from s where ssex='女' and grade<(select avg(grade) from sc)) 收起
