Site icon IT Tutorial

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

Chapter 6 – DML (Data Manipulation Language)

 

INSERT

 

INSERT INTO hr.departments

VALUES (11,

       'Computer Engineering',

        201,

        1700);


COMMIT;

 

COPYING DATA FROM ANOTHER TABLE

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

 

Before Update :

UPDATE hr.employees

SET salary = salary + 250

WHERE manager_id = 124;

After Update :

 

 

 

Before Update :

UPDATE hr.jobs

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

WHERE job_id = 'Computer Engineering';

After Update :

 

 

Before Update :

UPDATE hr.employees

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

WHERE employee_id = 198;

After Update :

 

 

DELETE

 

Before Delete :

DELETE FROM hr.jobs

WHERE job_id = 'Computer Architect';

After Delete :

 

 

Before Delete :

DELETE FROM hr.employees

WHERE department_id = (SELECT department_id

FROM hr.departments

WHERE department_name = 'Shipping');

After Delete :

 

 

TRUNCATE

CREATE TABLE hr.workers

AS

SELECT * FROM hr.employees;

Before Truncate :

TRUNCATE TABLE hr.workers;

After Truncate :

Exit mobile version