PL/SQL -> SQL -> SQL Queries

SQL Queries for Practice

Go Back

Insert

OODBMS:

SQL> cl scr 
SQL> SET VERIFY OFF 
SQL> cl scr 
SQL> CREATE OR REPLACE TYPE Student 
  2  AS 
  3  OBJECT 
  4  ( 
  5   Studid   NUMBER(6) 
  6  ,Sname   VARCHAR2(20) 
  7  ,DOB   DATE 
  8  ,DOA   DATE 
  9  ,FEES   NUMBER(7, 2) 
 10  ); 
 11  / 
 
Type created. 
 
SQL> DESC USER_OBJECTS 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 OBJECT_NAME                                        VARCHAR2(128) 
 SUBOBJECT_NAME                                     VARCHAR2(30) 
 OBJECT_ID                                          NUMBER 
 DATA_OBJECT_ID                                     NUMBER 
 OBJECT_TYPE                                        VARCHAR2(19) 
 CREATED                                            DATE 
 LAST_DDL_TIME                                      DATE 
 TIMESTAMP                                          VARCHAR2(19) 
 STATUS                                             VARCHAR2(7) 
 TEMPORARY                                          VARCHAR2(1) 
 GENERATED                                          VARCHAR2(1) 
 SECONDARY                                          VARCHAR2(1) 
 
SQL> COLUMN OBJECT_NAME FORMAT A10 
SQL> COLUMN OBJECT_TYPE FORMAT A10 
SQL> COLUMN OBJECT_ID FORMAT 9999999999999999 
SQL> SELECT OBJECT_ID, OBJECT_TYPE, OBJECT_NAME, CREATED 
  2  FROM USER_OBJECTS; 
 
        OBJECT_ID OBJECT_TYP OBJECT_NAM CREATED                                  
----------------- ---------- ---------- ---------                                
            52594 TYPE       STUDENT    29-JUL-10                                
 
SQL> SELECT OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, OBJECT_NAME, CREATED 
  2  FROM USER_OBJECTS 
  3   
SQL> COLUMN OBJECT_ID FORMAT 999999 
SQL> / 
 
OBJECT_ID DATA_OBJECT_ID OBJECT_TYP OBJECT_NAM CREATED                           
--------- -------------- ---------- ---------- ---------                         
    52594                TYPE       STUDENT    29-JUL-10                         
 
SQL> DESC USER_TYPES 
Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 TYPE_NAME                                 NOT NULL VARCHAR2(30) 
 TYPE_OID                                  NOT NULL RAW(16) 
 TYPECODE                                           VARCHAR2(30) 
 ATTRIBUTES                                         NUMBER 
 METHODS                                            NUMBER 
 PREDEFINED                                         VARCHAR2(3) 
 INCOMPLETE                                         VARCHAR2(3) 
 FINAL                                              VARCHAR2(3) 
 INSTANTIABLE                                       VARCHAR2(3) 
 SUPERTYPE_OWNER                                    VARCHAR2(30) 
 SUPERTYPE_NAME                                     VARCHAR2(30) 
 LOCAL_ATTRIBUTES                                   NUMBER 
 LOCAL_METHODS                                      NUMBER 
 TYPEID                                             RAW(16) 
 
SQL> COLUMN TYPE_NAME FORMAT A10 
SQL> COLUMN ATTRIBUTES FORMAT 999 
SQL> COLUMN TYPE_OID FORMAT A15 
SQL> COLUMN TYPE_CODE FORMAT A15 
SQL> SELECT TYPE_NAME, ATTRIBUTES, TYPE_OID, TYPE_CODE 
  2  FROM USER_TYPES; 
SELECT TYPE_NAME, ATTRIBUTES, TYPE_OID, TYPE_CODE 
                                        * 
ERROR at line 1: 
ORA-00904: "TYPE_CODE": invalid identifier  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT TYPE_NAME, ATTRIBUTES, TYPE_OID 
  2* FROM USER_TYPES 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT TYPE_NAME, ATTRIBUTES, TYPE_OID 
  2* FROM USER_TYPES 
SQL> / 
 
TYPE_NAME  ATTRIBUTES TYPE_OID                                                   
---------- ---------- ---------------                                            
STUDENT             5 1E74881F216B4EF                                            
                      E95ECA63DD27F72                                            
                      E7                                                         
SQL> cl scr 
 
SQL> DESC Student 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                             NUMBER(6) 
 SNAME                                              VARCHAR2(20) 
 DOB                                                DATE 
DOA                                                DATE 
 FEES                                               NUMBER(7,2) 
 
SQL> INSERT INTO Student 
  2  VALUES(1, 'SAMPLE01', SYSDATE, SYSDATE, 25000); 
INSERT INTO Student 
            * 
ERROR at line 1: 
ORA-04044: procedure, function, package, or type is not allowed here  
 
 
SQL> cl scr 
 
SQL> CREATE TABLE McaStudent 
  2  OF Student; 
 
Table created. 
 
SQL> DESC Student 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                             NUMBER(6) 
 SNAME                                              VARCHAR2(20) 
 DOB                                                DATE 
 DOA                                                DATE 
 FEES                                               NUMBER(7,2) 
 
SQL> DESC McaStudent 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                             NUMBER(6) 
 SNAME                                              VARCHAR2(20) 
 DOB                                                DATE 
 DOA                                                DATE 
 FEES                                               NUMBER(7,2) 
 
SQL> INSERT INTO McaStudent 
  2  VALUES(1234, 'KUMAR', '07-OCT-98', SYSDATE, 15000); 
 
1 row created. 
 
SQL> INSERT INTO McaStudent(StudID, SName) 
  2  VALUES(1235, 'KRISHNA'); 
 
1 row created. 
 
SQL> INSERT INTO McaStudent 
  2  VALUES( 
  3    Student( 
  4      1236, 
  5      'SATISH', 
  6      '05-SEP-99', 
  7      SYSDATE, 
  8      1300 
  9      ) 
 10    ); 

1 row created. 
 
SQL> COLUMN SName FORMAT A12 
SQL> COLUMN StudID FORMAT 9999 
SQL> cl scr 
 
SQL> SELECT * FROM MCAStudent; 
 
STUDID SNAME        DOB       DOA             FEES                               
------ ------------ --------- --------- ----------                               
  1234 KUMAR        07-OCT-98 29-JUL-10      15000                               
  1235 KRISHNA                                                                   
  1236 SATISH       05-SEP-99 29-JUL-10       1300                               
 
SQL> SELECT StudID, Sname 
  2  FROM McaStudent; 
 
STUDID SNAME                                                                     
------ ------------                                                              
  1234 KUMAR                                                                     
  1235 KRISHNA                                                                   
  1236 SATISH                                                                    
 
SQL> SELECT StudID, Sname 
  2  FROM McaStudent 
  3  WHERE StudID = 1234; 
 
STUDID SNAME                                                                     
------ ------------                                                              
  1234 KUMAR                                                                     
 
SQL> UPDATE McaStudent 
  2  SET Sname = 'SRI RAM' 
  3  WHERE StudID = 1234; 
 
1 row updated. 
 
SQL> SELECT StudID, Sname 
  2  FROM McaStudent 
  3  WHERE StudID = 1234; 
 
STUDID SNAME                                                                     
------ ------------                                                              
  1234 SRI RAM                                                                   
 
SQL> DELETE FROM McaStudent 
  2  WHERE StudID = 1234; 
 
1 row deleted. 
 
SQL> SELECT StudID, Sname 
  2  FROM McaStudent 
  3  WHERE StudID = 1234; 
 
no rows selected 

SQL> DELETE FROM  McaStudent; 
 
2 rows deleted. 
 
SQL> SELECT StudID, Sname 
  2  FROM McaStudent; 
 
no rows selected 
 
SQL> cl scr 
 
SQL> CREATE OR REPLACE TYPE Address 
  2  AS 
  3  OBJECT 
  4  ( 
  5   HouseNum  VARCHAR2(15) 
  6  ,Street    VARCHAR2(20) 
  7  ,City    VARCHAR2(20) 
  8  ,District    VARCHAR2(20) 
  9  ,State    VARCHAR2(20) 
 10  ,Country    VARCHAR2(20) 
 11  ,Pin    CHAR(6) 
 12  ); 
 13  / 
 
Type created. 
 
SQL> CREATE OR REPLACE TYPE Name 
  2  AS 
  3  OBJECT 
  4  ( 
  5    FirstName  VARCHAR2(20) 
  6   ,MiddleName  VARCHAR2(20) 
  7   ,LastName  VARCHAR2(20) 
  8  ); 
  9  / 
 
Type created. 
 
SQL> CREATE OR REPLACE TYPE Person 
  2  AS 
  3  OBJECT 
  4  ( 
  5   Personid    NUMBER(6) 
  6  ,PersonFullName  NAME 
  7  ,PersonDOB    DATE 
  8  ,PersonAddress  ADDRESS 
  9  ); 
 10  / 
 
Type created. 
 
SQL> CREATE OR REPLACE TYPE Fees 
  2  AS 
  3  OBJECT 
 4  ( 
  5    TutionFees  NUMBER(8, 2) 
  6   ,LabFees   NUMBER(6, 2) 
  7   ,SportsFees  NUMBER(6, 2) 
  8   ,LibraryFees  NUMBER(6, 2) 
  9   ,ExamFees  NUMBER(6, 2) 
 10   ,TransportFees NUMBER(7, 2) 
 11   ,HostelFees  NUMBER(8, 2) 
 12   ,MessFees  NUMBER(8, 2) 
 13  ); 
 14  / 
 
Type created. 
 
SQL> CREATE OR REPLACE TYPE StudentColl 
  2  AS 
  3  OBJECT 
  4  ( 
  5    Student   PERSON 
  6   ,StudentFees  FEES 
  7  ); 
  8  / 
 
Type created. 
 
SQL> CREATE TABLE MCAStudent 
  2  OF 
  3  StudentColl; 
CREATE TABLE MCAStudent 
             * 
ERROR at line 1: 
ORA-00955: name is already used by an existing object  
 
 
SQL> DROP TABLE MCAStudent; 
 
Table dropped. 
 
SQL> CREATE TABLE MCAStudent 
  2  OF 
  3  StudentColl; 
 
Table created. 
 
SQL> cl scr 
 
SQL> DESC MCAStudent 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDENT                                            PERSON 
 STUDENTFEES                                        FEES 
 
SQL> SET DESCRIBE DEPTH 1 
SQL> DESC MCAStudent 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
STUDENT                                            PERSON 
 STUDENTFEES                                        FEES 
 
SQL> SET DESCRIBE DEPTH 2 
SQL> DESC MCAStudent 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDENT                                            PERSON 
   PERSONID                                         NUMBER(6) 
   PERSONFULLNAME                                   NAME 
   PERSONDOB                                        DATE 
   PERSONADDRESS                                    ADDRESS 
 STUDENTFEES                                        FEES 
   TUTIONFEES                                       NUMBER(8,2) 
   LABFEES                                          NUMBER(6,2) 
   SPORTSFEES                                       NUMBER(6,2) 
   LIBRARYFEES                                      NUMBER(6,2) 
   EXAMFEES                                         NUMBER(6,2) 
   TRANSPORTFEES                                    NUMBER(7,2) 
   HOSTELFEES                                       NUMBER(8,2) 
   MESSFEES                                         NUMBER(8,2) 
 
SQL> SET DESCRIBE DEPTH 3 
SQL> DESC MCAStudent 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDENT                                            PERSON 
   PERSONID                                         NUMBER(6) 
   PERSONFULLNAME                                   NAME 
     FIRSTNAME                                      VARCHAR2(20) 
     MIDDLENAME                                     VARCHAR2(20) 
     LASTNAME                                       VARCHAR2(20) 
   PERSONDOB                                        DATE 
   PERSONADDRESS                                    ADDRESS 
     HOUSENUM                                       VARCHAR2(15) 
     STREET                                         VARCHAR2(20) 
     CITY                                           VARCHAR2(20) 
     DISTRICT                                       VARCHAR2(20) 
     STATE                                          VARCHAR2(20) 
     COUNTRY                                        VARCHAR2(20) 
     PIN                                            CHAR(6) 
 STUDENTFEES                                        FEES 
   TUTIONFEES                                       NUMBER(8,2) 
   LABFEES                                          NUMBER(6,2) 
   SPORTSFEES                                       NUMBER(6,2) 
   LIBRARYFEES                                      NUMBER(6,2) 
   EXAMFEES                                         NUMBER(6,2) 
   TRANSPORTFEES                                    NUMBER(7,2) 
   HOSTELFEES                                       NUMBER(8,2) 
   MESSFEES                                         NUMBER(8,2) 
 
SQL> INSERT INTO 
  2  MCAStudent 
  3  ( 
  4     Student 
  5    ,StudentFees 
 6   ) 
  7   VALUES 
  8   ( 
  9    PERSON(1234, NAME 
 10      ( 
 11      'SATISH', 'KUMAR', 'YELLANKI' 
 12      ) 
 13    ,'02-OCT-86' 
 14    ,ADDRESS 
 15     ( 
 16      '2-715/B/6' 
 17      ,'BALKAMPET ROAD' 
 18      ,'HYDERABAD' 
 19      ,'RANGA REDDY' 
 20      ,'ANDHRA PRADESH' 
 21      ,'INDIA' 
 22      ,'500016' 
 23      ) 
 24    ) 
 25  ,FEES 
 26   ( 
 27    100000 
 28   ,6000 
 29   ,2000 
 30   ,1500 
 31   ,5000 
 32   ,20000 
 33   ,12000 
 34   ,27000 
 35   ) 
 36  ); 
 
1 row created. 
 
SQL> INSERT INTO 
  2  MCAStudent 
  3  ( 
  4     Student 
  5    ,StudentFees 
  6   ) 
  7   VALUES 
  8   ( 
  9    PERSON(1235, NAME 
 10       ( 
 11        'RAVI', 'KUMAR', 'SHARMA' 
 12       ) 
 13       ,'03-JAN-86' 
 14       ,ADDRESS 
 15          ( 
 16            '2-716/B/6' 
 17           ,'SHIVAM ROAD' 
 18           ,'HYDERABAD' 
 19           ,'RANGA REDDY' 
 20           ,'ANDHRA PRADESH' 
 21           ,'INDIA' 
 22           ,'500016' 
23           ) 
 24    ) 
 25    ,FEES 
 26         ( 
 27           100000 
 28          ,6000 
 29          ,2000 
 30          ,1500 
 31          ,5000 
 32          ,20000 
 33          ,12000 
 34          ,27000 
 35          ) 
 36  ); 
 
1 row created. 
 
SQL> INSERT INTO 
  2  MCAStudent 
  3  ( 
  4     Student 
  5    ,StudentFees 
  6   ) 
  7   VALUES 
  8   ( 
  9    PERSON(1236, NAME 
 10       ( 
 11        'RAMANA', 'KUMAR', 'RANJAN' 
 12       ) 
 13       ,'23-JUN-86' 
 14       ,ADDRESS 
 15          ( 
 16            '2-716/B/6' 
 17           ,'GANDHI ROAD' 
 18           ,'HYDERABAD' 
 19           ,'RANGA REDDY' 
 20           ,'ANDHRA PRADESH' 
 21           ,'INDIA' 
 22           ,'500016' 
 23           ) 
 24    ) 
 25    ,FEES 
 26         ( 
 27           100000 
 28          ,6000 
 29          ,2000 
 30          ,1500 
 31          ,5000 
 32          ,20000 
 33          ,12000 
 34          ,27000 
 35          ) 
 36  ); 
 
1 row created. 
 
 

SQL> cl scr 
 
SQL> SELECT * FROM MCAStudent; 
 
STUDENT(PERSONID, PERSONFULLNAME(FIRSTNAME, MIDDLENAME, LASTNAME), PERSONDOB, PE 
-------------------------------------------------------------------------------- 
STUDENTFEES(TUTIONFEES, LABFEES, SPORTSFEES, LIBRARYFEES, EXAMFEES, TRANSPORTFEE 
-------------------------------------------------------------------------------- 
PERSON(1234, NAME('SATISH', 'KUMAR', 'YELLANKI'), '02-OCT-86', ADDRESS('2-715/B/ 
6', 'BALKAMPET ROAD', 'HYDERABAD', 'RANGA REDDY', 'ANDHRA PRADESH', 'INDIA', '50 
0016'))                                                                          
FEES(100000, 6000, 2000, 1500, 5000, 20000, 12000, 27000)                        
                                                                                 
PERSON(1235, NAME('RAVI', 'KUMAR', 'SHARMA'), '03-JAN-86', ADDRESS('2-716/B/6',  
'SHIVAM ROAD', 'HYDERABAD', 'RANGA REDDY', 'ANDHRA PRADESH', 'INDIA', '500016')) 
FEES(100000, 6000, 2000, 1500, 5000, 20000, 12000, 27000)                        
STUDENT(PERSONID, PERSONFULLNAME(FIRSTNAME, MIDDLENAME, LASTNAME), PERSONDOB, PE 
-------------------------------------------------------------------------------- 
STUDENTFEES(TUTIONFEES, LABFEES, SPORTSFEES, LIBRARYFEES, EXAMFEES, TRANSPORTFEE 
-------------------------------------------------------------------------------- 
PERSON(1236, NAME('RAMANA', 'KUMAR', 'RANJAN'), '23-JUN-86', ADDRESS('2-716/B/6' 
, 'GANDHI ROAD', 'HYDERABAD', 'RANGA REDDY', 'ANDHRA PRADESH', 'INDIA', '500016' 
))                                                                               
FEES(100000, 6000, 2000, 1500, 5000, 20000, 12000, 27000)                        
SQL> COLUMN FNAME FORMAT A10 
SQL> COLUMN Mname FORMAT A10 
SQL> COLUMN LName FORMAT A10 
SQL> COLUMN FullName FORMAT A30 
SQL> SELECT 
  2  E1.Student.PersonFullName.FirstName FName, 
  3  E1.Student.PersonFullName.MiddleName MName, 
  4  E1.Student.PersonFullName.LastName LName, 
  5  E1.Student.PersonFullName.FirstName||' ' 
  6  ||E1.Student.PersonFullName.MiddleName||' ' 
  7  ||E1.Student.PersonFullName.LastName FullName 
  8  FROM MCAStudent E1; 
 
FNAME      MNAME      LNAME      FULLNAME                                        
---------- ---------- ---------- ------------------------------                  
SATISH     KUMAR      YELLANKI   SATISH KUMAR YELLANKI                           
RAVI       KUMAR      SHARMA     RAVI KUMAR SHARMA                               
RAMANA     KUMAR      RANJAN     RAMANA KUMAR RANJAN                             
 
SQL> SELECT 
  2  E1.Student.PersonFullName.FirstName FName, 
  3  E1.Student.PersonFullName.MiddleName MName, 
  4  E1.Student.PersonFullName.LastName LName, 
  5  E1.Student.PersonFullName.FirstName||' ' 
  6  ||E1.Student.PersonFullName.MiddleName||' ' 
  7  ||E1.Student.PersonFullName.LastName FullName 
  8  FROM ( 
  9    SELECT * 
10    FROM MCAStudent 
 11    ) E1; 
 
FNAME      MNAME      LNAME      FULLNAME                                        
---------- ---------- ---------- ------------------------------                  
SATISH     KUMAR      YELLANKI   SATISH KUMAR YELLANKI                           
RAVI       KUMAR      SHARMA     RAVI KUMAR SHARMA                               
RAMANA     KUMAR      RANJAN     RAMANA KUMAR RANJAN                             
 
SQL> CREATE OR REPLACE VIEW StudentName 
  2  AS 
  3  SELECT 
  4  E1.Student.PersonFullName.FirstName FName, 
  5  E1.Student.PersonFullName.MiddleName MName, 
  6  E1.Student.PersonFullName.LastName LName, 
  7  E1.Student.PersonFullName.FirstName||' ' 
  8  ||E1.Student.PersonFullName.MiddleName||' ' 
  9  ||E1.Student.PersonFullName.LastName FullName 
 10  FROM MCAStudent E1; 
 
View created. 
 
SQL> DESC StudentName 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 FNAME                                              VARCHAR2(20) 
 MNAME                                              VARCHAR2(20) 
 LNAME                                              VARCHAR2(20) 
 FULLNAME                                           VARCHAR2(62) 
 
SQL> SELECT * FROM StudentName; 
 
FNAME      MNAME      LNAME      FULLNAME                                        
---------- ---------- ---------- ------------------------------                  
SATISH     KUMAR      YELLANKI   SATISH KUMAR YELLANKI                           
RAVI       KUMAR      SHARMA     RAVI KUMAR SHARMA                               
RAMANA     KUMAR      RANJAN     RAMANA KUMAR RANJAN                             
 
SQL> UPDATE MCAStudent 
  2  SET 
  3  FirstName = 'RAGHAVA' 
  4  WHERE PersonID = 1236; 
WHERE PersonID = 1236 
      * 
ERROR at line 4: 
ORA-00904: "PERSONID": invalid identifier  
 
 
SQL> UPDATE MCAStudent 
  2  SET 
  3  MCAStudent.Student.PersonFullName.FirstName = 'RAGHAVA' 
  4  WHERE MCAStudent.Student.PersonID = 1236; 
WHERE MCAStudent.Student.PersonID = 1236 
      * 
ERROR at line 4: 
ORA-00904: "MCASTUDENT"."STUDENT"."PERSONID": invalid identifier  

 
SQL> ED 
Wrote file afiedt.buf 
 
  1  UPDATE MCAStudent MCA 
  2  SET 
  3  MCA.Student.PersonFullName.FirstName = 'RAGHAVA' 
  4* WHERE MCA.Student.PersonID = 1236 
SQL> / 
 
1 row updated. 
 
SQL> SELECT * FROM StudentName; 
 
FNAME      MNAME      LNAME      FULLNAME                                        
---------- ---------- ---------- ------------------------------                  
SATISH     KUMAR      YELLANKI   SATISH KUMAR YELLANKI                           
RAVI       KUMAR      SHARMA     RAVI KUMAR SHARMA                               
RAGHAVA    KUMAR      RANJAN     RAGHAVA KUMAR RANJAN                            
 
SQL>  
SQL> UPDATE ( 
  2       SELECT * 
  3       FROM MCAStudent 
  4       ) MCA 
  5  SET 
  6  MCA.Student.PersonFullName.FirstName = 'RAGHAVA' 
  7  WHERE MCA.Student.PersonID = 1236; 
 
1 row updated. 
 
SQL> DELETE MCAStudent MCA 
  2  WHERE MCA.Student.PersonID = 1236; 
 
1 row deleted. 
 
SQL> SELECT * FROM StudentName; 
 
FNAME      MNAME      LNAME      FULLNAME                                        
---------- ---------- ---------- ------------------------------                  
SATISH     KUMAR      YELLANKI   SATISH KUMAR YELLANKI                           
RAVI       KUMAR      SHARMA     RAVI KUMAR SHARMA                               
 
SQL> DELETE ( 
  2       SELECT * 
  3       FROM MCAStudent 
  4       ) MCA 
  5  WHERE MCA.Student.PersonID = 1236; 
 
0 rows deleted. 
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> COLUMN InchargeName FORMAT A19 
 

SQL> COLUMN StudentIncharge FORMAT A25 
SQL> COLUMN OID FORMAT A25 
SQL> SELECT * FROM StudentIncharges; 
 
no rows selected 
 
SQL> cl scr 
 
SQL> DESC MCAStudent 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                             NUMBER(6) 
 SNAME                                              VARCHAR2(20) 
 DOB                                                DATE 
 DOA                                                DATE 
 FEES                                               NUMBER(7,2) 
 
SQL> COLUMN STUDID FORMAT 9999 
SQL> COLUMN SNAME FORMAT A10 
SQL> COLUMN Fees FORMAT 99999 
SQL> SELECT * FROM MCAStudent; 
 
STUDID SNAME      DOB       DOA         FEES                                     
------ ---------- --------- --------- ------                                     
  1100 KUMAR      07-OCT-80 30-JUL-10  15000                                     
  1101 SESHU      07-OCT-81 30-JUL-10  15000                                     
  1102 RAMANA     07-OCT-80 30-JUL-10  15000                                     
  1103 RAMU       07-OCT-82 30-JUL-10  15000                                     
  1104 SRIPATH    07-OCT-83 30-JUL-10  15000                                     
 
SQL> SELECT 
  2  'SUBRAMANYAM SHARMA' InchargeName, 
  3  REF(A) StudentIncharge 
  4  FROM McaStudent A 
  5  WHERE SName = 'SESHU'; 
 
INCHARGENAME        STUDENTINCHARGE                                              
------------------- -------------------------                                    
SUBRAMANYAM SHARMA  0000280209FA2A77B669F548C                                    
                    8A36B581FF12354371C2C7E2A                                    
                    1E654C848A33AA9EBB45597B0                                    
                    10000370004                                                  
SQL> CREATE TABLE MBAStudent 
  2  OF Student; 
 
Table created. 
 
SQL> CREATE TABLE MAStudent 
  2  OF Student; 
 
Table created. 
 
SQL> CREATE TABLE MComStudent 
  2  OF Student; 

Table created. 
 
SQL> cl scr 
 
SQL> SELECT * FROM Tab; 
 
TNAME                          TABTYPE  CLUSTERID                                
------------------------------ ------- ----------                                
MBASTUDENT                     TABLE                                             
MASTUDENT                      TABLE                                             
MCOMSTUDENT                    TABLE                                             
MCASTUDENT                     TABLE                                             
STUDENTINCHARGES               TABLE                                             
 
SQL> BEGIN 
  2  INSERT INTO McaStudent 
  3  VALUES(1100, 'KUMAR', '07-Oct-80', SYSDATE, 15000); 
  4  INSERT INTO McaStudent 
  5  VALUES(1101, 'SESHU', '07-Oct-81', SYSDATE, 15000); 
  6  INSERT INTO McaStudent 
  7  VALUES(1102, 'RAMANA', '07-Oct-80', SYSDATE, 15000); 
  8  INSERT INTO McaStudent 
  9  VALUES(1103, 'RAMU', '07-Oct-82', SYSDATE, 15000); 
 10  INSERT INTO McaStudent 
 11  VALUES(1104, 'SRIPATH', '07-Oct-83', SYSDATE, 15000); 
 12  INSERT INTO MBAStudent 
 13  VALUES(1200, 'SAMPATH', '10-DEC-85', SYSDATE, 25000); 
 14  INSERT INTO MBAStudent 
 15  VALUES(1201, 'SURESH', '10-DEC-80', SYSDATE, 25000); 
 16  INSERT INTO MBAStudent 
 17  VALUES(1202, 'RAJESH', '10-DEC-83', SYSDATE, 25000); 
 18  INSERT INTO MAStudent 
 19  VALUES(1300, 'BOBBY', '10-DEC-80', SYSDATE, 25000); 
 20  INSERT INTO MAStudent 
 21  VALUES(1301, 'SUNDER', '10-DEC-85', SYSDATE, 25000); 
 22  INSERT INTO MComStudent 
 23  VALUES(1400, 'RAMESH', '10-DEC-83', SYSDATE, 25000); 
 24  COMMIT; 
 25  END; 
 26  / 
 
PL/SQL procedure successfully completed. 
 
SQL> cl scr 
 
SQL> SELECT * FROM MCAStudent; 
 
STUDID SNAME      DOB       DOA         FEES                                     
------ ---------- --------- --------- ------                                     
  1101 SESHU      07-OCT-81 02-AUG-10  15000                                     
  1102 RAMANA     07-OCT-80 02-AUG-10  15000                                     
  1103 RAMU       07-OCT-82 02-AUG-10  15000                                     
  1100 KUMAR      07-OCT-80 30-JUL-10  15000                                     
  1101 SESHU      07-OCT-81 30-JUL-10  15000                                     
  1102 RAMANA     07-OCT-80 30-JUL-10  15000                                     
 1103 RAMU       07-OCT-82 30-JUL-10  15000                                     
  1104 SRIPATH    07-OCT-83 30-JUL-10  15000                                     
  1100 KUMAR      07-OCT-80 02-AUG-10  15000                                     
  1104 SRIPATH    07-OCT-83 02-AUG-10  15000                                     
 
10 rows selected. 
 
SQL> DELETE FROM MCASTUDENT; 
 
10 rows deleted. 
 
SQL> COMMIT; 
 
Commit complete. 
 
SQL> cl scr 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  BEGIN 
  2  INSERT INTO McaStudent 
  3  VALUES(1100, 'KUMAR', '07-Oct-80', SYSDATE, 15000); 
  4  INSERT INTO McaStudent 
  5  VALUES(1101, 'SESHU', '07-Oct-81', SYSDATE, 15000); 
  6  INSERT INTO McaStudent 
  7  VALUES(1102, 'RAMANA', '07-Oct-80', SYSDATE, 15000); 
  8  INSERT INTO McaStudent 
  9  VALUES(1103, 'RAMU', '07-Oct-82', SYSDATE, 15000); 
 10  INSERT INTO McaStudent 
 11  VALUES(1104, 'SRIPATH', '07-Oct-83', SYSDATE, 15000); 
 12  COMMIT; 
 13* END; 
SQL> / 
 
PL/SQL procedure successfully completed. 
 
SQL> cl scr 
 
SQL> SELECT * FROm MCAStudent; 
 
STUDID SNAME      DOB       DOA         FEES                                     
------ ---------- --------- --------- ------                                     
  1100 KUMAR      07-OCT-80 02-AUG-10  15000                                     
  1101 SESHU      07-OCT-81 02-AUG-10  15000                                     
  1102 RAMANA     07-OCT-80 02-AUG-10  15000                                     
  1103 RAMU       07-OCT-82 02-AUG-10  15000                                     
  1104 SRIPATH    07-OCT-83 02-AUG-10  15000                                     
 
SQL> SELECT * FROM MBAStudent; 
 
STUDID SNAME      DOB       DOA         FEES                                     
------ ---------- --------- --------- ------                                     
  1200 SAMPATH    10-DEC-85 02-AUG-10  25000                                     
  1201 SURESH     10-DEC-80 02-AUG-10  25000                                     
  1202 RAJESH     10-DEC-83 02-AUG-10  25000                                     

SQL> SELECT * FROM MAStudent; 
 
STUDID SNAME      DOB       DOA         FEES                                     
------ ---------- --------- --------- ------                                     
  1300 BOBBY      10-DEC-80 02-AUG-10  25000                                     
  1301 SUNDER     10-DEC-85 02-AUG-10  25000                                     
 
SQL> SELECT * FROM MComStudent; 
 
STUDID SNAME      DOB       DOA         FEES                                     
------ ---------- --------- --------- ------                                     
  1400 RAMESH     10-DEC-83 02-AUG-10  25000                                     
 
SQL> cl scr 
 
SQL> BEGIN 
  2  INSERT INTO 
  3  StudentIncharges 
  4  SELECT 
  5  'SUBRAMANYAM SHARMA', 
  6  REF(A) 
  7  FROM McaStudent A 
  8  WHERE SName = 'SESHU'; 
  9  INSERT INTO StudentIncharges 
 10  SELECT 
 11  'SUBRAMANYAM SHARMA', 
 12  REF(A) 
 13  FROM McaStudent A 
 14  WHERE SName = 'RAMANA'; 
 15  INSERT INTO StudentIncharges 
 16  SELECT 
 17  'SUBRAMANYAM SHARMA', 
 18  REF(A) 
 19  FROM McaStudent A 
 20  WHERE SName = 'SRIPATH'; 
 21  INSERT INTO StudentIncharges 
 22  SELECT 
 23  'NIRANJAN RAMORI', 
 24  REF(A) 
 25  FROM McaStudent A 
 26  WHERE SName = 'KUMAR'; 
 27  INSERT INTO StudentIncharges 
 28  SELECT 
 29  'NIRANJAN RAMORI', 
 30  REF(A) 
 31  FROM McaStudent A 
 32  WHERE SName = 'RAMU'; 
 33  END; 
 34  / 
 
PL/SQL procedure successfully completed. 
 
SQL> COMMIT; 
 
Commit complete. 

SQL> cl scr 
 
SQL> SELECT * 
  2  FROM StudentIncharges 
  3  WHERE InchargeName = 'SUBRAMANYAM SHARMA'; 
 
INCHARGENAME        STUDENTINCHARGE                                              
------------------- -------------------------                                    
SUBRAMANYAM SHARMA  0000220208ED66DAEA0E954BD                                    
                    4929E25DCCA43FAEE1C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
                                                                                 
SUBRAMANYAM SHARMA  0000220208F1A6DA06BC66440                                    
                    49FC333A08475B9E81C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
                                                                                 
SUBRAMANYAM SHARMA  0000220208F410869986DA415                                    
                    DB48FED91BF3C92EF1C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
 
INCHARGENAME        STUDENTINCHARGE                                              
------------------- -------------------------                                    
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT * 
  2  FROM StudentIncharges 
  3* WHERE InchargeName = 'NIRANJAN RAMORI' 
SQL> / 
 
INCHARGENAME        STUDENTINCHARGE                                              
------------------- -------------------------                                    
NIRANJAN RAMORI     0000220208B5154F3D38B1444                                    
                    F811E7EDDA5C0D3D51C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
                                                                                 
NIRANJAN RAMORI     000022020858AE1BD83198452                                    
                    C897A9119162C452A1C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
SQL> SELECT 
  2  DEREF(X.StudentIncharge) 
  3  FROM StudentIncharges X 
  4  WHERE InchargeName = 'SUBRAMANYAM SHARMA'; 
 
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
STUDENT(1101, 'SESHU', '07-OCT-81', '02-AUG-10', 15000)                          
STUDENT(1102, 'RAMANA', '07-OCT-80', '02-AUG-10', 15000)                         
STUDENT(1104, 'SRIPATH', '07-OCT-83', '02-AUG-10', 15000)                        
 
SQL> SELECT 
 2  DEREF(X.StudentIncharge) 
  3  FROM StudentIncharges X 
  4  WHERE InchargeName = 'NIRANJAN RAMORI'; 
 
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
STUDENT(1100, 'KUMAR', '07-OCT-80', '02-AUG-10', 15000)                          
STUDENT(1103, 'RAMU', '07-OCT-82', '02-AUG-10', 15000)                           
 
SQL> BEGIN 
  2  INSERT INTO StudentIncharges 
  3  SELECT 
  4  'SUBRAMANYAM SHARMA', 
  5  REF(A) 
  6  FROM MBAStudent A 
  7  WHERE SName = 'SAMPATH'; 
  8  INSERT INTO StudentIncharges 
  9  SELECT 
 10  'SUBRAMANYAM SHARMA', 
 11  REF(A) 
 12  FROM MBAStudent A 
 13  WHERE SName = 'RAJESH'; 
 14  INSERT INTO StudentIncharges 
 15  SELECT 
 16  'NIRANJAN RAMORI', 
 17  REF(A) 
 18  FROM MBAStudent A 
 19  WHERE SName = 'SURESH'; 
 20  END; 
 21  / 
 
PL/SQL procedure successfully completed. 
 
SQL> COMMIT; 
 
Commit complete. 
 
SQL> SELECT * 
  2  FROM StudentIncharges 
  3  WHERE InchargeName = 'SUBRAMANYAM SHARMA'; 
 
INCHARGENAME        STUDENTINCHARGE                                              
------------------- -------------------------                                    
SUBRAMANYAM SHARMA  0000220208ED66DAEA0E954BD                                    
                    4929E25DCCA43FAEE1C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
                                                                                 
SUBRAMANYAM SHARMA  0000220208F1A6DA06BC66440                                    
                    49FC333A08475B9E81C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
                                                                                 
SUBRAMANYAM SHARMA  0000220208F410869986DA415                                    
                    DB48FED91BF3C92EF1C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
 
INCHARGENAME        STUDENTINCHARGE                                              
 

------------------- -------------------------                                    
                                                                                 
SUBRAMANYAM SHARMA  000022020836EC82D132E24E2                                    
                    AAF045D0CCEB8EC71DC81AA66                                    
                    119E46B6BC9B12173D9331B6                                     
                                                                                 
SUBRAMANYAM SHARMA  0000220208E8080A7CA76845A                                    
                    38F8A5DF113D34A6FDC81AA66                                    
                    119E46B6BC9B12173D9331B6                                     
SQL> SELECT 
  2  DEREF(X.StudentIncharge) 
  3  FROM StudentIncharges X 
  4  WHERE InchargeName = 'SUBRAMANYAM SHARMA'; 
 
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
STUDENT(1101, 'SESHU', '07-OCT-81', '02-AUG-10', 15000)                          
STUDENT(1102, 'RAMANA', '07-OCT-80', '02-AUG-10', 15000)                         
STUDENT(1104, 'SRIPATH', '07-OCT-83', '02-AUG-10', 15000)                        
STUDENT(1200, 'SAMPATH', '10-DEC-85', '02-AUG-10', 25000)                        
STUDENT(1202, 'RAJESH', '10-DEC-83', '02-AUG-10', 25000)                         
 
SQL> SELECT 
  2  DEREF(X.StudentIncharge) 
  3  FROM StudentIncharges X 
  4  WHERE InchargeName = 'NIRANJAN RAMORI'; 
 
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
STUDENT(1100, 'KUMAR', '07-OCT-80', '02-AUG-10', 15000)                          
STUDENT(1103, 'RAMU', '07-OCT-82', '02-AUG-10', 15000)                           
STUDENT(1201, 'SURESH', '10-DEC-80', '02-AUG-10', 25000)                         
 
SQL> BEGIN 
  2  INSERT INTO StudentIncharges 
  3  SELECT 
  4  'NIRANJAN RAMORI', 
  5  REF(A) 
  6  FROM MAStudent A 
  7  WHERE SName = 'BOBBY'; 
  8  INSERT INTO StudentIncharges 
  9  SELECT 
 10  'NIRANJAN RAMORI', 
 11  REF(A) 
 12  FROM MAStudent A 
 13  WHERE SName = 'SUNDER'; 
 14  END; 
 15  / 
 
PL/SQL procedure successfully completed. 
 
SQL> COMMIT; 
 
Commit complete. 

SQL> SELECT 
  2  DEREF(X.StudentIncharge) 
  3  FROM StudentIncharges X 
  4  WHERE InchargeName = 'SUBRAMANYAM SHARMA'; 
 
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
STUDENT(1101, 'SESHU', '07-OCT-81', '02-AUG-10', 15000)                          
STUDENT(1102, 'RAMANA', '07-OCT-80', '02-AUG-10', 15000)                         
STUDENT(1104, 'SRIPATH', '07-OCT-83', '02-AUG-10', 15000)                        
STUDENT(1200, 'SAMPATH', '10-DEC-85', '02-AUG-10', 25000)                        
STUDENT(1202, 'RAJESH', '10-DEC-83', '02-AUG-10', 25000)                         
 
SQL> SELECT 
  2  DEREF(X.StudentIncharge) 
  3  FROM StudentIncharges X 
  4  WHERE InchargeName = 'NIRANJAN RAMORI'; 
 
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
STUDENT(1100, 'KUMAR', '07-OCT-80', '02-AUG-10', 15000)                          
STUDENT(1103, 'RAMU', '07-OCT-82', '02-AUG-10', 15000)                           
STUDENT(1201, 'SURESH', '10-DEC-80', '02-AUG-10', 25000)                         
STUDENT(1300, 'BOBBY', '10-DEC-80', '02-AUG-10', 25000)                          
STUDENT(1301, 'SUNDER', '10-DEC-85', '02-AUG-10', 25000)                         
 
SQL> BEGIN 
  2  INSERT INTO StudentIncharges 
  3  SELECT 
  4  'NIRANJAN RAMORI', 
  5  REF(A) 
  6  FROM MComStudent A 
  7  WHERE SName = 'RAMESH'; 
  8  END; 
  9  / 
 
PL/SQL procedure successfully completed. 
 
SQL> COMMIT; 
 
Commit complete. 
 
SQL> SELECT 
  2  DEREF(X.StudentIncharge) 
  3  FROM StudentIncharges X 
  4  WHERE InchargeName = 'NIRANJAN RAMORI'; 
 
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
STUDENT(1100, 'KUMAR', '07-OCT-80', '02-AUG-10', 15000)                          
STUDENT(1103, 'RAMU', '07-OCT-82', '02-AUG-10', 15000)                           
STUDENT(1201, 'SURESH', '10-DEC-80', '02-AUG-10', 25000)                         
STUDENT(1300, 'BOBBY', '10-DEC-80', '02-AUG-10', 25000)                          
STUDENT(1301, 'SUNDER', '10-DEC-85', '02-AUG-10', 25000)                         
STUDENT(1400, 'RAMESH', '10-DEC-83', '02-AUG-10', 25000)                         

6 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  InchargeName, DEREF(X.StudentIncharge) 
  3  FROM StudentIncharges X; 
 
INCHARGENAME                                                                     
-------------------                                                              
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
SUBRAMANYAM SHARMA                                                               
STUDENT(1101, 'SESHU', '07-OCT-81', '02-AUG-10', 15000)                          
                                                                                 
SUBRAMANYAM SHARMA                                                               
STUDENT(1102, 'RAMANA', '07-OCT-80', '02-AUG-10', 15000)                         
                                                                                 
SUBRAMANYAM SHARMA                                                               
STUDENT(1104, 'SRIPATH', '07-OCT-83', '02-AUG-10', 15000)                        
INCHARGENAME                                                                     
-------------------                                                              
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
NIRANJAN RAMORI                                                                  
STUDENT(1100, 'KUMAR', '07-OCT-80', '02-AUG-10', 15000)                          
                                                                                 
NIRANJAN RAMORI                                                                  
STUDENT(1103, 'RAMU', '07-OCT-82', '02-AUG-10', 15000)                           
                                                                                 
SUBRAMANYAM SHARMA                                                               
STUDENT(1200, 'SAMPATH', '10-DEC-85', '02-AUG-10', 25000)                        
INCHARGENAME                                                                     
-------------------                                                              
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
SUBRAMANYAM SHARMA                                                               
STUDENT(1202, 'RAJESH', '10-DEC-83', '02-AUG-10', 25000)                         
                                                                                 
NIRANJAN RAMORI                                                                  
STUDENT(1201, 'SURESH', '10-DEC-80', '02-AUG-10', 25000)                         
                                                                                 
NIRANJAN RAMORI                                                                  
STUDENT(1300, 'BOBBY', '10-DEC-80', '02-AUG-10', 25000)                          
INCHARGENAME                                                                     
-------------------                                                              
DEREF(X.STUDENTINCHARGE)(STUDID, SNAME, DOB, DOA, FEES)                          
-------------------------------------------------------------------------------- 
NIRANJAN RAMORI                                                                  
 

STUDENT(1301, 'SUNDER', '10-DEC-85', '02-AUG-10', 25000)                         
                                                                                 
NIRANJAN RAMORI                                                                  
STUDENT(1400, 'RAMESH', '10-DEC-83', '02-AUG-10', 25000)                         
11 rows selected. 
 
SQL> cl scr 
 
SQL> SELECT 
  2  InchargeName Incharge, 
  3  E1.Stu.StudId StudID, 
  4  E1.Stu.SName Sname, 
  5  E1.Stu.DOB DOB, 
  6  E1.Stu.DOA DOA, 
  7  E1.Stu.Fees Fees 
  8  FROM (SELECT 
  9    InchargeName, DEREF(X.StudentIncharge) Stu 
 10    FROM StudentIncharges X 
 11    ) E1; 
 
INCHARGE                       STUDID SNAME      DOB       DOA         FEES      
------------------------------ ------ ---------- --------- --------- ------      
SUBRAMANYAM SHARMA               1101 SESHU      07-OCT-81 02-AUG-10  15000      
SUBRAMANYAM SHARMA               1102 RAMANA     07-OCT-80 02-AUG-10  15000      
SUBRAMANYAM SHARMA               1104 SRIPATH    07-OCT-83 02-AUG-10  15000      
NIRANJAN RAMORI                  1100 KUMAR      07-OCT-80 02-AUG-10  15000      
NIRANJAN RAMORI                  1103 RAMU       07-OCT-82 02-AUG-10  15000      
SUBRAMANYAM SHARMA               1200 SAMPATH    10-DEC-85 02-AUG-10  25000      
SUBRAMANYAM SHARMA               1202 RAJESH     10-DEC-83 02-AUG-10  25000      
NIRANJAN RAMORI                  1201 SURESH     10-DEC-80 02-AUG-10  25000      
NIRANJAN RAMORI                  1300 BOBBY      10-DEC-80 02-AUG-10  25000      
NIRANJAN RAMORI                  1301 SUNDER     10-DEC-85 02-AUG-10  25000      
NIRANJAN RAMORI                  1400 RAMESH     10-DEC-83 02-AUG-10  25000      
 
11 rows selected. 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  CREATE OR REPLACE VIEW StudentINCHData 
  2  AS 
  3  SELECT 
  4  InchargeName Incharge, 
  5  E1.Stu.StudId StudID, 
  6  E1.Stu.SName Sname, 
  7  E1.Stu.DOB DOB, 
  8  E1.Stu.DOA DOA, 
  9  E1.Stu.Fees Fees 
 10  FROM (SELECT 
 11    InchargeName, DEREF(X.StudentIncharge) Stu 
 12    FROM StudentIncharges X 
 13*   ) E1 
SQL> / 
 
 

View created. 
 
SQL> DESC StudentINCHData 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 INCHARGE                                           VARCHAR2(30) 
 STUDID                                             NUMBER(6) 
 SNAME                                              VARCHAR2(20) 
 DOB                                                DATE 
 DOA                                                DATE 
 FEES                                               NUMBER(7,2) 
 
SQL> SELECT * FROM StudentINCHData; 
 
INCHARGE                       STUDID SNAME      DOB       DOA         FEES      
------------------------------ ------ ---------- --------- --------- ------      
SUBRAMANYAM SHARMA               1101 SESHU      07-OCT-81 02-AUG-10  15000      
SUBRAMANYAM SHARMA               1102 RAMANA     07-OCT-80 02-AUG-10  15000      
SUBRAMANYAM SHARMA               1104 SRIPATH    07-OCT-83 02-AUG-10  15000      
NIRANJAN RAMORI                  1100 KUMAR      07-OCT-80 02-AUG-10  15000      
NIRANJAN RAMORI                  1103 RAMU       07-OCT-82 02-AUG-10  15000      
SUBRAMANYAM SHARMA               1200 SAMPATH    10-DEC-85 02-AUG-10  25000      
SUBRAMANYAM SHARMA               1202 RAJESH     10-DEC-83 02-AUG-10  25000      
NIRANJAN RAMORI                  1201 SURESH     10-DEC-80 02-AUG-10  25000      
NIRANJAN RAMORI                  1300 BOBBY      10-DEC-80 02-AUG-10  25000      
NIRANJAN RAMORI                  1301 SUNDER     10-DEC-85 02-AUG-10  25000      
NIRANJAN RAMORI                  1400 RAMESH     10-DEC-83 02-AUG-10  25000      
 
11 rows selected. 
 
SQL> DELETE 
  2  FROM MCAStudent 
  3  WHERE SName = 'SESHU'; 
 
1 row deleted. 
 
SQL> SELECT * FROM StudentIncharges; 
 
INCHARGENAME        STUDENTINCHARGE                                              
------------------- -------------------------                                    
SUBRAMANYAM SHARMA  0000220208ED66DAEA0E954BD                                    
                    4929E25DCCA43FAEE1C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
                                                                                 
SUBRAMANYAM SHARMA  0000220208F1A6DA06BC66440                                    
                    49FC333A08475B9E81C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
                                                                                 
SUBRAMANYAM SHARMA  0000220208F410869986DA415                                    
                    DB48FED91BF3C92EF1C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
 
INCHARGENAME        STUDENTINCHARGE                                              
------------------- -------------------------                                    
                                                                                 
NIRANJAN RAMORI     0000220208B5154F3D38B1444                                    
                   F811E7EDDA5C0D3D51C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
                                                                                 
NIRANJAN RAMORI     000022020858AE1BD83198452                                    
                    C897A9119162C452A1C2C7E2A                                    
                    1E654C848A33AA9EBB45597B                                     
                                                                                 
SUBRAMANYAM SHARMA  000022020836EC82D132E24E2                                    
                    AAF045D0CCEB8EC71DC81AA66                                    
 
INCHARGENAME        STUDENTINCHARGE                                              
------------------- -------------------------                                    
                    119E46B6BC9B12173D9331B6                                     
                                                                                 
SUBRAMANYAM SHARMA  0000220208E8080A7CA76845A                                    
                    38F8A5DF113D34A6FDC81AA66                                    
                    119E46B6BC9B12173D9331B6                                     
                                                                                 
NIRANJAN RAMORI     000022020860E1ACFB0B734AA                                    
                    19995A4480B4873A0DC81AA66                                    
                    119E46B6BC9B12173D9331B6                                     
                                                                                 
NIRANJAN RAMORI     0000220208F8348FCEE75A421                                    
 
INCHARGENAME        STUDENTINCHARGE                                              
------------------- -------------------------                                    
                    FB19191E90630BD8BF6131B14                                    
                    BE6941D4886AF9685E255BC7                                     
                                                                                 
NIRANJAN RAMORI     000022020866E8E45C571A431                                    
                    0A57D80114C8509E5F6131B14                                    
                    BE6941D4886AF9685E255BC7                                     
                                                                                 
NIRANJAN RAMORI     00002202089904F2627FB64B0                                    
                    781029CB8EE47DC3655A6BBB2                                    
                    D4A3443ABC20A3BCDA8E5B39                                     
11 rows selected. 
 
SQL> SELECT * FROM StudentINCHData; 
 
INCHARGE                       STUDID SNAME      DOB       DOA         FEES      
------------------------------ ------ ---------- --------- --------- ------      
SUBRAMANYAM SHARMA                                                               
SUBRAMANYAM SHARMA               1102 RAMANA     07-OCT-80 02-AUG-10  15000      
SUBRAMANYAM SHARMA               1104 SRIPATH    07-OCT-83 02-AUG-10  15000      
NIRANJAN RAMORI                  1100 KUMAR      07-OCT-80 02-AUG-10  15000      
NIRANJAN RAMORI                  1103 RAMU       07-OCT-82 02-AUG-10  15000      
SUBRAMANYAM SHARMA               1200 SAMPATH    10-DEC-85 02-AUG-10  25000      
SUBRAMANYAM SHARMA               1202 RAJESH     10-DEC-83 02-AUG-10  25000      
NIRANJAN RAMORI                  1201 SURESH     10-DEC-80 02-AUG-10  25000      
NIRANJAN RAMORI                  1300 BOBBY      10-DEC-80 02-AUG-10  25000      
NIRANJAN RAMORI                  1301 SUNDER     10-DEC-85 02-AUG-10  25000      
NIRANJAN RAMORI                  1400 RAMESH     10-DEC-83 02-AUG-10  25000      
 
 

11 rows selected. 
 
SQL> DELETE FROM StudentINCHData 
  2  WHERE Stuid IS NULL AND INCHARGE = 'SUBRAMANYAM SHARMA'; 
WHERE Stuid IS NULL AND INCHARGE = 'SUBRAMANYAM SHARMA' 
      * 
ERROR at line 2: 
ORA-00904: "STUID": invalid identifier  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  DELETE FROM ( 
  2    SELECT 
  3          InchargeName,  
  4    DEREF(X.StudentIncharge) Stu 
  5          FROM StudentIncharges X 
  6          ) E1 
  7  WHERE  
  8  E1.Stu.StudId StudID IS NULL AND 
  9* InchargeName = 'SUBRAMANYAM SHARMA'  
SQL> / 
E1.Stu.StudId StudID IS NULL AND 
              * 
ERROR at line 8: 
ORA-00920: invalid relational operator  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  DELETE FROM ( 
  2    SELECT 
  3          InchargeName,  
  4    DEREF(X.StudentIncharge) Stu 
  5          FROM StudentIncharges X 
  6          ) E1 
  7  WHERE  
  8  E1.Stu.StudId IS NULL AND 
  9* InchargeName = 'SUBRAMANYAM SHARMA'  
SQL> / 
 
1 row deleted. 
 
SQL> SELECT * FROM StudentINCHData; 
 
INCHARGE                       STUDID SNAME      DOB       DOA         FEES      
------------------------------ ------ ---------- --------- --------- ------      
SUBRAMANYAM SHARMA               1102 RAMANA     07-OCT-80 02-AUG-10  15000      
SUBRAMANYAM SHARMA               1104 SRIPATH    07-OCT-83 02-AUG-10  15000      
NIRANJAN RAMORI                  1100 KUMAR      07-OCT-80 02-AUG-10  15000      
NIRANJAN RAMORI                  1103 RAMU       07-OCT-82 02-AUG-10  15000      
SUBRAMANYAM SHARMA               1200 SAMPATH    10-DEC-85 02-AUG-10  25000      
SUBRAMANYAM SHARMA               1202 RAJESH     10-DEC-83 02-AUG-10  25000      
NIRANJAN RAMORI                  1201 SURESH     10-DEC-80 02-AUG-10  25000      
 

NIRANJAN RAMORI                  1300 BOBBY      10-DEC-80 02-AUG-10  25000      
NIRANJAN RAMORI                  1301 SUNDER     10-DEC-85 02-AUG-10  25000      
NIRANJAN RAMORI                  1400 RAMESH     10-DEC-83 02-AUG-10  25000      
 
10 rows selected. 
 
SQL> cl scr 
 
SQL> Create Table Students 
  2  ( 
  3   Studid   NUMBER(6) 
  4   CONSTRAINT StudIDPK  PRIMARY KEY, 
  5   SName    VARCHAR2(30), 
  6   Street   VARCHAR2(40), 
  7   CityName  VARCHAR2(25), 
  8   StateName  VARCHAR2(40), 
  9   Pincode Number(6) 
 10   ); 
 
Table created. 
 
SQL> BEGIN 
  2  INSERT INTO Students 
  3  VALUES(1200, 'SAMPATH', 'SHIV BAGH', 'HYDERABAD', 'ANDHRA PRADESH', 
506001); 
  4  INSERT INTO Students 
  5  VALUES(1201, 'SRINIVAS', 'SHYAM NAGAR', 'SECUNDERABAD', 'ANDHRA PRADESH', 
506002); 
  6  INSERT INTO Students 
  7  VALUES(1202, 'SHIVAJI', 'ANDAL NAGAR', 'CHENNAI', 'TAMIL NADU', 606060); 
  8  COMMIT; 
  9  END; 
 10  / 
 
PL/SQL procedure successfully completed. 
 
SQL> COLUMN STUDID FORMAT 9999 
SQL> COLUMN SNAME FORMAT A9 
SQL> COLUMN STREET FORMAT A12 
SQL> COLUMN CITYNAME FORMAT A13 
SQL> COLUMN STATENAME FORMAT A15 
SQL> COLUMN PINCODE FORMAT 999999 
SQL> SELECT * 
  2  FROM Students; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1200 SAMPATH   SHIV BAGH    HYDERABAD     ANDHRA PRADESH   506001              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              
 
SQL> CREATE OR REPLACE TYPE MyAddress 
  2  AS OBJECT 
  3  ( 
  4   Street    VARCHAR2(40), 
  5   CityName   VARCHAR2(25), 
 6   StateName   VARCHAR2(40), 
  7   Pincode    NUMBER(6) 
  8   ); 
  9  / 
 
Type created. 
 
SQL> CREATE OR REPLACE TYPE MyStudent 
  2  AS OBJECT 
  3  ( 
  4   SName VARCHAR2(30), 
  5   Saddress Myaddress 
  6   ); 
  7  / 
 
Type created. 
 
SQL> SELECT 
  2  StudID, 
  3  SName, 
  4  Street,  CityName , StateName, Pincode 
  5  FROM Students; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1200 SAMPATH   SHIV BAGH    HYDERABAD     ANDHRA PRADESH   506001              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              
 
SQL> SELECT 
  2  StudID, 
  3  MyStudent(SName, 
  4  Myaddress(Street,  CityName , StateName, Pincode)) 
  5  FROM Students; 
 
STUDID                                                                           
------                                                                           
MYSTUDENT(SNAME,MYADDRESS(STREET,CITYNAME,STATENAME,PINCODE))(SNAME, SADDRESS(ST 
-------------------------------------------------------------------------------- 
  1200                                                                           
MYSTUDENT('SAMPATH', MYADDRESS('SHIV BAGH', 'HYDERABAD', 'ANDHRA PRADESH', 50600 
1))                                                                               1201                                                                           
MYSTUDENT('SRINIVAS', MYADDRESS('SHYAM NAGAR', 'SECUNDERABAD', 'ANDHRA PRADESH', 
 506002))                                                                         1202                                                                           
 
STUDID                                                                           
------                                                                           
MYSTUDENT(SNAME,MYADDRESS(STREET,CITYNAME,STATENAME,PINCODE))(SNAME, SADDRESS(ST 
-------------------------------------------------------------------------------- 
MYSTUDENT('SHIVAJI', MYADDRESS('ANDAL NAGAR', 'CHENNAI', 'TAMIL NADU', 606060))  
 

SQL> CREATE OR REPLACE VIEW 
  2  StudentOV(StudID, StudDEF) 
  3  AS 
  4  SELECT 
  5  StudID, 
  6  MyStudent(SName, 
  7  Myaddress(Street,  CityName , StateName, Pincode)) 
  8  FROM Students; 
 
View created. 
 
SQL> DESC StudentOV 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                    NOT NULL NUMBER(6) 
 STUDDEF                                            MYSTUDENT 
 
SQL> SET DESCRIBE DEPTH 2 
SQL> DESC StudentOV 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                    NOT NULL NUMBER(6) 
 STUDDEF                                            MYSTUDENT 
   SNAME                                            VARCHAR2(30) 
   SADDRESS                                         MYADDRESS 
 
SQL> SET DESCRIBE DEPTH 3 
SQL> DESC StudentOV 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                    NOT NULL NUMBER(6) 
 STUDDEF                                            MYSTUDENT 
   SNAME                                            VARCHAR2(30) 
   SADDRESS                                         MYADDRESS 
     STREET                                         VARCHAR2(40) 
     CITYNAME                                       VARCHAR2(25) 
     STATENAME                                      VARCHAR2(40) 
     PINCODE                                        NUMBER(6) 
 
SQL> SELECT * FROM StudentOV; 
 
STUDID                                                                           
------                                                                           
STUDDEF(SNAME, SADDRESS(STREET, CITYNAME, STATENAME, PINCODE))                   
-------------------------------------------------------------------------------- 
  1200                                                                           
MYSTUDENT('SAMPATH', MYADDRESS('SHIV BAGH', 'HYDERABAD', 'ANDHRA PRADESH', 50600 
1))                                                                               1201                                                                           
MYSTUDENT('SRINIVAS', MYADDRESS('SHYAM NAGAR', 'SECUNDERABAD', 'ANDHRA PRADESH', 
 506002))                                                                         1202                                                                           
 
STUDID                                                                           
 

------                                                                           
STUDDEF(SNAME, SADDRESS(STREET, CITYNAME, STATENAME, PINCODE))                   
-------------------------------------------------------------------------------- 
MYSTUDENT('SHIVAJI', MYADDRESS('ANDAL NAGAR', 'CHENNAI', 'TAMIL NADU', 606060))  
SQL> SELECT 
  2  StudID, 
  3  E1.StudDEF.Sname Sname, 
  4  E1.StudDEF.SAddress.Street Street, 
  5  E1.StudDEF.SAddress.CityName CityName, 
  6  E1.StudDEF.SAddress.StateName StateName, 
  7  E1.StudDEF.SAddress.PinCode PinCode 
  8  FROM (SELECT * 
  9    FROM StudentOV) E1; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1200 SAMPATH   SHIV BAGH    HYDERABAD     ANDHRA PRADESH   506001              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              
 
SQL> SPOOL OFF 
SQL> cl scr 
 
SQL> SELECT * FROM TAB; 
 
TNAME                          TABTYPE  CLUSTERID                                
------------------------------ ------- ----------                                
STUDENTS                       TABLE                                             
STUDENTOV                      VIEW                                              
 
SQL> SELECT * FROM STUDENTS; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1200 SAMPATH   SHIV BAGH    HYDERABAD     ANDHRA PRADESH   506001              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              
 
SQL> DESC STUDENTOV 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                    NOT NULL NUMBER(6) 
 STUDDEF                                            MYSTUDENT 
 
SQL> SET DESCRIBE DEPTH 2 
SQL> DESC STUDENTOV 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                    NOT NULL NUMBER(6) 
 STUDDEF                                            MYSTUDENT 
   SNAME                                            VARCHAR2(30) 
   SADDRESS                                         MYADDRESS 
 
SQL> SET DESCRIBE DEPTH 3 
 

SQL> DESC STUDENTOV 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                    NOT NULL NUMBER(6) 
 STUDDEF                                            MYSTUDENT 
   SNAME                                            VARCHAR2(30) 
   SADDRESS                                         MYADDRESS 
     STREET                                         VARCHAR2(40) 
     CITYNAME                                       VARCHAR2(25) 
     STATENAME                                      VARCHAR2(40) 
     PINCODE                                        NUMBER(6) 
 
SQL> SELECT * 
  2  FROM StudentOV; 
 
STUDID                                                                           
------                                                                           
STUDDEF(SNAME, SADDRESS(STREET, CITYNAME, STATENAME, PINCODE))                   
-------------------------------------------------------------------------------- 
  1200                                                                           
MYSTUDENT('SAMPATH', MYADDRESS('SHIV BAGH', 'HYDERABAD', 'ANDHRA PRADESH', 50600 
1))                                                                               1201                                                                           
MYSTUDENT('SRINIVAS', MYADDRESS('SHYAM NAGAR', 'SECUNDERABAD', 'ANDHRA PRADESH', 
 506002))                                                                         1202                                                                           
 
STUDID                                                                           
------                                                                           
STUDDEF(SNAME, SADDRESS(STREET, CITYNAME, STATENAME, PINCODE))                   
-------------------------------------------------------------------------------- 
MYSTUDENT('SHIVAJI', MYADDRESS('ANDAL NAGAR', 'CHENNAI', 'TAMIL NADU', 606060))  
SQL> SELECT 
  2  StudID, 
  3  E1.StudDEF.Sname Sname, 
  4  E1.StudDEF.SAddress.Street Street, 
  5  E1.StudDEF.SAddress.CityName CityName, 
  6  E1.StudDEF.SAddress.StateName StateName, 
  7  E1.StudDEF.SAddress.PinCode PinCode 
  8  FROM (SELECT * 
  9    FROM StudentOV) E1; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1200 SAMPATH   SHIV BAGH    HYDERABAD     ANDHRA PRADESH   506001              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              
 
SQL> INSERT INTO StudentOV 
  2  VALUES(1203, 
  3   MYStudent('RAMESH', 
  4    MyAddress( 
 5       'SHYAMA NAGAR', 
  6       'SECUNDERABAD', 
  7       'ANDHRA PRADESH', 
  8       506001 
  9       ) 
 10     ) 
 11   ); 
 
1 row created. 
 
SQL> SELECT * 
  2  FROM Students; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1200 SAMPATH   SHIV BAGH    HYDERABAD     ANDHRA PRADESH   506001              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              
  1203 RAMESH    SHYAMA NAGAR SECUNDERABAD  ANDHRA PRADESH   506001              
 
SQL> INSERT INTO StudentOV 
  2  VALUES(1203, 
  3     MYStudent('RAMESH', 
  4             MyAddress( 
  5                                     'SHYAMA NAGAR' 
  6                                     'SECUNDERABAD' 
  7                                     'ANDHRA PRADESH' 
  8                                     506 
  9                                     ) 
 10                     ) 
 11     ) 
 12   
SQL> cl scr 
 
SQL> DELETE StudentOV 
  2  WHERE StudId = 1200; 
 
1 row deleted. 
 
SQL> SELECT * 
  2  FROM Students; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              
  1203 RAMESH    SHYAMA NAGAR SECUNDERABAD  ANDHRA PRADESH   506001              
 
SQL> DELETE (SELECT * 
  2    FROM StudentOV) E1 
  3  WHERE E1.StudDef.Sname = 'SHIVAJI'; 
 
1 row deleted. 
 
SQL> SELECT * 
  2  FROM Students; 

STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1203 RAMESH    SHYAMA NAGAR SECUNDERABAD  ANDHRA PRADESH   506001              
 
SQL> UPDATE (SELECT * 
  2    FROM StudentOV) E1 
  3  SET E1.StudDef.Sname = 'SRINIVAS RAJU' 
  4  WHERE E1.StudID = 1201; 
 
1 row updated. 
 
SQL> SELECT * 
  2  FROM Students; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
       RAJU                                                                       1203 RAMESH    SHYAMA NAGAR SECUNDERABAD  ANDHRA PRADESH   506001              
 
SQL> cl scr 
 
SQL> ROLLBACK; 
 
Rollback complete. 
 
SQL> cl scr 
 
SQL> BEGIN 
  2  DELETE FROM STUDENTS; 
  3  INSERT INTO Students 
  4  VALUES(1200, 'SAMPATH', 'SHIV BAGH', 'HYDERABAD', 'ANDHRA PRADESH', 
506001); 
  5  INSERT INTO Students 
  6  VALUES(1201, 'SRINIVAS', 'SHYAM NAGAR', 'SECUNDERABAD', 'ANDHRA PRADESH', 
506002); 
  7  INSERT INTO Students 
  8  VALUES(1202, 'SHIVAJI', 'ANDAL NAGAR', 'CHENNAI', 'TAMIL NADU', 606060); 
  9  COMMIT; 
 10  END; 
 11  / 
 
PL/SQL procedure successfully completed. 
 
SQL> cl scr 
 
SQL> SELECT * FROM STUDENTS; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1200 SAMPATH   SHIV BAGH    HYDERABAD     ANDHRA PRADESH   506001              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              

SQL> CREATE TABLE StudentBooks 
  2  ( 
  3   LibTranNO  NUMBER(6), 
  4   StudID   NUMBER(6), 
  5   BookTitle  VARCHAR2(50), 
  6   LendingDate DATE, 
  7   CONSTRAINT LibtreamNoPK 
  8   PRIMARY KEY(LibTranNO, Studid), 
  9   CONSTRAINT StudentBooksFK 
 10   FOREIGN KEY(Studid) 
 11   REFERENCES Students(StudID) 
 12   ); 
 
Table created. 
 
SQL> BEGIN 
  2  INSERT INTO StudentBooks 
  3  VALUES(2000, 1200, 'THERMO DYNAMICS', SYSDATE); 
  4  INSERT INTO StudentBooks 
  5  VALUES(2001, 1200, 'FLUID MECHANICS', SYSDATE); 
  6  INSERT INTO StudentBooks 
  7  VALUES(2002, 1200, 'ATOMIC PHYSICS', SYSDATE); 
  8  INSERT INTO StudentBooks 
  9  VALUES(2003, 1201, 'STRUCTURAL DYNAMICS', SYSDATE); 
 10  INSERT INTO StudentBooks 
 11  VALUES(2004, 1201, 'AVIATION THEORY', SYSDATE); 
 12  INSERT INTO StudentBooks 
 13  VALUES(2005, 1202, 'PROPULSION THEORY', SYSDATE); 
 14  COMMIT; 
 15  END; 
 16  / 
 
PL/SQL procedure successfully completed. 
 
SQL> cl scr 
 
SQL> SELECT * FROM STUDENTS; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1200 SAMPATH   SHIV BAGH    HYDERABAD     ANDHRA PRADESH   506001              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              
 
SQL> BEGIN 
  2  INSERT INTO StudentBooks 
  3  VALUES(2000, 1200, 'THERMO DYNAMICS', SYSDATE); 
  4  INSERT INTO StudentBooks 
  5  VALUES(2001, 1200, 'FLUID MECHANICS', SYSDATE); 
  6  INSERT INTO StudentBooks 
  7  VALUES(2002, 1200, 'ATOMIC PHYSICS', SYSDATE); 
  8  INSERT INTO StudentBooks 
  9  VALUES(2003, 1201, 'STRUCTURAL DYNAMICS', SYSDATE); 
 10  INSERT INTO StudentBooks 
 11  VALUES(2004, 1201, 'AVIATION THEORY', SYSDATE); 
12  INSERT INTO StudentBooks 
 13  VALUES(2005, 1202, 'PROPULSION THEORY', SYSDATE); 
 14  COMMIT; 
 15  END 
 16   
 17  . 
SQL> cl scr 
 
SQL> SELECT * FROM Students; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1200 SAMPATH   SHIV BAGH    HYDERABAD     ANDHRA PRADESH   506001              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              
 
SQL> COLUMN LIBTRANNO FORMAT 9999 
SQL> COLUMN STUDID FORMAT 9999 
SQL> COLUMN BOOKTITLE FORMAT A20 
SQL> SELECT * 
  2  FROM StudentBooks; 
 
LIBTRANNO STUDID BOOKTITLE            LENDINGDA                                  
--------- ------ -------------------- ---------                                  
     2000   1200 THERMO DYNAMICS      03-AUG-10                                  
     2001   1200 FLUID MECHANICS      03-AUG-10                                  
     2002   1200 ATOMIC PHYSICS       03-AUG-10                                  
     2003   1201 STRUCTURAL DYNAMICS  03-AUG-10                                  
     2004   1201 AVIATION THEORY      03-AUG-10                                  
     2005   1202 PROPULSION THEORY    03-AUG-10                                  
 
6 rows selected. 
 
SQL> SELECT Sname, BookTitle, LendingDate 
  2  FROM Students S, StudentBooks SB 
  3  WHERE S.StudID = SB.StudID; 
 
SNAME     BOOKTITLE            LENDINGDA                                         
--------- -------------------- ---------                                         
SAMPATH   THERMO DYNAMICS      03-AUG-10                                         
SAMPATH   FLUID MECHANICS      03-AUG-10                                         
SAMPATH   ATOMIC PHYSICS       03-AUG-10                                         
SRINIVAS  STRUCTURAL DYNAMICS  03-AUG-10                                         
SRINIVAS  AVIATION THEORY      03-AUG-10                                         
SHIVAJI   PROPULSION THEORY    03-AUG-10                                         
 
6 rows selected. 
 
SQL> CREATE OR REPLACE TYPE StudentType 
  2  AS OBJECT 
  3  ( 
  4   Studid NUMBER(6), 
  5   Sname VARCHAR2(30), 
  6   Street Varchar2(40), 
  7   Cityname VARCHAR2(25), 
  8   Statename VARCHAR2(40), 
 9   Pincode NUMBER(6) 
 10   ); 
 11  / 
 
Type created. 
 
SQL> CREATE OR REPLACE VIEW STUDENTOV 
  2  OF StudentType 
  3  WITH OBJECT IDENTIFIER(StudID) 
  4  AS 
  5  SELECT StudID, Sname, 
  6  Street, CityName, 
  7  StateName, Pincode 
  8  FROM Students; 
 
View created. 
 
SQL> DESC STUDENTOV 
 Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                             NUMBER(6) 
 SNAME                                              VARCHAR2(30) 
 STREET                                             VARCHAR2(40) 
 CITYNAME                                           VARCHAR2(25) 
 STATENAME                                          VARCHAR2(40) 
 PINCODE                                            NUMBER(6) 
 
SQL> SELECT * FROM STUDENTOV; 
 
STUDID SNAME     STREET       CITYNAME      STATENAME       PINCODE              
------ --------- ------------ ------------- --------------- -------              
  1200 SAMPATH   SHIV BAGH    HYDERABAD     ANDHRA PRADESH   506001              
  1201 SRINIVAS  SHYAM NAGAR  SECUNDERABAD  ANDHRA PRADESH   506002              
  1202 SHIVAJI   ANDAL NAGAR  CHENNAI       TAMIL NADU       606060              
 
SQL> COLUMN OID FORMAT A25 
SQL> SELECT SNAME, REF(A) OID 
  2  FROM STUDENTOV; 
SELECT SNAME, REF(A) OID 
                  * 
ERROR at line 1: 
ORA-00904: "A": invalid identifier  
 
 
SQL> ED 
Wrote file afiedt.buf 
 
  1  SELECT SNAME, REF(A) OID 
  2* FROM STUDENTOV A 
SQL> / 
 
SNAME     OID                                                                    
--------- -------------------------                                              
SAMPATH   00004A038A0046A4805E25E10                                              
          5476690852B320CDCBAE00000                                              
          0014260100010001002900000                                              
         00000090606002A00078401FE                                              
          0000000A02C20D00000000000                                              
          0000000000000000000000000                                              
          0000                                                                   
                                                                                 
SRINIVAS  00004A038A0046A4805E25E10                                              
          5476690852B320CDCBAE00000                                              
          0014260100010001002900000                                              
 
SNAME     OID                                                                    
--------- -------------------------                                              
          00000090606002A00078401FE                                              
          0000000B03C20D02000000000                                              
          0000000000000000000000000                                              
          0000                                                                   
                                                                                 
SHIVAJI   00004A038A0046A4805E25E10                                              
          5476690852B320CDCBAE00000                                              
          0014260100010001002900000                                              
          00000090606002A00078401FE                                              
          0000000B03C20D03000000000                                              
          0000000000000000000000000                                              
 
SNAME     OID                                                                    
--------- -------------------------                                              
          0000                                                                   
SQL> cl scr 
 
SQL> SELECT 
  2  StudID, 
  3  LibTranNo, BookTitle, LendingDate 
  4  FROM StudentBooks; 
 
STUDID LIBTRANNO BOOKTITLE            LENDINGDA                                  
------ --------- -------------------- ---------                                  
  1200      2000 THERMO DYNAMICS      03-AUG-10                                  
  1200      2001 FLUID MECHANICS      03-AUG-10                                  
  1200      2002 ATOMIC PHYSICS       03-AUG-10                                  
  1201      2003 STRUCTURAL DYNAMICS  03-AUG-10                                  
  1201      2004 AVIATION THEORY      03-AUG-10                                  
  1202      2005 PROPULSION THEORY    03-AUG-10                                  
 
6 rows selected. 
 
SQL> CREATE VIEW StudentBooksOV 
  2  AS 
  3  SELECT 
  4  MAKE_REF(StudentOV, StudID) StudID, 
  5  LibTranNo, BookTitle, LendingDate 
  6  FROM StudentBooks; 
 
View created. 
 
SQL> DESC StudentBooksOV 
Name                                      Null?    Type 
 ----------------------------------------- -------- ---------------------------- 
 STUDID                                             REF OF STUDENTTYPE 
   STUDID                                           NUMBER(6) 
   SNAME                                            VARCHAR2(30) 
   STREET                                           VARCHAR2(40) 
   CITYNAME                                         VARCHAR2(25) 
   STATENAME                                        VARCHAR2(40) 
   PINCODE                                          NUMBER(6) 
 LIBTRANNO                                 NOT NULL NUMBER(6) 
 BOOKTITLE                                          VARCHAR2(50) 
 LENDINGDATE                                        DATE 
 
SQL> COLUMN LibTranNo FORMAT 9999 
SQL> COLUMN StudID FORMAT A20 
SQL> COLUMN BookTitle FORMAT A20 
SQL> SELECT * 
  2  FROM StudentBooksOV; 
 
STUDID               LIBTRANNO BOOKTITLE            LENDINGDA                    
-------------------- --------- -------------------- ---------                    
00004A038A0046A4805E      2000 THERMO DYNAMICS      03-AUG-10                    
25E105476690852B320C                                                             
DCBAE000000014260100                                                             
01000100290000000000                                                             
090606002A00078401FE                                                             
0000000A02C20D000000                                                             
00000000000000000000                                                             
00000000000000                                                                   
                                                                                 
00004A038A0046A4805E      2001 FLUID MECHANICS      03-AUG-10                    
25E105476690852B320C                                                             
 
STUDID               LIBTRANNO BOOKTITLE            LENDINGDA                    
-------------------- --------- -------------------- ---------                    
DCBAE000000014260100                                                             
01000100290000000000                                                             
090606002A00078401FE                                                             
0000000A02C20D000000                                                             
00000000000000000000                                                             
00000000000000                                                                   
                                                                                 
00004A038A0046A4805E      2002 ATOMIC PHYSICS       03-AUG-10                    
25E105476690852B320C                                                             
DCBAE000000014260100                                                             
01000100290000000000                                                             
 
STUDID               LIBTRANNO BOOKTITLE            LENDINGDA                    
-------------------- --------- -------------------- ---------                    
090606002A00078401FE                                                             
0000000A02C20D000000                                                             
00000000000000000000                                                             
00000000000000                                                                   
                                                                                 
00004A038A0046A4805E      2003 STRUCTURAL DYNAMICS  03-AUG-10                    
25E105476690852B320C                                                             
 

DCBAE000000014260100                                                             
01000100290000000000                                                             
090606002A00078401FE                                                             
0000000B03C20D020000                                                             
 
STUDID               LIBTRANNO BOOKTITLE            LENDINGDA                    
-------------------- --------- -------------------- ---------                    
00000000000000000000                                                             
00000000000000                                                                   
                                                                                 
00004A038A0046A4805E      2004 AVIATION THEORY      03-AUG-10                    
25E105476690852B320C                                                             
DCBAE000000014260100                                                             
01000100290000000000                                                             
090606002A00078401FE                                                             
0000000B03C20D020000                                                             
00000000000000000000                                                             
00000000000000                                                                   
 
STUDID               LIBTRANNO BOOKTITLE            LENDINGDA                    
-------------------- --------- -------------------- ---------                    
                                                                                 
00004A038A0046A4805E      2005 PROPULSION THEORY    03-AUG-10                    
25E105476690852B320C                                                             
DCBAE000000014260100                                                             
01000100290000000000                                                             
090606002A00078401FE                                                             
0000000B03C20D030000                                                             
00000000000000000000                                                             
00000000000000                                                                   
6 rows selected. 
 
SQL> SELECT DEREF(SB.StudID) 
  2  FROM StudentBooksOV SB 
  3  WHERE BookTitle = 'THERMO DYNAMICS'; 
 
DEREF(SB.STUDID)(STUDID, SNAME, STREET, CITYNAME, STATENAME, PINCODE)            
-------------------------------------------------------------------------------- 
STUDENTTYPE(1200, 'SAMPATH', 'SHIV BAGH', 'HYDERABAD', 'ANDHRA PRADESH', 506001) 
 
SQL> SELECT 
  2   LibTranNO, 
  3   BookTitle, 
  4   LendingDate, 
  5   DEREF(SB.StudID) StudentData 
  6  FROM StudentBooksOV SB 
  7  WHERE BookTitle = 'THERMO DYNAMICS'; 
 
LIBTRANNO BOOKTITLE            LENDINGDA                                         
--------- -------------------- ---------                                         
STUDENTDATA(STUDID, SNAME, STREET, CITYNAME, STATENAME, PINCODE)                 
-------------------------------------------------------------------------------- 
     2000 THERMO DYNAMICS      03-AUG-10                                         
STUDENTTYPE(1200, 'SAMPATH', 'SHIV BAGH', 'HYDERABAD', 'ANDHRA PRADESH', 506001) 
 
SQL> cl scr 
 
SQL> SELECT 
  2   StudentBooksData.StudentData.StudID StudID, 
  3   StudentBooksData.StudentData.Sname Sname, 
  4   LibTranNO, 
  5   BookTitle, 
  6   LendingDate 
  7  FROM (SELECT 
  8   LibTranNO, 
  9   BookTitle, 
 10   LendingDate, 
 11   DEREF(SB.StudID) StudentData 
 12   FROM StudentBooksOV SB 
 13   ) StudentBooksData; 
 
    STUDID SNAME     LIBTRANNO BOOKTITLE            LENDINGDA                    
---------- --------- --------- -------------------- ---------                    
########## SAMPATH        2002 ATOMIC PHYSICS       03-AUG-10                    
########## SAMPATH        2001 FLUID MECHANICS      03-AUG-10                    
########## SAMPATH        2000 THERMO DYNAMICS      03-AUG-10                    
########## SRINIVAS       2004 AVIATION THEORY      03-AUG-10                    
########## SRINIVAS       2003 STRUCTURAL DYNAMICS  03-AUG-10                    
########## SHIVAJI        2005 PROPULSION THEORY    03-AUG-10                    
 
6 rows selected. 
 
SQL> COLUMN STUDID FORMAT 9999 
SQL> / 
 
STUDID SNAME     LIBTRANNO BOOKTITLE            LENDINGDA                        
------ --------- --------- -------------------- ---------                        
  1200 SAMPATH        2002 ATOMIC PHYSICS       03-AUG-10                        
  1200 SAMPATH        2001 FLUID MECHANICS      03-AUG-10                        
  1200 SAMPATH        2000 THERMO DYNAMICS      03-AUG-10                        
  1201 SRINIVAS       2004 AVIATION THEORY      03-AUG-10                        
  1201 SRINIVAS       2003 STRUCTURAL DYNAMICS  03-AUG-10                        
  1202 SHIVAJI        2005 PROPULSION THEORY    03-AUG-10                        
 
6 rows selected. 
 
SQL> SPOOL OFF   

Go Back