SQL_实验1.4 清华大学出版社

/*author : Vit/csdn 
 *from: http://blog.csdn.net/svitter
 *if you love it, please show the original site*/

/*1.*/
create view cs
as select no,sid,cid,score
from choices
where score>=60;

/*another*/
create view cs
(no, sid ,cid ,score)
as select choices.no, choices.sid, choices.cid, choices.score
from choices
where choices.score>=60
with check option;
/*another*/
create view cs
as select no, sid, cid, score
from choices
where score >= 60
with check option;

/*2.*/
create view sct
(sname,cname,tname)
as select students.sname,courses.cname,teachers.tname
from choices, students, courses, teachers
where choices.tid=teachers.tid and choices.cid = courses.cid and choices.sid = students.sid;

/*3.*/
create view scc
(sname, cname, score)
as select students.sname, courses.cname, choices.score + 5
from choices, students, courses
where choices.cid = courses.cid and choices.sid = students.sid;

/*4.*/
create view s_g(sid,savg)
	as
		select sid,avg(score)
		from choices
		group by sid;

/*5.*/
create view s_c_s(sid ,ccount,savg)
as 
select sid,count(cs.cid), avg(score)
from cs
group by cs.sid;

/*6.find the sname who choice software engineering*/
select sname
from sct
where cname = 'software engineering';

/*7.add (600000000,823069829,10010,59)*/
insert
into cs
values('600000000','823069829','10010','59');
/*此处错误执行了delete - =少了4个元组。。）*/

/*8.*/
update cs
set score = score + 5
where cid = '10010' and score < 95;

/*9.delete sid='823069829'*/
delete cs
where sid = '823069829';

/*10.drop sct,cs)*/
drop view sct
drop view cs;

转载于:https://my.oschina.net/u/1017188/blog/333472