SQL Query Interview Questions

Table of Contents

91. Write Sql syntax to create Oracle Trigger before insert of each row in employee table?

CREATE OR REPLACE TRIGGER EMPLOYEE_ROW_ID_TRIGGER
    BEFORE INSERT ON EMPLOYEE FOR EACH ROW
DECLARE
    seq_no number(12);
BEGIN
    select EMPLOYEE_ID_SEQ.nextval into seq_no from dual ;
    :new EMPLOYEE_ID :=seq_no;
END;
SHOW ERRORS;

92. Oracle View?

An example oracle view script is given below:

CREATE   VIEW Employee_Incentive AS
  SELECT  FIRST_NAME,
         MAX(INCENTIVE_AMOUNT) INCENTIVE_AMOUNT
FROM   EMPLOYEE a,
  INCENTIVES b
WHERE   a.EMPLOYEE_ID=b.EMPLOYEE_REF_ID
GROUP BY
  FIRST_NAME;

 93. Oracle materialized view – Daily Auto Refresh

CREATE MATERIALIZED VIEW EMPLOYEE_INCENTIVE
REFRESH COMPLETE
START WITH SYSDATE
NEXT SYSDATE + 1 AS
SELECT FIRST_NAME,INCENTIVE_DATE,INCENTIVE_AMOUNT
    FROM EMPLOYEE A, INCENTIVES B
WHERE A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID;

94. Oracle materialized view – Fast Refresh on Commit?

Create materialized view log for fast refresh. Following materialized view script wont get executed if materialized view log doesn’t exists.

CREATE MATERIALIZED VIEW MAT_EMPLOYEE_INCENTIVE_REFRESH
BUILD IMMEDIATE
REFRESH FAST ON COMMIT AS
SELECT FIRST_NAME,MAX(INCENTIVE_AMOUNT) FROM EMPLOYEE A, INCENTIVES B
WHERE A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID GROUP BY FIRST_NAME;

95. What is SQL Injection ?

This is one of the techniques by which hackers hack the websites by injecting unwanted queries.

Pages: 1 2 3 4 5 6 7 8 9 10