Categories
Uncategorized

Trigger (cIA 8)

Write an after statement-level trigger. Whenever an insert, update, or delete operation occurs on the EMP table, a row is added to the employee audit table recording the date, user, and action. create or replace trigger audit1_trigger after insert or delete or update on emp declare v_action varchar2(18); begin if inserting then v_action:=’added emp’; elsif […]

Categories
Uncategorized

Pl/sql query(cIA5)

1. Create a PL/SQL block that selects the maximum department number in the DEPARTMENTS table and stores it in a variable. Print the results to the screen. Solution, declare V_max department.deptno%type; begin select max(deptno) into V_max from department; dbms_Output.put_line(V_max); end; / 3. Write a PL/SQL block which accepts employee name, basic and should displayEmployee name, […]

Categories
Uncategorized

PL/SQL Query

1. Create Pl/Sql block to delete the department created. print to the scree the number of rows affected.   set serveroutput on; declare V_count number(3); begin select count(*) into v_count from employee where deptno=(select max(deptno) from department); delete from employee where deptno=(select max(deptno) from department); dbms_output.put_line(‘no of rows deleted:’||V_count); end; / 2.  Write a PL/SQL […]

Categories
Uncategorized

Query(3)

1. Perform below specified DDL operations: Create a table of all the students attending this class which contains their student_id, name, department code and email id. The department code should be a valid one having reference in the existing dept table in the college schema.Ensure that the email id of all the students should be […]

Categories
Uncategorized

DBMS Query

CIA 1 and 2 Tables to be used: student (student_id, sname,deptno,dob,email) colldept(deptno, dname,hod) faculty(facultyid,fname, deptno,designation, salary) employee(empid, name,job hiredate,salary,deptno,mgrid,age) department(deptno,dname,loc)