1
create view V_StudInfo
as
select a.å¦å·,a.å§å,b.课ç¨å·,b.课ç¨å,
case when c.æ绩 between 90 and 100 then 'ä¼'
when c.æ绩 between 80 and 89 then 'è¯'
when c.æ绩 between 70 and 79 then 'ä¸'
when c.æ绩 between 60 and 69 then 'åæ ¼'
else 'ä¸åæ ¼' end æ绩ç级
from å¦ç表 a,课ç¨è¡¨ b, æ绩表 c where a.å¦å·=c.å¦å·
and b.课ç¨å·=c.课ç¨å·
2
create view V_Stud
as
select a.å¦å·,a.å§å,count(*) æä¿®ç§ç®æ°,avg(æ绩) å¹³åæ绩
from å¦ç表 a,课ç¨è¡¨ b, æ绩表 c where a.å¦å·=c.å¦å·
and b.课ç¨å·=c.课ç¨å·
and a.å¦å· in
(select a.å¦å·
from å¦ç表 a,课ç¨è¡¨ b, æ绩表 c where a.å¦å·=c.å¦å·
and b.课ç¨å·=c.课ç¨å· and b.课ç¨å='è±è¯' and c.æ绩>75)
表ååå段åï¼èªå·±è·ä½ å®é
çæ ¸å¯¹ä¸ä¸ï¼ä¸åçæ¹ä¸ä¸