Oracle SQL Tutorials – Chapter 6 (Part 1 of 2)

Chapter 6 – DML (Data Manipulation Language)

• You can add, delete or update data, using the Data Manipulation Language.
• INSERT : Adding data to a table.
• UPDATE : Changing data of a table.
• DELETE : Deleting data from a table.

INSERT

• The ‘INSERT’ command is used to add data to a table.
• Here is the syntax for adding a data to a table :

• Let’s add data to departments table.

INSERT INTO hr.departments

VALUES (11,

       'Computer Engineering',

        201,

        1700);


COMMIT;

COPYING DATA FROM ANOTHER TABLE

• We use sub-query to copy data from another table.
• The VALUES statement is not used.
• The column count in the subquery and INSERT statement must be same.
• All the records that came from the subquery is inserted to table.
• Let’s add a record to hr.jobs table from hr.departments.

INSERT INTO hr.jobs (job_id,
                     job_title,
                     min_salary,
                     max_salary)
  SELECT 'COMP_ENG',
          department_name,
          30000,
          80000
  FROM hr.departments
  WHERE department_id = 11;

COMMIT;

UPDATE

• UPDATE is used to change records in a table.
• Multiple records can be updated at a time.

• Let’s raise the salaries of employees which manager_id is ‘124’.

Before Update :

UPDATE hr.employees

SET salary = salary + 250

WHERE manager_id = 124;

After Update :

• Let’s update the Compuer Engineering title in the hr.jobs table.

Before Update :

UPDATE hr.jobs

SET job_id = 'Computer Architect', job_title = 'Computer Experts'

WHERE job_id = 'Computer Engineering';

After Update :

• Let’s change the salary of employee which employee_id is ‘198’, with the maximum salary.

Before Update :

UPDATE hr.employees

SET salary = (SELECT MAX (salary) FROM hr.employees)

WHERE employee_id = 198;

After Update :

DELETE

• DELETE command is used to deleting records from a table.
• Multiple records can be deleted at a time.

• Let’s delete the records which job_id is ‘Computer Architect’.

Before Delete :

DELETE FROM hr.jobs

WHERE job_id = 'Computer Architect';

After Delete :

• Let’s delete the employees which department is ‘Shipping’.

Before Delete :

DELETE FROM hr.employees

WHERE department_id = (SELECT department_id

FROM hr.departments

WHERE department_name = 'Shipping');

After Delete :

TRUNCATE

• It deletes all of the data in a table.
• It is irreversible, we can not Rollback.
• Let’s make an example with the hr.employees table.

CREATE TABLE hr.workers

AS

SELECT * FROM hr.employees;

Before Truncate :

TRUNCATE TABLE hr.workers;

After Truncate :