数据库实验报告(河北工业大学)

数据库实验报告第一章 李云霄 实验1.1 （1）

CREATETABLECUSTOMER

(CIDCHAR(8)UNIQUE,CNAMECHAR(20),CITYCHAR(8), DISCNTINT, PRIMARYKEY(CID)) CREATETABLEAGENTS

(AIDCHAR(8)UNIQUE,ANAMECHAR(20),CITYCHAR(8), PERCENTSFLOAT,PRIMARYKEY(AID)) CREATETABLEPRODUCTS

(PIDCHAR(8)UNIQUE,PNAMECHAR(20),PRIMARYKEY (PID)) （2）

CREATETABLEORDERS

(ORDNACHAR(8)UNIQUE,MONTHINT,CIDCHAR(8),AIDCHAR (8), PIDCHAR(8),QTYINT,DOLLARSFLOAT,PRIMARYKEY (ORDNA), FOREIGNKEY(CID)REFERENCESCUSTOMER,FOREIGNKEY(AID) REFERENCESAGENTS,FOREIGNKEY(PID)REFERENCESPRODUCTS) （3）

ALTERTABLEPRODUCTSADDCITYCHAR(8) ALTERTABLEPRODUCTSADDQUANTITYINT ALTERTABLEPRODUCTSADDPRICEFLOAT （4）

CREATEINDEXXSNOONCUSTOMER(CID) CREATEINDEXXSNOONAGENTS(AID) CREATEINDEXXSNOONPRODUCTS(PID) CREATEINDEXXSNOONORDERS(ORDNA) （5）

DROPINDEXCUSTOMER.XSNO DROPINDEXAGENTS.XSNO DROPINDEXPRODUCTS.XSNO DROPINDEXORDERS.XSNO

实验1.2

（1）SELECT*FROMCOURSES （2）SELECTSIDFROMCHOICES

（3）SELECTCIDFROMCOURSESWHEREhour<88

（4）SELECTSIDFROMCHOICESGROUPBYSIDHAVINGSUM(SCORE)>400 （5）SELECTCOUNT(CID)FROMCOURSES

（6）SELECTCID,COUNT(CID)FROMCHOICESGROUPBYcid

（7）SELECTSIDFROMCHOICESWHEREscore>60 GROUPBYsidHAVINGCOUNT(cid)>2 （8）SELECTSID,COUNT(CID),AVG(SCORE)FROMCHOICESGROUPBYsid （9）SELECTSTUDENT.sid,snamefromstudent,choice,COURSES

wherestudent.sid=choice.sidandchoice.cid=COURSES.cid

andCOURSES.cname='java'

（10）SELECTCHOICES.sid,CHOICES.scoreFROMCHOICES,STUDENTS

WHEREsname='SSSHT'ANDCHOICES.sid=STUDENTS.sid SELECTCIDSCOREFROMCHOICESWHEREsidIN(

SELECTSTUDENTS.sidFROMSTUDENTSWHEREsname='SSSHT')

（11）SELECTC1.CNAMEFROMCOURSESASC1,COURSESASC2 WHEREC1.hour>C2.hourANDC2.cname='C++' （12）SELECTSID,SNAMEFROMSTUDENTS

WHEREsidIN(

SELECTC1.SIDFROMCHOICESASC1,CHOICESASC2 WHEREC1.score>C2.scoreANDC1.cid=C2.cid

ANDC2.sid=(SELECTsidFROMSTUDENTSWHEREsname='ZNKOO') ANDC1.cid=(SELECTcidFROMCOURSESWHEREcname='C++'))

（13）SELECTSNAMEFROMSTUDENTSWHEREgradeIN(

SELECTgradeFROMSTUDENTSWHEREsidIN('883794999','850955252'))

（14）SELECTSNAMEFROMSTUDENTSWHEREsidNOTIN

(SELECTsidFROMCHOICESWHEREcid=(SELECTcidFROM COURSESWHEREcname='JAVA'))

（15）SELECT*FROMCOURSESWHEREhour<=ALL(SELECThourFROMCOURSES) （16）SELECTCHOICES.tid,CIDFROMCHOICESWHERENOTEXISTS

(SELECT*FROMTEACHERSWHERETEACHERS.salary>=

(SELECTsalaryFROMTEACHERSWHERETEACHERS.tid=CHOICES.tid))

（17）SELECTSIDFROMCHOICESWHEREscore=(

SELECTMAX(score)FROMCHOICESWHEREcid=( SELECTcidFROMCOURSESWHEREcname='ERP'))

（18）SELECTCNAMEFROMCOURSESWHEREcidNOTIN( SELECTcidFROMCHOICES)

（19）SELECTCNAMEFROMCOURSESWHEREcid=SOME(

SELECTcidFROMCHOICESWHEREtid=SOME(

SELECTtidFROMCOURSES,CHOICESWHEREcname='UML'AND COURSES.cid=CHOICES.cid))

（20）SELECTSNAMEFROMSTUDENTSWHERENOTEXISTS(

SELECT*FROMCHOICESASC1WHERENOTEXISTS(

SELECT*FROMCHOICESASC2WHEREC2.sid=STUDENTS.sid ANDC2.cid=C1.cidANDC2.tid='200102901'))

（21）SELECTSIDFROMCHOICES,COURSESWHERECOURSES.cid=CHOICES.cid

ANDCOURSES.cname='DATABASE' UNION

SELECTSIDFROMCHOICES,COURSESWHERECOURSES.cid=CHOICES.cidANDCOURSES.cname='UML'

（22）SELECTX.sidFROMCHOICESASX,CHOICESASY

WHERE (X.cid=(SELECTcidFROMCOURSESWHEREcname='DATABASE') ANDY.cid=(SELECTcidFROMCOURSESWHEREcname='UML')) ANDX.sid=Y.sid

（23）SELECTX.SIDFROMCHOICESASX,CHOICESASY

WHERE (X.cid=(SELECTcidFROMCOURSESWHEREcname='DATABASE')) ANDX.sid=Y.sid

ANDNOT(Y.cid=(SELECTcidFROMCOURSESWHEREcname='UML'))

实验1.3

（1）INSERTINTOSTUDENTS(sid,sname) VALUES('8000022222','WANGLAN') （2）INSERTINTOTEACHERS

VALUES('200001000','LXL','S4ZRCK@PEW.NET','3024')

（3）UPDATETEACHERS

SETsalary=4000 WHEREtid='200010493'

（4）UPDATETEACHERS

SETsalary=2500 WHEREsalary<2500

（5）UPDATECHOICES

SETtid=

(SELECTtidFROMTEACHERSWHEREtname='RNUPX') WHEREtid='200016731'

（6）UPDATESTUDENTS

SETgrade=2001 WHEREsid='800071780'

（7）DELETEFROMCOURSES

WHEREcidNOTIN(

SELECTcidFROMCHOICESGROUPBYcid)

（8）DELETEFROMSTUDENTS WHEREgrade<1998

（9）DELETEFROMSTUDENTSWHEREsidNOTIN (SELECTsidFROMCHOICESGROUPBYsid) （10）DELETEFROMCHOICESWHEREscore<60 实验1.4

（1）CREATEVIEWVIEWCAS

SELECTCHOICES.no,CHOICES.sid,CHOICES.tid,COURSES.cname,CHOICES.scoreFROMCHOICES,COURSES

WHERECHOICES.cid=COURSES.cid

（2）CREATEVIEWVIEWSAS

SELECTCHOICES.no,STUDENTS.sname,CHOICES.tid,CHOICES.cid,CHOICES.scoreFROMCHOICES,STUDENTS

WHERECHOICES.sid=STUDENTS.sid

（3）CREATEVIEWS2(SID,SNAME,GRADE)AS

SELECTSTUDENTS.sid,STUDENTS.sname,STUDENTS.grade FROMSTUDENTS WHEREgrade>1998

（4）SELECT*FROMVIEWSWHERESNAME='UXJOF'

（5）SELECTSID,SCOREFROMVIEWCWHERECNAME='UML'

（6）INSERTINTOs1VALUES('60000001','LILY','2001') （7）CREATEVIEWS1(SID,SNAME,GREADE)AS

SELECTSID,SNAME,GRADEFROMSTUDENTS WHEREgrade>1998 WITHCHECKOPTION

（8）UPDATEVIEWSSETSCORE=SCORE+5WHERESNAME='UXJOF' （9）DROPVIEWVIEWC

DROPVIEWVIEWS DROPVIEWS1

实验1.6

（1） SELECTCID,HOUR*18 FROMCOURSES

（2）selectcount(*)

fromchoices,courses

wherechoices.cid=courses.cidandcname='C++'andscoreisnull

（3）selectsid,score

fromchoices,courses

wherechoices.cid=courses.cidandcname='C++' orderbyscore

（4）SELECTDISTINCT

SCOREFROMCHOICESWHEREcid=(

SELECTcidFROMCOURSESWHEREcname='C++') ORDERBYscore

（5）SELECTDISTINCTGRADEFROMSTUDENTSGROUPBYgrade