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 :