问题描述:
已知关系模式:S (SNO,SNAME) 学生关系。SNO 为学号,SNAME 为姓名
C (CNO,CNAME,CTEACHER) 课程关系。CNO 为课程号,CNAME 为课程名,CTEACHER 为任课教师
SC(SNO,CNO,SCGRADE) 选课关系。SCGRADE 为成绩
1. 找出没有选修过“李明”老师讲授课程的所有学生姓名
2. 列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
create table s(sno int,sname varchar(20)); insert s values('1','刘一'),('2','王二'),('3','张三'); create table c(cno int auto_increment,cname varchar(20), cteacher varchar(20),primary key(cno)); insert c(cname,cteacher) values('语文','王玲'),('数学','李明'),('政治','赵云'); create table sc(sno int,cno int,scgrade int); insert sc values(1,1,94),(2,3,100),(3,2,97); select sname from s inner join sc on s.sno=sc.sno where sc.cno not in (select cno from c where cteacher='李明');
1、select sname from S where sno not in (select sno from S where S.cno=(select cno from C where cteacher="李明"));
2、SELECT SC1.SNO,[1号课成绩]=SC1.SCGRADE,[2号课成绩]=SC2.SCGRADE
FROM S,SC SC1,SC SC2
WHERE SC1.CNO='1'
AND SC2.SNO='2'
AND SC1.CNO=S.CNO
AND SC1.SCGRADE>SC2.SCGRADE
select s.sname from sc , s , c where sc.sno = s.sno and sc.cno = c.cno and cteacher != '李明'; select s1.sno, s1.scgrade c1,s2.scgrade c2 from sc s1 join sc s2 on s1.cno = '1' and s2.cno = '2' and s1.sno =s2.sno where s1.scgrade > s2.scgrade;