ocp1z0-047: Multiple table inserts ――pivoting Insert (rotate Insert)

The point of knowledge of this topic is to understand the insert usage in Oracle

A, pivoting Insert (rotary insert)

1. CREATE TABLE Marks_details

Gyj@ocm> CREATE TABLE Marks_details (  
  2  student_id number (4) NOT NULL,  
  3  subject_id1 number (2),  
  4  marks_english number (3),  
  5  subject_id2 Number (2),  
  6  marks_math Number (3),  
  7  subject_id3 Number (2),  
  8  marks_physics Number (3),  
  9  subject_id4 Number (2),  
 10  marks_chemistry Number (3),  
 subject_id5 number (  2),  
 marks_biology number  (3)  
 13   );  
      
Table created.

2, insert a row of data into the table Marks_details

gyj@ocm> INSERT into marks_details values (1001,01,90,02,80,03,85,04,95,05,75);   
       
1 row created.  
      
Gyj@ocm> commit;  
      
Commit complete.  
      
Gyj@ocm> SELECT * from  marks_details;  
      
student_id subject_id1 marks_english subject_id2 marks_math subject_id3 marks_physics SUBJECT_ID4 MARKS_CHEMISTRY SUBJECT_ID5 marks_biology  
--------------------------------------------------------------------------------- ------------------------------------------------  
      1001           1           2           3           4    5             75

3. CREATE TABLE Marks

Gyj@ocm> CREATE TABLE Marks (  
  2   studnet_id number (4) NOT NULL,  
  3   subject_id number (2),  
  4< C25/>marks Number (3)  
  5   );  
      
Table created.

4, now to insert the data of the Marks_details table into the marks table

gyj@ocm> Insert All  
  2 in   marks values (student_id,subject_id1,marks_english)  
  3 into   marks values (Student_id,subject_id2,marks_math)  
  4 into   the marks values (student_id,subject_id3,marks_physics)  
  5 into   marks values (STUDENT_ID,SUBJECT_ID4, Marks_chemistry)  
  6 into   marks values (student_id,subject_id5,marks_biology)  
  7   Select student_id , SUBJECT_ID1,MARKS_ENGLISH,SUBJECT_ID2,MARKS_MATH,SUBJECT_ID3,  
  8   Marks_physics,subject_id4,marks_ Chemistry,subject_id5,marks_biology  
  9 from   marks_details;   
      
5 rows created.  
      
Gyj@ocm> commit;  
      
Commit complete.  
      
Gyj@ocm> select * from Marks;  
      
studnet_id subject_id      MARKS  
------------------------------  
      1001          1  
      1001          2  
      1001          3  
      1001          4  
          1001          5         75

This question is test what is pivoting insert?

B, unconditional Insert (unconditional insert all multiple-row inserts)

Then continue to create the table with the above question

Gyj@ocm> CREATE TABLE Marks_english (2 studnet_id number (4) not NULL, 3 subject_id number (2), 4 Mar  
      
KS Number (3) 5);  
      
Table created. Gyj@ocm> CREATE TABLE Marks_math (2 studnet_id number (4) not NULL, 3 subject_id number (2), 4 marks N  
      
      
Umber (3) 5);  
      
CREATE Table Marks_physics (table created.   Gyj@ocm> gyj@ocm> 2 studnet_id number (4) not NULL, 3 subject_id number (2), 4 marks number (3) 5  
      
      
);  
      
Table created. gyj@ocm> gyj@ocm> CREATE TABLE Marks_chemistry (2 studnet_id number (4) not NULL, 3 subject_id number (2)  
      
      
, 4 marks Number (3) 5);  
      
Table created.  
  gyj@ocm> gyj@ocm> CREATE TABLE marks_biology (2 studnet_id number (4) not NULL, 3 subject_id number (2),  
      
4 marks Number (3) 5);  
      
Table created. gyj@ocm> insert all 2 into Marks_english ValuES (student_id,subject_id1,marks_english) 3 into Marks_math values (Student_id,subject_id2,marks_math) 4 into Mark S_physics VALUES (student_id,subject_id3,marks_physics) 5 into marks_chemistry values (student_id,subject_id4,marks_ Chemistry) 6 into marks_biology values (student_id,subject_id5,marks_biology) 7 Select Student_id,subject_id1,mar Ks_english,subject_id2,marks_math,subject_id3,marks_physics,subject_id4,marks_chemistry,subject_id5,marks_  
      
Biology 8 from Marks_details;  
      
5 rows created.  
      
Gyj@ocm> commit;  
      
Commit complete.  
      
Gyj@ocm> select * from Marks_english; studnet_id subject_id MARKS------------------------------1001 1 Gyj@ocm&gt ;  
      
SELECT * from Marks_math; studnet_id subject_id MARKS------------------------------1001 2 Gyj@ocm&gt ;  
      
SELECT * from Marks_physics; studnet_id subject_id MARKS------------------------------1001 3 gyj@ocm> select * from Marks_chemistry; studnet_id subject_id MARKS------------------------------1001 4 Gyj  
      
@OCM > select * from Marks_biology; studnet_id subject_id MARKS------------------------------1001 5 75

C, Conditionalall (insert conditional Insertall)