基本SQL操作(二)

已知关系模式:

S (SNO,SNAME) 学生关系。SNO 为学号，SNAME 为姓名

C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号，CNAME 为课程名，CTEACHER 为任课教师

SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩

---建表

create table s
(
sno varchar(50) not null,
sname varchar(50) not null,

)

create table c
(
cno varchar(50) not null,
cname varchar(50) not null,
cteacher varchar(50)
)
create table sc
(
sno varchar(50) not null,
cno varchar(50) not null,
scgrade varchar(50) not null
)

---插入语句

insert into s (sno,sname) values ('10000','王刚')
insert into s (sno,sname) values ('10001','金亮')

insert into s (sno,sname) values ('10002','张三')
insert into s (sno,sname) values ('10003','李茂')

insert into c (cno,cname,cteacher) values('c1','语文','李明')
insert into c (cno,cname,cteacher) values('c2','政治','张新')
insert into c (cno,cname,cteacher) values('c3','英语','王华')
insert into c (cno,cname,cteacher) values('c4','数学','吴三')

insert into sc (sno,cno,scgrade) values('10000','c1','98')
insert into sc (sno,cno,scgrade) values('10000','c2','98')
insert into sc (sno,cno,scgrade) values('10000','c3','98')
insert into sc (sno,cno,scgrade) values('10001','c1','98')
insert into sc (sno,cno,scgrade) values('10001','c4','98')
insert into sc (sno,cno,scgrade) values('10002','c3','98')
insert into sc (sno,cno,scgrade) values('10002','c2','98')
insert into sc (sno,cno,scgrade) values('10003','c1','98')
insert into sc (sno,cno,scgrade) values('10003','c3','98')
1. 找出没有选修过“李明”老师讲授课程的所有学生姓名

--没选‘李明’老师的所有学生姓名
select * from s where sno not in (select sno from c,sc where sc.cno=c.cno and cteacher='李明')

--2. 列出有二门以上(含两门)不及格课程的学生姓名及其平均成绩

Select S.SNO,S.SNAME,AVG_SCGRADE=AVG(SC.SCGRADE)

FROM S,SC,(

Select SNO

FROM SC

Where SCGRADE<60

GROUP BY SNO

HAVING COUNT(DISTINCT CNO)>=2

)A Where S.SNO=A.SNO AND SC.SNO=A.SNO

GROUP BY S.SNO,S.SNAME

---列出学过C1又学过C2课程的学生姓名
select sno,sname from s where sno in (select sno from sc where cno in('c1','c2') group by sno having count(distinct cno)=2)

-- 列出“c1”号课成绩比“c2”号同学该门课成绩高的所有学生的学号
--列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩