Home » Technical Interview Questions » SQL Interview Questions » SQL Query Interview Questions

SQL Query Interview Questions


Reading Time - 2 mins

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.

READ  Privileges and Roles in SQL
Array Interview Questions
Graph Interview Questions
LinkedList Interview Questions
String Interview Questions
Tree Interview Questions