表STUDENTS (sid,sname,email,grade);
表TEACHERS(tid,tname,email,salary);
表COURSES(cid,cname,chour);
表CHOECES(cno,sid,tid,cid,score);1.查询选修了编号200102901的教师开设的所有课程的学生编号2.查询学生的选课成绩合格的课程成绩,并把成绩换算为积点(60分对应积点为1,每增加1分,积点增加0.1)
表TEACHERS(tid,tname,email,salary);
表COURSES(cid,cname,chour);
表CHOECES(cno,sid,tid,cid,score);1.查询选修了编号200102901的教师开设的所有课程的学生编号2.查询学生的选课成绩合格的课程成绩,并把成绩换算为积点(60分对应积点为1,每增加1分,积点增加0.1)
from teachers T,CHOECES C,STUDENTS S
where teachers.tid=200102901 and
T.tid=c.tid and C.sid=S.sdiselect (score-60)*0.1+1
from choeces
where score>60
select
*
from
TEACHERS
where
tid='200102901'select
[sid],cid,[积点]=sum(score/60 +(score-60)*.1)
from
CHOECES
where
score>=60
group by [sid],cid
1.select b.tid,a.* from TEACHERS a join CHOECES b ON a.cid=b.cid
2.select b.tid,a.* ,积点= 1+(b.score-60)*0.1from TEACHERS a join CHOECES b ON a.cid=b.cid
where b.score>60
select a.sid
from STUDENTS a, TEACHERS b, COURSES c,CHOECES d
where d.sid=a.sid and d.tid=b.tid and d.cid=c.cid
b.tid='200102901'
2.
select a.sname,b.cname,c.score,(c.score-60)*0.1+1 as '积点'
from STUDENTS a, COURSES b,CHOECES c
where c.sid=a.sid and c.cid=b.cid and c.score>=60
编号为200102901的老师开了N门课
要查询出这N门都选了的学生的编号
(学生可以选M门课,老师可以开K门课,一门课只有一个老师)
--(1)SELECT sid
FROM CHOECES
WHERE cid IN (SELECT CID FROM CHOECES WHERE tid='200102901')
GROUP BY sid
HAVING COUNT(Cid)=(SELECT COUNT(DISTINCT cid) FROM CHOECES WHERE tid='200102901') --(2)SELECT cid,
point=1+(Score-60)*0.1
FROM CHOECES
WHERE Score>=60