数据库实验报告第一章李云霄实验1.1（1）CREATETABLE CUSTOMER(CID CHAR(8)UNIQUE,CNAME CHAR(20),CITY CHAR(8),DISCNT INT,PRIMARYKEY(CID))CREATETABLE AGENTS(AID CHAR(8)UNIQUE,ANAME CHAR(20),CITY CHAR(8),PERCENTS FLOAT,PRIMARYKEY(AID))CREATETABLE PRODUCTS(PID CHAR(8)UNIQUE,PNAME CHAR(20),PRIMARYKEY (PID))（2）CREATETABLE ORDERS(ORDNA CHAR(8)UNIQUE,MONTH INT,CID CHAR(8),AID CHAR (8),PID CHAR(8),QTY INT,DOLLARS FLOAT,PRIMARYKEY (ORDNA),FOREIGNKEY(CID)REFERENCES CUSTOMER,FOREIGNKEY(AID)REFERENCES AGENTS,FOREIGNKEY(PID)REFERENCES PRODUCTS)（3）ALTERTABLE PRODUCTS ADD CITY CHAR(8)ALTERTABLE PRODUCTS ADD QUANTITY INTALTERTABLE PRODUCTS ADD PRICE FLOAT（4）CREATEINDEX XSNO ON CUSTOMER(CID)CREATEINDEX XSNO ON AGENTS(AID)CREATEINDEX XSNO ON PRODUCTS(PID)CREATEINDEX XSNO ON ORDERS(ORDNA)（5）DROPINDEX CUSTOMER.XSNODROPINDEX AGENTS.XSNODROPINDEX PRODUCTS.XSNODROPINDEX ORDERS.XSNO实验1.2（1）SELECT*FROM COURSES（2）SELECTSIDFROM CHOICES（3）SELECT CID FROM COURSES WHEREhour<88（4）SELECTSIDFROM CHOICES GROUPBYSIDHAVING SUM(SCORE)>400（5）SELECT COUNT(CID)FROM COURSES（6）SELECT CID,COUNT(CID)FROM CHOICES GROUPBY cid（7）SELECTSIDFROM CHOICES WHERE score>60 GROUPBYsidHAVING COUNT(cid)>2（8）SELECTSID,COUNT(CID),AVG(SCORE)FROM CHOICES GROUPBYsid（9）SELECT STUDENT.sid,sname from student,choice,COURSESwhere student.sid=choice.sid and choice.cid=COURSES.cidand ame='java'（10）SELECT CHOICES.sid,CHOICES.score FROM CHOICES,STUDENTSWHERE sname='SSSHT'AND CHOICES.sid=STUDENTS.sidSELECT CIDSCORE FROM CHOICES WHEREsid IN(SELECT STUDENTS.sidFROM STUDENTS WHERE sname='SSSHT')（11）SELECT AME FROM COURSES AS C1,COURSES AS C2WHERE C1.hour>C2.hour AND ame='C++'（12）SELECTSID,SNAME FROM STUDENTSWHEREsid IN(SELECT C1.SIDFROM CHOICES AS C1,CHOICES AS C2WHERE C1.score>C2.score AND C1.cid=C2.cidAND C2.sid=(SELECTsidFROM 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 WHEREsid IN('883794999','850955252'))（14）SELECT SNAME FROM STUDENTS WHEREsid NOTIN(SELECTsidFROM CHOICES WHERE cid=(SELECT cid FROMCOURSES WHERE cname='JAVA'))（15）SELECT*FROM COURSES WHEREhour<=ALL(SELECThourFROM COURSES)（16）SELECT CHOICES.tid,CID FROM CHOICES WHERE NOTEXISTS(SELECT*FROM TEACHERS WHERE TEACHERS.salary>=(SELECT salary FROM TEACHERS WHERE TEACHERS.tid=CHOICES.tid))（17）SELECTSIDFROM CHOICES WHERE score=(SELECT MAX(score)FROM CHOICES WHERE cid=(SELECT cid FROM COURSES WHERE cname='ERP'))（18）SELECT CNAME FROM COURSES WHERE cid NOTIN(SELECT cid FROM CHOICES)（19）SELECT CNAME FROM COURSES WHERE cid=SOME(SELECT cid FROM CHOICES WHERE tid=SOME(SELECT tid FROM COURSES,CHOICES WHERE cname='UML'ANDCOURSES.cid=CHOICES.cid))（20）SELECT SNAME FROM STUDENTS WHERE NOTEXISTS(SELECT*FROM CHOICES AS C1WHERE NOTEXISTS(SELECT*FROM CHOICES AS C2WHERE C2.sid=STUDENTS.sidAND C2.cid=C1.cid AND C2.tid='200102901'))（21）SELECTSIDFROM CHOICES,COURSES WHERE COURSES.cid=CHOICES.cidAND ame='DATABASE'UNIONSELECTSIDFROM CHOICES,COURSES WHERE COURSES.cid=CHOICES.cid AND ame='UML'（22）SELECT X.sidFROM CHOICES AS X,CHOICES AS YWHERE (X.cid=(SELECT cid FROM COURSES WHERE cname='DATABASE')AND Y.cid=(SELECT cid FROM COURSES WHERE cname='UML'))AND X.sid=Y.sid（23）SELECT X.SIDFROM CHOICES AS X,CHOICES AS YWHERE (X.cid=(SELECT cid FROM COURSES WHERE cname='DATABASE'))AND X.sid=Y.sidANDNOT(Y.cid=(SELECT cid FROM COURSES WHERE cname='UML'))实验1.3（1）INSERTINTO STUDENTS(sid,sname)VALUES('8000022222','WANGLAN')（2）INSERTINTO TEACHERSVALUES('200001000','LXL','S4ZRCK@','3024')（3）UPDATE TEACHERSSET salary=4000WHERE tid='200010493'（4）UPDATE TEACHERSSET salary=2500WHERE salary<2500（5）UPDATE CHOICESSET tid=(SELECT tid FROM TEACHERS WHERE tname='RNUPX')WHERE tid='200016731'（6）UPDATE STUDENTSSET grade=2001WHEREsid='800071780'（7）DELETEFROM COURSESWHERE cid NOTIN(SELECT cid FROM CHOICES GROUPBY cid)（8）DELETEFROM STUDENTSWHERE grade<1998（9）DELETEFROM STUDENTS WHEREsid NOTIN(SELECTsidFROM CHOICES GROUPBYsid)（10）DELETEFROM CHOICES WHERE score<60实验1.4（1）CREATEVIEW VIEWC ASSELECT CHOICES.no,CHOICES.sid,CHOICES.tid,ame,CHOICES.score F ROM CHOICES,COURSESWHERE CHOICES.cid=COURSES.cid（2）CREATEVIEW VIEWS ASSELECT CHOICES.no,STUDENTS.sname,CHOICES.tid,CHOICES.cid,CHOICES.score FROM CHOICES,STUDENTSWHERE CHOICES.sid=STUDENTS.sid（3）CREATEVIEW S2(SID,SNAME,GRADE)ASSELECT STUDENTS.sid,STUDENTS.sname,STUDENTS.gradeFROM STUDENTSWHERE grade>1998（4）SELECT*FROM VIEWS WHERE SNAME='UXJOF'（5）SELECTSID,SCORE FROM VIEWC WHERE CNAME='UML'（6）INSERTINTO s1VALUES('60000001','LILY','2001')（7）CREATEVIEW S1(SID,SNAME,GREADE)ASSELECTSID,SNAME,GRADE FROM STUDENTSWHERE grade>1998WITHCHECKOPTION（8）UPDATE VIEWS SET SCORE=SCORE+5WHERE SNAME='UXJOF'（9）DROPVIEW VIEWCDROPVIEW VIEWSDROPVIEW S1实验1.6（1）SELECT CID,HOUR*18 FROM COURSES（2）select count(*)from choices,courseswhere choices.cid=courses.cid and cname='C++'and score isnull（3）selectsid,scorefrom choices,courseswhere choices.cid=courses.cid and cname='C++'orderby score（4）SELECTDISTINCTSCORE FROM CHOICES WHERE cid=(SELECT cid FROM COURSES WHERE cname='C++')ORDERBY score（5）SELECTDISTINCT GRADE FROM STUDENTS GROUPBY grade（6）SELECT AVG(SCORE),COUNT(*),MAX(SCORE),MIN(SCORE) FROM CHOICES GROUPBY cid（7）SELECT GRADE FROM STUDENTS WHERE GRADE>=ALL(SELECT grade FROM STUDENTS)（8）SELECT COUNT(*)FROM STUDENTS,TEACHERSWHERE TEACHERS.TID=STUDENTS.SID。