Oracle DB for system administrators, part7

In previous posts I use only commands to retrieve information. Now i time to change something in our database.

31. Insert new employee - long way
SQL> INSERT INTO HR (NOM, FNAME, LNAME, HIREDATE, JOB, SAL, MGR, DEPTNO)
VALUES (501, 'Romeo', 'Ninov', to_date('2010-01-01', 'YYYY-MM-DD'), 'sysadmin', 9000, 999, 210);  2

1 row created.

SQL>
Here I explicitly define mapping between column and value and fill all the columns.

32. So next is to insert new employee - short way
SQL> INSERT INTO HR VALUES (501, 'Test1', 'User1', NULL, NULL,  to_date('2010-01-01', 'YYYY-MM-DD'), 9000, 210);
INSERT INTO HR VALUES (501, 'Test1', 'User1', NULL, NULL,  to_date('2010-01-01', 'YYYY-MM-DD'), 9000, 210)
*
ERROR at line 1:
ORA-00001: unique constraint (ROMEO.SYS_C0011794) violated
Ha, i get a error, so lets try to investigate whats happen. If you go back to the creation of table HR you will see NOM column is a primary key and should be unique, so lets rewrite this insert and try again
SQL>  INSERT INTO HR VALUES (502, 'Test1', 'User1', NULL, NULL,  to_date('2010-01-01', 'YYYY-MM-DD'), 9000, 210);

1 row created.

SQL>

Voila, we insert another user.

33. Next we will create user similar to existing one. I will use user Test1 as template
SQL>  INSERT INTO hr (nom, fname, lname, job, mgr, hiredate, sal, deptno) select 503 , 'Test2', 'User2',  job, mgr, hiredate, sal, deptno from hr where nom=502;

1 row created.

And lets check what is the content of the table
SQL> select * from hr where nom>501;

       NOM FNAME           LNAME           JOB               MGR HIREDATE
---------- --------------- --------------- ---------- ---------- ---------
       SAL     DEPTNO
---------- ----------
       502 Test1           User1                                 01-JAN-10
      9000        210

       503 Test2           User2                                 01-JAN-10
      9000        210
So the work is done.

34. But I want to increase salary of employee Romeo by 3000
SQL> UPDATE hr SET sal = (select sal from hr where nom=501)+3000 WHERE nom=501;

1 row updated.
and check the new salary
SQL>  select sal from hr where nom=501;

       SAL
----------
     12000
35. And I want to delete the employee Test1 and Test2
SQL> delete hr where nom=502 or nom=503;

2 rows deleted.
36. In case of usage of operations change the information to use command COMMIT or ROLLBACK to finish the transaction or return to previous status respectively. Be aware when you exit from sqlplus the commit command will be executed automatically. If you loose connection to the machine i.e broke connection to sqlplus rollback command will be executed. So I want to confirm changes and store the information
SQL> commit;

Commit complete.