Create view, table, procedure based on some business scenario

Create 2 views, 1 trigger, 2 stored procedures based on some situation.

1.) Create student  and fee table

2.) generate roll number procedure

3.) Insert Procedure

4.) Trigger to audit updatation of address

5.)  Create view to visualize student paid fee and along with student information.

6.)  Create view for old and new address

CREATE TABLE STUDENT_NEW(StudentId VARCHAR2(8), FirstName VARCHAR2(20), LastName VARCHAR2(20), Age NUMBER, Address VARCHAR2(50), 

Mobile NUMBER, Email VARCHAR2(100), CONSTRAINT PK_StudentId PRIMARY KEY (StudentId));

--drop table student_new

INSERT INTO STUDENT_NEW VALUES('00000001', 'Ravi','Shastri', 26, 'Red Cross Marg, Ulhasnagar, Mumbai', '9112340987','Ravi001@gmail.com')

To generate new roll no

Insert new student information

CREATE OR REPLACE PROCEDURE GENERATE_STUDENT_ID(pStudentId OUT VARCHAR2) AS

v_StudentId VARCHAR2(8);

BEGIN

SELECT MAX(STUDENTID) INTO v_StudentId  FROM STUDENT_NEW;

--DBMS_OUTPUT.PUT_LINE(v_StudentId+1);

v_StudentId := LPAD(TO_CHAR(v_StudentId+1),8,'0');

--DBMS_OUTPUT.PUT_LINE(v_StudentId);

pStudentId := v_StudentId;

EXCEPTION

WHEN NO_DATA_FOUND THEN

v_StudentId := '00000001';

--DBMS_OUTPUT.PUT_LINE(v_StudentId);

pStudentId := v_StudentId;

END;

DECLARE

V_SID VARCHAR2(8);

BEGIN

GENERATE_STUDENT_ID(V_SID);

DBMS_OUTPUT.PUT_LINE(V_SID);

END;

CREATE OR REPLACE PROCEDURE INSERT_RECORD(pFirstName VARCHAR2, pLastName VARCHAR2, Age NUMBER, 

Address VARCHAR2 DEFAULT NULL, Mobile NUMBER DEFAULT NULL, Email VARCHAR2 DEFAULT NULL) AS

v_StudentID VARCHAR(8);

BEGIN

GENERATE_STUDENT_ID(v_StudentID);

INSERT INTO STUDENT_NEW VALUES(v_StudentID,pFirstName, pLastName, Age, Address, Mobile, Email);

COMMIT;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE(SQLERRM);

END;

exec INSERT_RECORD('Kavi', 'Kant','24');

select * from student_new;

CREATE TABLE AUDIT_ADDREES(StudentId VARCHAR2(8), FirstName VARCHAR2(20), LastName VARCHAR2(20), Age NUMBER, OLD_Address VARCHAR2(50), NEW_Address VARCHAR2(50) );

CREATE OR REPLACE  TRIGGER AUDIT_RECORD 

AFTER UPDATE 

ON STUDENT_NEW

FOR EACH ROW

BEGIN

INSERT INTO AUDIT_ADDREES VALUES(StudentId,FirstName, LastName,Age, :OLD.OLD_Address,:NEW.Address );

END;

DROP TRIGGER AUDIT_RECORD