Posts Tagged ‘db’

Counting DISTINCT over multiple columns in SQL

My database contents

ID CLASS_CODE ROLL_NO NAME
1 IX 1 Jijo K Jose
2 IX 3 James Jacob
3 X 1 Joy Mathew
4 X 2 Sam George
5 IX 2 Miller John
6 X 3 Thomas John

Display values of distinct rows over one column

select distinct(ROLL_NO) from STUDENT;

ROLL_NO  
1
3
2

Read more »

PL/SQL Procedure to INSERT values with EXCEPTION handling

By using PL/SQL we can execute multiple queries at a time. The following example is used to insert student details from STUDENT_TEMP table to STUDENT table and if any error occurred during the insertion process ( like primary key violation, unique key violation etc ) then the corresponding errors will be captured by the EXCEPTION block and will continue the execution.

create or replace
PROCEDURE STUDENT_ADD_PL AS
BEGIN
    FOR REC IN
    (
    SELECT    
        STUDENT_ID,
        ROLL_NO,
        STUDENT_CODE,
        CLASS_ID,
        COLLEGE_NAME,
        PLACE
    FROM STUDENT_TEMP
    )
    LOOP
        BEGIN
            Insert into STUDENT
            (
                STUDENT_ID,
                ROLL_NO,
                STUDENT_CODE,
                CLASS_ID,
                COLLEGE_NAME,
                PLACE

Read more »

PL/SQL Trigger 9 – Trigger Having Conditions Example

Create trigger which prevent insertion/updation of EMPNO in EMP_<employee_id> table (Triggered only if inserting empno is greater than 1000)

Answer

CREATE OR REPLACE TRIGGER bf1_empp
BEFORE INSERT ON EMPPP_a5518
FOR EACH ROW
WHEN (NEW.EMPNO > 1000)
declare
    tot number(5);
BEGIN
    
    :NEW.TSAL:=:NEW.BSAL + :NEW.ALLW;
    
END bf1_empp;
/

 

Read more »