{"id":11976,"date":"2019-11-08T21:12:41","date_gmt":"2019-11-08T21:12:41","guid":{"rendered":"https:\/\/ittutorial.org\/?p=11976"},"modified":"2019-11-18T00:31:10","modified_gmt":"2019-11-18T00:31:10","slug":"big-data-sets","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/big-data-sets\/","title":{"rendered":"Oracle SQL Tutorial -11 Big Data Sets"},"content":{"rendered":"

Hi ,<\/span><\/span><\/p>\n

In this article , I will tell you how to use the default value , how to copy data from another table .<\/span><\/span><\/p>\n

USING DEFAULT VALUE<\/span><\/span> <\/span><\/span><\/p>\n

The default value specified for the table column is assigned .<\/span><\/span> It tries to prevent users from entering incorrectly and can be used in Insert and Update .<\/span> Here’s an example of using the default value :<\/span><\/span> <\/span><\/p>\n

INSERT INTO ADMIN.NEW_TABLE(EMPLOYEE_ID,FIRST_NAME,LAST_NAME) VALUES (12,'Marlyn',DEFAULT);<\/em><\/pre>\n
SELECT * FROM ADMIN.NEW_TABLE WHERE EMPLOYEE_ID=12;<\/em><\/pre>\n
\"\"<\/p>\n
    \n
  • \n
    In our current example we create a table and we will add columns such as name and surname to this table , then we will make a default column that we have chosen so that if we don’t add anything to that column , we will return the default value .<\/span><\/span> You will understand more easily when you look at the pictures below<\/span> \ud83d\ude42 <\/span><\/p>\n<\/li>\n<\/ul>\n
    CREATE TABLE TEST1<\/em>\r\n(<\/em>\r\nISIM VARCHAR2(20),<\/em>\r\nSOYISIM VARCHAR2(20) DEFAULT 'Duran',<\/em>\r\nTARIH DATE<\/em>\r\n)<\/em><\/pre>\n
    Here we created our table and set our default value .<\/span> <\/span><\/span><\/p>\n
    INSERT INTO TEST1 VALUES('Melike',DEFAULT,SYSDATE);<\/em><\/pre>\n
    Now we have added to our table above .<\/span> <\/span><\/span><\/p>\n
    SELECT * FROM TEST1;<\/em><\/pre>\n
    And the result :<\/span> <\/span><\/span><\/p>\n
    \"\"<\/p>\n
    COPYING DATA FROM ANY OTHER TABLE<\/span><\/span> <\/span><\/span><\/p>\n
    With insert we can copy data from one table to another table using subqueries<\/span><\/span> . <\/span>But Values are not used here<\/span> . <\/span>The number of columns in the insert statement and the number of columns in the subquery must be the same .<\/span><\/span> <\/span><\/p>\n
      \n
    • \n
      When we say WHERE 1 = 0 , we only get the structure of the table , not the data .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
      CREATE TABLE NEW_TEST AS SELECT EMPLOYEE_ID,LAST_NAME,HIRE_DATE,JOB_ID,SALARY FROM ADMIN.NEW_TABLE WHERE 1=0;<\/pre>\n
      As you can see above , we created a new table and when we created this table , we took the structure of adm<\/span>in.<\/span>new_table table .<\/span> <\/span><\/span><\/p>\n
      INSERT INTO NEW_TEST SELECT EMPLOYEE_ID,LAST_NAME,HIRE_DATE,JOB_ID,SALARY FROM ADMIN.NEW_TABLE WHERE EMPLOYEE_ID=6;<\/em><\/pre>\n
      Now , we have added the person whose employee_id is equal to 6 to the table we created.<\/span> <\/span><\/span><\/p>\n
      SELECT * FROM NEW_TEST;<\/em><\/pre>\n
      \"\"<\/p>\n
      MULTI INSERT<\/span><\/span> <\/span><\/span><\/p>\n
      We’ve seen just inserting data into our table with the Insert statement , and now we’ll see how to add records to multiple tables at the same time with an insert clause under this topic .<\/span> <\/span><\/span><\/p>\n
      Multiple inserts are widely used in DataWarehouse systems .<\/span><\/span><\/span> We can provide Conditional Insert using IF \u2013 THEN .<\/span> Use of :<\/span><\/p>\n
      INSERT ALL <\/i><\/span><\/span>\r\nINTO target_a VALUES ( ... , .....)<\/i><\/span><\/span>\r\nINTO target_b VALUES ( ... , .....)<\/i><\/span><\/span>\r\nINTO target_c VALUES ( ... , .....)<\/i><\/span><\/span>\r\nSELECT .......<\/i><\/span><\/span>\r\nFROM sourcetab<\/i><\/span><\/span>\r\nWHERE ....... ;<\/i><\/span><\/span><\/pre>\n
      WHAT ARE THE MULTIPLE INSERT TYPES ?<\/span><\/span> <\/span><\/span><\/p>\n
        \n
      • \n
        UNDERSTANDING INSERT<\/span> <\/span><\/span><\/p>\n<\/li>\n
      • \n
        CONDITIONAL INSERT ALL<\/span> <\/span><\/span><\/p>\n<\/li>\n
      • \n
        CONDITIONAL INSERT FIRST<\/span> <\/span><\/span><\/p>\n<\/li>\n
      • \n
        VERTICAL INSERT<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
        UNDERSTANDING INSERT ALL<\/span> <\/span><\/span><\/p>\n
        Now let’s create 2 tables from admin.new_table table and add the person named marlyn to these tables at the same time :<\/span> <\/span><\/span><\/p>\n
        CREATE TABLE BOLUM_TEST AS SELECT * FROM ADMIN.NEW_TABLE WHERE 1=0;<\/em><\/pre>\n
        CREATE TABLE BOLUM_TEST2 AS SELECT * FROM ADMIN.NEW_TABLE WHERE 1=0;<\/em><\/pre>\n
        Now we have created our tables with conditional insert all , we want to add marlyn to both tables at the same time :<\/span><\/span><\/p>\n
        INSERT ALL<\/em>\r\nINTO BOLUM_TEST VALUES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE-DATE,JOB_ID,SALARY)<\/em>\r\nINTO BOLUM_TEST2 VALUES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE-DATE,JOB_ID,SALARY) SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY FROM ADMIN.NEW_TABLE WHERE FIRST_NAME='Marlyn';\r\n<\/em>SELECT * FROM BOLUM_TEST2;<\/em><\/pre>\n
        And we’re checking to see if it was last added :<\/span> <\/span><\/span><\/p>\n
        \"\"<\/p>\n
        CONDITIONAL INSERT ALL<\/span> <\/span><\/span><\/p>\n
        INSERT ALL<\/em>\r\nWHEN EMPLOYEE_ID>4 THEN<\/em>\r\nINTO BOLUM_TEST VALUES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY) WHEN FIRST_NAME IS NOT NULL THEN INTO BOLUM_TEST2 VALUES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY) SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY FROM ADMIN.NEW_TABLE;<\/em><\/pre>\n
        SELECT * FROM BOLUM_TEST;<\/em><\/pre>\n
        \"\"<\/p>\n
        SELECT * FROM BOLUM_TEST2;<\/em><\/pre>\n
        \"\"<\/p>\n
        CONDITIONAL INSERT FIRST<\/span> <\/span><\/span><\/p>\n
          \n
        • \n
          Now let’s create 3 experiment tables to understand this topic .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
          CREATE TABLE SALARY_HIGH AS SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY FROM ADMIN.NEW_TABLE WHERE 1=0;<\/em><\/pre>\n
          CREATE TABLE SALARY_LOW AS SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY FROM ADMIN.NEW_TABLE WHERE 1=0;<\/em><\/pre>\n
          CREATE TABLE SALARY_MIDDLE AS SELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY FROM ADMIN.NEW_TABLE WHERE 1=0;<\/em><\/pre>\n
            \n
          • \n
            Let’s insert the new_table table at the same time <\/span><\/span>. <\/span>In doing so , according to the salary , let’s insert it into 3 .<\/span> <\/span><\/p>\n<\/li>\n<\/ul>\n
            INSERT FIRST<\/em>\r\nWHEN SALARY < 4500 THEN<\/em>\r\nINTO SALARY_LOW VALUES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY)<\/em>\r\nWHEN SALARY BETWEEN 4500 AND 7000 THEN<\/em>\r\nINTO SALARY_MIDDLE VALUES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY)<\/em>\r\nELSE<\/em>\r\nINTO SALARY_HIGH VALUES(EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY)<\/em>\r\nSELECT EMPLOYEE_ID,FIRST_NAME,LAST_NAME,HIRE_DATE,JOB_ID,SALARY<\/em>\r\nFROM ADMIN.NEW_TABLE;<\/em><\/pre>\n
            Now let’s list the highest salary areas :<\/span> <\/span><\/span><\/p>\n
            SELECT * FROM SALARY_HIGH;<\/pre>\n
            \"\"<\/p>\n
            Now the areas between the two salary values are :<\/span> <\/span><\/span><\/p>\n
            SELECT * FROM SALARY_MIDDLE;<\/em><\/pre>\n
            \"\"<\/p>\n
            And finally, to see salaries below 4500 :<\/span> <\/span><\/span><\/p>\n
            SELECT * FROM SALARY_LOW;<\/em><\/pre>\n
            \"\"<\/p>\n
            VERTICAL INSERT<\/span> <\/span><\/span><\/p>\n
              \n
            • \n
              Let’s create a table named SALES_SOURCE .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
              CREATE TABLE SALES_SOURCE(<\/em>\r\nEMPNO NUMBER(5),<\/em>\r\nSALES_M NUMBER(8,2),<\/em>\r\nSALES_TU NUMBER(8,2),<\/em>\r\nSALES_W NUMBER(8,2),<\/em>\r\nSALES_TH NUMBER(8,2),<\/em>\r\nSALES_F NUMBER(8,2));<\/em><\/pre>\n
                \n
              • \n
                And create a table named SALES_INFO .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
                CREATE TABLE SALES_INFO(<\/em>\r\nEMPID NUMBER(6),<\/em>\r\nWEEK NUMBER(2),<\/em>\r\nSALES NUMBER(8,2));<\/em><\/pre>\n
                  \n
                • \n
                  Now add to the SALES_SOURCE table :<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
                  INSERT INTO SALES_SOURCE VALUES(180,1000,2000,3000,4000,5000);<\/em><\/pre>\n
                    \n
                  • \n
                    Let’s insert the data of sales_source table into the sales_info table by making vertical insert :<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
                    INSERT ALL<\/em>\r\nINTO SALES_INFO VALUES(EMPNO,WEEKID,SALES_M)<\/em>\r\nINTO SALES_INFO VALUES(EMPNO,WEEKID,SALES_TU)<\/em>\r\nINTO SALES_INFO VALUES(EMPNO,WEEKID,SALES_W)<\/em>\r\nINTO SALES_INFO VALUES(EMPNO,WEEKID,SALES_TH)<\/em>\r\nINTO SALES_INFO VALUES(EMPNO,WEEKID,SALES_F)<\/em>\r\nSELECT EMPNO,WEEKID,SALES_M,SALES_TU,SALES_W,SALES_TH,SALES_F FROM SALES_SOURCE;<\/em><\/pre>\n
                      \n
                    • \n
                      We check the sales_info table to check :<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
                      SELECT * FROM SALES_INFO;<\/em><\/pre>\n
                      MERGE<\/span><\/span><\/p>\n
                      Provides conditional execution of insert , update, delete operations in a table<\/span><\/span> . <\/span>If the corresponding row is present in the table , update becomes insert . Used in DataWirehouse applications .<\/span> And it saves us from updating separately .<\/span><\/span> <\/span><\/p>\n
                        \n
                      • \n
                        Create a merge_test table from the NEW_TABLE table .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
                        CREATE TABLE MERGE_TEST AS SELECT * FROM ADMIN.NEW_TABLE WHERE 1=0;<\/em><\/pre>\n
                        MERGE INTO MERGE_TEST c <\/em>\r\nUSING(SELECT * FROM ADMIN.NEW_TABLE) e<\/em>\r\nON(c.EMPLOYEE_ID=e.EMPLOYEE_ID)<\/em>\r\nWHEN MATCHED THEN<\/em>\r\nUPDATE SET<\/em>\r\nc.FIRST_NAME=e.FIRST_NAME,<\/em>\r\nc.LAST_NAME=e.LAST_NAME<\/em>\r\nWHEN NOT MATCHED THEN<\/em>\r\nINSERT VALUES(e.EMPLOYEE_ID,e.FIRST_NAME,e.LAST_NAME,e.HIRE_DATE,e.SALARY);<\/em><\/pre>\n
                          \n
                        • \n
                          And finally we run the following query to see the merge_test table .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
                          SELECT * FROM MERGE_TEST;<\/em><\/pre>\n
                            \n
                          • \n
                            We will see that the records in the new_table table are added to the merge_test table .<\/span> <\/span><\/span><\/p>\n<\/li>\n<\/ul>\n
                            \"\"<\/p>\n
                            See you in my next post.<\/b><\/span><\/span><\/p>\n
                            \n","protected":false},"excerpt":{"rendered":"
                            Hi , In this article , I will tell you how to use the default value , how to copy data from another table . USING DEFAULT VALUE The default value specified for the table column is assigned . It tries to prevent users from entering incorrectly and can be used in Insert and Update …<\/p>\n","protected":false},"author":10447,"featured_media":12291,"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":[3019],"tags":[5477,1340,6841,1572,6846,6811,6836,3000,3002],"class_list":["post-11976","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle-sql","tag-how-to-learn-oracle-sql","tag-oracle-sql","tag-oracle-sql-information","tag-sql","tag-sql-big-data-sets","tag-sql-information","tag-sql-queries","tag-sql-tutorial","tag-what-is-sql"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/11\/MergeVideoHero2-768x576.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11976","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\/10447"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=11976"}],"version-history":[{"count":7,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11976\/revisions"}],"predecessor-version":[{"id":12276,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/11976\/revisions\/12276"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/12291"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=11976"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=11976"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=11976"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}