Insert Update Delete
INSERT INTO tablename [(COLUM1,COLUM2, ...)] VALUES (VALUE1,VALUE2, ...);
After accessing the Scott account with SQL * PLUS, run the following:
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES (1562,'Alison','SALESMAN',7698,SYSDATE,2000,1000,30);
If you supply all the values according to the column order, you can omit the column list in parentheses after the table name. You can omit Single quotes for column value of type NUMBER. SYSDATE stores the current date data in Oracle.
INSERT INTO EMP VALUES (1500,'Bill','SALESMAN',7698,SYSDATE,1800,1200,30);
The employee number is the primary key. Therefore, anohter employee with the same employee number can not exist in the table.
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,DEPTNO) VALUES (1697,'Carol','MANAGER',7839,SYSDATE,3000,30);
There is no COMM from column list and no value for the COMM column from the value list, that inserts NULL into the COMM column.
UPDATE tableName SET column1='value1', column2='value2', ... [WHERE Conditions]
SET: Used to change the value of a specific column with the UPDATE statement.
The value can be a number or character, or combination of an operable column and an operator.
[WHERE Conditions]: Used to change the value of a column that meets the condition.
If you omit the WHERE condition in the UPDATE statement, all records in the table are changed.
Change Bill's salary to 2800
UPDATE EMP SET SAL=2800 WHERE ENAME='Bill';
Increase Alison's salary by 300 and change COMM to 500
UPDATE EMP SET SAL=SAL + 300,COMM=500 WHERE ENAME='Alison';
After executing INSERT and UPDATE, run COMMIT to manage data permanently.
DELETE FROM tableName [WHERE conditions]
If you omit the WHERE conditions in the DELETE statement, that will delete all records.
Delete Alison, Bill, and Carol from the Employee table
DELETE FROM EMP WHERE ENAME IN ('Alison','Bill','Carol');