SQL Query Interview Questions and Answers with Examples

59. Select first_name, incentive amount from employee and incentives table for those employees who have incentives

Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

60. Select first_name, incentive amount from employee and incentives table for those employees who have incentives and incentive amount greater than 3000

Select FIRST_NAME,INCENTIVE_AMOUNT from employee a inner join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID and INCENTIVE_AMOUNT >3000

61. Select first_name, incentive amount from employee and incentives table for all employes even if they didn't get incentives

Select FIRST_NAME,INCENTIVE_AMOUNT from employee a left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

62. Select first_name, incentive amount from employee and incentives table for all employees even if they didn't get incentives and set incentive amount as 0 for those employees who didn't get incentives.

SQL Queries in Oracle, Select FIRST_NAME,nvl(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

SQL Queries in SQL Server, Select FIRST_NAME, ISNULL(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

SQL Queries in MySQL, Select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee a left join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

63. Select first_name, incentive amount from employee and incentives table for all employees who got incentives using left join

SQL Queries in Oracle, Select FIRST_NAME,nvl(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

SQL Queries in SQL Server, Select FIRST_NAME, isnull(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

SQL Queries in MySQL, Select FIRST_NAME, IFNULL(INCENTIVE_AMOUNT,0) from employee a right join incentives B on A.EMPLOYEE_ID=B.EMPLOYEE_REF_ID

64. Select max incentive with respect to employee from employee and incentives table using sub query

SQL Queries in Oracle, select DEPARTMENT,(select nvl(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE

SQL Queries in SQL Server, select DEPARTMENT,(select ISNULL(max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE

SQL Queries in SQL Server, select DEPARTMENT,(select IFNULL (max(INCENTIVE_AMOUNT),0) from INCENTIVES where EMPLOYEE_REF_ID=EMPLOYEE_ID) Max_incentive from EMPLOYEE

"Top N Salary" SQL Interview Questions and Answers

65. Select TOP 2 salary from employee table

SQL Queries in Oracle, select * from (select * from employee order by SALARY desc) where rownum <3

SQL Queries in SQL Server, select top 2 * from employee order by salary desc

SQL Queries in MySQL, select * from employee order by salary desc limit 2

66. Select TOP N salary from employee table

SQL Queries in Oracle, select * from (select * from employee order by SALARY desc) where rownum <N + 1

SQL Queries in SQL Server, select top N * from employee

SQL Queries in MySQL, select * from employee order by salary desc limit N

67. Select 2nd Highest salary from employee table

SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum <3)

SQL Queries in SQL Server, select min(SALARY) from (select top 2 * from employee) a

SQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit 2) a

68. Select Nth Highest salary from employee table

SQL Queries in Oracle, select min(salary) from (select * from (select * from employee order by SALARY desc) where rownum <N + 1)

SQL Queries in SQL Server, select min(SALARY) from (select top N * from employee) a

SQL Queries in MySQL, select min(SALARY) from (select * from employee order by salary desc limit N) a

"SQL Union" Query Interview Questions

69. Select First_Name,LAST_NAME from employee table as separate rows

select FIRST_NAME from EMPLOYEE union select LAST_NAME from EMPLOYEE

70. What is the difference between UNION and UNION ALL ?

Both UNION and UNION ALL is used to select information from structurally similar tables. That means corresponding columns specified in the union should have same data type. For example, in the above query, if FIRST_NAME is DOUBLE and LAST_NAME is STRING above query wont work. Since the data type of both the columns are VARCHAR, union is made possible. Difference between UNION and UNION ALL is that , UNION query return only distinct values. 

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.