select a.Sname from students a,courses b,sc c,precourese d where a.sno=c.sno and b.cno=c.cno and c.cno=d.cno and b.grade>60 and d.precNO='数据库'
STUDENTS(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)--学生资料 COURSES(CNO,CNAME,CREDIT,SEMENTER)--课程资料 SC(SNO,CNO,GRADE)--成绩表 PRECOURSES(CNO,PRECNO)--先修课程表 select a.* from students a,courses b,sc c,precourses d where b.cname='数据库' and b.cno=d.cno and b.cno=c.cno and c.sno=a.sno and d.precno='C++' and d.precno='操作系统'
select a.* from students a,courses b,sc c,precourses d where b.cname='数据库' and b.cno=d.cno and b.cno=c.cno and c.sno=a.sno and d.precno='C++' and d.precno='操作系统' and c.score>=60
select a.* from students a,courses b,sc c,precourses d where b.cname='数据库' and b.cno=d.cno and b.cno=c.cno and c.sno=a.sno and c.score>=60 and not exist (sd.precon not in (select precon from precourses where cname='数据库'))
语法有问题哦,好像不可以and not exists (d.precno not in (select precno from precourses where cname='数据库')) 的。exists 里面应该是个子查询
select a.* from students a,courses b,sc c,precourses d,courses e where b.cno=d.cno and d.precno=e.cno and c.cno=e.cno and a.sno=c.sno and b.cname='数据库' group by a.sno,a.sname,a.sex,a.bdate,a.height,a.department having min(c.grade)>='60' 注意:group by 后面列出的是学生表中的所有字段, 请校正以下表名和字段名.试一下没有坏处见笑了.
select a.* from students a,courses b,sc c,precourses d,courses e where b.cno=d.cno and d.precno=e.cno and c.cno=e.cno and a.sno=c.sno and b.cname='数据库' and c.grade>='60' 这回试一下吧
select * from students where SNO in (select a.sno from sc a,PRECOURSES b where a.cno=b.cno and a.grade>60 and b.cno in (select cno from COURSES))
如果不考虑数据库课程的先修课程还有先修课程课程的话, 可以试试下列语句: SELECT STUDENTS.SNO FROM STUDENTS, (SELECT SNO, SUM1 = SUM(1) FROM SC, PRECOURSES WHERE SC.CNO = PRECOURSES.PRECNO AND PRECOURSES.CNO = '数据库' AND GRADE >= 60 GROUP BY SNO)A WHERE STUDENTS.SNO = A.SNO AND A.SUM1 = ISNULL((SELECT COUNT(*) FROM PRECOURSES WHERE PRECOURSES.CNO = '数据库'))或者SELECT SNO, SUM1 = SUM(1) FROM SC, PRECOURSES WHERE SC.CNO = PRECOURSES.PRECNO AND PRECOURSES.CNO = '数据库' AND GRADE >= 60 GROUP BY SNO HAVING SUM(1) = ISNULL((SELECT COUNT(*) FROM PRECOURSES WHERE PRECOURSES.CNO = '数据库'))
不考虑数据库课程的先修课程还有先修课程课程: select * from students T where sno in (select sno from sc where cno in (select PRECNO from PRECOURSES where cno = '数据库' ) ) and (select min(isnull(grade,0)) from sc,PRECOURSES where sc.cno =* PRECOURSES.PRECNO) >= 60 select * from students T where sno in (select sno from sc where cno in (select PRECNO from PRECOURSES where cno = '数据库' ) ) and (select min(case when grade ='合格' then 60 else 59 end ) from sc,PRECOURSES where sc.cno =* PRECOURSES.PRECNO) >= 60
1. select * from students where sno in (select students.sno from students,PRECOURSES,sc where students.sno *= sc.sno and PRECOURSES.PRECNO *=sc.cno and PRECOURSES.cno='数据库' group by students.sno having min(isnull(grade,0)) >= 60) 2. select * from students where sno in (select students.sno from students,PRECOURSES, sc where students.sno *= sc.sno and PRECOURSES.PRECNO *=sc.cno and PRECOURSES.cno='数据库' group by students.sno having min(case when grade ='合格' then 60 else 59 end) >=60)
我之所以要多一个用子查询,是因为用这样写的SQL打开的记录集是可更新的! 本来很多查询的目的就是为了更新: update students set ... where sno in (select students.sno from students,PRECOURSES, sc where students.sno *= sc.sno and PRECOURSES.PRECNO *=sc.cno and PRECOURSES.cno='数据库' group by students.sno having min(case when grade ='合格' then 60 else 59 end) >=60)
我是要选修过C++和操作系统并成绩合格的学生.
CNO是课程号,引用COURSES中的CNO做外建.
PRECON是先修课程号
例如:CNO | PRECNO
-------------------
数据库 | C++
数据库 | 操作系统
select a.Sname from students a,courses b,sc c,precourese d
where a.sno=c.sno and b.cno=c.cno and c.cno=d.cno and b.grade>60 and d.precNO='数据库'
COURSES(CNO,CNAME,CREDIT,SEMENTER)--课程资料
SC(SNO,CNO,GRADE)--成绩表
PRECOURSES(CNO,PRECNO)--先修课程表
select a.* from
students a,courses b,sc c,precourses d
where b.cname='数据库'
and b.cno=d.cno
and b.cno=c.cno
and c.sno=a.sno
and d.precno='C++'
and d.precno='操作系统'
students a,courses b,sc c,precourses d
where b.cname='数据库'
and b.cno=d.cno
and b.cno=c.cno
and c.sno=a.sno
and d.precno='C++'
and d.precno='操作系统'
and c.score>=60
students a,courses b,sc c,precourses d
where b.cname='数据库'
and b.cno=d.cno
and b.cno=c.cno
and c.sno=a.sno
and c.score>=60
and not exist
(sd.precon not in (select precon from precourses where cname='数据库'))
(d.precno not in (select precno from precourses where cname='数据库'))
的。exists 里面应该是个子查询
b.cno=d.cno and d.precno=e.cno and c.cno=e.cno and a.sno=c.sno and b.cname='数据库' group by a.sno,a.sname,a.sex,a.bdate,a.height,a.department
having min(c.grade)>='60'
注意:group by 后面列出的是学生表中的所有字段,
请校正以下表名和字段名.试一下没有坏处见笑了.
b.cno=d.cno and d.precno=e.cno and c.cno=e.cno and a.sno=c.sno and b.cname='数据库' and c.grade>='60'
这回试一下吧
SELECT STUDENTS.SNO
FROM STUDENTS, (SELECT SNO, SUM1 = SUM(1)
FROM SC, PRECOURSES
WHERE SC.CNO = PRECOURSES.PRECNO AND PRECOURSES.CNO = '数据库' AND GRADE >= 60
GROUP BY SNO)A
WHERE STUDENTS.SNO = A.SNO AND A.SUM1 = ISNULL((SELECT COUNT(*) FROM PRECOURSES WHERE PRECOURSES.CNO = '数据库'))或者SELECT SNO, SUM1 = SUM(1)
FROM SC, PRECOURSES
WHERE SC.CNO = PRECOURSES.PRECNO AND PRECOURSES.CNO = '数据库' AND GRADE >= 60
GROUP BY SNO
HAVING SUM(1) = ISNULL((SELECT COUNT(*) FROM PRECOURSES WHERE PRECOURSES.CNO = '数据库'))
select *
from students T
where sno in (select sno
from sc
where cno in (select PRECNO
from PRECOURSES
where cno = '数据库'
)
)
and (select min(isnull(grade,0))
from sc,PRECOURSES
where sc.cno =* PRECOURSES.PRECNO) >= 60
select *
from students T
where sno in (select sno
from sc
where cno in (select PRECNO
from PRECOURSES
where cno = '数据库'
)
)
and (select min(case when grade ='合格' then 60 else 59 end )
from sc,PRECOURSES
where sc.cno =* PRECOURSES.PRECNO) >= 60
select *
from students
where sno in (select students.sno
from students,PRECOURSES,sc
where students.sno *= sc.sno and PRECOURSES.PRECNO *=sc.cno
and PRECOURSES.cno='数据库'
group by students.sno
having min(isnull(grade,0)) >= 60)
2.
select *
from students
where sno in (select students.sno
from students,PRECOURSES, sc
where students.sno *= sc.sno and PRECOURSES.PRECNO *=sc.cno
and PRECOURSES.cno='数据库'
group by students.sno
having min(case when grade ='合格' then 60 else 59 end) >=60)
本来很多查询的目的就是为了更新:
update students
set ...
where sno in (select students.sno
from students,PRECOURSES, sc
where students.sno *= sc.sno and PRECOURSES.PRECNO *=sc.cno
and PRECOURSES.cno='数据库'
group by students.sno
having min(case when grade ='合格' then 60 else 59 end) >=60)