20200623---sql面试题

create table student(sid char(4) not null,sname varchar(20) not null,sage int(3) not null,ssex char(4) not null,primary key(sid));

insert into student  values(‘s001‘,‘张三 ‘,‘16‘,‘男‘),(‘s002‘,‘王五 ‘,‘15‘,‘男‘),(‘s003‘,‘张三 ‘,‘16‘,‘女‘);
insert into student  values(‘s001‘,‘zhangsan ‘,‘16‘,‘nan‘),(‘s002‘,‘wangwu ‘,‘15‘,‘nan‘),(‘s003‘,‘zhuli‘,‘16‘,‘nv‘);

create table course(cid char(4) not null,cname varchar(20) not null, primary key(cid));

insert into course values(‘c001‘,‘语文‘),(‘c002‘,‘数学‘),(‘c003‘,‘英语‘);
insert into course values(‘c001‘,‘chinese‘),(‘c002‘,‘maths‘),(‘c003‘,‘english‘);

create table teacher(tid char(4) not null,tname varchar(20) not null, cid char(4) not null,primary key(tid),foreign key(cid) references course(cid));

insert into teacher values(‘t001‘,‘王刚‘,‘c001‘),(‘t002‘,‘华罗庚‘,‘c002‘),(‘t003‘,‘刘怡‘,‘c003‘);
insert into teacher values(‘t001‘,‘wanggang‘,‘c001‘),(‘t002‘,‘hualuogen‘,‘c002‘),(‘t003‘,‘liuyi‘,‘c003‘);

create table score(id char(4) not null,sid char(4) not null,cid char(4) not null,score int(3) not null,primary key(id),foreign key(sid) references student(sid),foreign key(cid) references course(cid));

insert into score values(‘001‘,‘s002‘,‘c001‘,80),(‘002‘,‘s001‘,‘c001‘,77),(‘003‘,‘s002‘,‘c002‘,85),(‘004‘,‘s003‘,‘c001‘,50),(‘005‘,‘s001‘,‘c003‘,82),(‘006‘,‘s002‘,‘c003‘,79),(‘007‘,‘s001‘,‘c002‘,90),(‘008‘,‘s003‘,‘c002‘,76),(‘009‘,‘s003‘,‘c003‘,79);