基本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”號課的成績