SQL/Oracle Essential Query

Update teacher table eiin from institute where intitute table institute_id equal teacher table institute_id

update teacher t set t.EIIN = (select eiin from institute i where I.INS_ID = T.INS_ID );

Update institute type(School/ College/ Madrasha)

update institute i set (I.INS_TYPE_SCM_LOOKUP , I.SCM_NAME) = ( select INSP.INS_TYPE_SCM_LOOKUP, INSP.SCM_NAME  from INSTITUTE_INSP insp  where I.EIIN = INSP.EIIN )

 

select distinct eiin from TEACHER_01 t where T.EIIN not   IN ( select distinct I.EIIN from institute i );

select   *  from TEACHER_01 t where T.EIIN not   IN ( select   I.EIIN from institute i );

select  * from institute i where i.eiin not in ( select t.eiin from teacher_01 t );

insert into teacher  select * from TEACHER_01  t where T.EIIN     IN ( select   I.EIIN from institute i) ;

CREATE A SEQUENCE

DROP SEQUENCE EMS1.TRAINING_PROJECT_SEQ;

 CREATE SEQUENCE EMS1.TRAINING_PROJECT_SEQ

  START WITH 1

  MAXVALUE 999999999999999999999999999

  MINVALUE 1

  NOCYCLE

  NOCACHE

  NOORDER;

CREATE A TRIGGER

DROP TRIGGER EMS1.TRAINING_PROJECT_TGR;

CREATE OR REPLACE TRIGGER EMS1.TRAINING_PROJECT_TGR

Before Insert

ON   TRAINING_PROJECT Referencing New As New Old As Old

For Each Row

Declare V_Val Number;

Begin

        Select  TRAINING_PROJECT_SEQ.NextVal into V_Val From Dual;

    If Inserting Then

        :New.PROJECT_NO := V_Val;

    End If;

End;

/

CREATE A STORE PROCEDURE

CREATE OR REPLACE PROCEDURE EMS1.SP_INSERT_TRAINING_TEST(  

                               V_ID    NUMBER,

                               V_NAME  NVARCHAR2

                              )

                              IS

BEGIN

INSERT INTO TRAINING_TEST  (

                                   ID,

                               NAME    

                               )  

                                VALUES (V_ID,

                                V_NAME  

);

END;

/

 

execute SP_INSERT_TRAINING_TEST(1, 'Assalamualaikum');

CREATE TABLE TRAINING_TEST

DROP TABLE EMS1.TRAINING_TEST CASCADE CONSTRAINTS;

CREATE TABLE EMS1.TRAINING_TEST

(

  ID    NUMBER,

  NAME  NVARCHAR2(90)

)

TABLESPACE USERS

PCTUSED    0

PCTFREE    10

INITRANS   1

MAXTRANS   255

STORAGE    (

            INITIAL          64K

            NEXT             1M

            MINEXTENTS       1

            MAXEXTENTS       UNLIMITED

            PCTINCREASE      0

            BUFFER_POOL      DEFAULT

           )

LOGGING

NOCOMPRESS

NOCACHE

NOPARALLEL

MONITORING;