{"id":10536,"date":"2019-07-18T14:33:21","date_gmt":"2019-07-18T14:33:21","guid":{"rendered":"https:\/\/ittutorial.org\/?p=10536"},"modified":"2019-07-18T14:53:01","modified_gmt":"2019-07-18T14:53:01","slug":"oracle-sql-tutorials-chapter-6-part-1-of-2","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/oracle-sql-tutorials-chapter-6-part-1-of-2\/","title":{"rendered":"Oracle SQL Tutorials \u2013 Chapter 6 (Part 1 of 2)"},"content":{"rendered":"<p><strong>Chapter 6 \u2013 DML (Data Manipulation Language)<\/strong><\/p>\n<ul>\n<li>You can add, delete or update data, using the Data Manipulation Language.<\/li>\n<li><strong>INSERT : <\/strong>Adding data to a table.<\/li>\n<li><strong>UPDATE :<\/strong> Changing data of a table.<\/li>\n<li><strong>DELETE :<\/strong> Deleting data from a table.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p><strong>INSERT<\/strong><\/p>\n<ul>\n<li>The \u2018INSERT\u2019 command is used to add data to a table.<\/li>\n<li>Here is the syntax for adding a data to a table :<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10537\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-121.png\" width=\"502\" height=\"81\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-121.png 787w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-121-300x48.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-121-768x124.png 768w\" sizes=\"auto, (max-width: 502px) 100vw, 502px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s add data to departments table.<\/li>\n<\/ul>\n<pre>INSERT INTO hr.departments\r\n\r\nVALUES (11,\r\n\r\n       'Computer Engineering',\r\n\r\n        201,\r\n\r\n        1700);\r\n\r\n\r\nCOMMIT;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"406\" height=\"172\" class=\"wp-image-10538\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-122.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-122.png 406w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-122-300x127.png 300w\" sizes=\"auto, (max-width: 406px) 100vw, 406px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>COPYING DATA FROM ANOTHER TABLE<\/strong><\/p>\n<ul>\n<li>We use sub-query to copy data from another table.<\/li>\n<li>The VALUES statement is not used.<\/li>\n<li>The column count in the subquery and INSERT statement must be same.<\/li>\n<li>All the records that came from the subquery is inserted to table.<\/li>\n<li>Let\u2019s add a record to hr.jobs table from hr.departments.<\/li>\n<\/ul>\n<pre>INSERT INTO hr.jobs (job_id,\r\n                     job_title,\r\n                     min_salary,\r\n                     max_salary)\r\n  SELECT 'COMP_ENG',\r\n          department_name,\r\n          30000,\r\n          80000\r\n  FROM hr.departments\r\n  WHERE department_id = 11;\r\n\r\nCOMMIT;<\/pre>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"449\" height=\"172\" class=\"wp-image-10539\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-123.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-123.png 449w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-123-300x115.png 300w\" sizes=\"auto, (max-width: 449px) 100vw, 449px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><strong>UPDATE<\/strong><\/p>\n<ul>\n<li>UPDATE is used to change records in a table.<\/li>\n<li>Multiple records can be updated at a time.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10540\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-124.png\" width=\"503\" height=\"84\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-124.png 718w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-124-300x50.png 300w\" sizes=\"auto, (max-width: 503px) 100vw, 503px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s raise the salaries of employees which manager_id is \u2018124\u2019.<\/li>\n<\/ul>\n<p>Before Update :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"898\" height=\"174\" class=\"wp-image-10541\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-125.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-125.png 898w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-125-300x58.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-125-768x149.png 768w\" sizes=\"auto, (max-width: 898px) 100vw, 898px\" \/><\/p>\n<pre>UPDATE hr.employees\r\n\r\nSET salary = salary + 250\r\n\r\nWHERE manager_id = 124;\r\n\r\n<\/pre>\n<p>After Update :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"899\" height=\"172\" class=\"wp-image-10542\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-126.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-126.png 899w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-126-300x57.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-126-768x147.png 768w\" sizes=\"auto, (max-width: 899px) 100vw, 899px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s update the Compuer Engineering title in the hr.jobs table.<\/li>\n<\/ul>\n<p>Before Update :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"448\" height=\"149\" class=\"wp-image-10543\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-127.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-127.png 448w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-127-300x100.png 300w\" sizes=\"auto, (max-width: 448px) 100vw, 448px\" \/><\/p>\n<pre>UPDATE hr.jobs\r\n\r\nSET job_id = 'Computer Architect', job_title = 'Computer Experts'\r\n\r\nWHERE job_id = 'Computer Engineering';\r\n\r\n<\/pre>\n<p>After Update :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"451\" height=\"171\" class=\"wp-image-10544\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-128.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-128.png 451w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-128-300x114.png 300w\" sizes=\"auto, (max-width: 451px) 100vw, 451px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s change the salary of employee which employee_id is \u2018198\u2019, with the maximum salary.<\/li>\n<\/ul>\n<p>Before Update :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"599\" height=\"47\" class=\"wp-image-10545\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-129.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-129.png 599w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-129-300x24.png 300w\" sizes=\"auto, (max-width: 599px) 100vw, 599px\" \/><\/p>\n<pre>UPDATE hr.employees\r\n\r\nSET salary = (SELECT MAX (salary) FROM hr.employees)\r\n\r\nWHERE employee_id = 198;\r\n\r\n<\/pre>\n<p>After Update :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"599\" height=\"45\" class=\"wp-image-10546\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-130.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-130.png 599w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-130-300x23.png 300w\" sizes=\"auto, (max-width: 599px) 100vw, 599px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>DELETE<\/strong><\/p>\n<ul>\n<li>DELETE command is used to deleting records from a table.<\/li>\n<li>Multiple records can be deleted at a time.<\/li>\n<\/ul>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"wp-image-10552\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-131.png\" width=\"503\" height=\"86\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-131.png 673w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-131-300x51.png 300w\" sizes=\"auto, (max-width: 503px) 100vw, 503px\" \/><\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s delete the records which job_id is \u2018Computer Architect\u2019.<\/li>\n<\/ul>\n<p>Before Delete :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"451\" height=\"110\" class=\"wp-image-10553\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-132.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-132.png 451w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-132-300x73.png 300w\" sizes=\"auto, (max-width: 451px) 100vw, 451px\" \/><\/p>\n<pre>DELETE FROM hr.jobs\r\n\r\nWHERE job_id = 'Computer Architect';\r\n\r\n<\/pre>\n<p>After Delete :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"449\" height=\"86\" class=\"wp-image-10554\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-133.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-133.png 449w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-133-300x57.png 300w\" sizes=\"auto, (max-width: 449px) 100vw, 449px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<ul>\n<li>Let\u2019s delete the employees which department is \u2018Shipping\u2019.<\/li>\n<\/ul>\n<p>Before Delete :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"919\" height=\"148\" class=\"wp-image-10555\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-134.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-134.png 919w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-134-300x48.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-134-768x124.png 768w\" sizes=\"auto, (max-width: 919px) 100vw, 919px\" \/><\/p>\n<pre>DELETE FROM hr.employees\r\n\r\nWHERE department_id = (SELECT department_id\r\n\r\nFROM hr.departments\r\n\r\nWHERE department_name = 'Shipping');\r\n\r\n<\/pre>\n<p>After Delete :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"938\" height=\"151\" class=\"wp-image-10556\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-135.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-135.png 938w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-135-300x48.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-135-768x124.png 768w\" sizes=\"auto, (max-width: 938px) 100vw, 938px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p><strong>TRUNCATE<\/strong><\/p>\n<ul>\n<li>It deletes all of the data in a table.<\/li>\n<li>It is irreversible, we can not Rollback.<\/li>\n<li>Let\u2019s make an example with the hr.employees table.<\/li>\n<\/ul>\n<pre>CREATE TABLE hr.workers\r\n\r\nAS\r\n\r\nSELECT * FROM hr.employees;\r\n\r\n<\/pre>\n<p>Before Truncate :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"940\" height=\"170\" class=\"wp-image-10557\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-136.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-136.png 940w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-136-300x54.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-136-768x139.png 768w\" sizes=\"auto, (max-width: 940px) 100vw, 940px\" \/><\/p>\n<pre>TRUNCATE TABLE hr.workers;<\/pre>\n<p>After Truncate :<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"876\" height=\"209\" class=\"wp-image-10558\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-137.png\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-137.png 876w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-137-300x72.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/07\/word-image-137-768x183.png 768w\" sizes=\"auto, (max-width: 876px) 100vw, 876px\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Chapter 6 \u2013 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. &nbsp; INSERT The \u2018INSERT\u2019 command is used to add data to a table. Here is &hellip;<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1994,3019],"tags":[],"class_list":["post-10536","post","type-post","status-publish","format-standard","","category-oracle","category-oracle-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10536","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=10536"}],"version-history":[{"count":3,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10536\/revisions"}],"predecessor-version":[{"id":10574,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/10536\/revisions\/10574"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=10536"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=10536"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=10536"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}