| ëÁÔÁÌÏÇ | ïÇÌÁ×ÌÅÎÉÅ | éÎÄÅËÓ ÒÁÚÄÅÌÁ |
| îÁÚÁÄ | ÷ÐÅÒÅÄ |
äÌÑ ÚÁÐÏÌÎÅÎÉÑ ÂÁÚÙ ÄÁÎÎÙÈ ÎÁÍÉ ÐÏÄÇÏÔÏ×ÌÅÎ ÓËÒÉÐÔ, ÓÏÄÅÒÖÁÝÉÊ ÎÁÂÏÒ SQL-ÏÐÅÒÁÔÏÒÏ× INSERT. îÉÖÅ ÐÒÉ×ÏÄÉÔÓÑ ÞÁÓÔØ ÜÔÏÇÏ ÓËÒÉÐÔÁ - ÐÏ ÎÅÓËÏÌØËÏ ËÏÍÁÎÄ ÄÌÑ ËÁÖÄÏÊ ÔÁÂÌÉÃÙ:
delete from work; delete from staff; delete from education; delete from department; delete from edu_level; delete from edu_profile; delete from job; delete from employee; INSERT INTO department VALUES (10, 'ÕÐÒÁ×ÌÅÎÉÅ', 28500); INSERT INTO department VALUES (20, 'ÏÔÄÅÌ ôï', 10500); . . . INSERT INTO edu_level VALUES(10,'ÎÁÞÁÌØÎÏÅ'); INSERT INTO edu_level VALUES(20,'ÓÒÅÄÎÅÅ'); . . . INSERT INTO edu_profile VALUES(0,'ÏÂÝÅÅ'); INSERT INTO edu_profile VALUES(1,'ÔÅÈÎÉÞÅÓËÏÅ'); . . . INSERT INTO job VALUES (1, 'ÄÉÒÅËÔÏÒ', 5000, 6000, 40, 1, 5); INSERT INTO job VALUES (2, 'ÚÁÍ.ÄÉÒÅËÔÏÒÁ', 3000, 5000, 40, 1, 3); . . . -- ÕÐÒÁ×ÌÅÎÉÅ INSERT INTO staff VALUES (101, 1, 10, 1); INSERT INTO staff VALUES (102, 2, 10, 1); . . . INSERT INTO employee VALUES (1947858035, 'óÁÂÁÄÁÛ', '÷ÌÁÄÉÍÉÒ', 'æÅÄÏÒÏ×ÉÞ', '11.05.1949', 'ÕÌ.ëÕÒÓËÁÑ,16,Ë×.40', 156785); INSERT INTO education VALUES (60, 1, 1947858035, 1983); INSERT INTO education VALUES (40, 2, 1947858035, 1994); INSERT INTO work (empl_id, staff_id, salary, start_date, rate) VALUES (1947858035, 101, 5700, '01/01/1990', 1); INSERT INTO employee VALUES (1074305123, 'çÏÒÉÄÚÅ', 'á×ÁÓ', 'çÏÇÉÅ×ÉÞ', '10.01.1954', 'ÕÌ.äÁÎÉÌÅ×ÓËÏÇÏ,19,Ë×.103', 476589); INSERT INTO education VALUES (40, 2, 1074305123, 1993); INSERT INTO work VALUES (1074305123, 110, 4800, '01/01/1990', 1, '1'); . . .
ïÂÒÁÝÁÅÍ ×ÎÉÍÁÎÉÅ ÎÁ ÓÌÅÄÕÀÝÉÅ ÏÓÏÂÅÎÎÏÓÔÉ ÜÔÏÇÏ ÓËÒÉÐÔÁ:
äÁÎÎÙÊ ÓËÒÉÐÔ ÐÏÄÇÏÔÏ×ÌÅÎ ÄÌÑ óõâä DB2, ÎÏ ÓÐÅÃÉÆÉËÏÊ ÅÇÏ ÏÒÉÅÎÔÁÃÉÉ ÎÁ DB2 Ñ×ÌÑÅÔÓÑ ÌÉÛØ ÔÏ, ÞÔÏ ÚÎÁÞÅÎÉÑ ÄÁÔ ÐÒÅÄÓÔÁ×ÌÅÎÙ × ÆÏÒÍÁÔÅ, ÐÒÉÎÑÔÏÍ ÐÏ ÕÍÏÌÞÁÎÉÀ ÄÌÑ DB2. îÉÖÅ ÐÒÉ×ÏÄÉÔÓÑ ÐÒÏÔÏËÏÌ ×ÙÐÏÌÎÅÎÉÑ ÏÄÎÏÊ ËÏÍÁÎÄÙ INSERT × ÓÒÅÄÅ DB2 Command Center:
äÌÑ ×ÙÐÏÌÎÅÎÉÑ ÜÔÏÇÏ ÖÅ ÓËÒÉÐÔÁ × Oracle ÎÕÖÎÏ:
ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY';
îÉÖÅ ÐÒÉ×ÏÄÉÔÓÑ ÐÒÏÔÏËÏÌ ×ÙÐÏÌÎÅÎÉÑ ÎÅÓËÏÌØËÉÈ ËÏÍÁÎÄ × ÓÒÅÄÅ Oracle SQL*Plus:
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD/MM/YYYY'; Session altered. SQL> INSERT INTO department VALUES (10, 'ÕÐÒÁ×ÌÅÎÉÅ', 28500); 1 row created. . . . SQL> INSERT INTO work (empl_id, staff_id, salary, start_date, rate) 2 VALUES (7288467531, 905, 1300, '01/04/1999', 1); 1 row created. SQL> commit; Commit complete.ðÒÏ×ÅÒËÁ ÚÁÎÅÓÅÎÉÑ ÄÁÎÎÙÈ × ÂÁÚÕ
ðÒÏ×ÅÒËÁ ÚÁÎÅÓÅÎÉÑ ÄÁÎÎÙÈ × ÂÁÚÕ ÐÒÏÉÚ×ÏÄÉÔÓÑ ×ÙÐÏÌÎÅÎÉÅÍ ÏÐÅÒÁÔÏÒÁ SELECT * FROM ... - ÄÌÑ ×ÓÅÈ ÚÁÐÏÌÎÑÅÍÙÈ ÔÁÂÌÉÃ. îÉÖÅ ÐÒÉ×ÏÄÑÔÓÑ ÒÅÚÕÌØÔÁÔÙ ×ÙÐÏÌÎÅÎÉÑ ÔÁËÉÈ ÏÐÅÒÁÔÏÒÏ×:
ÄÌÑ DB2:
------------------------------ Command Entered ------------------------------
SELECT * FROM edu_profile ;
-----------------------------------------------------------------------------
PROF_ID PROF_NAME
----------- --------------------
0 ÏÂÝÅÅ
1 ÔÅÈÎÉÞÅÓËÏÅ
2 ÜËÏÎÏÍÉÞÅÓËÏÅ
3 ÀÒÉÄÉÞÅÓËÏÅ
4 ÇÕÍÁÎÉÔÁÒÎÏÅ
5 ×ÏÅÎÎÏÅ
6 record(s) selected.
------------------------------ Command Entered ------------------------------
SELECT * FROM department ;
-----------------------------------------------------------------------------
DEP_ID DEP_NAME SALARY_LIMIT
----------- -------------------- ------------
10 ÕÐÒÁ×ÌÅÎÉÅ 28500.00
20 ÏÔÄÅÌ ôï 10500.00
30 ÐÒÏÅËÔ á 22000.00
40 ÌÁÂÏÒÁÔÏÒÉÑ ðëó 16000.00
50 ÌÁÂÏÒÁÔÏÒÉÑ óõâä 26500.00
60 ÌÁÂÏÒÁÔÏÒÉÑ íí 18500.00
70 ÐÒÏÅËÔ óôáôõó 2200.00
80 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 13000.00
90 ÆÉÎ.ÏÔÄÅÌ 12500.00
9 record(s) selected.
------------------------------ Command Entered ------------------------------
SELECT * FROM employee ;
-----------------------------------------------------------------------------
EMPL_ID NAME_LAST NAME_1ST NAME_2ND BIRTH ADDRESS PHONE
------------ -------------------- -------------------- -------------------- ---------- -------------------------------------------------------------------------------- ---------
1947858035. óÁÂÁÄÁÛ ÷ÌÁÄÉÍÉÒ æÅÄÏÒÏ×ÉÞ 11/05/1949 ÕÌ.ëÕÒÓËÁÑ,16,Ë×.40 156785.
1074305123. çÏÒÉÄÚÅ á×ÁÓ çÏÇÉÅ×ÉÞ 10/01/1954 ÕÌ.äÁÎÉÌÅ×ÓËÏÇÏ,19,Ë×.103 476589.
7832201384. çÁÒÍÁÛ ÷ÁÌÅÒÉÊ îÉËÏÌÁÅ×ÉÞ 12/07/1967 ðÕÛËÉÎÓËÉÊ ×ßÅÚÄ,12,Ë×.7 438967.
243734638. óÁËÒÉÓÑÎ íÉËÁÜÌ ÷ÁÒÁÐÅÔÏ×ÉÞ 02/11/1976 ÕÌ.æÒÕÎÚÅ,3,Ë×.37 431231.
4320928880. çÕÓÅ×Á îÁÔÁÌØÑ îÉËÏÌÁÅ×ÎÁ 21/07/1960 ÐÒ.ìÅÎÉÎÁ,12,Ë×.14 334257.
1480018888. äÏÒÏÖËÉÎ áÌÅËÓÁÎÄÒ íÉÈÁÊÌÏ×ÉÞ 11/06/1966 ÕÌ.þÉÞÉÂÁÂÉÎÁ,9,Ë×.16 334256.
8926907763. òÅÄËÌÉÆ ÷ÉËÔÏÒ ëÏÎÓÔÁÎÔÉÎÏ×ÉÞ 19/10/1948 ÕÌ.èÕÂÉÌÁÑ,21,Ë×.5 124244.
1409137333. ëÏÓÔÉÎ ÷ÁcÉÌÉÊ ëÏÎÓÔÁÎÔÉÎÏ×ÉÞ 22/07/1959 ÕÌ.ëÕÌØÔÕÒÙ,8,Ë×.27 754667.
1918439239. çÅÎÄÅÌØÍÁÎ óÏÆÉÑ íÁÒËÏ×ÎÁ 31/03/1958 ÐÒ.50-ÌÅÔÉÑ ÷ìëóí,31,Ë×.76 321134.
2625252655. ðÏÇÏÒÅÌÏ×Á ïÌØÇÁ é×ÁÎÏ×ÎÁ 02/07/1982 ÕÌ.îÁÂÅÒÅÖÎÁÑ,24,Ë×.15 645911.
2534958724. âÕÚÏ×ÓËÉÊ óÔÅÐÁÎ é×ÁÎÏ×ÉÞ 13/04/1984 ÕÌ.ðÕÛËÉÎÓËÁÑ,32,Ë×.11 436577.
1409137338. ëÒÑË×ÉÎ ÷ÌÁÄÉÍÉÒ ÷ÌÁÄÉÍÉÒÏ×ÉÞ 29/11/1959 ÕÌ.ëÕÌØÔÕÒÙ,18,Ë×.12 754447.
1078473332. ðÕÛËÏ× îÉËÏÌÁÊ é×ÁÎÏ×ÉÞ 04/02/1961 ÕÌ.ëÏÓÍÉÞÅÓËÁÑ,23,Ë×.4 435657.
2391388889. íÅÌØÎÉË áÌÅËÓÁÎÄÒ îÉËÏÌÁÅ×ÉÞ 12/05/1961 ÐÒ.ôÒÁËÔÏÒÏÓÔÒÏÉÔÅÌÅÊ,42,Ë×.11 132245.
6739201177. ëÏÔÅÊËÉÎ óÅÒÇÅÊ òÅÎÁÔÏ×ÉÞ 11/03/1977 ÕÌ.ìÅÓÎÁÑ,10,Ë×.11 329686.
8458465673. ëÁÒÁ×ÁÅ×Á äÁÒØÑ âÏÒÉÓÏ×ÎÁ 21/06/1978 ÕÌ.óÔÕÄÅÎÞÅÓËÁÑ,24,Ë×.7 150876.
2039521299. íÙÛËÉÎ éÌØÑ áÎÄÒÅÅ×ÉÞ 02/02/1983 ÕÌ.á.âÁÒÂÀÓÁ,2,Ë×.43 354578.
7132317222. íÁÌÅÅ×Á ÷ÉËÔÏÒÉÑ óÅÒÇÅÅ×ÎÁ 02/11/1964 ÐÒ.ðÒÁ×ÄÙ,3,Ë×.7 112342.
2456267797. çÒÖÉÍÁÊÌÏ ÷ÁÌÅÎÔÉÎ áÌÅËÓÁÎÄÒÏ×ÉÞ 09/06/1976 ÕÌ.âÁËÕÎÉÎÁ,41,Ë×.47 435347.
9534582434. âÏÓÉÎ íÁÔ×ÅÊ íÉÈÁÊÌÏ×ÉÞ 13/07/1979 ÕÌ.ï.ñÒÏÛÁ,55,Ë×.41 475678.
6747332241. äÁ×ÙÄÅÎËÏ ìÁÒÉÓÁ áÎÄÒÅÅ×ÎÁ 19/11/1980 ÕÌ.ó.åÓÅÎÉÎÁ,6,Ë×.14 651221.
2254623007. ëÏÂÅÌÅ×Á ìÁÒÉÓÁ ÷ÉËÔÏÒÏ×ÎÁ 12/12/1945 ÕÌ.äÅÒÅ×ÑÎËÏ,10,Ë×.61 321131.
1134718229. äÕÍÂÁÅ× æÁÒÉÚ áÂÄÕÌÁÅ×ÉÞ 07/09/1980 ÕÌ.ëÒÁÓÎÏÚÎÁÍÅÎÎÁÑ,8,Ë×.23 334526.
7529798001. âÅÓÃÅÎÎÙÊ àÒÉÊ çÌÅÂÏ×ÉÞ 31/01/1956 ÕÌ.ã×ÅÔÏÞÎÁÑ,15,Ë×.1 491772.
437003460. íÁÌÉÎÉÎÁ ó×ÅÔÌÁÎÁ éÇÏÒÅ×ÎÁ 09/10/1951 ÕÌ.ûÅËÓÐÉÒÁ,20,Ë×.131 655489.
800065212. ëÏÌÂÁÓÉÎ óÔÁÎÉÓÌÁ× íÉÈÁÊÌÏ×ÉÞ 13/08/1969 ÕÌ.òÅÂÒÏ×Á,14,Ë×.3 132246.
8341207763. ÷ÁÌØÔÍÁÎ àÒÉÊ òÏÌÁÎÄÏ×ÉÞ 09/06/1952 ÐÒ.ëÏÒÉÏÌÉÓÁ,128,Ë×.15 223254.
2009140271. ìÑÛÅÎËÏ éÒÉÎÁ óÅÒÇÅÅ×ÎÁ 27/03/1947 ÕÌ.â.æÒÁÎËÌÉÎÁ,7,Ë×.51 438938.
9052443124. ñÎÏ×Á éÒÉÎÁ ñËÏ×ÌÅ×ÎÁ 27/02/1970 ÕÌ.ëÏÓÍÏÎÁ×ÔÏ×,7,Ë×.27 433887.
7078144561. çÁÂÁÊ çÁÌÉÎÁ éÌØÉÎÉÞÎÁ 23/04/1966 ÕÌ.ûÅ×ÞÅÎËÏ,44,Ë×.7 431748.
2625798001. úÁÈÁÒÏ× ïÌÅÇ áÌÅËÓÅÅ×ÉÞ 02/07/1962 ÕÌ.23 Á×ÇÕÓÔÁ,54,Ë×.77 921187.
6738776923. ìÑÛÅÎËÏ ìÀÄÍÉÌÁ óÅÒÇÅÅ×ÎÁ 15/07/1974 ÐÒ.ðÒÁ×ÄÙ,2,Ë×.11 721118.
5325297899. òÁÓËÏÌØÎÉËÏ× òÏÄÉÏÎ òÏÍÁÎÏ×ÉÞ 21/11/1983 äÅÇÔÑÒÎÙÊ ×ßÅÚÄ,11 492187.
1256858411. óÉÎÑ×ÓËÉÊ áÌÅËÓÁÎÄÒ ìÅÏÎÉÄÏ×ÉÞ 03/02/1972 ÕÌ.ðÌÉÔÏÞÎÁÑ,16,Ë×.11 121278.
8214285776. áÎÀÔÉÎÁ ôÁÔØÑÎÁ àÒØÅ×ÎÁ 18/08/1973 ÕÌ.òÅÐÉÎÁ,14,Ë×.12 455116.
278521112. óÔÕÐÉÎ áÒÎÏÌØÄ ìÀÄ×ÉÇÏ×ÉÞ 15/07/1976 ÐÒ.ðÒÁ×ÄÙ,2,Ë×.27 721448.
9010900005. áÛÉÈÍÁÎ ÷ÉËÔÏÒ ÷ÁÓÉÌØÅ×ÉÞ 15/07/1976 ÕÌ.á.âÁÒÂÀÓÁ,12,Ë×.122 221214.
9888100983. íÁÔÏÞËÉÎÁ å×ÇÅÎÉÑ îÉËÏÌÁÅ×ÎÁ 15/07/1976 ÕÌ.ìÁËÏËÒÁÓÏÞÎÁÑ,64,Ë×.4 451677.
9872018273. çÁ×ÒÉÌËÏ ÷ÉËÔÏÒÉÑ é×ÁÎÏ×ÎÁ 15/07/1976 ÕÌ.óÔÁÓÏ×Á,47,Ë×.10 547890.
9027161000. âÅÒÍÁÎ ÷ÉËÔÏÒÉÑ òÕ×ÉÍÏ×ÎÁ 02/02/1967 ÕÌ.ï.ñÒÏÛÁ,55,Ë×.50 475883.
7501827859. ðÕÓÔÏ×ÅÃËÁÑ åÌÉÚÁ×ÅÔÁ ðÏÒÆÉÒØÅ×ÎÁ 01/01/1970 ÕÌ.÷ÅÔÅÒÉÎÁÒÎÁÑ,4,Ë×.37 475581.
6729108174. ëÕÍÕÛËÉÎ ÷ÉËÔÏÒ ðÁ×ÌÏ×ÉÞ 12/04/1974 ÕÌ.ëÏÐÙÔÎÁÑ,147 441705.
3390284007. íÕÖÉÞËÏ× áÎÁÔÏÌÉÊ îÉËÏÌÁÅ×ÉÞ 07/09/1966 ÐÒ.ìÅÎÉÎÁ,7,Ë×.132 471491.
5680933223. ïÓÔÒÏÕÍÏ× óÅÒÇÅÊ áÎÁÔÏÌØÅ×ÉÞ 02/08/1973 ÐÒ.ìÅÎÉÎÁ,12,Ë×.64 476571.
3803859822. ðÁÒÛÉÎÁ îÁÔÁÌØÑ îÉËÏÌÁÅ×ÎÁ 12/10/1983 ÕÌ.ðÕÛËÉÎÓËÁÑ,72,Ë×.30 473894.
1020738337. íÉÝÅÎËÏ íÉÈÁÉÌ îÉËÏÌÁÅ×ÉÞ 07/09/1977 ÕÌ.ëÕÒÞÁÔÏ×Á,10,Ë×.32 332455.
388373382. ëÒÁÓÎÏ×Á áÎÎÁ óÔÅÐÁÎÏ×ÎÁ 30/03/1981 ÕÌ.óÕÍÓËÁÑ,27,Ë×.11 476677.
8287520022. ëÏÇÁÎ ó×ÅÔÌÁÎÁ íÁÒËÏ×ÎÁ 07/09/1962 ÕÌ.çÉÒÛÍÁÎÁ,11,Ë×.2 476570.
7262929332. óÔÅÐÁÎÏ×Á áÌÅ×ÔÉÎÁ óÔÅÐÁÎÏ×ÎÁ 07/09/1969 ðÕÛËÉÎÓËÉÊ ×ßÅÚÄ,8,Ë×.14 471031.
9001840871. ëÏÛËÉÎÁ îÉÎÁ ÷ÉÌÅÎÏ×ÎÁ 17/09/1975 ÕÌ.þÁÊËÏ×ÓËÏÇÏ,12,Ë×.7 471253.
5637811442. ëÏÒÏÓÔÙÌÅ×Á áÎÎÁ ïÌÅÇÏ×ÎÁ 07/09/1966 ÕÌ.óÔÕÄÅÎÞÅÓËÁÑ,11,Ë×.72 496493.
6189374389. óÔÁÒÏ×ÏÊÔÏ× îÉËÏÌÁÊ âÏÒÉÓÏ×ÉÞ 07/09/1981 ÕÌ.çÕÄÁÎÏ×Á,3,Ë×.21 470092.
4465434344. ìÑÛÅÎËÏ óÁ×ÅÌÉÊ áÎÔÏÎÏ×ÉÞ 14/11/1967 ÐÒ.íÏÓËÏ×ÓËÉÊ,108,Ë×.37
441914.
3675651220. ÷ÁÓÉÌÅÎËÏ àÒÉÊ ôÁÒÁÓÏ×ÉÞ 24/10/1965 ÕÌ.ìÅÓÎÁÑ,8,Ë×.23 741832.
9632982451. ìÑÛÅÎËÏ éÒÉÎÁ ÷ÌÁÄÉÍÉÒÏ×ÎÁ 17/01/1971 ÐÒ.íÏÓËÏ×ÓËÉÊ,108,Ë×.37 441914.
8725034455. âÅÌØÞÅÎËÏ îÁÄÅÖÄÁ áÎÔÏÎÏ×ÎÁ 04/05/1970 ÕÌ.ìÅÎÉÎÁ,48,Ë×.2 442931.
7288492531. ëÉÓÌÑÒÓËÉÊ òÕÓÌÁÎ òÏÍÁÎÏ×ÉÞ 20/10/1965 ÐÒ.ôÒÁËÔÏÒÏÓÔÒÏÉÔÅÌÅÊ,133,Ë×.86 300029.
7002878201. ëÏÃÀÂÁ ÷ÉÔÁÌÉÊ áÄÁÍÏ×ÉÞ 05/10/1974 ÕÌ.ëÏÓÍÉÞÅÓËÁÑ,24,Ë×.12 476577.
58 record(s) selected.
------------------------------ Command Entered ------------------------------
SELECT * FROM education ;
-----------------------------------------------------------------------------
LEVEL_ID PROF_ID EMPL_ID WHEN_YEAR
----------- ----------- ------------ ---------
60 1 1947858035. 1983
40 2 1947858035. 1994
40 2 1074305123. 1993
60 1 7832201384. 1994
40 3 243734638. 1999
40 1 4320928880. 1983
40 1 1480018888. 1993
30 5 8926907763. 1969
20 0 1409137333. 1975
20 0 1918439239. 1975
30 1 2625252655. 1991
20 0 2534958724. 2000
70 1 1409137338. 2000
50 1 1078473332. 1985
40 4 1078473332. 1991
60 1 2391388889. 1986
40 1 6739201177. 1994
30 1 8458465673. 1998
20 1 2039521299. 1999
40 1 7132317222. 1989
40 1 2456267797. 1999
30 1 9534582434. 1998
30 1 6747332241. 1999
40 1 2254623007. 1973
20 0 1134718229. 1996
40 1 7529798001. 1998
40 1 437003460. 1974
40 1 800065212. 1994
70 1 8341207763. 1988
40 1 2009140271. 1978
40 1 9052443124. 1995
40 1 7078144561. 1991
40 1 2625798001. 1987
40 1 6738776923. 1999
20 0 5325297899. 1999
40 1 1256858411. 1997
40 1 8214285776. 1998
30 1 278521112. 1997
30 1 9010900005. 1997
30 1 9888100983. 1997
30 1 9872018273. 1997
40 1 9027161000. 1992
40 1 7501827859. 1995
40 1 6729108174. 1999
40 1 3390284007. 1993
40 1 5680933223. 1998
20 0 3803859822. 1999
30 1 1020738337. 1997
20 0 388373382. 1997
40 4 8287520022. 1987
40 4 7262929332. 1993
30 4 1020738337. 1993
20 0 5637811442. 1998
20 0 6189374389. 1998
40 2 4465434344. 1993
40 2 3675651220. 1992
40 2 9632982451. 1996
40 2 8725034455. 1995
40 4 7288492531. 1980
30 2 7288492531. 1987
40 2 7002878201. 1999
61 record(s) selected.
------------------------------ Command Entered ------------------------------
SELECT * FROM staff ;
-----------------------------------------------------------------------------
STAFF_ID JOB_ID DEP_ID RATE
----------- ----------- ----------- --------
101 1 10 1.00
102 2 10 1.00
110 21 10 1.00
103 5 10 0.50
104 8 10 0.50
105 15 10 1.00
106 20 10 1.00
107 19 10 0.50
108 17 10 2.00
109 16 10 1.00
201 10 20 1.00
202 13 20 2.00
301 9 30 1.00
302 11 30 3.00
303 14 30 3.00
304 17 30 0.50
401 12 40 1.00
402 11 40 1.00
403 14 40 3.00
404 16 40 1.00
501 12 50 1.00
502 11 50 4.00
503 14 50 2.00
504 16 50 3.00
601 12 60 1.00
602 11 60 1.00
603 16 60 6.00
701 9 70 1.00
702 11 70 4.00
703 14 70 1.00
704 17 70 0.50
705 16 70 2.00
801 18 80 2.00
802 17 80 3.00
803 16 80 1.00
901 3 90 1.00
902 4 90 2.00
903 6 90 1.00
904 7 90 3.00
905 16 90 1.00
40 record(s) selected.
------------------------------ Command Entered ------------------------------
SELECT * FROM work ;
-----------------------------------------------------------------------------
EMPL_ID STAFF_ID SALARY START_DATE RATE MNG_FLAG
------------ ----------- --------- ---------- ---- --------
1947858035. 101 5700.00 01/01/1990 1.0 -
1074305123. 110 4800.00 01/01/1990 1.0 1
7832201384. 102 4000.00 01/09/1994 1.0 -
243734638. 103 2000.00 01/11/1999 0.5 -
4320928880. 104 1700.00 01/10/1998 0.5 -
1480018888. 105 2000.00 01/10/1998 1.0 -
8926907763. 106 2000.00 01/01/1990 1.0 -
1409137333. 107 2500.00 01/01/1990 0.5 -
1918439239. 108 2000.00 01/01/1990 1.0 -
2625252655. 108 1200.00 01/11/1991 0.5 -
2534958724. 109 1000.00 01/01/2001 0.5 -
1409137338. 301 4500.00 01/10/2000 1.0 1
1078473332. 302 3000.00 01/06/1995 1.0 -
2391388889. 302 3000.00 01/08/1995 1.0 -
6739201177. 303 1500.00 01/07/1997 1.0 -
8458465673. 303 1700.00 01/01/1999 1.0 -
2039521299. 303 1500.00 01/01/2001 0.5 -
2039521299. 304 1000.00 01/06/2001 0.5 -
2625252655. 304 1700.00 01/01/1999 0.5 -
7132317222. 401 2500.00 01/04/1995 1.0 1
2456267797. 402 2200.00 01/04/1999 1.0 -
9534582434. 403 1500.00 01/10/1999 1.0 -
6747332241. 403 1500.00 01/12/1999 1.0 -
2254623007. 403 1500.00 01/01/1990 1.0 -
1134718229. 404 1200.00 01/07/1998 1.0 -
7529798001. 201 2700.00 01/01/2000 1.0 1
7529798001. 202 3000.00 01/01/1990 1.0 -
800065212. 202 2600.00 01/01/1992 1.0 -
8341207763. 501 3500.00 01/01/1990 1.0 1
2009140271. 502 3000.00 01/01/1990 1.0 -
9052443124. 502 2500.00 01/01/1998 1.0 -
7078144561. 502 2000.00 01/01/1995 1.0 -
2625798001. 503 2100.00 01/01/1995 1.0 -
6738776923. 503 2100.00 01/07/1999 1.0 -
5325297899. 504 1000.00 01/07/1999 1.0 -
1256858411. 601 3200.00 01/07/1999 1.0 1
8214285776. 602 2800.00 01/07/1999 1.0 -
278521112. 603 2800.00 01/07/1999 1.0 -
9010900005. 603 2800.00 01/07/1999 1.0 -
9888100983. 603 2800.00 01/07/1999 1.0 -
9872018273. 603 2800.00 01/07/1999 1.0 -
9027161000. 701 4000.00 01/08/1995 1.0 1
7501827859. 702 2800.00 01/08/1995 1.0 -
6729108174. 702 2000.00 01/03/2000 1.0 -
3390284007. 702 2000.00 01/06/1997 1.0 -
5680933223. 703 1800.00 01/10/1999 1.0 -
3803859822. 704 1000.00 01/01/2000 0.5 -
1020738337. 705 1800.00 01/03/1996 1.0 -
388373382. 705 1100.00 01/02/2000 1.0 -
8287520022. 801 2500.00 01/01/1997 1.0 1
7262929332. 801 2500.00 01/01/1997 1.0 -
7262929332. 802 2000.00 01/01/1998 1.0 -
5637811442. 802 1300.00 01/01/1999 1.0 -
3803859822. 802 1000.00 01/01/2000 0.5 -
6189374389. 803 1000.00 01/08/1999 1.0 -
4465434344. 901 2500.00 01/01/1996 1.0 1
3675651220. 902 2000.00 01/01/1995 1.0 -
9632982451. 902 1500.00 01/06/1996 1.0 -
8725034455. 903 2000.00 01/01/1996 1.0 -
7288492531. 903 1800.00 01/03/1999 1.0 -
7002878201. 903 1500.00 01/01/2000 1.0 -
7288492531. 905 1300.00 01/04/1999 1.0 -
62 record(s) selected.
ÄÌÑ Oracle:
SQL> SELECT * FROM edu_level;
LEVEL_ID LEVEL_NAME
--------- --------------------
10 ÎÁÞÁÌØÎÏÅ
20 ÓÒÅÄÎÅÅ
30 ÓÒÅÄÎÅÅ ÓÐÅÃÉÁÌØÎÏÅ
40 ×ÙÓÛÅÅ
50 ÁÓÐÉÒÁÎÔÕÒÁ
60 ËÁÎÄÉÄÁÔ ÎÁÕË
70 ÄÏËÔÏÒ ÎÁÕË
7 rows selected.
SQL> SELECT * FROM edu_profile;
PROF_ID PROF_NAME
--------- --------------------
0 ÏÂÝÅÅ
1 ÔÅÈÎÉÞÅÓËÏÅ
2 ÜËÏÎÏÍÉÞÅÓËÏÅ
3 ÀÒÉÄÉÞÅÓËÏÅ
4 ÇÕÍÁÎÉÔÁÒÎÏÅ
5 ×ÏÅÎÎÏÅ
6 rows selected.
SQL> SELECT * FROM department;
DEP_ID DEP_NAME SALARY_LIMIT
--------- -------------------- ------------
10 ÕÐÒÁ×ÌÅÎÉÅ 28500
20 ÏÔÄÅÌ ôï 10500
30 ÐÒÏÅËÔ á 22000
40 ÌÁÂÏÒÁÔÏÒÉÑ ðëó 16000
50 ÌÁÂÏÒÁÔÏÒÉÑ óõâä 26500
60 ÌÁÂÏÒÁÔÏÒÉÑ íí 18500
70 ÐÒÏÅËÔ óôáôõó 2200
80 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 13000
90 ÆÉÎ.ÏÔÄÅÌ 12500
9 rows selected.
SQL> SELECT TO_CHAR(empl_id), name_last, name_1st, name_2nd, birth, phone
2 FROM employee;
TO_CHAR(EMPL_ID) NAME_LAST NAME_1ST
NAME_2ND BIRTH PHONE
---------------------------------------- -------------------- --------------------
-------------------- ---------- ---------
1134718229 äÕÍÂÁÅ× æÁÒÉÚ
áÂÄÕÌÁÅ×ÉÞ 07/09/1980 334526
7529798001 âÅÓÃÅÎÎÙÊ àÒÉÊ
çÌÅÂÏ×ÉÞ 31/01/1956 491772
437003460 íÁÌÉÎÉÎÁ ó×ÅÔÌÁÎÁ
éÇÏÒÅ×ÎÁ 09/10/1951 655489
800065212 ëÏÌÂÁÓÉÎ óÔÁÎÉÓÌÁ×
íÉÈÁÊÌÏ×ÉÞ 13/08/1969 132246
8341207763 ÷ÁÌØÔÍÁÎ àÒÉÊ
òÏÌÁÎÄÏ×ÉÞ 09/06/1952 223254
2009140271 ìÑÛÅÎËÏ éÒÉÎÁ
óÅÒÇÅÅ×ÎÁ 27/03/1947 438938
9052443124 ñÎÏ×Á éÒÉÎÁ
TO_CHAR(EMPL_ID) NAME_LAST NAME_1ST
NAME_2ND BIRTH PHONE
---------------------------------------- -------------------- --------------------
-------------------- ---------- ---------
ñËÏ×ÌÅ×ÎÁ 27/02/1970 433887
7078144561 çÁÂÁÊ çÁÌÉÎÁ
éÌØÉÎÉÞÎÁ 23/04/1966 431748
2625798001 úÁÈÁÒÏ× ïÌÅÇ
áÌÅËÓÅÅ×ÉÞ 02/07/1962 921187
6738776923 ìÑÛÅÎËÏ ìÀÄÍÉÌÁ
óÅÒÇÅÅ×ÎÁ 15/07/1974 721118
5325297899 òÁÓËÏÌØÎÉËÏ× òÏÄÉÏÎ
òÏÍÁÎÏ×ÉÞ 21/11/1983 492187
1256858411 óÉÎÑ×ÓËÉÊ áÌÅËÓÁÎÄÒ
ìÅÏÎÉÄÏ×ÉÞ 03/02/1972 121278
8214285776 áÎÀÔÉÎÁ ôÁÔØÑÎÁ
àÒØÅ×ÎÁ 18/08/1973 455116
TO_CHAR(EMPL_ID) NAME_LAST NAME_1ST
---------------------------------------- -------------------- --------------------
NAME_2ND BIRTH PHONE
-------------------- ---------- ---------
278521112 óÔÕÐÉÎ áÒÎÏÌØÄ
ìÀÄ×ÉÇÏ×ÉÞ 15/07/1976 721448
9010900005 áÛÉÈÍÁÎ ÷ÉËÔÏÒ
÷ÁÓÉÌØÅ×ÉÞ 15/07/1976 221214
9888100983 íÁÔÏÞËÉÎÁ å×ÇÅÎÉÑ
îÉËÏÌÁÅ×ÎÁ 15/07/1976 451677
9872018273 çÁ×ÒÉÌËÏ ÷ÉËÔÏÒÉÑ
é×ÁÎÏ×ÎÁ 15/07/1976 547890
9027161000 âÅÒÍÁÎ ÷ÉËÔÏÒÉÑ
òÕ×ÉÍÏ×ÎÁ 02/02/1967 475883
7501827859 ðÕÓÔÏ×ÅÃËÁÑ åÌÉÚÁ×ÅÔÁ
ðÏÒÆÉÒØÅ×ÎÁ 01/01/1970 475581
TO_CHAR(EMPL_ID) NAME_LAST NAME_1ST
NAME_2ND BIRTH PHONE
---------------------------------------- -------------------- --------------------
-------------------- ---------- ---------
6729108174 ëÕÍÕÛËÉÎ ÷ÉËÔÏÒ
ðÁ×ÌÏ×ÉÞ 12/04/1974 441705
3390284007 íÕÖÉÞËÏ× áÎÁÔÏÌÉÊ
îÉËÏÌÁÅ×ÉÞ 07/09/1966 471491
5680933223 ïÓÔÒÏÕÍÏ× óÅÒÇÅÊ
áÎÁÔÏÌØÅ×ÉÞ 02/08/1973 476571
1947858035 óÁÂÁÄÁÛ ÷ÌÁÄÉÍÉÒ
æÅÄÏÒÏ×ÉÞ 11/05/1949 156785
7832201384 çÁÒÍÁÛ ÷ÁÌÅÒÉÊ
îÉËÏÌÁÅ×ÉÞ 12/07/1967 438967
243734638 óÁËÒÉÓÑÎ íÉËÁÜÌ
÷ÁÒÁÐÅÔÏ×ÉÞ 02/11/1976 431231
4320928880 çÕÓÅ×Á îÁÔÁÌØÑ
TO_CHAR(EMPL_ID) NAME_LAST NAME_1ST
NAME_2ND BIRTH PHONE
---------------------------------------- -------------------- --------------------
-------------------- ---------- ---------
îÉËÏÌÁÅ×ÎÁ 21/07/1960 334257
1480018888 äÏÒÏÖËÉÎ áÌÅËÓÁÎÄÒ
íÉÈÁÊÌÏ×ÉÞ 11/06/1966 334256
8926907763 òÅÄËÌÉÆ ÷ÉËÔÏÒ
ëÏÎÓÔÁÎÔÉÎÏ×ÉÞ 19/10/1948 124244
1409137333 ëÏÓÔÉÎ ÷ÁcÉÌÉÊ
ëÏÎÓÔÁÎÔÉÎÏ×ÉÞ 22/07/1959 754667
1918439239 çÅÎÄÅÌØÍÁÎ óÏÆÉÑ
íÁÒËÏ×ÎÁ 31/03/1958 321134
2625252655 ðÏÇÏÒÅÌÏ×Á ïÌØÇÁ
é×ÁÎÏ×ÎÁ 02/07/1982 645911
2534958724 âÕÚÏ×ÓËÉÊ óÔÅÐÁÎ
é×ÁÎÏ×ÉÞ 13/04/1984 436577
TO_CHAR(EMPL_ID) NAME_LAST NAME_1ST
NAME_2ND BIRTH PHONE
---------------------------------------- -------------------- --------------------
-------------------- ---------- ---------
1409137338 ëÒÑË×ÉÎ ÷ÌÁÄÉÍÉÒ
÷ÌÁÄÉÍÉÒÏ×ÉÞ 29/11/1959 754447
1078473332 ðÕÛËÏ× îÉËÏÌÁÊ
é×ÁÎÏ×ÉÞ 04/02/1961 435657
2391388889 íÅÌØÎÉË áÌÅËÓÁÎÄÒ
îÉËÏÌÁÅ×ÉÞ 12/05/1961 132245
6739201177 ëÏÔÅÊËÉÎ óÅÒÇÅÊ
òÅÎÁÔÏ×ÉÞ 11/03/1977 329686
8458465673 ëÁÒÁ×ÁÅ×Á äÁÒØÑ
âÏÒÉÓÏ×ÎÁ 21/06/1978 150876
2039521299 íÙÛËÉÎ éÌØÑ
áÎÄÒÅÅ×ÉÞ 02/02/1983 354578
TO_CHAR(EMPL_ID) NAME_LAST NAME_1ST
NAME_2ND BIRTH PHONE
---------------------------------------- -------------------- --------------------
-------------------- ---------- ---------
7132317222 íÁÌÅÅ×Á ÷ÉËÔÏÒÉÑ
óÅÒÇÅÅ×ÎÁ 02/11/1964 112342
2456267797 çÒÖÉÍÁÊÌÏ ÷ÁÌÅÎÔÉÎ
áÌÅËÓÁÎÄÒÏ×ÉÞ 09/06/1976 435347
9534582434 âÏÓÉÎ íÁÔ×ÅÊ
íÉÈÁÊÌÏ×ÉÞ 13/07/1979 475678
6747332241 äÁ×ÙÄÅÎËÏ ìÁÒÉÓÁ
áÎÄÒÅÅ×ÎÁ 19/11/1980 651221
2254623007 ëÏÂÅÌÅ×Á ìÁÒÉÓÁ
÷ÉËÔÏÒÏ×ÎÁ 12/12/1945 321131
3803859822 ðÁÒÛÉÎÁ îÁÔÁÌØÑ
îÉËÏÌÁÅ×ÎÁ 12/10/1983 473894
TO_CHAR(EMPL_ID) NAME_LAST NAME_1ST
NAME_2ND BIRTH PHONE
---------------------------------------- -------------------- --------------------
-------------------- ---------- ---------
1020738337 íÉÝÅÎËÏ íÉÈÁÉÌ
îÉËÏÌÁÅ×ÉÞ 07/09/1977 332455
388373382 ëÒÁÓÎÏ×Á áÎÎÁ
óÔÅÐÁÎÏ×ÎÁ 30/03/1981 476677
8287520022 ëÏÇÁÎ ó×ÅÔÌÁÎÁ
íÁÒËÏ×ÎÁ 07/09/1962 476570
7262929332 óÔÅÐÁÎÏ×Á áÌÅ×ÔÉÎÁ
óÔÅÐÁÎÏ×ÎÁ 07/09/1969 471031
9001840871 ëÏÛËÉÎÁ îÉÎÁ
÷ÉÌÅÎÏ×ÎÁ 17/09/1975 471253
5637811442 ëÏÒÏÓÔÙÌÅ×Á áÎÎÁ
ïÌÅÇÏ×ÎÁ 07/09/1966 496493
6189374389 óÔÁÒÏ×ÏÊÔÏ× îÉËÏÌÁÊ
âÏÒÉÓÏ×ÉÞ 07/09/1981 470092
TO_CHAR(EMPL_ID) NAME_LAST NAME_1ST
NAME_2ND BIRTH PHONE
---------------------------------------- -------------------- --------------------
-------------------- ---------- ---------
4465434344 ìÑÛÅÎËÏ óÁ×ÅÌÉÊ
áÎÔÏÎÏ×ÉÞ 14/11/1967 441914
3675651220 ÷ÁÓÉÌÅÎËÏ àÒÉÊ
ôÁÒÁÓÏ×ÉÞ 24/10/1965 741832
9632982451 ìÑÛÅÎËÏ éÒÉÎÁ
÷ÌÁÄÉÍÉÒÏ×ÎÁ 17/01/1971 441914
8725034455 âÅÌØÞÅÎËÏ îÁÄÅÖÄÁ
áÎÔÏÎÏ×ÎÁ 04/05/1970 442931
7288492531 ëÉÓÌÑÒÓËÉÊ òÕÓÌÁÎ
òÏÍÁÎÏ×ÉÞ 20/10/1965 300029
7002878201 ëÏÃÀÂÁ ÷ÉÔÁÌÉÊ
áÄÁÍÏ×ÉÞ 05/10/1974 476577
TO_CHAR(EMPL_ID) NAME_LAST NAME_1ST
NAME_2ND BIRTH PHONE
---------------------------------------- -------------------- --------------------
-------------------- ---------- ---------
1074305123 çÏÒÉÄÚÅ á×ÁÓ
çÏÇÉÅ×ÉÞ 10/01/1954 476589
58 rows selected.
ïÂÒÁÔÉÔÅ ×ÎÉÍÁÎÉÅ ÎÁ ÔÏ, ÞÔÏ ÐÒÉ ×ÙÂÏÒËÅ ÉÚ ÚÎÁÞÅÎÉÑ ÓÔÏÌÂÃÁ empl_id ÚÄÅÓØ É ÄÁÌÅÅ ÍÙ ÚÁÄÁÅÍ ÐÒÅÏÂÒÁÚÏ×ÁÎÉÅ ÚÎÁÞÅÎÉÑ ÓÔÏÌÂÃÁ empl_id × ÓÉÍ×ÏÌØÎÏÅ ÐÒÅÄÓÔÁ×ÌÅÎÉÅ. ðÏÓËÏÌØËÕ ÉÄÅÎÔÉÆÉËÁÃÉÏÎÎÙÊ ËÏÄ - 10-ÚÎÁÞÎÏÅ ÄÅÓÑÔÉÞÎÏÅ ÞÉÓÌÏ, ÅÇÏ ÚÎÁÞÅÎÉÅ ÍÏÖÅÔ ÐÒÅ×ÙÛÁÔØ ÍÁËÓÉÍÁÌØÎÏ ÄÏÐÕÓÔÉÍÏÅ ÚÎÁÞÅÎÉÅ ÄÌÑ INTEGER. ÷ ÜÔÏÍ ÓÌÕÞÁÅ ÚÎÁÞÅÎÉÅ ÂÕÄÅÔ ÐÏ ÕÍÏÌÞÁÎÉÀ ×Ù×ÏÄÉÔØÓÑ × E-ÆÏÒÍÅ, ÎÁÐÒÉÍÅÒ, ÚÎÁÞÅÎÉÅ 7288467531 ÂÅÚ ÐÒÅÏÂÒÁÚÏ×ÁÎÉÑ ×Ù×ÅÌÏÓØ ÂÙ ËÁË 7.288E+09.
SQL> SELECT level_id, TO_CHAR(empl_id), prof_id, when_year FROM education
LEVEL_ID TO_CHAR(EMPL_ID) PROF_ID WHEN_YEAR
--------- ---------------------------------------- --------- ---------
60 1947858035 1 1983
40 1947858035 2 1994
40 1074305123 2 1993
60 7832201384 1 1994
40 243734638 3 1999
40 4320928880 1 1983
40 1480018888 1 1993
30 8926907763 5 1969
20 1409137333 0 1975
20 1918439239 0 1975
30 2625252655 1 1991
20 2534958724 0 2000
70 1409137338 1 2000
50 1078473332 1 1985
40 1078473332 4 1991
60 2391388889 1 1986
40 6739201177 1 1994
30 8458465673 1 1998
20 2039521299 1 1999
40 7132317222 1 1989
40 2456267797 1 1999
LEVEL_ID TO_CHAR(EMPL_ID) PROF_ID WHEN_YEAR
--------- ---------------------------------------- --------- ---------
30 9534582434 1 1998
30 6747332241 1 1999
40 2254623007 1 1973
20 1134718229 0 1996
40 7529798001 1 1998
40 437003460 1 1974
40 800065212 1 1994
70 8341207763 1 1988
40 2009140271 1 1978
40 9052443124 1 1995
40 7078144561 1 1991
40 2625798001 1 1987
40 6738776923 1 1999
20 5325297899 0 1999
40 1256858411 1 1997
40 8214285776 1 1998
30 278521112 1 1997
30 9010900005 1 1997
30 9888100983 1 1997
30 9872018273 1 1997
40 9027161000 1 1992
LEVEL_ID TO_CHAR(EMPL_ID) PROF_ID WHEN_YEAR
--------- ---------------------------------------- --------- ---------
40 7501827859 1 1995
40 6729108174 1 1999
40 3390284007 1 1993
40 5680933223 1 1998
20 3803859822 0 1999
30 1020738337 1 1997
20 388373382 0 1997
40 8287520022 4 1987
40 7262929332 4 1993
30 1020738337 4 1993
20 5637811442 0 1998
20 6189374389 0 1998
40 4465434344 2 1993
40 3675651220 2 1992
40 9632982451 2 1996
40 8725034455 2 1995
40 7288492531 4 1980
30 7288492531 2 1987
40 7002878201 2 1999
61 rows selected.
SQL> SELECT * FROM staff;
STAFF_ID JOB_ID DEP_ID RATE
--------- --------- --------- ---------
101 1 10 1
102 2 10 1
110 21 10 1
103 5 10 .5
104 8 10 .5
105 15 10 1
106 20 10 1
107 19 10 .5
108 17 10 2
109 16 10 1
201 10 20 1
202 13 20 2
301 9 30 1
302 11 30 3
303 14 30 3
304 17 30 .5
401 12 40 1
402 11 40 1
403 14 40 3
404 16 40 1
501 12 50 1
STAFF_ID JOB_ID DEP_ID RATE
--------- --------- --------- ---------
502 11 50 4
503 14 50 2
504 16 50 3
601 12 60 1
602 11 60 1
603 16 60 6
701 9 70 1
702 11 70 4
703 14 70 1
704 17 70 .5
705 16 70 2
801 18 80 2
802 17 80 3
803 16 80 1
901 3 90 1
902 4 90 2
903 6 90 1
904 7 90 3
905 16 90 1
40 rows selected.
SQL> SELECT TO_CHAR(empl_id), staff_id, salary, start_date, rate, mng_flag
2 FROM work;
TO_CHAR(EMPL_ID) STAFF_ID SALARY START_DATE RATE M
---------------------------------------- --------- --------- ---------- --------- -
1947858035 101 5700 01/01/1990 1
1074305123 110 4800 01/01/1990 1 1
7832201384 102 4000 01/09/1994 1
243734638 103 2000 01/11/1999 .5
4320928880 104 1700 01/10/1998 .5
1480018888 105 2000 01/10/1998 1
8926907763 106 2000 01/01/1990 1
1409137333 107 2500 01/01/1990 .5
1918439239 108 2000 01/01/1990 1
2625252655 108 1200 01/11/1991 .5
2534958724 109 1000 01/01/2001 .5
1409137338 301 4500 01/10/2000 1 1
1078473332 302 3000 01/06/1995 1
2391388889 302 3000 01/08/1995 1
6739201177 303 1500 01/07/1997 1
8458465673 303 1700 01/01/1999 1
2039521299 303 1500 01/01/2001 .5
2039521299 304 1000 01/06/2001 .5
2625252655 304 1700 01/01/1999 .5
7132317222 401 2500 01/04/1995 1 1
2456267797 402 2200 01/04/1999 1
TO_CHAR(EMPL_ID) STAFF_ID SALARY START_DATE RATE M
---------------------------------------- --------- --------- ---------- --------- -
9534582434 403 1500 01/10/1999 1
6747332241 403 1500 01/12/1999 1
2254623007 403 1500 01/01/1990 1
1134718229 404 1200 01/07/1998 1
7529798001 201 2700 01/01/2000 1 1
7529798001 202 3000 01/01/1990 1
800065212 202 2600 01/01/1992 1
8341207763 501 3500 01/01/1990 1 1
2009140271 502 3000 01/01/1990 1
9052443124 502 2500 01/01/1998 1
7078144561 502 2000 01/01/1995 1
2625798001 503 2100 01/01/1995 1
6738776923 503 2100 01/07/1999 1
5325297899 504 1000 01/07/1999 1
1256858411 601 3200 01/07/1999 1 1
8214285776 602 2800 01/07/1999 1
278521112 603 2800 01/07/1999 1
9010900005 603 2800 01/07/1999 1
9888100983 603 2800 01/07/1999 1
9872018273 603 2800 01/07/1999 1
9027161000 701 4000 01/08/1995 1 1
TO_CHAR(EMPL_ID) STAFF_ID SALARY START_DATE RATE M
---------------------------------------- --------- --------- ---------- --------- -
7501827859 702 2800 01/08/1995 1
6729108174 702 2000 01/03/2000 1
3390284007 702 2000 01/06/1997 1
5680933223 703 1800 01/10/1999 1
3803859822 704 1000 01/01/2000 .5
1020738337 705 1800 01/03/1996 1
388373382 705 1100 01/02/2000 1
8287520022 801 2500 01/01/1997 1 1
7262929332 801 2500 01/01/1997 1
7262929332 802 2000 01/01/1998 1
5637811442 802 1300 01/01/1999 1
3803859822 802 1000 01/01/2000 .5
6189374389 803 1000 01/08/1999 1
4465434344 901 2500 01/01/1996 1 1
3675651220 902 2000 01/01/1995 1
9632982451 902 1500 01/06/1996 1
8725034455 903 2000 01/01/1996 1
7288492531 903 1800 01/03/1999 1
7002878201 903 1500 01/01/2000 1
7288492531 905 1300 01/04/1999 1
62 rows selected.
ïÂÒÁÔÉÔÅ ×ÎÉÍÁÎÉÅ: ×Ï ×ÓÅÈ ×ÙÛÅÐÒÉ×ÅÄÅÎÎÙÈ ×ÙÂÏÒËÁÈ ÄÁÔÁ ×Ù×ÏÄÉÔÓÑ × ÆÏÒÍÁÔÅ DD/MM/YYYY, ËÏÔÏÒÙÊ ÍÙ ÚÁÄÁÌÉ × ÏÐÅÒÁÔÏÒÅ ALTER SESSION. åÓÌÉ ÍÙ ÎÁÞÎÅÍ ÎÏ×ÙÊ ÓÅÁÎÓ, × ËÏÔÏÒÏÍ ÎÅ ××ÅÄÅÍ ÏÐÅÒÁÔÏÒ ALTER SESSION, ÔÏ ÄÁÔÁ, ÎÁÐÒÉÍÅÒ, 01/04/1999 ÂÕÄÅÔ ×Ù×ÏÄÉÔØÓÑ ËÁË 01-APR-99.
÷ ÐÒÉ×ÅÄÅÎÎÏÍ ÎÉÖÅ ÓËÒÉÐÔÅ ÉÓÐÏÌØÚÕÅÔÓÑ ÆÏÒÍÁ ËÏÍÍÅÎÔÁÒÉÅ×, ÐÒÉÎÑÔÁÑ × DB2 - Ä×Á ÚÎÁËÁ -- × ÎÁÞÁÌÅ ÓÔÒÏËÉ ËÏÍÍÅÎÔÁÒÉÅ×. ÷ Oracle ÔÅËÓÔ ËÏÍÍÅÎÔÁÒÉÑ ÂÅÒÅÔÓÑ × "ÓËÏÂËÉ" - /* ... */
SELECT name_last, name_1st, name_2nd
FROM employee
WHERE empl_id NOT IN
-- ÓÏÔÒÕÄÎÉËÉ, ËÏÔÏÒÙÅ ÉÍÅÀÔ ×ÙÓÛÅÅ ÔÅÈÎÉÞÅÓËÏÅ ÏÂÒÁÚÏ×ÁÎÉÅ
(SELECT employee.empl_id
FROM employee, education, edu_profile
WHERE employee.empl_id=education.empl_id
AND education.prof_id=edu_profile.prof_id
AND prof_name='ÔÅÈÎÉÞÅÓËÏÅ'
AND level_id >= (SELECT level_id
FROM edu_level
WHERE level_name='×ÙÓÛÅÅ'))
AND empl_id IN
-- ÓÏÔÒÕÄÎÉËÉ, ËÏÔÏÒÙÅ ÚÁÎÉÍÁÀÔ ÉÎÖÅÎÅÒÎÙÅ ÄÏÌÖÎÏÓÔÉ
(SELECT employee.empl_id
FROM employee, work, staff
WHERE employee.empl_id=work.empl_id
AND staff.staff_id=work.staff_id
AND job_id IN
-- ÄÏÌÖÎÏÓÔÉ, ÄÌÑ ËÏÔÏÒÙÈ ÎÕÖÎÏ ×ÙÓÛÅÅ ÔÅÈÎÉÞÅÓËÏÅ ÏÂÒÁÚÏ×ÁÎÉÅ
(SELECT job_id
FROM job, edu_level, edu_profile
WHERE job.level_id=edu_level.level_id
AND job.prof_id=edu_profile.prof_id
AND prof_name='ÔÅÈÎÉÞÅÓËÏÅ'
AND level_name='×ÙÓÛÅÅ'));
òÅÚÕÌØÔÁÔ ×ÙÐÏÌÎÅÎÉÑ ÜÔÏÇÏ ÏÐÅÒÁÔÏÒÁ × DB2:
NAME_LAST NAME_1ST NAME_2ND -------------------- -------------------- -------------------- ëÁÒÁ×ÁÅ×Á äÁÒØÑ âÏÒÉÓÏ×ÎÁ íÙÛËÉÎ éÌØÑ áÎÄÒÅÅ×ÉÞ âÏÓÉÎ íÁÔ×ÅÊ íÉÈÁÊÌÏ×ÉÞ äÁ×ÙÄÅÎËÏ ìÁÒÉÓÁ áÎÄÒÅÅ×ÎÁ 4 record(s) selected.÷ Oracle: NAME_LAST NAME_1ST NAME_2ND -------------------- -------------------- -------------------- íÙÛËÉÎ éÌØÑ áÎÄÒÅÅ×ÉÞ äÁ×ÙÄÅÎËÏ ìÁÒÉÓÁ áÎÄÒÅÅ×ÎÁ ëÁÒÁ×ÁÅ×Á äÁÒØÑ âÏÒÉÓÏ×ÎÁ âÏÓÉÎ íÁÔ×ÅÊ íÉÈÁÊÌÏ×ÉÞ
SELECT dep_name, salary_limit - s_sal
FROM department,
(SELECT dep_id, SUM(salary*work.rate) as s_sal
FROM staff, work
WHERE staff.staff_id=work.staff_id
GROUP BY dep_id) t1
WHERE department.dep_id=t1.dep_id;
òÅÚÕÌØÔÁÔ ×ÙÐÏÌÎÅÎÉÑ ÜÔÏÇÏ ÏÐÅÒÁÔÏÒÁ × DB2:
DEP_NAME 2 -------------------- --------------------------------- ÕÐÒÁ×ÌÅÎÉÅ 3800.000 ÏÔÄÅÌ ôï 2200.000 ÐÒÏÅËÔ á 6200.000 ÌÁÂÏÒÁÔÏÒÉÑ ðëó 5600.000 ÌÁÂÏÒÁÔÏÒÉÑ óõâä 10300.000 ÌÁÂÏÒÁÔÏÒÉÑ íí 1300.000 ÐÒÏÅËÔ óôáôõó -13800.000 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 3200.000 ÆÉÎ.ÏÔÄÅÌ -100.000 9 record(s) selected.
÷ Oracle:
DEP_NAME SALARY_LIMIT-S_SAL -------------------- ------------------ ÕÐÒÁ×ÌÅÎÉÅ 3800 ÏÔÄÅÌ ôï 2200 ÐÒÏÅËÔ á 6200 ÌÁÂÏÒÁÔÏÒÉÑ ðëó 5600 ÌÁÂÏÒÁÔÏÒÉÑ óõâä 10300 ÌÁÂÏÒÁÔÏÒÉÑ íí 1300 ÐÒÏÅËÔ óôáôõó -13800 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 3200 ÆÉÎ.ÏÔÄÅÌ -100 9 rows selected.
SELECT dep_name, job_name, work.rate FROM employee, work, staff, department, job WHERE job.job_id=staff.job_id AND department.dep_id=staff.dep_id AND staff.staff_id=work.staff_id AND work.empl_id=employee.empl_id AND name_last='ëÉÓÌÑÒÓËÉÊ' ORDER BY 3 DESC;
òÅÚÕÌØÔÁÔ ×ÙÐÏÌÎÅÎÉÑ ÜÔÏÇÏ ÏÐÅÒÁÔÏÒÁ × DB2:
DEP_NAME JOB_NAME RATE -------------------- ---------------------------------------- ---- ÆÉÎ.ÏÔÄÅÌ ÓÔ.ÜËÏÎÏÍÉÓÔ 1.0 ÆÉÎ.ÏÔÄÅÌ ÏÐÅÒÁÔÏÒ ü÷í 1.0 2 record(s) selected.
÷ Oracle:
DEP_NAME JOB_NAME RATE -------------------- ---------------------------------------- --------- ÆÉÎ.ÏÔÄÅÌ ÓÔ.ÜËÏÎÏÍÉÓÔ 1 ÆÉÎ.ÏÔÄÅÌ ÏÐÅÒÁÔÏÒ ü÷í 1
SELECT DISTINCT name_last, birth
FROM employee, work, staff, department
WHERE department.dep_id=staff.dep_id
AND staff.staff_id=work.staff_id
AND work.empl_id=employee.empl_id
AND dep_name='ÐÒÏÅËÔ á'
AND birth = (SELECT MAX(birth)
FROM employee, work, staff, department
WHERE department.dep_id=staff.dep_id
AND staff.staff_id=work.staff_id
AND work.empl_id=employee.empl_id
AND dep_name='ÐÒÏÅËÔ á');
üÔÏÔ ×ÁÒÉÁÎÔ ÚÁÐÒÏÓÁ ÍÏÖÅÔ ÂÙÔØ ×ÙÐÏÌÎÅÎ × ÌÀÂÏÊ óõâä. äÌÑ DB2 ×ÏÚÍÏÖÅÎ É ÂÏÌÅÅ ËÏÍÐÁËÔÎÙÊ ×ÁÒÉÁÎÔ Ó ÉÓÐÏÌØÚÏ×ÁÎÉÅÍ ×ÒÅÍÅÎÎÏÇÏ ÐÒÅÄÓÔÁ×ÌÅÎÉÑ:
WITH tmp AS (SELECT name_last, birth
FROM employee, work, staff, department
WHERE department.dep_id=staff.dep_id
AND staff.staff_id=work.staff_id
AND work.empl_id=employee.empl_id
AND dep_name='ÐÒÏÅËÔ á')
SELECT DISTINCT name_last, birth
FROM tmp
WHERE birth = (SELECT MAX(birth) FROM tmp);
òÅÚÕÌØÔÁÔ ×ÙÐÏÌÎÅÎÉÑ ÜÔÏÇÏ ÏÐÅÒÁÔÏÒÁ × DB2:
NAME_LAST BIRTH -------------------- ---------- íÙÛËÉÎ 02/02/1983 1 record(s) selected.
÷ Oracle: NAME_LAST BIRTH -------------------- ---------- íÙÛËÉÎ 02-FEB-83
SELECT dep_name, name_last, phone FROM employee, department, staff, work WHERE department.dep_id=staff.dep_id AND staff.staff_id=work.staff_id AND work.empl_id=employee.empl_id AND mng_flag IS NOT NULL;
òÅÚÕÌØÔÁÔ ×ÙÐÏÌÎÅÎÉÑ ÜÔÏÇÏ ÏÐÅÒÁÔÏÒÁ × DB2:
DEP_NAME NAME_LAST PHONE -------------------- -------------------- --------- ÕÐÒÁ×ÌÅÎÉÅ çÏÒÉÄÚÅ 476589. ÌÁÂÏÒÁÔÏÒÉÑ íí óÉÎÑ×ÓËÉÊ 121278. ÐÒÏÅËÔ á ëÒÑË×ÉÎ 754447. ÆÉÎ.ÏÔÄÅÌ ìÑÛÅÎËÏ 441914. ÌÁÂÏÒÁÔÏÒÉÑ ðëó íÁÌÅÅ×Á 112342. ÏÔÄÅÌ ôï âÅÓÃÅÎÎÙÊ 491772. ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ ëÏÇÁÎ 476570. ÌÁÂÏÒÁÔÏÒÉÑ óõâä ÷ÁÌØÔÍÁÎ 223254. ÐÒÏÅËÔ óôáôõó âÅÒÍÁÎ 475883. 9 record(s) selected.
÷ Oracle:
DEP_NAME NAME_LAST PHONE -------------------- -------------------- --------- ÕÐÒÁ×ÌÅÎÉÅ çÏÒÉÄÚÅ 476589 ÐÒÏÅËÔ á ëÒÑË×ÉÎ 754447 ÌÁÂÏÒÁÔÏÒÉÑ ðëó íÁÌÅÅ×Á 112342 ÏÔÄÅÌ ôï âÅÓÃÅÎÎÙÊ 491772 ÌÁÂÏÒÁÔÏÒÉÑ óõâä ÷ÁÌØÔÍÁÎ 223254 ÌÁÂÏÒÁÔÏÒÉÑ íí óÉÎÑ×ÓËÉÊ 121278 ÐÒÏÅËÔ óôáôõó âÅÒÍÁÎ 475883 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ ëÏÇÁÎ 476570 ÆÉÎ.ÏÔÄÅÌ ìÑÛÅÎËÏ 441914 9 rows selected.
ðÒÉ ÆÏÒÍÕÌÉÒÏ×ÁÎÉÉ ÜÔÏÇÏ ÚÁÐÒÏÓÁ ÍÙ ÐÏÓÔÁÒÁÅÍÓÑ ÆÏÒÍÁÔÉÒÏ×ÁÔØ ×Ù×ÏÄ.
äÌÑ DB2, ×Ï-ÐÅÒ×ÙÈ, ×ÙÐÏÌÎÑÅÔÓÑ ÐÒÅÏÂÒÁÚÏ×ÁÎÉÅ s2 Ë ÔÉÐÕ FLOAT, ÔÁË ËÁË ×ÙÐÏÌÎÅÎÉÅ ÄÅÌÅÎÉÑ Ó ÔÏÞÎÏÓÔØÀ ÄÏ Ä×ÕÈ ÚÎÁËÏ× ÐÏÓÌÅ ÔÏÞËÉ ÐÒÉ×ÅÄÅÔ Ë ÐÏÔÅÒÅ ÔÏÞÎÏÓÔÉ. òÅÚÕÌØÔÁÔ ÄÅÌÅÎÉÑ ÐÒÅÏÂÒÁÚÕÅÔÓÑ Ë ÔÉÐÕ DECIMAL(4,2) .
SELECT dep_name, DECIMAL((FLOAT(s2)/s1*100),4,2)
FROM department,
-- ÓÕÍÍÁ ÛÔÁÔÎÙÈ ÅÄÉÎÉÃ ÐÏ ÏÔÄÅÌÁÍ
(SELECT dep_id, SUM(rate) AS s1
FROM staff
GROUP BY dep_id) t1,
-- ÓÕÍÍÁ ÅÄÉÎÉÃ ÐÏ ÏÔÄÅÌÁÍ, ÄÌÑ ËÏÔÏÒÙÈ ÔÒÅÂÕÅÔÓÑ
-- ÔÏÌØËÏ ÏÂÝÅÅ ÏÂÒÁÚÏ×ÁÎÉÅ
(SELECT dep_id, SUM(rate) AS s2
FROM job, staff, edu_profile
WHERE staff.job_id=job.job_id
AND job.prof_id=edu_profile.prof_id
AND prof_name='ÏÂÝÅÅ'
GROUP BY dep_id) t2
WHERE t1.dep_id=t2.dep_id
AND t1.dep_id=department.dep_id;
òÅÚÕÌØÔÁÔ ×ÙÐÏÌÎÅÎÉÑ ÜÔÏÇÏ ÏÐÅÒÁÔÏÒÁ × DB2:
DEP_NAME 2 -------------------- ------ ÕÐÒÁ×ÌÅÎÉÅ 36.84 ÐÒÏÅËÔ á 6.66 ÐÒÏÅËÔ óôáôõó 5.88 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 50.00 4 record(s) selected.
äÌÑ Oracle:
SELECT dep_name, TO_CHAR(s2/s1*100,'99.99')
FROM department,
/* ÓÕÍÍÁ ÛÔÁÔÎÙÈ ÅÄÉÎÉÃ ÐÏ ÏÔÄÅÌÁÍ */
(SELECT dep_id, SUM(rate) AS s1
FROM staff
GROUP BY dep_id) t1,
/* ÓÕÍÍÁ ÅÄÉÎÉÃ ÐÏ ÏÔÄÅÌÁÍ, ÄÌÑ ËÏÔÏÒÙÈ ÔÒÅÂÕÅÔÓÑ */
/* ÔÏÌØËÏ ÏÂÝÅÅ ÏÂÒÁÚÏ×ÁÎÉÅ */
(SELECT dep_id, SUM(rate) AS s2
FROM job, staff, edu_profile
WHERE staff.job_id=job.job_id
AND job.prof_id=edu_profile.prof_id
AND prof_name='ÏÂÝÅÅ'
GROUP BY dep_id) t2
WHERE t1.dep_id=t2.dep_id
AND t1.dep_id=department.dep_id;
òÅÚÕÌØÔÁÔ ×ÙÐÏÌÎÅÎÉÑ ÜÔÏÇÏ ÏÐÅÒÁÔÏÒÁ × Oracle:
DEP_NAME TO_CHA -------------------- ------ ÕÐÒÁ×ÌÅÎÉÅ 36.84 ÐÒÏÅËÔ á 6.67 ÐÒÏÅËÔ óôáôõó 5.88 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 50.00
äÌÑ DB2:
SELECT
SUBSTR(name_last||' '||name_1st||' '||name_2nd,1,35) AS name,
DECIMAL(s_salary,6,2) AS salary
FROM employee,
(SELECT empl_id, SUM(salary*rate) AS s_salary
FROM work
GROUP BY empl_id) t1
WHERE t1.empl_id=employee.empl_id
ORDER BY 1;
NAME SALARY
----------------------------------- --------
áÎÀÔÉÎÁ ôÁÔØÑÎÁ àÒØÅ×ÎÁ 2800.00
áÛÉÈÍÁÎ ÷ÉËÔÏÒ ÷ÁÓÉÌØÅ×ÉÞ 2800.00
âÅÌØÞÅÎËÏ îÁÄÅÖÄÁ áÎÔÏÎÏ×ÎÁ 2000.00
âÅÒÍÁÎ ÷ÉËÔÏÒÉÑ òÕ×ÉÍÏ×ÎÁ 4000.00
âÅÓÃÅÎÎÙÊ àÒÉÊ çÌÅÂÏ×ÉÞ 5700.00
âÏÓÉÎ íÁÔ×ÅÊ íÉÈÁÊÌÏ×ÉÞ 1500.00
âÕÚÏ×ÓËÉÊ óÔÅÐÁÎ é×ÁÎÏ×ÉÞ 500.00
÷ÁÌØÔÍÁÎ àÒÉÊ òÏÌÁÎÄÏ×ÉÞ 3500.00
÷ÁÓÉÌÅÎËÏ àÒÉÊ ôÁÒÁÓÏ×ÉÞ 2000.00
çÁÂÁÊ çÁÌÉÎÁ éÌØÉÎÉÞÎÁ 2000.00
çÁ×ÒÉÌËÏ ÷ÉËÔÏÒÉÑ é×ÁÎÏ×ÎÁ 2800.00
çÁÒÍÁÛ ÷ÁÌÅÒÉÊ îÉËÏÌÁÅ×ÉÞ 4000.00
çÅÎÄÅÌØÍÁÎ óÏÆÉÑ íÁÒËÏ×ÎÁ 2000.00
çÏÒÉÄÚÅ á×ÁÓ çÏÇÉÅ×ÉÞ 4800.00
çÒÖÉÍÁÊÌÏ ÷ÁÌÅÎÔÉÎ áÌÅËÓÁÎÄÒÏ×ÉÞ 2200.00
çÕÓÅ×Á îÁÔÁÌØÑ îÉËÏÌÁÅ×ÎÁ 850.00
äÁ×ÙÄÅÎËÏ ìÁÒÉÓÁ áÎÄÒÅÅ×ÎÁ 1500.00
äÏÒÏÖËÉÎ áÌÅËÓÁÎÄÒ íÉÈÁÊÌÏ×ÉÞ 2000.00
äÕÍÂÁÅ× æÁÒÉÚ áÂÄÕÌÁÅ×ÉÞ 1200.00
úÁÈÁÒÏ× ïÌÅÇ áÌÅËÓÅÅ×ÉÞ 2100.00
ëÁÒÁ×ÁÅ×Á äÁÒØÑ âÏÒÉÓÏ×ÎÁ 1700.00
ëÉÓÌÑÒÓËÉÊ òÕÓÌÁÎ òÏÍÁÎÏ×ÉÞ 3100.00
ëÏÂÅÌÅ×Á ìÁÒÉÓÁ ÷ÉËÔÏÒÏ×ÎÁ 1500.00
ëÏÇÁÎ ó×ÅÔÌÁÎÁ íÁÒËÏ×ÎÁ 2500.00
ëÏÌÂÁÓÉÎ óÔÁÎÉÓÌÁ× íÉÈÁÊÌÏ×ÉÞ 2600.00
ëÏÒÏÓÔÙÌÅ×Á áÎÎÁ ïÌÅÇÏ×ÎÁ 1300.00
ëÏÓÔÉÎ ÷ÁcÉÌÉÊ ëÏÎÓÔÁÎÔÉÎÏ×ÉÞ 1250.00
ëÏÔÅÊËÉÎ óÅÒÇÅÊ òÅÎÁÔÏ×ÉÞ 1500.00
ëÏÃÀÂÁ ÷ÉÔÁÌÉÊ áÄÁÍÏ×ÉÞ 1500.00
ëÒÁÓÎÏ×Á áÎÎÁ óÔÅÐÁÎÏ×ÎÁ 1100.00
ëÒÑË×ÉÎ ÷ÌÁÄÉÍÉÒ ÷ÌÁÄÉÍÉÒÏ×ÉÞ 4500.00
ëÕÍÕÛËÉÎ ÷ÉËÔÏÒ ðÁ×ÌÏ×ÉÞ 2000.00
ìÑÛÅÎËÏ éÒÉÎÁ ÷ÌÁÄÉÍÉÒÏ×ÎÁ 1500.00
ìÑÛÅÎËÏ éÒÉÎÁ óÅÒÇÅÅ×ÎÁ 3000.00
ìÑÛÅÎËÏ ìÀÄÍÉÌÁ óÅÒÇÅÅ×ÎÁ 2100.00
ìÑÛÅÎËÏ óÁ×ÅÌÉÊ áÎÔÏÎÏ×ÉÞ 2500.00
íÁÌÅÅ×Á ÷ÉËÔÏÒÉÑ óÅÒÇÅÅ×ÎÁ 2500.00
íÁÔÏÞËÉÎÁ å×ÇÅÎÉÑ îÉËÏÌÁÅ×ÎÁ 2800.00
íÅÌØÎÉË áÌÅËÓÁÎÄÒ îÉËÏÌÁÅ×ÉÞ 3000.00
íÉÝÅÎËÏ íÉÈÁÉÌ îÉËÏÌÁÅ×ÉÞ 1800.00
íÕÖÉÞËÏ× áÎÁÔÏÌÉÊ îÉËÏÌÁÅ×ÉÞ 2000.00
íÙÛËÉÎ éÌØÑ áÎÄÒÅÅ×ÉÞ 1250.00
ïÓÔÒÏÕÍÏ× óÅÒÇÅÊ áÎÁÔÏÌØÅ×ÉÞ 1800.00
ðÁÒÛÉÎÁ îÁÔÁÌØÑ îÉËÏÌÁÅ×ÎÁ 1000.00
ðÏÇÏÒÅÌÏ×Á ïÌØÇÁ é×ÁÎÏ×ÎÁ 1450.00
ðÕÓÔÏ×ÅÃËÁÑ åÌÉÚÁ×ÅÔÁ ðÏÒÆÉÒØÅ×ÎÁ 2800.00
ðÕÛËÏ× îÉËÏÌÁÊ é×ÁÎÏ×ÉÞ 3000.00
òÁÓËÏÌØÎÉËÏ× òÏÄÉÏÎ òÏÍÁÎÏ×ÉÞ 1000.00
òÅÄËÌÉÆ ÷ÉËÔÏÒ ëÏÎÓÔÁÎÔÉÎÏ×ÉÞ 2000.00
óÁÂÁÄÁÛ ÷ÌÁÄÉÍÉÒ æÅÄÏÒÏ×ÉÞ 5700.00
óÁËÒÉÓÑÎ íÉËÁÜÌ ÷ÁÒÁÐÅÔÏ×ÉÞ 1000.00
óÉÎÑ×ÓËÉÊ áÌÅËÓÁÎÄÒ ìÅÏÎÉÄÏ×ÉÞ 3200.00
óÔÁÒÏ×ÏÊÔÏ× îÉËÏÌÁÊ âÏÒÉÓÏ×ÉÞ 1000.00
óÔÅÐÁÎÏ×Á áÌÅ×ÔÉÎÁ óÔÅÐÁÎÏ×ÎÁ 4500.00
óÔÕÐÉÎ áÒÎÏÌØÄ ìÀÄ×ÉÇÏ×ÉÞ 2800.00
ñÎÏ×Á éÒÉÎÁ ñËÏ×ÌÅ×ÎÁ 2500.00
56 record(s) selected.
äÌÑ Oracle:
SELECT
SUBSTR(name_last||' '||name_1st||' '||name_2nd,1,35) AS name,
TO_CHAR(s_salary,'9999.99') AS salary
FROM employee,
(SELECT empl_id, SUM(salary*rate) AS s_salary
FROM work
GROUP BY empl_id) t1
WHERE t1.empl_id=employee.empl_id
ORDER BY 1;
NAME SALARY
----------------------------------- --------
áÎÀÔÉÎÁ ôÁÔØÑÎÁ àÒØÅ×ÎÁ 2800.00
áÛÉÈÍÁÎ ÷ÉËÔÏÒ ÷ÁÓÉÌØÅ×ÉÞ 2800.00
âÅÌØÞÅÎËÏ îÁÄÅÖÄÁ áÎÔÏÎÏ×ÎÁ 2000.00
âÅÒÍÁÎ ÷ÉËÔÏÒÉÑ òÕ×ÉÍÏ×ÎÁ 4000.00
âÅÓÃÅÎÎÙÊ àÒÉÊ çÌÅÂÏ×ÉÞ 5700.00
âÏÓÉÎ íÁÔ×ÅÊ íÉÈÁÊÌÏ×ÉÞ 1500.00
âÕÚÏ×ÓËÉÊ óÔÅÐÁÎ é×ÁÎÏ×ÉÞ 500.00
÷ÁÌØÔÍÁÎ àÒÉÊ òÏÌÁÎÄÏ×ÉÞ 3500.00
÷ÁÓÉÌÅÎËÏ àÒÉÊ ôÁÒÁÓÏ×ÉÞ 2000.00
çÁÂÁÊ çÁÌÉÎÁ éÌØÉÎÉÞÎÁ 2000.00
çÁ×ÒÉÌËÏ ÷ÉËÔÏÒÉÑ é×ÁÎÏ×ÎÁ 2800.00
çÁÒÍÁÛ ÷ÁÌÅÒÉÊ îÉËÏÌÁÅ×ÉÞ 4000.00
çÅÎÄÅÌØÍÁÎ óÏÆÉÑ íÁÒËÏ×ÎÁ 2000.00
çÏÒÉÄÚÅ á×ÁÓ çÏÇÉÅ×ÉÞ 4800.00
çÒÖÉÍÁÊÌÏ ÷ÁÌÅÎÔÉÎ áÌÅËÓÁÎÄÒÏ×ÉÞ 2200.00
çÕÓÅ×Á îÁÔÁÌØÑ îÉËÏÌÁÅ×ÎÁ 850.00
äÁ×ÙÄÅÎËÏ ìÁÒÉÓÁ áÎÄÒÅÅ×ÎÁ 1500.00
äÏÒÏÖËÉÎ áÌÅËÓÁÎÄÒ íÉÈÁÊÌÏ×ÉÞ 2000.00
äÕÍÂÁÅ× æÁÒÉÚ áÂÄÕÌÁÅ×ÉÞ 1200.00
úÁÈÁÒÏ× ïÌÅÇ áÌÅËÓÅÅ×ÉÞ 2100.00
ëÁÒÁ×ÁÅ×Á äÁÒØÑ âÏÒÉÓÏ×ÎÁ 1700.00
NAME SALARY
----------------------------------- --------
ëÉÓÌÑÒÓËÉÊ òÕÓÌÁÎ òÏÍÁÎÏ×ÉÞ 3100.00
ëÏÂÅÌÅ×Á ìÁÒÉÓÁ ÷ÉËÔÏÒÏ×ÎÁ 1500.00
ëÏÇÁÎ ó×ÅÔÌÁÎÁ íÁÒËÏ×ÎÁ 2500.00
ëÏÌÂÁÓÉÎ óÔÁÎÉÓÌÁ× íÉÈÁÊÌÏ×ÉÞ 2600.00
ëÏÒÏÓÔÙÌÅ×Á áÎÎÁ ïÌÅÇÏ×ÎÁ 1300.00
ëÏÓÔÉÎ ÷ÁcÉÌÉÊ ëÏÎÓÔÁÎÔÉÎÏ×ÉÞ 1250.00
ëÏÔÅÊËÉÎ óÅÒÇÅÊ òÅÎÁÔÏ×ÉÞ 1500.00
ëÏÃÀÂÁ ÷ÉÔÁÌÉÊ áÄÁÍÏ×ÉÞ 1500.00
ëÒÁÓÎÏ×Á áÎÎÁ óÔÅÐÁÎÏ×ÎÁ 1100.00
ëÒÑË×ÉÎ ÷ÌÁÄÉÍÉÒ ÷ÌÁÄÉÍÉÒÏ×ÉÞ 4500.00
ëÕÍÕÛËÉÎ ÷ÉËÔÏÒ ðÁ×ÌÏ×ÉÞ 2000.00
ìÑÛÅÎËÏ éÒÉÎÁ ÷ÌÁÄÉÍÉÒÏ×ÎÁ 1500.00
ìÑÛÅÎËÏ éÒÉÎÁ óÅÒÇÅÅ×ÎÁ 3000.00
ìÑÛÅÎËÏ ìÀÄÍÉÌÁ óÅÒÇÅÅ×ÎÁ 2100.00
ìÑÛÅÎËÏ óÁ×ÅÌÉÊ áÎÔÏÎÏ×ÉÞ 2500.00
íÁÌÅÅ×Á ÷ÉËÔÏÒÉÑ óÅÒÇÅÅ×ÎÁ 2500.00
íÁÔÏÞËÉÎÁ å×ÇÅÎÉÑ îÉËÏÌÁÅ×ÎÁ 2800.00
íÅÌØÎÉË áÌÅËÓÁÎÄÒ îÉËÏÌÁÅ×ÉÞ 3000.00
íÉÝÅÎËÏ íÉÈÁÉÌ îÉËÏÌÁÅ×ÉÞ 1800.00
íÕÖÉÞËÏ× áÎÁÔÏÌÉÊ îÉËÏÌÁÅ×ÉÞ 2000.00
íÙÛËÉÎ éÌØÑ áÎÄÒÅÅ×ÉÞ 1250.00
NAME SALARY
----------------------------------- --------
ïÓÔÒÏÕÍÏ× óÅÒÇÅÊ áÎÁÔÏÌØÅ×ÉÞ 1800.00
ðÁÒÛÉÎÁ îÁÔÁÌØÑ îÉËÏÌÁÅ×ÎÁ 1000.00
ðÏÇÏÒÅÌÏ×Á ïÌØÇÁ é×ÁÎÏ×ÎÁ 1450.00
ðÕÓÔÏ×ÅÃËÁÑ åÌÉÚÁ×ÅÔÁ ðÏÒÆÉÒØÅ×ÎÁ 2800.00
ðÕÛËÏ× îÉËÏÌÁÊ é×ÁÎÏ×ÉÞ 3000.00
òÁÓËÏÌØÎÉËÏ× òÏÄÉÏÎ òÏÍÁÎÏ×ÉÞ 1000.00
òÅÄËÌÉÆ ÷ÉËÔÏÒ ëÏÎÓÔÁÎÔÉÎÏ×ÉÞ 2000.00
óÁÂÁÄÁÛ ÷ÌÁÄÉÍÉÒ æÅÄÏÒÏ×ÉÞ 5700.00
óÁËÒÉÓÑÎ íÉËÁÜÌ ÷ÁÒÁÐÅÔÏ×ÉÞ 1000.00
óÉÎÑ×ÓËÉÊ áÌÅËÓÁÎÄÒ ìÅÏÎÉÄÏ×ÉÞ 3200.00
óÔÁÒÏ×ÏÊÔÏ× îÉËÏÌÁÊ âÏÒÉÓÏ×ÉÞ 1000.00
óÔÅÐÁÎÏ×Á áÌÅ×ÔÉÎÁ óÔÅÐÁÎÏ×ÎÁ 4500.00
óÔÕÐÉÎ áÒÎÏÌØÄ ìÀÄ×ÉÇÏ×ÉÞ 2800.00
ñÎÏ×Á éÒÉÎÁ ñËÏ×ÌÅ×ÎÁ 2500.00
56 rows selected.
DELETE FROM staff WHERE staff_id=110;
òÅÚÕÌØÔÁÔ ×ÙÐÏÌÎÅÎÉÑ ÜÔÏÇÏ ÏÐÅÒÁÔÏÒÁ ÎÕÖÎÏ ÐÒÏ×ÅÒÉÔØ ÏÐÅÒÁÔÏÒÏÍ:
SELECT * FROM staff WHERE staff_id=110;É, ÐÏÓËÏÌØËÕ × ÏÐÉÓÁÎÉÉ ×ÎÅÛÎÅÇÏ ËÌÀÞÁ ÔÁÂÌÉÃÙ work ÕÄÁÌÅÎÉÑ ËÁÓËÁÄÉÒÕÀÔÓÑ:
SELECT * FROM work WHERE staff_id=110;
÷ÏÔ ÒÅÚÕÌØÔÁÔÙ, ÐÏÌÕÞÅÎÎÙÅ × DB2:
DELETE FROM staff WHERE staff_id=110; DB20000I The SQL command completed successfully. SELECT * FROM staff WHERE staff_id=110; STAFF_ID JOB_ID DEP_ID RATE ----------- ----------- ----------- -------- 0 record(s) selected. SELECT * FROM work WHERE staff_id=110; EMPL_ID STAFF_ID SALARY START_DATE END_DATE RATE MNG_FLAG ------------ ----------- --------- ---------- ---------- ---- -------- 0 record(s) selected.
÷ÏÔ ÒÅÚÕÌØÔÁÔÙ, ÐÏÌÕÞÅÎÎÙÅ × Oracle:
SQL> DELETE FROM staff WHERE staff_id=110; 1 row deleted. SQL> SELECT * FROM staff WHERE staff_id=110; no rows selected SQL> SELECT * FROM work WHERE staff_id=110; no rows selected
ðÏÓÌÅ ÜÔÏÇÏ ×ÏÓÓÔÁÎÏ×ÉÍ ÚÎÁÞÅÎÉÑ × ÂÁÚÅ ÄÁÎÎÙÈ, ×ÙÐÏÌÎÉ× ÏÐÅÒÁÔÏÒÙ:
INSERT INTO staff VALUES (110, 21, 10, 1); INSERT INTO work (empl_id, staff_id, salary, start_date, rate, mng_flag) VALUES (1074305123, 110, 4800, '01/01/1990', 1, '1');
DELETE FROM employee WHERE empl_id=1074305123;
òÅÚÕÌØÔÁÔ ×ÙÐÏÌÎÅÎÉÑ ÜÔÏÇÏ ÏÐÅÒÁÔÏÒÁ ÎÕÖÎÏ ÐÒÏ×ÅÒÉÔØ ÏÐÅÒÁÔÏÒÁÍÉ:
SELECT name_last FROM employee WHERE empl_id=1074305123; SELECT name_last FROM employee WHERE empl_id=1074305123; SELECT * FROM work WHERE empl_id=1074305123;
÷ÏÔ ÒÅÚÕÌØÔÁÔÙ, ÐÏÌÕÞÅÎÎÙÅ × DB2:
DELETE FROM employee WHERE empl_id=1074305123; DB20000I The SQL command completed successfully. SELECT name_last FROM employee WHERE empl_id=1074305123; NAME_LAST -------------------- 0 record(s) selected. SELECT * FROM work WHERE empl_id=1074305123; EMPL_ID STAFF_ID SALARY START_DATE END_DATE RATE MNG_FLAG ------------ ----------- --------- ---------- ---------- ---- -------- 0 record(s) selected. SELECT * FROM education WHERE empl_id=1074305123;
÷ÏÔ ÒÅÚÕÌØÔÁÔÙ, ÐÏÌÕÞÅÎÎÙÅ × Oracle:
SQL> DELETE FROM employee WHERE empl_id=1074305123; 1 row deleted. SQL> SELECT name_last FROM employee WHERE empl_id=1074305123; no rows selected SQL> SELECT name_last FROM employee WHERE empl_id=1074305123; no rows selected SQL> SELECT * FROM work WHERE empl_id=1074305123; no rows selected
äÌÑ ×ÏÓÓÔÁÎÏ×ÌÅÎÉÑ ÄÁÎÎÙÈ ×ÙÐÏÌÎÑÅÔÓÑ:
INSERT INTO employee VALUES (1074305123, 'çÏÒÉÄÚÅ', 'á×ÁÓ', 'çÏÇÉÅ×ÉÞ', '10.01.1954', 'ÕÌ.äÁÎÉÌÅ×ÓËÏÇÏ,19,Ë×.103', 476589); INSERT INTO education VALUES (40, 2, 1074305123, 1993); INSERT INTO work (empl_id, staff_id, salary, start_date, rate, mng_flag) VALUES (1074305123, 110, 4800, '01/01/1990', 1, '1');
äÌÑ DB2:
INSERT INTO education VALUES (40, 2, 4000411289, 1993); DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0530N The insert or update value of the FOREIGN KEY "DEREV.EDUCATION.SQL010717162624080" is not equal to any value of the parent key of the parent table. SQLSTATE=23503 INSERT INTO work (empl_id, staff_id, salary, start_date, rate) VALUES (4000411289, 904, 1000, '01/03/1993', 1); DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0530N The insert or update value of the FOREIGN KEY "DEREV.WORK.SQL010717162625671" is not equal to any value of the parent key of the parent table. SQLSTATE=23503 INSERT INTO employee VALUES (4000411289, 'ëÕËÉÎ','÷ÉËÔÏÒ', 'áÌÅËÓÁÎÄÒÏ×ÉÞ', '21.03.1956', 'ÕÌ.ëÕÌØÔÕÒÙ,10,Ë×.21', 456619); DB20000I The SQL command completed successfully.
óÏÏÂÝÅÎÉÑ Ï ÏÛÉÂËÁÈ × ÐÅÒ×ÙÈ Ä×ÕÈ ÏÐÅÒÁÔÏÒÁÈ ×ÙÚ×ÁÎÙ ÔÅÍ, ÞÔÏ ÍÙ ÐÙÔÁÅÍÓÑ ×ÎÅÓÔÉ ÓÔÒÏËÉ ÓÏ ÓÓÙÌËÁÍÉ ÎÁ empl_id, ËÏÔÏÒÏÇÏ ÎÅÔ × ÔÁÂÌÉÃÅ employee. åÓÌÉ ÍÙ ÐÏÓÌÅ ÕÓÐÅÛÎÏÇÏ ×ÙÐÏÌÎÅÎÉÑ ÐÅÒ×ÏÇÏ ÏÐÅÒÁÔÏÒÁ ×ÎÏ×Ø ×ÙÐÏÌÎÉÍ ÐÅÒ×ÙÅ Ä×Á, ÔÏ ÍÙ ÐÏÌÕÞÉÍ:
INSERT INTO education VALUES (40, 2, 4000411289, 1993); DB20000I The SQL command completed successfully. INSERT INTO work (empl_id, staff_id, salary, start_date, rate) VALUES (4000411289, 904, 1000, '01/03/1993', 1); DB20000I The SQL command completed successfully.
äÌÑ Oracle:
SQL> INSERT INTO education VALUES (40, 2, 4000411289, 1993);
INSERT INTO education VALUES (40, 2, 4000411289, 1993)
*
ERROR at line 1:
ORA-02291: integrity constraint (U1.SYS_C001244) violated - parent key not found
SQL> INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
2 VALUES (4000411289, 904, 1000, '01/03/1993', 1);
INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
*
ERROR at line 1:
ORA-02291: integrity constraint (U1.SYS_C001257) violated - parent key not found
SQL> INSERT INTO employee VALUES (4000411289, 'ëÕËÉÎ','÷ÉËÔÏÒ', 'áÌÅËÓÁÎÄÒÏ×ÉÞ', '21.03.1956', 'ÕÌ.ëÕÌØÔÕÒÙ,10,Ë×.21', 456619);
1 row created.
SQL> INSERT INTO education VALUES (40, 2, 4000411289, 1993);
1 row created.
SQL> INSERT INTO work (empl_id, staff_id, salary, start_date, rate)
2 VALUES (4000411289, 904, 1000, '01/03/1993', 1);
1 row created.
äÌÑ DB2:
UPDATE edu_level SET level_id=41 WHERE level_id=40; DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0531N The parent key in a parent row of relationship "DEREV.JOB.SQL010717162623480" cannot be updated. SQLSTATE=23001
äÌÑ Oracle:
SQL> UPDATE edu_level SET level_id=41 WHERE level_id=40;
UPDATE edu_level SET level_id=41 WHERE level_id=40
*
ERROR at line 1:
ORA-02292: integrity constraint (U1.SYS_C001237) violated - child record found
÷ÙÐÏÌÎÉÔØ ÕËÁÚÁÎÎÏÅ ÉÚÍÅÎÅÎÉÅ ÎÅ ÕÄÁÓÔÓÑ, ÔÁË ËÁË ÏÎÏ ÎÁÒÕÛÁÅÔ ÄÅËÌÁÒÁÔÉ×ÎÙÅ ÏÇÒÁÎÉÞÅÎÉÑ ÃÅÌÏÓÔÎÏÓÔÉ.
äÌÑ DB2:
UPDATE education SET level_id=41 WHERE empl_id=1074305123;
äÌÑ Oracle:
SQL> UPDATE education SET level_id=41 WHERE empl_id=1074305123;
UPDATE education SET level_id=41 WHERE empl_id=1074305123
*
ERROR at line 1:
ORA-02291: integrity constraint (U1.SYS_C001242) violated - parent key not found
÷ÙÐÏÌÎÉÔØ ÕËÁÚÁÎÎÏÅ ÉÚÍÅÎÅÎÉÅ ÎÅ ÕÄÁÓÔÓÑ, ÔÁË ËÁË ÏÎÏ ÎÁÒÕÛÁÅÔ ÄÅËÌÁÒÁÔÉ×ÎÙÅ ÏÇÒÁÎÉÞÅÎÉÑ ÃÅÌÏÓÔÎÏÓÔÉ.
| îÁÚÁÄ | ÷ÐÅÒÅÄ |
| ëÁÔÁÌÏÇ | ïÇÌÁ×ÌÅÎÉÅ | éÎÄÅËÓ ÒÁÚÄÅÌÁ |