Top 80 + SQL Query Interview Questions and Answers with Examples

    601 Votes


SQL Interview Questions on "SQL Table Scripts"

71. Write create table syntax for employee table
Oracle -CREATE TABLE EMPLOYEE (
EMPLOYEE_ID NUMBER,
FIRST_NAME VARCHAR2(20 BYTE),
LAST_NAME VARCHAR2(20 BYTE),
SALARY FLOAT(126),
JOINING_DATE TIMESTAMP (6) DEFAULT sysdate,
DEPARTMENT VARCHAR2(30 BYTE) )
SQL Server -CREATE TABLE EMPLOYEE(
EMPLOYEE_ID int NOT NULL,
FIRST_NAME varchar(50) NULL,
LAST_NAME varchar(50) NULL,
SALARY decimal(18, 0) NULL,
JOINING_DATE datetime2(7) default getdate(),
DEPARTMENT varchar(50) NULL)

 

72. Write syntax to delete table employee

DROP table employee;

73. Write syntax to set EMPLOYEE_ID as primary key in employee table

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID)

74. Write syntax to set 2 fields(EMPLOYEE_ID,FIRST_NAME) as primary key in employee table

ALTER TABLE EMPLOYEE add CONSTRAINT EMPLOYEE_PK PRIMARY KEY(EMPLOYEE_ID,FIRST_NAME)

75. Write syntax to drop primary key on employee table

Alter TABLE EMPLOYEE drop CONSTRAINT EMPLOYEE_PK;

76. Write Sql Syntax to create EMPLOYEE_REF_ID in INCENTIVES table as foreign key with respect to EMPLOYEE_ID in employee table

ALTER TABLE INCENTIVES ADD CONSTRAINT INCENTIVES_FK FOREIGN KEY (EMPLOYEE_REF_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)

77. Write SQL to drop foreign key on employee table

ALTER TABLE INCENTIVES drop CONSTRAINT INCENTIVES_FK;

78. Write SQL to create Orcale Sequence

CREATE SEQUENCE EMPLOYEE_ID_SEQ START WITH 0 NOMAXVALUE MINVALUE 0 NOCYCLE NOCACHE NOORDER;

79. 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;

80. Oracle Procedure81. 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

82. 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

83. 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

84. What is SQL Injection ?

SQL Injection is one of the the techniques uses by hackers to hack a website by injecting SQL commands in data fields.
Page 7 of 7

Related Articles

Database & SQL Interview Questions
Oracle Interview Questions and Answers on SQL Queries and Database Theory
Advanced SQL Interview Questions and Answers
SQL Server Interview Questions and Answers

Popular Videos

How to speak to people

How to speak so that people want to listen.

Got a tip or Question?
Let us know