School of Technology, Innovation and Research (TIR)

Trigger Case Study  CREATE TABLE LOG_TRIG_TABLE (USER_ID VARCHAR2(8), LOG_DATE DATE, ACTION VARCHAR2(20)); CREATE TABLE LOG_TRIG_TABLE_ALL (USER_ID VARCHAR2(8), LOG_DATE DATE, ACTION VARCHAR2(20)); CREATE TABLE DML_LOG_TRIG_TABLE (USER_NAME VARCHAR2(8), DML_DATE DATE, DML_TYPE VARCHAR2(8)); -------------------  TRACK BEFORE LOGON  CREATE OR REPLACE TRIGGER LOGON_TRIG AFTER LOGON ON SCHEMA BEGIN INSERT INTO  LOG_TRIG_TABLE  (USER_ID,LOG_DATE, ACTION) VALUES (USER,SYSDATE,'LOGGING ON '); END; / CREATE OR REPLACE TRIGGER LOGON_TRIG_ALL AFTER LOGON ON DATABASE BEGIN INSERT INTO  LOG_TRIG_TABLE_ALL  (USER_ID,LOG_DATE, ACTION) VALUES (USER,SYSDATE,'LOGGING ON '); END; / -------------------  TRACK BEFORE LOGOFF  CREATE OR REPLACE TRIGGER LOGOFF_TRIG BEFORE LOGOFF ON SCHEMA BEGIN INSERT INTO  LOG_TRIG_TABLE  (USER_ID,LOG_DATE, ACTION) VALUES (USER,SYSDATE,'LOGGING OFF '); END; /    -- Update CREATE OR REPLACE TRIGGER update_trig AFTER UPDATE ON student

  Cursor Practice What is PL/SQL Cursor? The purpose of the cursor is to make a quarry in a table of a database and change (DML) the table column of a database. -- Lab Practice DROP TABLE student; CREATE TABLE student (roll NUMBER(6) PRIMARY KEY, name VARCHAR(20), sex CHAR(1), sub1 NUMBER(2), sub2 NUMBER(2), total NUMBER(3), result CHAR(2)); -- Data insert into student table INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100000,'RASHID','0',45,56); INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100001,'Md. Abdul Khalek','0',65,56); INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100002,'Most. Dilruba Aktar','1',95,56);   INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100003,'RASHID','0',45,56); INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100004,'Md. Abdul Khalek','0',65,56); INSERT INTO student (roll,name,sex,sub1,sub2)VALUES(100005,'Shahina Sultana','1',95,66); IN

What is Data Manipulation Language (DML)? DML is referred to as a programming language that performs SELECT, INSERT, UPDATE, and DELETE operations. DML is some kind of command that manipulate data of oracle database tables.  Command Examples are- PGDICT4D Batch 13 /* --select 5*9 from dual; -- Table creation create table student (st_roll number(4) primary key, st_name varchar2(20), st_group char(1), st_beng number(3), st_eng number(3), st_math number(3), st_total number(3)); select * from tab; */ insert into student (st_roll,st_name,st_group,st_beng,st_eng,st_math) VALUES (1000,'Md. Abdur Rashid','S',42,90,100); insert into student (st_roll,st_name,st_group,st_beng,st_eng,st_math) VALUES (1001,'Md. Adur Salam','S',42,70,100); insert into student (st_roll,st_name,st_group,st_beng,st_eng,st_math) VALUES (1002,'Md. Abdur Gaful','S',42,70,100); insert into student (st_roll,st_name,st_group,st_beng,st_eng,st_math) VALUES (1003,'Mohsena'

  -- Database Objects Lists: Tables store our data are given as input to the oracle database. Very powerful and secured data storage devise in the world. Data is stored in the tables in a row and column format. First list the names of fields that are needed to design as required. Then create a table.  --Table Creation Sql> create table student (roll number(6), name varchar2(20), sex char(1),CONSTRAINT STUDENT_ROLL_PK PRIMARY KEY (ROLL)); Sql> desc tablename; Sql>commit; Sql> drop  table student; Sql>commit; --Table Creation Sql>  CREATE TABLE dept     (deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,             dname VARCHAR2(14) ,             loc VARCHAR2(13) ) ;  --Master Table CREATE TABLE DEPT        (deptno NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,             DNAME VARCHAR2(14) ,             LOC VARCHAR2(13) ) ; -- Detail Table Sql>DROP TABLE EMP; Sql>CREATE TABLE EMP        (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,             ENAME VARCHAR2(10),

  Controlling User Access •         Two administrative user accounts are automatically created when Oracle Database is installed: •         SYS  (default password: CHANGE_ON_INSTALL) •         SYS •         When you create an Oracle database, the use SYS is automatically created and granted the DBA role. •         Don’t do anything using SYS user. Read the following for details.  NB: All the base tables and views are stored in the database in the SYS schema. No user will be a changer and handle anything by SYS user. The base tables and views are very critical for the operation of the oracle database. To maintain the integrity of the data dictionary, No one will disturb the SYS schema. No one should create any table using the schema of the SYS user. •    SYSTEM  (default password: MANAGER) •         User •         SYS (Default) also treated as superuser, have all control to oracle database •         SYSTEM (Default) is very powerful and has dba power •         RASHID (Additional user s

Compute a new variable from an existing variable Say,  Saving = Income – Expenditure Say, BMI = (weight / Height 2 ) Find BMI of a family- ü   BMI Formula = Weight in Kg / (Height in meter) 2 ü    Enter bmi data as follows below. Give the height and weight of a family member Transform-> Compute Variable-> Give new variable name say BMI-> Give your formula>OK We got a new Colum i.e. BMI with calculated data. See data window. Doctors can advise now following the obtained bmi column data.  Key Findings and Result: The researcher explored from the following image that 4 respondents' bmi was above 25.  w variable using existing variables Md. Abdur Rashid