sql语句讨论课讲解.pptx

sql;create table Student1 ( sno char(6), constraint StudentKey primary key(sno), sname char(8), birthday char(4), phonenum char(11) );;create table Course1 ( cno char(4), constraint CourseKey primary key(cno), cname char(10), credit smallint );;create table SC1 ( sno char(6), constraint fk_sn foreign key(sno) references student1, cno char(4), constraint fk_cn foreign key(cno) references course1, grade smallint constraint con_gra check(grade between 0 and 100 or grade is NULL), constraint pk_sc primary key(sno,cno) );;2;2;3;create view graderange as (select case when grade = 100 then 100 when grade between 95 and 99 then 95-99 ……… when grade = 60 then 60以下 end as g_range, count(sno) as p_sum,cno from sc1 group by cno, case when grade = 100 then 100 when grade between 95 and 99 then 95-99‘ ……… when grade = 60 then 60以下 end);;create view view3(cno,cname,g_range,avg,v_grade,min_grade,max_grade) as select sc1.cno,cname,g_range,trunc(AVG(grade),4),VARIANCE(grade),MIN(grade),MAX(grade) from course1,sc1,student1,graderange where course1.cno = sc1.cno and sc1.sno = student1.sno and graderange.cno = sc1.cno group by sc1.cno,cname,g_range;;3;4;4;rank() over (partition?by?分组字段?order?by?排序字段 顺序);create view view5(sno,sname,cno,cname,grade) as select sno,sname,cno,cname,grade from (select rank() over(partition by sc1.cno order by grade desc) rk,sc1.sno,sname,sc1.cno,cname,grade from student1,sc1,course1 where course1.cno = sc1.cno and student1.sno = sc1.sno )T where T.rk = 3;;Create table recording_sc ( time char(35), primary key(time), user_now char(20), sno char(6), ocno char(4), ncno char(4), ograde smallint, ngrade smallint ); ;create trigger sc_tri after insert or update or delete on sc1 for each row begin insert into recording_sc(time,user_now,sno,ocno,ncno,ograde,ngrade) values(to_char(sysdate, YYYY/MM/DD),user,:new.sno,:old.cno,:new.cno,:old.grade,:new.grade); end sc_tri;;7;8;8;