| ëÁÔÁÌÏÇ | ïÇÌÁ×ÌÅÎÉÅ | éÎÄÅËÓ ÒÁÚÄÅÌÁ |
| îÁÚÁÄ | ÷ÐÅÒÅÄ |
ôÁÂÌÉÃÙ É ÐÒÅÄÓÔÁ×ÌÅÎÉÑ, ÓÏÚÄÁÎÎÙÅ ÎÁÍÉ × ÐÒÅÄÙÄÕÝÉÈ ÒÁÂÏÔÁÈ, ÂÙÌÉ ÓÏÚÄÁÎÙ × ÓÈÅÍÅ U1, ÉÈ ×ÌÁÄÅÌØÃÅÍ Ñ×ÌÑÅÔÓÑ ÐÏÌØÚÏ×ÁÔÅÌØ U1.
÷ SQL*Plus ÎÁÞÉÎÁÅÔÓÑ ÓÅÁÎÓ ÐÏÌØÚÏ×ÁÔÅÌÑ U1 É ÏÔ ÅÇÏ ÉÍÅÎÉ ×ÙÐÏÌÎÑÀÔÓÑ ÓÌÅÄÕÀÝÉÅ ÏÐÅÒÁÔÏÒÙ:
GRANT INSERT, SELECT, DELETE, UPDATE ON edu_level TO u2; GRANT INSERT, SELECT, DELETE, UPDATE ON edu_profile TO u2; GRANT INSERT, SELECT, DELETE, UPDATE ON department TO u2; GRANT SELECT, UPDATE (prof_id, level_id, experience) ON job TO u2; GRANT SELECT, UPDATE (birth, address, phone) ON employee TO u2; GRANT SELECT ON education TO u2; GRANT SELECT ON staff TO u2; GRANT SELECT ON work TO u2;
÷ Oracle Navigator ÓÏÚÄÁÅÔÓÑ ÐÏÌØÚÏ×ÁÔÅÌØ U2. ðÏÌØÚÏ×ÁÔÅÌÀ U2 ÄÁÅÔÓÑ ÐÒÁ×Ï SELECT ÄÌÑ ÐÒÅÄÓÔÁ×ÌÅÎÉÑ emplview.
÷ SQL*Plus ÎÁÞÉÎÁÅÔÓÑ ÓÅÁÎÓ ÐÏÌØÚÏ×ÁÔÅÌÑ U2 × ÜÔÏÍ ÓÅÁÎÓÅ ×ÙÐÏÌÎÑÀÔÓÑ ×ÓÅ ÓÌÅÄÕÀÝÉÅ ÓËÒÉÐÔÙ
úÁÐÒÏÓÙ, ×ÙÐÏÌÎÑ×ÛÉÅÓÑ × ÒÁÂÏÔÅ 2:
÷ÙÂÒÁÔØ ÓÏÔÒÕÄÎÉËÏ×, ËÏÔÏÒÙÅ ÚÁÎÉÍÁÀÔ ÉÎÖÅÎÅÒÎÙÅ ÄÏÌÖÎÏÓÔÉ, ÎÅ ÉÍÅÑ ×ÙÓÛÅÇÏ ÏÂÒÁÚÏ×ÁÎÉÑ
SQL> SELECT name_last, name_1st, name_2nd 2 FROM u1.employee 3 WHERE empl_id NOT IN 4 (SELECT employee.empl_id 5 FROM u1.employee, u1.education, u1.edu_profile 6 WHERE employee.empl_id= education.empl_id 7 AND education.prof_id= edu_profile.prof_id 8 AND prof_name='ÔÅÈÎÉÞÅÓËÏÅ' 9 AND level_id >= (SELECT level_id 10 FROM u1.edu_level 11 WHERE level_name='×ÙÓÛÅÅ')) 12 AND empl_id IN 13 (SELECT employee.empl_id 14 FROM u1.employee, u1.work, u1.staff 15 WHERE employee.empl_id= work.empl_id 16 AND staff.staff_id=work.staff_id 17 AND job_id IN 18 (SELECT job_id 19 FROM u1.job, u1.edu_level, u1.edu_profile 20 WHERE job.level_id=edu_level.level_id 21 AND job.prof_id=edu_profile.prof_id 22 AND prof_name='ÔÅÈÎÉÞÅÓËÏÅ' 23 AND level_name='×ÙÓÛÅÅ')); NAME_LAST NAME_1ST NAME_2ND -------------------- -------------------- -------------------- íÙÛËÉÎ éÌØÑ áÎÄÒÅÅ×ÉÞ äÁ×ÙÄÅÎËÏ ìÁÒÉÓÁ áÎÄÒÅÅ×ÎÁ ëÁÒÁ×ÁÅ×Á äÁÒØÑ âÏÒÉÓÏ×ÎÁ âÏÓÉÎ íÁÔ×ÅÊ íÉÈÁÊÌÏ×ÉÞ
÷ÙÂÒÁÔØ ÒÅÚÅÒ× ÆÏÎÄÁ ÚÁÒÐÌÁÔÙ ÐÏ ×ÓÅÍ ÐÏÄÒÁÚÄÅÌÅÎÉÑÍ
SQL> SELECT dep_name, salary_limit - s_sal 2 FROM u1.department, 3 (SELECT dep_id, SUM(salary*work.rate) as s_sal 4 FROM u1.staff, u1.work 5 WHERE staff.staff_id=work.staff_id 6 GROUP BY dep_id) t1 7 WHERE department.dep_id=t1.dep_id; DEP_NAME SALARY_LIMIT-S_SAL -------------------- ------------------ ÕÐÒÁ×ÌÅÎÉÅ 3800 ÏÔÄÅÌ ôï 2200 ÐÒÏÅËÔ á 6200 ÌÁÂÏÒÁÔÏÒÉÑ ðëó 5600 ÌÁÂÏÒÁÔÏÒÉÑ óõâä 10300 ÌÁÂÏÒÁÔÏÒÉÑ íí 1300 ÐÒÏÅËÔ óôáôõó -13800 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 3200 ÆÉÎ.ÏÔÄÅÌ -100 9 rows selected.
÷ÙÂÒÁÔØ, × ËÁËÉÈ ÐÏÄÒÁÚÄÅÌÅÎÉÑÈ ÒÁÂÏÔÁÅÔ ëÉÓÌÑÒÓËÉÊ
SQL> SELECT dep_name, job_name, work.rate 2 FROM u1.employee, u1.work, u1.staff, u1.department, u1.job 3 WHERE job.job_id=staff.job_id 4 AND department.dep_id=staff.dep_id 5 AND staff.staff_id=work.staff_id 6 AND work.empl_id=employee.empl_id 7 AND name_last='ëÉÓÌÑÒÓËÉÊ' 8 8 ORDER BY 3 DESC; DEP_NAME JOB_NAME RATE -------------------- ---------------------------------------- --------- ÆÉÎ.ÏÔÄÅÌ ÓÔ.ÜËÏÎÏÍÉÓÔ 1 ÆÉÎ.ÏÔÄÅÌ ÏÐÅÒÁÔÏÒ ü÷í 1
÷ÙÂÒÁÔØ ÓÁÍÏÇÏ ÍÏÌÏÄÏÇÏ ÓÏÔÒÕÄÎÉËÁ × ÐÏÄÒÁÚÄÅÌÅÎÉÉ ðÒÏÅËÔ á
SQL> SELECT DISTINCT name_last, birth 2 FROM u1.employee, u1.work, u1.staff, u1.department 3 WHERE department.dep_id=staff.dep_id 4 AND staff.staff_id=work.staff_id 5 AND work.empl_id=employee.empl_id 6 AND dep_name='ÐÒÏÅËÔ á' 7 AND birth = (SELECT MAX(birth) 8 FROM u1.employee, u1.work, u1.staff, u1.department 9 WHERE department.dep_id=staff.dep_id 10 AND staff.staff_id=work.staff_id 11 AND work.empl_id=employee.empl_id 12 AND dep_name='ÐÒÏÅËÔ á'); NAME_LAST BIRTH -------------------- --------- íÙÛËÉÎ 02-FEB-83
SQL> INSERT INTO u1.edu_level VALUES(100,'ÏÂÒÁÚÏ×ÁÎÉÅ X');
1 row created.
SQL> SELECT * FROM u1.edu_level WHERE level_id=100;
LEVEL_ID LEVEL_NAME
--------- --------------------
100 ÏÂÒÁÚÏ×ÁÎÉÅ X
SQL> UPDATE u1.edu_level SET level_name='ÏÂÒÁÚÏ×ÁÎÉÅ Y' WHERE level_id=100;
1 row updated.
SQL> SELECT * FROM u1.edu_level WHERE level_id=100;
LEVEL_ID LEVEL_NAME
--------- --------------------
100 ÏÂÒÁÚÏ×ÁÎÉÅ Y
SQL> DELETE FROM u1.edu_level WHERE level_id=100;
1 row deleted.
SQL> SELECT * FROM u1.edu_level WHERE level_id=100;
no rows selected
óÌÅÄÕÀÝÉÊ ÐÒÏÔÏËÏÌ ×ÙÐÏÌÎÅÎÉÑ ÐÏËÁÚÙ×ÁÅÔ ÄÏÓÔÕÐ Ë ÔÁÂÌÉÃÅ, ÄÌÑ ËÏÔÏÒÏÊ ÎÁÚÎÁÞÅÎÙ ÐÒÁ×Á SELECT É UPDATE - ÄÌÑ ÎÅËÏÔÏÒÙÈ ÓÔÏÌÂÃÏ×.
SQL> SELECT * FROM u1.job WHERE job_id=1;
JOB_ID JOB_NAME MIN_SALARY MAX_SALARY LEVEL_ID PROF_ID EXPERIENCE
--------- ---------------------------------------- ---------- ---------- --------- --------- ----------
1 ÄÉÒÅËÔÏÒ 5000 6000 40 1 5
SQL> UPDATE u1.job SET max_salary=6000, min_salary=1000 WHERE job_id=1;
UPDATE u1.job SET max_salary=6000, min_salary=1000 WHERE job_id=1
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> SELECT * FROM u1.job WHERE job_id=1;
JOB_ID JOB_NAME MIN_SALARY MAX_SALARY LEVEL_ID PROF_ID EXPERIENCE
--------- ---------------------------------------- ---------- ---------- --------- --------- ----------
1 ÄÉÒÅËÔÏÒ 5000 6000 40 1 5
SQL> UPDATE u1.job SET level_id=50, experience=1 WHERE job_id=1;
1 row updated.
SQL> SELECT * FROM u1.job WHERE job_id=1;
JOB_ID JOB_NAME MIN_SALARY MAX_SALARY LEVEL_ID PROF_ID EXPERIENCE
--------- ---------------------------------------- ---------- ---------- --------- --------- ----------
1 ÄÉÒÅËÔÏÒ 5000 6000 50 1 1
SQL> DELETE FROM u1.job WHERE job_id=1;
DELETE FROM u1.job WHERE job_id=1
*
ERROR at line 1:
ORA-01031: insufficient privileges
óÌÅÄÕÀÝÉÊ ÐÒÏÔÏËÏÌ ×ÙÐÏÌÎÅÎÉÑ ÐÏËÁÚÙ×ÁÅÔ ÄÏÓÔÕÐ Ë ÔÁÂÌÉÃÅ, ÄÌÑ ËÏÔÏÒÏÊ ÎÁÚÎÁÞÅÎÏ ÔÏÌØËÏ ÐÒÁ×Ï SELECT.
SQL> SELECT * FROM u1.work WHERE empl_id=1947858035 AND staff_id=101;
EMPL_ID STAFF_ID SALARY START_DAT RATE M
--------- --------- --------- --------- --------- -
1.948E+09 101 5700 01-JAN-90 1
SQL> UPDATE u1.work SET salary=salary+1000 WHERE empl_id=1947858035 AND staff_id=101;
UPDATE u1.work SET salary=salary+1000 WHERE empl_id=1947858035 AND staff_id=101
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> INSERT INTO u1.work VALUES (1947858035, 303, 4000, '01-MAR-2000', 0.5, '1');
INSERT INTO u1.work VALUES (1947858035, 303, 4000, '01-MAR-2000', 0.5, '1')
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> DELETE FROM u1.work WHERE empl_id=1947858035 AND staff_id=101;
DELETE FROM u1.work WHERE empl_id=1947858035 AND staff_id=101
*
ERROR at line 1:
ORA-01031: insufficient privileges
SQL> SELECT * FROM u1.work WHERE empl_id=1947858035 AND staff_id=101;
EMPL_ID STAFF_ID SALARY START_DAT RATE M
--------- --------- --------- --------- --------- -
1.948E+09 101 5700 01-JAN-90 1
óÌÅÄÕÀÝÉÅ ÐÒÏÔÏËÏÌÙ ÄÅÍÏÎÓÔÒÉÒÕÀÔ ÄÏÓÔÕÐ Ë ÐÒÅÄÓÔÁ×ÌÅÎÉÀ, ÄÌÑ ËÏÔÏÒÏÇÏ ÎÁÚÎÁÞÅÎÏ ÐÒÁ×Ï SELECT. üÔÏ ÚÁÐÒÏÓÙ, ×ÙÐÏÌÎÑ×ÛÉÅÓÑ × ÒÁÂÏÔÅ 4.
÷ÙÂÒÁÔØ ÓÐÉÓÏË ÓÏÔÒÕÄÎÉËÏ×, ÒÁÂÏÔÁÀÝÉÈ × ÐÏÄÒÁÚÄÅÌÅÎÉÉ ðÒÏÅËÔ á × ÐÏÒÑÄËÅ ÕÍÅÎØÛÅÎÉÑ ÉÈ ÚÁÒÐÌÁÔÙ:
SQL> SELECT name_last, SUM(salary*rate) AS salary 2 FROM u1.emplview 3 WHERE dep_name='ÐÒÏÅËÔ á' 4 GROUP BY name_last, name_1st, name_2nd 5 ORDER BY 2 DESC; NAME_LAST SALARY -------------------- --------- ëÒÑË×ÉÎ 4500 íÅÌØÎÉË 3000 ðÕÛËÏ× 3000 ëÁÒÁ×ÁÅ×Á 1700 ëÏÔÅÊËÉÎ 1500 íÙÛËÉÎ 1250 ðÏÇÏÒÅÌÏ×Á 850 7 rows selected.
÷ÙÂÒÁÔØ ÓÐÉÓÏË ÏÔÄÅÌÏ× × ÐÏÒÑÄËÅ ÕÍÅÎØÛÅÎÉÑ ËÏÌÉÞÅÓÔ×Á ÒÅÁÌØÎÏ ÉÓÐÏÌØÚÕÅÍÙÈ ÓÔÁ×ÏË SQL> SELECT dep_name, SUM(rate) AS rate 2 FROM u1.emplview 3 GROUP BY dep_name 4 ORDER BY 2; DEP_NAME RATE -------------------- --------- ÏÔÄÅÌ ôï 3 ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 5.5 ÌÁÂÏÒÁÔÏÒÉÑ íí 6 ÌÁÂÏÒÁÔÏÒÉÑ ðëó 6 ÐÒÏÅËÔ á 6.5 ÌÁÂÏÒÁÔÏÒÉÑ óõâä 7 ÆÉÎ.ÏÔÄÅÌ 7 ÐÒÏÅËÔ óôáôõó 7.5 ÕÐÒÁ×ÌÅÎÉÅ 8.5 9 rows selected.
ôÁÂÌÉÃÙ É ÐÒÅÄÓÔÁ×ÌÅÎÉÑ, ÓÏÚÄÁÎÎÙÅ ÎÁÍÉ × ÐÒÅÄÙÄÕÝÉÈ ÒÁÂÏÔÁÈ, ÂÙÌÉ ÓÏÚÄÁÎÙ × ÓÈÅÍÅ DEREV, ÉÈ ×ÌÁÄÅÌØÃÅÍ Ñ×ÌÑÅÔÓÑ ÐÏÌØÚÏ×ÁÔÅÌØ DEREV.
÷ ÓÒÅÄÅ DB2 Command Center ÓÏÅÄÉÎÑÅÍÓÑ Ó ÂÁÚÏÊ ÄÁÎÎÙÈ DB2EDU, ÁÕÔÅÎÔÉÆÉÃÉÒÕÑÓØ ËÁË DEREV.
÷ÙÐÏÌÎÑÅÍ ÓÌÅÄÕÀÝÉÊ ÓËÒÉÐÔ:
GRANT INSERT, SELECT, DELETE, UPDATE ON edu_level TO stud18; GRANT INSERT, SELECT, DELETE, UPDATE ON edu_profile TO stud18; GRANT INSERT, SELECT, DELETE, UPDATE ON department TO stud18; GRANT SELECT, UPDATE (prof_id, level_id, experience) ON job TO stud18; GRANT SELECT, UPDATE (birth, address, phone) ON employee TO stud18; GRANT SELECT ON education TO stud18; GRANT SELECT ON staff TO stud18; GRANT SELECT ON work TO stud18;
÷ ÓÒÅÄÅ DB2 Control Center ÓÏÅÄÉÎÑÅÍÓÑ Ó ÂÁÚÏÊ ÄÁÎÎÙÈ DB2EDU, ÁÕÔÅÎÔÉÆÉÃÉÒÕÑÓØ ËÁË DEREV. îÁÚÎÁÞÁÅÍ ÐÏÌØÚÏ×ÁÔÅÌÀ STUD18 ÐÒÉ×ÉÌÅÇÉÀ SELECT ÄÌÑ ÐÒÅÄÓÔÁ×ÌÅÎÉÑ emplview. óÇÅÎÅÒÉÒÏ×ÁÎÎÙÊ ÐÒÉ ÜÔÏÍ ÏÐÅÒÁÔÏÒ, ÐÏËÁÚÁÎÎÙÊ × Show SQL:
GRANT SELECT ON DEREV.EMPLVIEW TO USER STUD18;
÷ ÓÒÅÄÅ DB2 Command Center ÓÏÅÄÉÎÑÅÍÓÑ Ó ÂÁÚÏÊ ÄÁÎÎÙÈ DB2EDU, ÁÕÔÅÎÔÉÆÉÃÉÒÕÑÓØ ËÁË STUD18. ÷ÙÐÏÌÎÑÅÍ ÔÅ ÖÅ ÚÁÐÒÏÓÙ, ÞÔÏ É × ÐÅÒ×ÏÊ ÞÁÓÔÉ ÒÁÂÏÔÙ. ïÔÌÉÞÉÅ × ÜÔÏÊ ÞÁÓÔÉ DB2 ÏÔ Oracle ÓÏÓÔÏÉÔ × ÔÏÍ, ÞÔÏ × Oracle ÐÏÌÎÏÅ Ë×ÁÌÉÆÉÃÉÒÏ×ÁÎÎÏÅ ÉÍÑ ÔÁÂÌÉÃÙ (ÐÒÅÄÓÔÁ×ÌÅÎÉÑ), ÎÁÈÏÄÑÝÅÊÓÑ × ÞÕÖÏÊ ÓÈÅÍÅ ÕËÁÚÙ×ÁÅÔÓÑ ÔÏÌØËÏ ×Ï ÆÒÁÚÅ FROM, Á × DB2 - ×Ï ×ÓÅÈ ÓÌÕÞÁÑÈ.
úÁÐÒÏÓÙ, ×ÙÐÏÌÎÑ×ÛÉÅÓÑ × ÒÁÂÏÔÅ 2
------------------------------ Command Entered ------------------------------
SELECT name_last, name_1st, name_2nd
FROM derev.employee
WHERE empl_id NOT IN
(SELECT derev.employee.empl_id
FROM derev.employee, derev.education, derev.edu_profile
WHERE derev.employee.empl_id = derev.education.empl_id
AND derev.education.prof_id = derev.edu_profile.prof_id
AND prof_name='ÔÅÈÎÉÞÅÓËÏÅ'
AND level_id >= (SELECT level_id
FROM derev.edu_level
WHERE level_name='×ÙÓÛÅÅ'))
AND empl_id IN
(SELECT derev.employee.empl_id
FROM derev.employee, derev.work, derev.staff
WHERE derev.employee.empl_id = derev.work.empl_id
AND derev.staff.staff_id = derev.work.staff_id
AND job_id IN
(SELECT job_id
FROM derev.job, derev.edu_level, derev.edu_profile
WHERE derev.job.level_id = derev.edu_level.level_id
AND derev.job.prof_id = derev.edu_profile.prof_id
AND prof_name='ÔÅÈÎÉÞÅÓËÏÅ'
AND level_name='×ÙÓÛÅÅ')) ;
-----------------------------------------------------------------------------
NAME_LAST NAME_1ST NAME_2ND
-------------------- -------------------- --------------------
ëÁÒÁ×ÁÅ×Á äÁÒØÑ âÏÒÉÓÏ×ÎÁ
íÙÛËÉÎ éÌØÑ áÎÄÒÅÅ×ÉÞ
âÏÓÉÎ íÁÔ×ÅÊ íÉÈÁÊÌÏ×ÉÞ
äÁ×ÙÄÅÎËÏ ìÁÒÉÓÁ áÎÄÒÅÅ×ÎÁ
4 record(s) selected.
------------------------------ Command Entered ------------------------------
SELECT dep_name, salary_limit - s_sal
FROM derev.department,
(SELECT dep_id, SUM(salary*derev.work.rate) as s_sal
FROM derev.staff, derev.work
WHERE derev.staff.staff_id = derev.work.staff_id
GROUP BY dep_id) t1
WHERE derev.department.dep_id=t1.dep_id ;
-----------------------------------------------------------------------------
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.
------------------------------ Command Entered ------------------------------
SELECT dep_name, job_name, derev.work.rate
FROM derev.employee, derev.work, derev.staff, derev.department, derev.job
WHERE derev.job.job_id = derev.staff.job_id
AND derev.department.dep_id = derev.staff.dep_id
AND derev.staff.staff_id = derev.work.staff_id
AND derev.work.empl_id = derev.employee.empl_id
AND name_last='ëÉÓÌÑÒÓËÉÊ'
ORDER BY 3 DESC ;
-----------------------------------------------------------------------------
DEP_NAME JOB_NAME RATE
-------------------- ---------------------------------------- ----
ÆÉÎ.ÏÔÄÅÌ ÓÔ.ÜËÏÎÏÍÉÓÔ 1.0
ÆÉÎ.ÏÔÄÅÌ ÏÐÅÒÁÔÏÒ ü÷í 1.0
2 record(s) selected.
------------------------------ Command Entered ------------------------------
WITH tmp AS (SELECT name_last, birth
FROM derev.employee, derev.work, derev.staff, derev.department
WHERE derev.department.dep_id=derev.staff.dep_id
AND derev.staff.staff_id=derev.work.staff_id
AND derev.work.empl_id=derev.employee.empl_id
AND dep_name='ÐÒÏÅËÔ á')
SELECT DISTINCT name_last, birth
FROM tmp
WHERE birth = (SELECT MAX(birth) FROM tmp) ;
-----------------------------------------------------------------------------
NAME_LAST BIRTH
-------------------- ----------
íÙÛËÉÎ 02/02/1983
1 record(s) selected.
äÏÓÔÕÐ Ë ÔÁÂÌÉÃÅ, ÄÌÑ ËÏÔÏÒÏÊ ÎÁÚÎÁÞÅÎÙ ÐÒÁ×Á INSERT, SELECT, UPDATE, DELETE.
------------------------------ Command Entered ------------------------------
INSERT INTO derev.edu_level VALUES(100,'ÏÂÒÁÚÏ×ÁÎÉÅ X') ;
-----------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Command Entered ------------------------------
SELECT * FROM derev.edu_level WHERE level_id=100 ;
-----------------------------------------------------------------------------
LEVEL_ID LEVEL_NAME
----------- --------------------
100 ÏÂÒÁÚÏ×ÁÎÉÅ X
1 record(s) selected.
------------------------------ Command Entered ------------------------------
UPDATE derev.edu_level SET level_name='ÏÂÒÁÚÏ×ÁÎÉÅ Y' WHERE level_id=100 ;
-----------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Command Entered ------------------------------
SELECT * FROM derev.edu_level WHERE level_id=100 ;
-----------------------------------------------------------------------------
LEVEL_ID LEVEL_NAME
----------- --------------------
100 ÏÂÒÁÚÏ×ÁÎÉÅ Y
1 record(s) selected.
------------------------------ Command Entered ------------------------------
DELETE FROM derev.edu_level WHERE level_id=100 ;
-----------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Command Entered ------------------------------
SELECT * FROM derev.edu_level WHERE level_id=100 ;
-----------------------------------------------------------------------------
LEVEL_ID LEVEL_NAME
----------- --------------------
0 record(s) selected.
äÏÓÔÕÐ Ë ÔÁÂÌÉÃÅ, ÄÌÑ ËÏÔÏÒÏÊ ÎÁÚÎÁÞÅÎÙ ÐÒÁ×Á SELECT É UPDATE - ÄÌÑ ÎÅËÏÔÏÒÙÈ ÓÔÏÌÂÃÏ×.
------------------------------ Command Entered ------------------------------
SELECT * FROM derev.job WHERE job_id=1 ;
-----------------------------------------------------------------------------
JOB_ID JOB_NAME MIN_SALARY MAX_SALARY LEVEL_ID PROF_ID EXPERIENCE
----------- ---------------------------------------- ---------- ---------- ----------- ----------- ----------
1 ÄÉÒÅËÔÏÒ 5000.00 6000.00 40 1 5
1 record(s) selected.
------------------------------ Command Entered ------------------------------
UPDATE derev.job SET max_salary=6000, min_salary=1000 WHERE job_id=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:
SQL0551N "STUD18" does not have the privilege to perform operation "UPDATE"
on object "DEREV.JOB". SQLSTATE=42501
------------------------------ Command Entered ------------------------------
UPDATE derev.job SET level_id=50, experience=1 WHERE job_id=1 ;
-----------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Command Entered ------------------------------
SELECT * FROM derev.job WHERE job_id=1 ;
-----------------------------------------------------------------------------
JOB_ID JOB_NAME MIN_SALARY MAX_SALARY LEVEL_ID PROF_ID EXPERIENCE
----------- ---------------------------------------- ---------- ---------- ----------- ----------- ----------
1 ÄÉÒÅËÔÏÒ 5000.00 6000.00 50 1 1
1 record(s) selected.
------------------------------ Command Entered ------------------------------
DELETE FROM derev.job WHERE job_id=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:
SQL0551N "STUD18" does not have the privilege to perform operation "DELETE"
on object "DEREV.JOB". SQLSTATE=42501
äÏÓÔÕÐ Ë ÔÁÂÌÉÃÅ, ÄÌÑ ËÏÔÏÒÏÊ ÎÁÚÎÁÞÅÎÏ ÔÏÌØËÏ ÐÒÁ×Ï SELECT.
------------------------------ Command Entered ------------------------------ SELECT * FROM derev.work WHERE empl_id=1947858035 AND staff_id=101 ; ----------------------------------------------------------------------------- EMPL_ID STAFF_ID SALARY START_DATE RATE MNG_FLAG ------------ ----------- --------- ---------- ---- -------- 1947858035. 101 5700.00 01/01/1990 1.0 - 1 record(s) selected. ------------------------------ Command Entered ------------------------------ UPDATE derev.work SET salary=salary+1000 WHERE empl_id=1947858035 AND staff_id=101 ; ----------------------------------------------------------------------------- DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0551N "STUD18" does not have the privilege to perform operation "UPDATE" on object "DEREV.WORK". SQLSTATE=42501 ------------------------------ Command Entered ------------------------------ INSERT INTO derev.work VALUES (1947858035, 303, 4000, '01/03/2000', 0.5, '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: SQL0551N "STUD18" does not have the privilege to perform operation "INSERT" on object "DEREV.WORK". SQLSTATE=42501 ------------------------------ Command Entered ------------------------------ DELETE FROM derev.work WHERE empl_id=1947858035 AND staff_id=101; DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0551N "STUD18" does not have the privilege to perform operation "DELETE" on object "DEREV.WORK". SQLSTATE=42501
úÁÐÒÏÓÙ Ë ÐÒÅÄÓÔÁ×ÌÅÎÉÀ, ×ÙÐÏÌÎÑ×ÛÉÅÓÑ × ÒÁÂÏÔÅ 4.
------------------------------ Command Entered ------------------------------
SELECT name_last, SUM(salary*rate) AS salary
FROM derev.emplview
WHERE dep_name='ÐÒÏÅËÔ á'
GROUP BY name_last, name_1st, name_2nd
ORDER BY 2 DESC ;
-----------------------------------------------------------------------------
NAME_LAST SALARY
-------------------- ---------------------------------
ëÒÑË×ÉÎ 4500.000
íÅÌØÎÉË 3000.000
ðÕÛËÏ× 3000.000
ëÁÒÁ×ÁÅ×Á 1700.000
ëÏÔÅÊËÉÎ 1500.000
íÙÛËÉÎ 1250.000
ðÏÇÏÒÅÌÏ×Á 850.000
7 record(s) selected.
------------------------------ Command Entered ------------------------------
SELECT dep_name, SUM(rate) AS rate
FROM derev.emplview
GROUP BY dep_name
ORDER BY 2 ;
-----------------------------------------------------------------------------
DEP_NAME RATE
-------------------- ---------------------------------
ÏÔÄÅÌ ôï 3.0
ÁÒÈÉ× É ÂÉÂÌÉÏÔÅËÁ 5.5
ÌÁÂÏÒÁÔÏÒÉÑ íí 6.0
ÌÁÂÏÒÁÔÏÒÉÑ ðëó 6.0
ÐÒÏÅËÔ á 6.5
ÌÁÂÏÒÁÔÏÒÉÑ óõâä 7.0
ÆÉÎ.ÏÔÄÅÌ 7.0
ÐÒÏÅËÔ óôáôõó 7.5
ÕÐÒÁ×ÌÅÎÉÅ 8.5
9 record(s) selected.
| îÁÚÁÄ | ÷ÐÅÒÅÄ |
| ëÁÔÁÌÏÇ | ïÇÌÁ×ÌÅÎÉÅ | éÎÄÅËÓ ÒÁÚÄÅÌÁ |