Nested Query with Insert Statement

Nested Query can be also used with INSERT Statement. The INSERT statement uses the data returned from the sub-query to insert into another table. The basic syntax of Nested Query with Insert Statement is as follows :
 SQL> INSERT INTO TABLE_NAME VALUES (SELECT column_name1,..., column_nameN FROM TABLE_NAME 
  WHERE VALUE IN (SELECT COLUMN_NAME FROM TABLE_NAME WHERE [condition]));
For example consider the below table students :
 STUDENT_ID   STUDENT_NAME     MARKS    GRADE
 ----------- --------------  --------- -------
   101           John            89       A    
   102           Bruce           85       A    
   103           Kevin           91       A    
   104           Rick            37       C    
   105           Joe             95       A    
   106           Patrik          57       B    
   107           Alice           50       B    
   108           Peter           87       A    
Now we need to create a separate table for the students who got A grade. To do this first we create a table A_GRADES_STUD :
 SQL> CREATE TABLE A_GRADES_STUD ( ID NUMBER(3), STUDENT_NAME VARCHAR(20));
Than the below query will insert all the students from STUDENTS table who got A grade into the A_GRADES_STUD table.
 SQL> INSERT INTO A_GRADES_STUD VALUES (SELECT STUDENT_ID, STUDENT_NAME FROM STUDENTS 
          WHERE STUDENT_ID IN (SELECT STUDENT_ID FROM STUDENTS WHERE MARKS >= 70));
And the result would be :
 SQL > SELECT * FROM A_GRADES_STUD;
   ID      STUDENT_NAME        
 -------- --------------------
   101        John                
   102        Bruce               
   103        Kevin               
   105        Joe                 
   108        Peter            


Next Topic :

No comments:

Post a Comment