PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

Getting Started With PLSQL:

Insert:

SQL> cl scr 
 
SQL> DESC Students 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDNO                                             NUMBER(6) 
 FNAME                                              VARCHAR2(30) 
 LNAME                                              VARCHAR2(30) 
 DOB                                                DATE 
 DOJ                                                DATE 
 FEES                                               NUMBER(7,2) 
 GENDER                                             VARCHAR2(1) 
 INSERTBY                                           VARCHAR2(10) 
 
SQL> INSERT INTO Students 
  2  VALUES( 
  3    1234, 
  4    'SAMPATH', 
  5    'KUMAR', 
  6    '29-JAN-80', 
  7    '30-MAR-95', 
  8    25000, 
  9    'M', 
 10    'BHARATH' 
 11    ); 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1235, 
  4    'SRINIVAS', 
  5    'SHARMA', 
  6    '29-MAR-80', 
  7    '15-APR-95', 
  8    25000, 
  9    'M', 
 10    'BHARATH' 
 11*   ) 
SQL> / 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    '1236', 
  4    'RAMANA', 
  5    'KUMAR', 
  6    '29-MAR-80', 
  7    '15-APR-95', 

  8    25000, 
  9    'M', 
 10    'BHARATH' 
 11*   ) 
SQL> / 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    '1237A', 
  4    'RAMANA', 
  5    'KUMAR', 
  6    '29-MAR-80', 
  7    '15-APR-95', 
  8    25000, 
  9    'M', 
 10    'BHARATH' 
 11*   ) 
SQL> / 
  '1237A', 
  * 
ERROR at line 3: 
ORA-01722: invalid number  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1237, 
  4    'RAMANA', 
  5    KUMAR, 
  6    '29-MAR-80', 
  7    '15-APR-95', 
  8    25000, 
  9    'M', 
 10    'BHARATH' 
 11*   ) 
SQL> / 
  KUMAR, 
  * 
ERROR at line 5: 
ORA-00984: column not allowed here  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1237, 

  4    'RAMANA', 
  5    'KUMAR', 
  6    '29-MAR-80', 
  7    '15-APR-95', 
  8    25000, 
  9    'M', 
 10    'BHARATH' 
 11*   ) 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1237, 
  4    'RAMANA', 
  5    'KUMAR', 
  6    '29-MAR-80', 
  7    '15-APR-95', 
  8    25000, 
  9    'M', 
 10    'BHARATH' 
 11*   ) 
SQL> / 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1238, 
  4    'RAJA', 
  5    'KUMAR', 
  6    '29-MARCH-80', 
  7    '15-APRIL-95', 
  8    25000, 
  9    'M', 
 10    'BHARATH' 
 11*   ) 
SQL> / 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1239, 
  4    'RAMA', 
  5    'KUMAR', 
  6    '29-MARCH-1980', 
  7    '15-APRIL-2010', 
  8    25000, 
  9    'M', 

 10    'BHARATH' 
 11*   ) 
SQL> / 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1240, 
  4    'RAVI', 
  5    'KUMAR', 
  6    '29MARCH1980', 
  7    '15APRIL2010', 
  8    25000, 
  9    'M', 
 10    'BHARATH' 
 11*   ) 
SQL> / 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1240, 
  4    'RAVI', 
  5    'KUMAR', 
  6    '29-03-1980', 
  7    '15-04-2010', 
  8    25000, 
  9    'M', 
 10    'BHARATH' 
 11*   ) 
SQL> / 
  '29-03-1980', 
  * 
ERROR at line 6: 
ORA-01843: not a valid month  
 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1240, 
  4    'RAVI' 
  5    'KUMAR', 
  6    29-MAR-1980', 

  7    '15-APR-2010', 
  8    25000, 
  9    'MALE', 
 10    'BHARATH', 
 11*   ) 
SQL> / 
ERROR: 
ORA-01756: quoted string not properly terminated  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1240, 
  4    'RAVI' 
  5    'KUMAR', 
  6    '29-MAR-1980', 
  7    '15-APR-2010', 
  8    25000, 
  9    'MALE', 
 10    'BHARATH', 
 11*   ) 
SQL> / 
  'KUMAR', 
  * 
ERROR at line 5: 
ORA-00917: missing comma  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1240, 
  4    'RAVI' 
  5    'KUMAR', 
  6    29-MAR-1980', 
  7    '15-APR-2010', 
  8    25000, 
  9    'MALE', 
 10    'BHARATH', 
 11*   ) 
SQL> / 
ERROR: 
ORA-01756: quoted string not properly terminated  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1240, 

  4    'RAVI' 
  5    'KUMAR', 
  6    '29-MAR-1980', 
  7    '15-APR-2010', 
  8    25000, 
  9    'MALE', 
 10    'BHARATH', 
 11*   ) 
SQL> / 
  'KUMAR', 
  * 
ERROR at line 5: 
ORA-00917: missing comma  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1240, 
  4    'RAVI', 
  5    'KUMAR', 
  6    '29-MAR-1980', 
  7    '15-APR-2010', 
  8    25000, 
  9    'MALE', 
 10    'BHARATH', 
 11*   ) 
SQL> / 
  ) 
  * 
ERROR at line 11: 
ORA-00936: missing expression  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3    1240, 
  4    'RAVI' 
  5    'KUMAR', 
  6    '29-MAR-1980', 
  7    '15-APR-2010', 
  8    25000, 
  9    'MALE', 
 10    'BHARATH', 
 11*   ) 
SQL> / 
  'KUMAR', 
  * 
ERROR at line 5: 
ORA-00917: missing comma  
 

 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3     1240 
  4    ,'RAVI' 
  5    'KUMAR' 
  6    ,'29-MAR-1980' 
  7    ,'15-APR-2010' 
  8    ,25000 
  9    ,'MALE' 
 10    ,'BHARATH' 
 11*   ) 
SQL> / 
  'KUMAR' 
  * 
ERROR at line 5: 
ORA-00917: missing comma  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3     1240 
  4    ,'RAVI' 
  5    ,'KUMAR' 
  6    ,'29-MAR-1980' 
  7    ,'15-APR-2010' 
  8    ,25000 
  9    ,'MALE' 
 10    ,'BHARATH' 
 11*   ) 
SQL> / 
  ,'MALE' 
   * 
ERROR at line 9: 
ORA-12899: value too large for column "SCOTT"."STUDENTS"."GENDER" (actual: 4,  
maximum: 1)  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO Students 
  2  VALUES( 
  3     1240 
  4    ,'RAVI' 
  5    ,'KUMAR' 
  6    ,'29-MAR-1980' 
  7    ,'15-APR-2010' 
  8    ,25000 
  9    ,'M' 

 10    ,'BHARATH' 
 11*   ) 
SQL> / 
 
1 row created. 
 
SQL> INSERT INTO 
  2  Students 
  3  (StudNo, Fname, Lname,  DOJ, Gender) 
  4  VALUES 
  5  (1235, 'Raj', 'Kumar', '20-Feb-85', 'M'); 
 
1 row created. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO 
  2  Students 
  3  (StudNo, Fname, Lname,  DOJ, Gender) 
  4  VALUES 
  5* (1235, 'Raj', 'Kumar', 'M') 
SQL> / 
VALUES 
* 
ERROR at line 4: 
ORA-00947: not enough values  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO 
  2  Students 
  3  (StudNo, Fname, Lname,  DOJ, Gender) 
  4  VALUES 
  5* (1235, 'Raj', 'Kumar', '10-AUG-09', 'M', 'BHARATH') 
SQL> / 
Students 
* 
ERROR at line 2: 
ORA-00913: too many values  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO  
  2  Students(StudNo, Fname, Lname, DOB, DOJ, Fees, Gender) 
  3* VALUES(1234, 'Krishna', NULL, NULL, '28-FEB-04', NULL, 'M') 
SQL> / 
 
1 row created. 
 
SQL> SELECT * FROM Students; 
 

    STUDNO FNAME                          LNAME                                  
---------- ------------------------------ ------------------------------         
DOB       DOJ             FEES G INSERTBY                                        
--------- --------- ---------- - ----------                                      
      1234 SAMPATH                        KUMAR                                  
29-JAN-80 30-MAR-95      25000 M BHARATH                                          
                                                                                 
      1235 SRINIVAS                       SHARMA                                 
29-MAR-80 15-APR-95      25000 M BHARATH                                          
                                                                                 
      1236 RAMANA                         KUMAR                                  
29-MAR-80 15-APR-95      25000 M BHARATH                                          
                                                                                 
 
    STUDNO FNAME                          LNAME                                  
---------- ------------------------------ ------------------------------         
DOB       DOJ             FEES G INSERTBY                                        
--------- --------- ---------- - ----------                                      
      1237 RAMANA                         KUMAR                                  
29-MAR-80 15-APR-95      25000 M BHARATH                                          
                                                                                 
      1238 RAJA                           KUMAR                                  
29-MAR-80 15-APR-95      25000 M BHARATH                                          
                                                                                 
      1239 RAMA                           KUMAR                                  
29-MAR-80 15-APR-10      25000 M BHARATH                                          
                                                                                 
 
    STUDNO FNAME                          LNAME                                  
---------- ------------------------------ ------------------------------         
DOB       DOJ             FEES G INSERTBY                                        
--------- --------- ---------- - ----------                                      
      1240 RAVI                           KUMAR                                  
29-MAR-80 15-APR-10      25000 M BHARATH                                          
                                                                                 
      1240 RAVI                           KUMAR                                  
29-MAR-80 15-APR-10      25000 M BHARATH                                          
                                                                                 
      1235 Raj                            Kumar                                  
          20-FEB-85            M                                                 
                                                                                 
 
    STUDNO FNAME                          LNAME                                  
---------- ------------------------------ ------------------------------         
DOB       DOJ             FEES G INSERTBY                                        
--------- --------- ---------- - ----------                                      
      1234 Krishna                                                               
          28-FEB-04            M                                                 
                                                                                 
 
10 rows selected. 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> DESC Students 

 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDNO                                             NUMBER(6) 
 FNAME                                              VARCHAR2(30) 
 LNAME                                              VARCHAR2(30) 
 DOB                                                DATE 
 DOJ                                                DATE 
 FEES                                               NUMBER(7,2) 
 GENDER                                             VARCHAR2(1) 
 INSERTBY                                           VARCHAR2(10) 
 
SQL> SELECT * FROm Students; 
 
STUDNO FNAME        LNAME        DOB       DOJ         FEES G INSERTBY           
------ ------------ ------------ --------- --------- ------ - ----------         
  1234 SAMPATH      KUMAR        29-JAN-80 30-MAR-95  25000 M BHARATH             
  1235 Raj          Kumar                  20-FEB-85        M                    
  1234 Krishna                             28-FEB-04        M                    
 
SQL> INSERT INTO 
  2  Students(StudNo,  Fname, DOJ, Fees, Gender,   InsertBy) 
  3  VALUES(1234, 'Mohan', SYSDATE, 25000, 'M', USER); 
 
1 row created. 
 
SQL> SELECT * FROm Students; 
 
STUDNO FNAME        LNAME        DOB       DOJ         FEES G INSERTBY           
------ ------------ ------------ --------- --------- ------ - ----------         
  1234 SAMPATH      KUMAR        29-JAN-80 30-MAR-95  25000 M BHARATH             
  1235 Raj          Kumar                  20-FEB-85        M                    
  1234 Krishna                             28-FEB-04        M                    
  1234 Mohan                               29-JUL-10  25000 M SCOTT              
 
SQL> INSERT INTO 
  2  Students(StudNo,  Fname, DOJ, Fees, Gender,   InsertBy) 
  3  VALUES(1234, 'Mohan', SYSDATE, 25000, 'M', USER); 
 
1 row created. 
 
SQL> / 
 
1 row created. 
 
SQL> INSERT INTO Students 
  2  (StudNo, Fname, LName, Dob, Fees) 
  3  VALUES( 
  4    &StudNo, 
  5    '&Name', 
  6    &LastName, 
  7    '&DOB', 
  8    25000 
  9    ) ; 
Enter value for studno: 1235 
old   4:   &StudNo, 
new   4:   1235, 

Enter value for name: RAVI 
old   5:   '&Name', 
new   5:   'RAVI', 
Enter value for lastname: 'KUMAR' 
old   6:   &LastName, 
new   6:   'KUMAR', 
Enter value for dob: 16-APR-80 
old   7:   '&DOB', 
new   7:   '16-APR-80', 
 
1 row created. 
 
SQL> / 
Enter value for studno: 1236 
old   4:   &StudNo, 
new   4:   1236, 
Enter value for name: RAMANA 
old   5:   '&Name', 
new   5:   'RAMANA', 
Enter value for lastname: NULL 
old   6:   &LastName, 
new   6:   NULL, 
Enter value for dob: 21-MAR-86 
old   7:   '&DOB', 
new   7:   '21-MAR-86', 
 
1 row created. 
 
SQL> SELECT * FROm Students; 
 
STUDNO FNAME        LNAME        DOB       DOJ         FEES G INSERTBY           
------ ------------ ------------ --------- --------- ------ - ----------         
  1234 SAMPATH      KUMAR        29-JAN-80 30-MAR-95  25000 M BHARATH             
  1235 Raj          Kumar                  20-FEB-85        M                    
  1234 Krishna                             28-FEB-04        M                    
  1234 Mohan                               29-JUL-10  25000 M SCOTT              
  1234 Mohan                               29-JUL-10  25000 M SCOTT              
  1234 Mohan                               29-JUL-10  25000 M SCOTT              
  1235 RAVI         KUMAR        16-APR-80            25000                      
  1236 RAMANA                    21-MAR-86            25000                      
 
8 rows selected. 
 
SQL> SET VERIFY OFF 
SQL> INSERT INTO Students 
  2  (StudNo, Fname, LName, Dob, Fees) 
  3  VALUES( 
  4             &StudNo, 
  5             '&Name', 
  6             &LastName, 
  7             '&DOB', 
  8             25000 
  9             ) ; 
Enter value for studno: 1237 
Enter value for name: SAMPATH 
Enter value for lastname: NULL 

Enter value for dob: 12-MAY-88 
 
1 row created. 
 
SQL> cl scr 
 
SQL> INSERT INTO 
  2  Students(StudNo, Fname, Lname, Dob, Doj, Fees, Gender) 
  3  Values( 
  4    &StudNo, 
  5    '&FirstName', 
  6    '&LastName', 
  7    '&DOB', 
  8    SYSDATE, 
  9    &&Fees, 
 10    '&Gender' 
 11    ); 
Enter value for studno: 1240 
Enter value for firstname: SRINIVAS 
Enter value for lastname: SHARMA 
Enter value for dob: 21-MAY-88 
Enter value for fees: 20000 
Enter value for gender: M 
 
1 row created. 
 
SQL> / 
Enter value for studno: 1241 
Enter value for firstname: SRAVAN 
Enter value for lastname: NULL 
Enter value for dob: 23-NOV-88 
Enter value for gender: M 
 
1 row created. 
 
SQL> / 
Enter value for studno: 1242 
Enter value for firstname: RAVI 
Enter value for lastname: KUMAR 
Enter value for dob: 24-DEC-86 
Enter value for gender: M 
 
1 row created. 
 
SQL> UNDEFINE Fees 
SQL> / 
Enter value for studno: 1243 
Enter value for firstname: RAMANA 
Enter value for lastname: KUMAR 
Enter value for dob: 22-SEP-88 
Enter value for fees: 15000 
Enter value for gender: M 
 
1 row created. 
 
SQL> / 

Enter value for studno: 1244 
Enter value for firstname: SRAVAN 
Enter value for lastname: NULL 
Enter value for dob: 30-MAR-88 
Enter value for gender: M 
 
1 row created. 
 
SQL> DEFINE Fees = 10000 
SQL> / 
Enter value for studno: 1245 
Enter value for firstname: SRINIVAS 
Enter value for lastname: SHARMA 
Enter value for dob: 20-DEC-85 
Enter value for gender: M 
 
1 row created. 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  INSERT INTO 
  2  Students(StudNo, Fname, Lname, Dob, Doj, Fees, Gender) 
  3  Values( 
  4    &StudNo, 
  5    '&FirstName', 
  6    '&LastName', 
  7    '&DOB', 
  8    SYSDATE, 
  9    &&Fees, 
 10    '&Gender' 
 11*   ) 
SQL> SPOOL OFF 

Go Back