数据结库原理实验报告.docxVIP

实验11.1(1)create table customer(cid char(8) unique,cname char(20),city char(8),discnt int, primary key(cid))create table agents(aid char(8) unique,aname char(20),city char(8), percents float, primary key(aid))create table products(pid char(8) unique ,pname char(20),primary key (pid))(2)create table orders(ordan char(8) unique, month int ,cid char (8),aid char(8),pid char(8),qty int,dollars float, primary key (ordan),foreign key (cid) references customer, foreign key (aid) references agents, foreign key (pid) references products)(3)alter table products add city char(8)alter table products add quantity intalter table products add price float(4)create index xsno on customer (cid)create index xsno on agents (aid)create index xsno on products (pid)create index xsno on orders (ordan)(5)drop index customer.xsnodrop index agents.xsnodrop index products.xsnodrop index orders.xsno1.2 (1)select * from courses(2)select sid from choices(3)select cid from courses where hour88(4)select sid from choices group by sid having sum (score)400(5)select count (cid)from courses(6)select cid,count(sid)from choices group by cid(7)select sid from choices where score60 group by sid having count (cid)2(8)select sid,count(cid),avg(score)from choices group by sid(9)select students.sid,sname from students,choices,courseswhere students.sid=choices.sid and choices.cid=courses.cid and courses.cname=java(10)select choices.cid,choices.score from choices, students where sname=ssshtand choices.sid=students.sidselect cid,score from choices where sid in(select students.sid from students where sname=sssht)(11)select c1.cname from courses as c1,courses as c2 where c1.hourc2.hour and c2.cname=c++(12)select sid,sname from students where sid in (select c1.sid from choices as c1,choices as c2 where c1.scorec2.score and c1.cid=c2.cidand c2.sid=(select sid from students where sname=znkoo)and c1.cid=(select cid from courses where cname=c++))(13)select sname from students where grade in(select grade from students wh