數(shù)據(jù)庫實(shí)驗(yàn)三

1、西南石油大學(xué)實(shí)驗(yàn)報告課程名稱：數(shù)據(jù)庫原理插入你的照片實(shí)驗(yàn)項(xiàng)目名稱：實(shí)驗(yàn)3 SQL數(shù)據(jù)定義語言專業(yè)年級班級、姓名、學(xué)號：電子郵件地址：實(shí)驗(yàn)所用機(jī)器名：實(shí)驗(yàn)時間地點(diǎn)：2015.12.11明理樓實(shí)驗(yàn)指導(dǎo)教師：孫瑜成績批改人批改日期注意：在粘貼截圖時請保留窗口完整標(biāo)題，但只需保留關(guān)鍵界面，多余的 空白界面請刪除。一、實(shí)驗(yàn)課時：4二、實(shí)驗(yàn)?zāi)康?1) 掌握使用T-SQL語句創(chuàng)建、刪除數(shù)據(jù)庫的方法。掌握使用T-SQL語句創(chuàng)建、修改、刪除表的方法。(3) 掌握使用T-SQL語句創(chuàng)建、刪除數(shù)據(jù)庫完整性約束條件的方法。(4) 掌握使用T-SQL語句對表添加、修改、刪除數(shù)據(jù)的方法。(5) 掌握使用T-SQL語句創(chuàng)建

2、、修改、刪除、查詢視圖的方法。三、實(shí)驗(yàn)要求(1) 使用SQL Server 2008查詢分析器。(2) 嚴(yán)格依照操作步驟進(jìn)行。(3) 在本地服務(wù)器中創(chuàng)建和管理數(shù)據(jù)庫。四、實(shí)驗(yàn)環(huán)境(1) PC 機(jī)。 SQL Server 200&五、實(shí)驗(yàn)內(nèi)容及步驟（請?zhí)貏e注意實(shí)驗(yàn)步驟：第6項(xiàng)的第1小項(xiàng)，即“插入數(shù) 據(jù)”操作必須在第4項(xiàng)以前執(zhí)行）1 使用Transact-SQL語句創(chuàng)建JOBS數(shù)據(jù)庫，數(shù)據(jù)庫名格式為JOBS_SunYi即JOBS你的中文名字拼音）CREATE Database JOBS_DengZhiPengSQLQuerySql - HLLSrriHtwr (h <5L)*CREA

3、TE Database JOBS DengZhiPeng;< 1rrr目消息命令已成功完成。2 使用Transact-SQL語句創(chuàng)建JOBS數(shù)據(jù)庫包含的所有表EMPLOYEECREATE TABLE EMPLOYEE EMPNOSMALLINT NOT NULL, SUPNAME VARCHAR 50) NOT NULL, FORENAMESVARCHAR5O) NOT NULL, DOB DATE NOT NULL,ADDRESS VARCHAR 50) NOT NULL, TELNO CHAR 10) NOT NULL, DEPNO SMALLINT NOT NULL);£

4、QLQuery3.£ql -Perg (sa (Si)*ECREATE TABLE EMPLOYEE(EMPNO SMALLINT NOT NULL, SUPNAME VARCHAR(50) NOT NULL, FORENAMES VARCHAR(50) NOT NULL, DOB DATE NOT NULL,ADDRESS VARCHAR(50) NOT NULL, TELNO CHAR(10) NOT NULL,DEPNO SMALLINT NOT NULLL)；h消息命令已成功完成;JOBHISTORYCREATE TABLE JOBHISTORY( EMPNOSMALLINT

5、 NOT NULL, POSITION VARCHAR50) NOT NULL, STARDATE DATE NOT NULL,ENDDATE DATE NULL, SALARY INT NOT NULL);IF/ SLQueryd.sql - H.tiiPeng » (S1)J* |CREATE TABLE JOBHISTORY(EMPNO SMALLINT NOT NULL,POSITION VARCHAR(50) NOT NULL STARDATE DATE NOT NULL,ENDDATE DATE NULL,SALARY INT NOT NULL|加消息命令已成功堯成。CO

6、URSECREATE TABLE COURSECOURSENOSMALLINT NOT NULL, CNAMEVARCHAR50) NOT NULL, CDATE DATE NOT NULLSLQuery3.sql - H.ZhiPeng (sa (31)*CREATE TABLE COURSE(COURSENO SMALLINT NOTCNAME VARCHAR(50) NOTCDATE DATE NOT NULL)；NULL,NULL,屮1HfGj消息命令已成功完成。)；DEPARTMENTCREATE TABLE DEPARTMENTDEPNO SMALLINT NOT NULL, DN

7、AMEVARCHAR50) NOT NULL, LOCATION VARCHAR10) NOT NULL, HEAD SMALLINT NOT NULL)；nQLQuery5.sq|- HZhiPeng 0a (51)*CREATE TABLE DEPARTMENT (DEPNO SMALLINT NOT NULL,DNAME VARCHAR(50) NOT NULL, LOCATION VARCHAR(10) NOT NULL, HEAD SMALLINT NOT NULLL);(i 川|自腐息金令巳咸功完成。EMPCOURSECREATE TABLE EMPCOURSEEMPNO SMAL

8、LINT NOT NULL, COURSENOSMALLINT NOT NULL);/SQ【LQuerySql HiPeng【陽CREATE TABLE EMPCOURSE( EMPNO SMALLINT NOT NULL, COURSENO SMALLINT NOT NULL)；nr百消息命令已成勸完成。3.使用Transact-SQL語句創(chuàng)建JOBS數(shù)據(jù)庫包含表的主鍵、外鍵約束條 件創(chuàng)建每個表的主鍵：ALTER TABLE EMPLOYEEADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY ( EMPNO;* ILQuery3k&q 1 - H.ZhiPe

9、ng (sa 51)*3ALTER TABLE EMPLOYEEADD CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPNO)；tfh由消息命令已成功完成*ALTER TABLE JOBHISTORYADD CONSTRAINT PK_JOBHISTORYPRIMARY KEY ( EMPNO POSITION , STARDATE);SQLQueryj.sql - H_ZhiPeng (sa (51)*ALTER TAELE JOBHISTORYADD CONSTRAINT PK_JOBHISTORY-PRIMARY KEY (EMPNOf POSITION,S

10、TARDATE)；_j消息命令已成功完成>ALTER TABLE COURSEADD CONSTRAINT PK_COURSEPRIMARY KEY ( COURSENOSQLQueryl.sql - H.hiPcng (sa (51)*QALTER TABLE COURSEADD CONSTRAINT PK_COURSE PRIMARY KEY (COURSENO)；< I|方消息命令已成照成廠ALTER TABLE DEPARTMENTADD CONSTRAINT PK_DEPARTMENTPRIMARY KEY ( DEPNO)；EQLQuery3.sql - H.ZhiPe

11、ng (saI BALTER TABLE DEPARTMENTADD CONSTRAINT PKDEPARTMENTL PRIMARY KEY (DEPNO);內(nèi)消息命令已成功圭成。ALTER TABLE EMPCOURSEADD CONSTRAINT PK_EMCOURSE PRIMARY KEY ( EMPNO COURSENOSQLQuery3.sql - HZhiPerig (sa(51)*WALTER TABLE EMPCOURSEADD CONSTRAINT PK EMCOURSE-PRIMARY KEY (EMPNO,COURSENO)；frrr國消息命令已成功完成。創(chuàng)建關(guān)系：A

12、LTER TABLE JOBHISTORYADD CONSTRAINT FK_JOBHISTORY_EMPLOYEEFOREIGN KEY ( EMPNOREFERENCES EMPLOYEE ( EMPNO；£qLQu&ry3Lsql - HhiPeng (sa (Sl)fcEEALTER TABLE JOBHISTORYADD CONSTRAINT FK JOBHISTORY EMPLOYEEFOREIGN KEY (EMPNO)-REFERENCES EMPLOYEE (EMPNO)；*匚nrh消息命令已成功完成。ALTER TABLE EMPCOURSEADD CON

13、STRAINT FK_EMPCOURSE_EMPLOYEE FOREIGN KEY ( EMPNOREFERENCES EMPLOYEE ( EMPNO;兀LQuery生門 1 - H.hiPeng (sa 卩助尸WALTER TABLE EMPCOURSEADD CONSTRAINT FK EMPCOURSE EMPLOYEEFOREIGN KEY (EMPNO)-REFERENCES EMPLOYEE(EMPNO)；pii_J消息命令已成功完成。ALTER TABLE EMPCOURSEADD CONSTRAINT FK_EMPCOURSE_COURSE FOREIGN KEY ( COu

14、RSeNOREFERENCES COURSE ( COURSENO|/5QLQuwy玄絢I二H.NhiPM尊(sa (51)尸匚ALTER TABLE EMPCOURSEADD CONSTRAINT FK_EMPCOURSE_COURSE FOREIGN KEY (COURSENO)-REFERENCES COURSE (COURSENO)；_j消息命令已碩功完成°ALTER TABLE EMPLOYEEADD constraint FK_EMPLOYEE_DEPARTMENT FOREIGN KEY ( depnoreferences department ( DEPNC);SQk

15、Qtryj.刊 I - H是hiP的g (sa (51)7ALTER TAELE EMPLOYEEADD CONSTRAINT FK_EMPLOYEE_DEPARTMENTFOREIGN KEY (DEPNO)REFERENCES DEPARTMENT (DEPNO);(I布命令已遍完咸。ALTER TABLE DEPARTMENTADD CONSTRAINT FK_DEPARTMENT_EMPLOYEE FOREIGN KEY ( HEADfREFERENCES EMPLOYEE EMPNO；&QLQuery3.sql - H.ZhiPeng (sa (Sl)s ALTER TABL

16、E DE PARADD CONSTRAINT FK_DEP/RTMENT_EMPLOYEEFOREIGN KEY (HEAD亍-RE FERENCES EMPLOYEE(EMPNO)；SQkQtryj.刊 I - H是hiP的g (sa (51)7ALTER TAELE EMPLOYEEADD CONSTRAINT FK_EMPLOYEE_DEPARTMENTFOREIGN KEY (DEPNO)REFERENCES DEPARTMENT (DEPNO);SQkQtryj.刊 I - H是hiP的g (sa (51)7ALTER TAELE EMPLOYEEADD CONSTRAINT FK_

17、EMPLOYEE_DEPARTMENTFOREIGN KEY (DEPNO)REFERENCES DEPARTMENT (DEPNO);具體關(guān)系：EXECUTE sp_helpconstraintDEPARTMENTUEXE亡UTE sphelpconatlalnt DEPARTMENTLQbfQCl Name BL1 : DEPAFTMEWT fHNts0wl_1ypecwslraiil_nymucx)rra(rainl_kQys1 :FOREIGN KEY": FK_DEPARTMENT.EMPLOYEEHEAD2 REfEA ENGES JOTS-DengZhiPeng dbo

18、 EMPLOYEE lEMPNO)3 PRIMARY KEY (cfuslsiMl； PK_DEPARrMGNTD£PIW)EMPCOURSEEXECUTE sp_helpconstraint5'.vn_bj i：SQLQuEiyJ.sql H_ZI>iEig (xa (Jl)fliEXECUTE sp helpconstraint LHPCOURi>LEQycryJxql H_ZhiPrg :nKXECUTE su heluconstj-aint JjhHlSLCRr;即 M . AEOtijod Ndrr») EMPLOYEE 1（所有JOBS中表的

19、約束圖。注意：必須如上圖所示清楚完整顯示約束的 constraint_type constraint_name constraint_keys等信息。）Obj&d Name1 'eMPCCXJR5E'EXECUTE sp_helpconstraintJOBHISTORY;I _ _- UT jObiect rtaine1 j JOBtilSTORY :ccri5bart_iiutiiecorfttidiiiL_kesFK_JOGHI STOR Y_EMP LOYT 匚EWNORHII4ARY KtYtU5®5d； -V, JOtJHISrQRYREFERENC

20、ES JOBS_DengZhPeng dbo EMPLOYEE (EMPMO)EMPWQ. POSlriQN 51ARL1ATEEXECUTE sp_helpconstraintEMPLOYEE說LQS竹塞斗I mH科睥. W CIWl*L execute sp helpconstraiiit employee；con5pr<nnt k 巧f OTPWDREFERENCF.IOFLS DengZhiPEngdbDDEPARfTMEhrTpEWO) EMPNOPT I MRY KEY (d ustei ed PK_E MCOUR8ECDnscrani_kBy5l :、t_r, _REftH

21、tMC七S JOBS.DengZhiFerig dbo COURSE (COUHSENO> tPNOREFERENCES JOBS.OngZ'iiPeng dbo EMPLOYEE (EMPTOt tTdFNQp 匚 OU RS ENOCCflstlfiril hypflconistriint nanflPRI MARY KF¥ 怛*仙阿 PK_F MPL OYEFconstran_narT*e _m-l-OHLIGN KEYF KEMPCOURSE_E MPLOYEE:rOREl 酬 KEY FOREIGN KEYFK FMPL DYFF nFFARTMFNTcori

22、£tian_ JHLIGN KbY4 備份JOBS數(shù)據(jù)庫5.使用Transact-SQL語句在JOBS數(shù)據(jù)庫里創(chuàng)建視圖（注意：在以下各個小題中，后續(xù)題目可以利用前面題目創(chuàng)建的視圖） 創(chuàng)建一個名為“ firstview ”的視圖，列出不重復(fù)的所有選修了課程的emp no(插入定義該視圖的SQL窗口)CREATE VIEW firstview ( EMPNO ASSELECT DISTINCT EMPNOFROM EMPCOURSECREATE VIEW firstview(EMPNO)嚴(yán)SELECT DISTINCT EMPNOFROM EMPCOURSE；消息命令已成功完成。（插入查

23、詢該視圖的SQL窗口及結(jié)果）!師的 y證憚1|廠|越岡主登|至SQLQueiyLtql - HhiPeng (m (53)* |SELECT 大 FROM firstview;；HUO-Pt£QLEXf>RE5S (SO 據(jù)庫|庫匚 ampany_D engzliipen gJO B $_DengZh iPeng丄加庫去蠱圖 口表LU 3圖拮杲I山槻H 口至蜿視関+1 囲 dbc.firstyiw口同義詞口可幕程性Servi ce Broker3貞創(chuàng)建一個名為“ secondview”的視圖，列出所有 empno小于5的員工信(插入定義該視圖的SQL窗口)CREATE VIEW

24、 secondview ( EMPNO SUPNAME FORENAMES DOB ADDRESS TELNO, DEPNC) ASSELECT *FROM EMPLOYEEWHEREEMPNOv 5 ;SQLQueryLsql - iHtiiPeng (sa 卩3)尸|日CREATE VIEW secondview(EMPNO,SUPNAME, FORENAMES,DOB.ADDRESS,TELNOfDEPNO) ASSELECT *FROM EMPLOYEE-WHERE EMPNO < 5;<川岳涓息命令已成功完成pIf J（插入查詢該視圖的SQL窗口及結(jié)果）JC BS_O=

25、ngZhiergfrQ>LQutfYLsA|-HB .ZhiPtng (»a (53)*SELECT 十 FROM 蕊L(fēng)'_L-.i, J.LX Kz gcEF3i"y_TjT- peig IDH口訶 q tEE刁 LI SAE1C123 dbc.fi dbOrScconcivifrrf可漏左性_jBroter-J Wzr 氐 EX5RFODftSerrertSLEX* 1苗真一 1哨宜EMPNOSUPHA.FORENAMDOBADDRETELNODEPNO1h JJonesEli 圏 beth1944 01 0526 Agr»e2123371 n2

26、2SmrthRobed1947-02-0718 Mars.031732.i33WhitoAlnri1961 05 OSfi iRnot1215551A4ReidGordon19G3-08-W9 Noble.(329424.i(3)創(chuàng)建一個名為“ thirdview”的視圖，列出每個empno及其相應(yīng)的選修課 程數(shù)(插入定義該視圖的SQL窗口)CREATE VIEW thirdview( EMPNO COURSENUMASSELECT EL. EMPNO COUNT*)FROM EMPLOYEE EL JOINEMPCOURSEEC ON EL. EMPNO = EC. EMPNOGROUPBY

27、 EL. EMPNOJO BS_Denghi Pengf晰兇 v號障gif警|購圜Q 1港誑審訊LQxryl.*叩 ” H.ZhiPeng (利(51)*ECREATE VIEW thirdview(EMPNO,COURSENUM) ASSELECT EL.EMPNO.COUNT(*)FROM EMPLOYEE EL JOINEMPCOURSE EC ON EL.EMPNO = EC,EMPNO-GROUP BY EL.EMPNO;(插入查詢該視圖的SQL窗口及結(jié)果)1 JOBS.DengZhiPeng !魁兇卜 “ 璋 #1| P | 館團(tuán)Q | 1 巻 | 華寧 | 軸<SQLQue

28、ryLql - H. ,ZhiPeng(51)*曰一CREATE VIEW thirdview (EMPNO, COURSENUM)IULASJ3tlSELECT * FROM thjrdyiew；3*irr戸如曲消患1EMPNO COURSENUM111 ' 'J222 2 創(chuàng)建一個名為“ fourthview ”的視圖，列出每個 empno及其已經(jīng)或正在(插入定義該視圖的SQL窗口)CREATE VIEW fourthview ( EMPN。JOBNUM ASSELECT EL. EMPNO, COUNT*) FROM EMPLOYEE ELJOIN JOBHISTORY

29、JO ON EL. EMPNO =JO. EMPNOGROUPBY EL. EMPNO1JOBS_DengZhi?eng | !卜“密 0| 1° 財琶醫(yī)Q 二 2 準(zhǔn)車翳靈<SQLQuerylsql - HhiPeng(51)*2CRZATE VIEW fourthviewEMPNO,JOBNUM)lASJFlaSELECT EL.EMPNO ,COUNT(*) FROMBEMPLOYEE EL3JOIN JOBHISTORY JO ON EL,EMPNO 二 JO.EMPNOLGROUP BY EL-EMPNO；” L.,rr曲消慝命等已成功芫成。（插入查詢該視圖的SQL窗

30、口及結(jié)果）SELECT * FROM FOURTHVIEW占QLQumryL釣 1 - H.hiPeng (sa (51)*SELECT 大 FROM EQ卿購結(jié)果區(qū)直息EMPNOJOBNUM-1H1 42233324A1552861JO B S_D engZhieng(5)創(chuàng)建一個合并第3和第4小題視圖的SELECT語句，以查詢每個empno 對應(yīng)的工作數(shù)和課程數(shù)。你不需要重復(fù)創(chuàng)建視圖，只需要利用前面兩個 小題中已經(jīng)創(chuàng)建好的視圖。如果某員工號對應(yīng)的課程數(shù)為 0,則在查詢 結(jié)果中應(yīng)顯示為NULL。提示：用外連接(插入該查詢的SQL窗口及結(jié)果)SELECT EL. EMPNQT. COURSEN

31、UMF. JOBNUMFROM EMPLOYEE ELLEFT JOIN THIRDVIEW T ONT. EMPNO= EL. EMPNOJOIN FOURTHVIEW F ONEL. EMPNO = F. EMPNOJ 0 BSDeri gZ hiPer g|r sutw & 7證屬圓|汀豊|83匱勒|互=»卜mySQLQueryl.sql - hLZhiFng (u <51)p SELECT EL.EMPNO,T COURSENUM*FJOBNUM FROM EMPLOYEE ELLEFT JOIN THIRDVIEW T ON T.EMPNO 二 EL.EMPN

32、O LJOIN 舷lEUiXI馳 F ON EL , EMPNO =忑翩哋；拒VICE:Sei;Sci:結(jié)果a満息EMPNOCOURSENJMJOBNUM1I 124222333NULL244NULL155NLJLI2rMNULL16 66.使用Transact-SQL語句對表添加、修改、刪除數(shù)據(jù)(1)插入數(shù)據(jù)按照ActiveSQL JobsDB.rar文件里的數(shù)據(jù)庫狀態(tài)圖插入所有表的數(shù)據(jù)(如果試圖插入的數(shù)據(jù)將會違反第3步創(chuàng)建的約束條件，則可跳過該行數(shù)據(jù)的輸入)0INSERT INTOCOURSE COURSENOCNAME CDATE)VALUES ( 1, 'Basic Accou

33、nting', '1989-0111');INSERT INTOCOURSE COURSENOCNAME CDATE)VALUES (2, 'Further Accounting', '198901-25');INSERT INTOCOURSE COURSENOCNAME CDATE)VALUES (3, 'Issues InAdministration' , '1988-09-27');INSERT INTOCOURSE COURSENOCNAME CDATE)VALUES (4, 'More A

34、dministration', '1988-10-16');select * from courseSLucryZEql hLNiiPong (a (53)*0 INSERT INTO COURSE (C OURS ENO f CNAME, CDATE; -VALUES 'More Administration '1988-10-161); select 大 froni course;41J結(jié)異_ J泊自COURSENO GNAMECDATE1j i§JBasic Accounting199-01-1172FurlhRf Aocd jnting

35、33Issues In Administration1988-09-2744More Adn hi strati or1088 10 16ALTER TABLE EMPLOYEE ALTER COLUMNDEPNO SMALLINT NULL;INSERT INTO EMPLOYEE EMPNO SUPNAME FORENAMES DOB ADDRESS TELNO, DEPNO VALUES ( 1, 'Jones' , 'Elizabeth Barbara' '1944-01-05', '26 AgnewsTerrace,Shamro

36、ck Bay' ,'2123372288', NULL);INSERT INTO EMPLOYEE EMPNO SUPNAME FORENAMES DOB ADDRESS TELNO, DEPNO VALUES (2, 'Smith' , 'Robert', '1947-02-07', '18 MarshStreet,Tollcross,Ed in burgh','0317328972', NULL);INSERT INTO EMPLOYEE EMPNO SUPNAME FORENAMES

37、DOB ADDRESS TELNO, DEPNO VALUES ( 3, 'White' , 'Allan' , '1961-05-05', '6 Remote Place,NorthBerwick' , '1215556622' , NULL);INSERT INTO EMPLOYEE( EMPNO, SUPNAME, FORENAMES, DOB, ADDRESS, TELNO, DEPNO) VALUES ( 4, 'Reid' , 'Gordon' , '1963-0

38、8-10' , '9 Noble Road,Penicuik' , '6294246713' , NULL);INSERT INTO EMPLOYEE( EMPNO, SUPNAME, FORENAMES, DOB, ADDRESS, TELNO, DEPNO) VALUES ( 5, 'MacCallan' , 'Claire' , '1958-09-18' , '25 Crisis Avenue,Leith,Edinburgh' , '0313374166' , NULL

39、);INSERT INTO EMPLOYEE( EMPNO, SUPNAME, FORENAMES, DOB, ADDRESS, TELNO, DEPNO) VALUES ( 6, 'Murphy' , 'Brian Charles' , '1954-06-30' , '9 Roberts Street,Biggar' '3312294147' , NULL);當(dāng)錄制完DEPARTMENT以后，再將NULL設(shè)置為相 應(yīng)的DEPN0值。UPDATE EMPLOYEESET DEPN0 = 1WHEREDEPN0 IS

40、 NULL;最后的結(jié)果：，一區(qū)JCBS. ZicrgZh P«rn+ J 斷兇 p/打 r | .-r rcHa怙*f dgEhlpg#厲 HUASHUOPCQL,. dt«i.J?E=AR_MENI SQJ.Qu«ryLH|l - HZNftng ( 1U»'Jj SELECT * FROM EMPLOYEE；UA(韶二ri也誌金二消昱ull1FMPNOL.1SUPNAMFJonasFORENAMESFlizatith FSarhaaiDDB1944*01-05ADDRESS?6 Agnfw=; Tarraca Shamrock B百yTELN

41、O21?337?28SEPNO1it!27SmithRobert1047 02-07IBMarah Strost.TolcroK.EAiburgh031732(1972133WMbAllan)061 06 05Q Ronnob Place North Borwidc1215556622144RetdGordon)903 08 109 Nobto Road,Pevikuk62942467131Is5MacCialanGlaie195B D9-1BQi5iAirioe.LeriFi,tdjiibLirgh03133741001e6MurphyBrian Charles1954-M309 Hobei

42、rb SlieeL Biggar33122941471EIINSERT INTODEPARTMENTDEPNQ DNAME LOCATION, HEAD)VALUES ( 1, 'accounts', 'floor3' , 1);INSERT INTODEPARTMENTDEPNO, DNAME LOCATION, HEAD) VALUES (2,'administration', 'floor2' , 1);INSERT INTODEPARTMENTDEPNO, DNAME LOCATION, HEAD) VALUES ( 3,

43、 'software design' , 'floor1' , 2);INSERT INTODEPARTMENTDEPNO, DNAME LOCATION, HEAD) VALUES ( 4, 'communications', 'floor4' , 3);select * from DEPARTMENTSQLQUEryLsql - H_Zh|#ng (u (ID)' INSERT INTO DEPARTMENT(DEPWO,DNAME,LOCATIOK,HEAD) -VALUES (lf faccounts 1f f f

44、loor31f1);INSERT INTO DEPARTMENTDEPNO,DNAME,LOCATION,HEAD) f VALUES (2,Administration1,f fLoor21f 1);INSERT INTO DEPARTMENT (DEPNO, DMTkME, LOCATION, HEAD) -VATiUES (3r 1 software design 1 f 1 floorlT , 2);INSERT INTO DEPARTMENTDEPNO,DNAME,LOCATION,HEAD) -VTkLUES (4 J ccmmunications 1, * floors 1,3)

45、;-sele匚t * f匚Din DEPARTMENT;DEPNO DNAME1accourilsadmin istra tionI OCAJION HF ADflCKJl3floor?floorlfluor4software designccrnriiLjnicaloisINSERT INTO EMPCOURSEEMPNO COURSENO VALUES ( 1, 1);INSERT INTO EMPCOURSEEMPNO COURSENO VALUES (1,2);INSERT INTO EMPCOURSEEMPNO COURSENO VALUES (2, 1);INSERT INTO E

46、MPCOURSEEMPNO COURSENOVALUES (2, 2);select * from EMPCOURSEryhsql ” H.ZhiPcng («a (52)*B INSERT INTO EMP COURSE (EMPNO, COURS ENO) I-VALUES (lf 1);n INSERT into emp COURSE (EMPNO? COURS ENO) -VALUES (lz 2)；iINSERT INTO EMPCOURSE(EMPNO,COURSENO)-VALUES (2Z1)；iINSERT INTO EMPCOURSE(EMPNO,COURSENO

47、)-VALUES (2f 2)；Lselect 大 from EMPCOURSE;If I-1111鉛案Jjj消懸EMPNQCOURSENO1f Jill 1 KKIIBIIH111! 1212321422INSERT INTOJOBHISTORY( EMPNO POSITION , STARDATE END DATE, SALARY)VALUES ( 1, 'Accounts Manager', '1976-0112' ,NULL, 30000 );INSERT INTOJOBHISTORY( EMPNQ POSITION , STARDATE ENDDATE

48、, SALARY)VALUES ( 1, 'Assistant AccountsManager' , '1972-02-11', '1976-0112' , 22000 );INSERT INTOJOBHISTORY( EMPNQ POSITION , STARDATE ENDDATE, SALARY)VALUES ( 1, 'Accountant', '1968-0310' ,'1972-02-11', 15000 );INSERT INTOJOBHISTORY( EMPNO, POSITION

49、, STARDATE, ENDDATE, SALARY)VALUES ( 1, 'Junior Accountant', '196404-09' , '1968-03-10' , 6000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 2, 'Assistant AccountsManager' , '1976-05-08' ,NULL, 25000 );INSERT INTOJOBHISTORY( EMP

50、NO, POSITION , STARDATE, END DATE, SALARY)VALUES ( 2, 'Accountant', '1971-0607' , '1976-05-08' , 16000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 2, 'Junior Accountant', '196707-06' , '1971-06-07' , 8000 );INSERT INTO

51、JOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 3, 'Accountant', '1981-0805' ,NULL, 16000 );INSERT INTOJOBHISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 3, 'Junior Accountant', '198109-04' , '1984-08-05' , 8000 );INSERT INTOJOBH

52、ISTORY( EMPNO, POSITION , STARDATE, ENDDATE, SALARY)VALUES ( 4, 'Accountant', '1989-1005' ,NULL, 16000 );INSERT INTOJOBHISTORY( EMPNO POSITION , STARDATE ENDDATE, SALARY)VALUES (5, 'Accountant', '1980-1102' ,NULL, 16000 );INSERT INTOJOBHISTORY( EMPNQ POSITION , STARDA

53、TE ENDDATE, SALARY)VALUES (5, 'Junior Accountant', '197812-01', '1980-11-02', 8000 );INSERT INTOJOBHISTORY( EMPNQ POSITION , STARDATE ENDDATE, SALARY)VALUES ( 6, 'Accountant', '1980-0112' ,NULL, 16000 );select * from JOBHISTORY;H.-Zhnj 如WO)'.INSERT INTO JO

54、BHISTORYPOSITION, ST AR DATE, END DATE, SALARY)I VALUES (6f 'Accountant* # ' 1980-01-12,NULL, 16000);Lselect * from JOBHISTORY;FMPMOposmoNSTARDATFFNDDATESAI ARY1IAccountant1968-03-101972-02-111500021AcuuLint!,107fiDI 12NULL3000031Assslant Aocotinls Mai :agwi1972-02-1119J6-01-122200041Junkir Accountantle&d-w-og1968-03-10fiODO52Accourtant1971 06 071976 05 081600062Assisiant Accounts btanagerNULL2500072junior Accountant19B7-D7-06I&am