数据库实验三

1、西南石油大学实验报告课程名称：数据库原理插入你的照片实验项目名称：实验3 SQL数据定义语言专业年级班级、姓名、学号：电子邮件地址：实验所用机器名：实验时间地点：2015.12.11明理楼实验指导教师：孙瑜成绩批改人批改日期注意：在粘贴截图时请保留窗口完整标题，但只需保留关键界面，多余的 空白界面请删除。一、实验课时：4二、实验目的(1) 掌握使用T-SQL语句创建、删除数据库的方法。掌握使用T-SQL语句创建、修改、删除表的方法。(3) 掌握使用T-SQL语句创建、删除数据库完整性约束条件的方法。(4) 掌握使用T-SQL语句对表添加、修改、删除数据的方法。(5) 掌握使用T-SQL语句创建

2、、修改、删除、查询视图的方法。三、实验要求(1) 使用SQL Server 2008查询分析器。(2) 严格依照操作步骤进行。(3) 在本地服务器中创建和管理数据库。四、实验环境(1) PC 机。 SQL Server 200&五、实验内容及步骤（请特别注意实验步骤：第6项的第1小项，即“插入数 据”操作必须在第4项以前执行）1 使用Transact-SQL语句创建JOBS数据库，数据库名格式为JOBS_SunYi即JOBS你的中文名字拼音）CREATE Database JOBS_DengZhiPengSQLQuerySql - HLLSrriHtwr (h <5L)*CREA

3、TE Database JOBS DengZhiPeng;< 1rrr目消息命令已成功完成。2 使用Transact-SQL语句创建JOBS数据库包含的所有表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语句创建JOBS数据库包含表的主键、外键约束条 件创建每个表的主键：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);內消息命令已成功圭成。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国消息命令已成功完成。创建关系：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);具体关系：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数据库5.使用Transact-SQL语句在JOBS数据库里创建视图（注意：在以下各个小题中，后续题目可以利用前面题目创建的视图） 创建一个名为“ firstview ”的视图，列出不重复的所有选修了课程的emp no(插入定义该视图的SQL窗口)CREATE VIEW firstview ( EMPNO ASSELECT DISTINCT EMPNOFROM EMPCOURSECREATE VIEW firstview(EMPNO)严SELECT DISTINCT EMPNOFROM EMPCOURSE；消息命令已成功完成。（插入查

23、询该视图的SQL窗口及结果）!師的 y證惮1|厂|越岡主登|至SQLQueiyLtql - HhiPeng (m (53)* |SELECT 大 FROM firstview;；HUO-Pt£QLEXf>RE5S (SO 据库|库匚 ampany_D engzliipen gJO B $_DengZh iPeng丄加库去蛊图 口表LU 3图拮杲I山槻H 口至蜿视関+1 囲 dbc.firstyiw口同义词口可幕程性Servi ce Broker3貞创建一个名为“ 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窗口及结果）JC BS_O=

25、ngZhiergfrQ>LQutfYLsA|-HB .ZhiPtng (»a (53)*SELECT 十 FROM 蕊L'_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)创建一个名为“ thirdview”的视图，列出每个empno及其相应的选修课 程数(插入定义该视图的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窗口及结果)1 JOBS.DengZhiPeng !魁凶卜 “ 璋 #1| P | 館團Q | 1 巻 | 華寧 | 轴<SQLQue

28、ryLql - H. ,ZhiPeng(51)*曰一CREATE VIEW thirdview (EMPNO, COURSENUM)IULASJ3tlSELECT * FROM thjrdyiew；3*irr戸如曲消患1EMPNO COURSENUM111 ' 'J222 2 创建一个名为“ fourthview ”的视图，列出每个 empno及其已经或正在(插入定义该视图的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° 财琶医Q 二 2 準車翳灵<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、口及结果）SELECT * FROM FOURTHVIEW占QLQumryL钓 1 - H.hiPeng (sa (51)*SELECT 大 FROM EQ卿购结果區直息EMPNOJOBNUM-1H1 42233324A1552861JO B S_D engZhieng(5)创建一个合并第3和第4小题视图的SELECT语句，以查询每个empno 对应的工作数和课程数。你不需要重复创建视图，只需要利用前面两个 小题中已经创建好的视图。如果某员工号对应的课程数为 0,则在查询 结果中应显示为NULL。提示：用外连接(插入该查询的SQL窗口及结果)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:结果a満息EMPNOCOURSENJMJOBNUM1I 124222333NULL244NULL155NLJLI2rMNULL16 66.使用Transact-SQL语句对表添加、修改、删除数据(1)插入数据按照ActiveSQL JobsDB.rar文件里的数据库状态图插入所有表的数据(如果试图插入的数据将会违反第3步创建的约束条件，则可跳过该行数据的输入)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结異_ 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);当录制完DEPARTMENT以后，再将NULL设置为相 应的DEPN0值。UPDATE EMPLOYEESET DEPN0 = 1WHEREDEPN0 IS

40、 NULL;最后的结果：，一區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