SQL Interview Questions:

1. What are ACID properties in a transaction

Answer: In order to maintain consistency in a database ‘before and after’ transactions,

 Atomicity: This means the transaction must happen fully and cannot be left midway.
 Consistency: To maintain integrity constraints hence valid data enters the database
 Isolation: Controls Concurrency
 Durability: Once a transaction is committed it remains committed

2. What is de-normalization, and when do you go for it?

Answer: De-normalization is a technique sometimes used to improve performance so the

3. What is a primary key, a foreign key, and unique key

The primary key is a field in the table which uniquely identifies a row. It cannot be NULL

A foreign key is a field in one table, which is a primary key in another table. A relationship is
created between the two tables by referencing the foreign key of one table with the primary
key of another table.

Unique Key uniquely identifies a record in a table. There can be many unique key
constraints defined on a table.

4.Can we use TRUNCATE with a WHERE clause?

Answer: No, we cannot use TRUNCATE with the WHERE clause.

5. How do you maintain database integrity where deletions from one table will
automatically cause deletions in another table?

Answer: ON DELETE CASCADE is a command that is used when deletions happen in the
parent table, and all child records are automatically deleted, and the child table is referenced
by the foreign key in the parent table.

6. What is a schema?

Answer: A schema is a collection of database objects in a database for a particular

7. How can you create an empty table from an existing table?

CREATE TABLE NEW_TABLE_NAME AS SELECT [column1, column2 ……column]

8. What is a composite key?

Answer: When more than one column is used to define the primary key, it is called a
composite key.

9. What is the role of GRANT and REVOKE commands?

Answer: The GRANT command enables privileges on the database objects and the
REVOKE command removes them. They are DCL commands

10.Does the data stored in the stored procedure increase access time or execution
time? Explain

Answer: Data stored in stored procedures can be retrieved much faster than the data stored
in the SQL database. Data can be precompiled and stored in stored procedures. This
reduces the time gap between query and compiling as the data has been pre-compiled and
stored in the procedure.

11. What is data integrity?

Data integrity defines the accuracy, consistency, and reliability of data that is stored in the
database.

There are four kinds of data integrity:

1. Row integrity
2. Column integrity
3. Referential integrity
4. User-defined integrity

12. What is the TRUNCATE command? How is it different from the DELETE
command?

DELETE TRUNCATE

DML command DDL command

We can use WHERE clause We cannot use WHERE clause

Deletes a row from the table. Deletes all rows from the table.

We can rollback. We cannot rollback.

13. Distinguish between BETWEEN and IN conditional operators.

BETWEEN- Displays the rows based on range of values

IN- Checks for values contained in a specific set of values.

14. What is the MERGE statement?

15. What do you understand by Auto Increment?

AUTO_INCREMENT is used in SQL to automatically generate a unique number whenever a

Since the primary key is unique for each record, we add this primary field as the
AUTO_INCREMENT field so that it is incremented when a new record is inserted.

The AUTO-INCREMENT value is by default starts from 1 and incremented by 1 whenever a

16 What is a stored procedure? Give an example.

A stored procedure is a prepared SQL code that can be saved and reused. In other words,
we can consider a stored procedure to be a function consisting of many SQL statements to
access the database system. We can consolidate several SQL statements into a stored
procedure and execute them whenever and wherever required.

A stored procedure can be used as a means of modular programming, i.e., we can create a
stored procedure once, store it, and call it multiple times as required. This also supports
faster execution when compared to executing multiple queries.

17. What do you understand by a Temporary Table?

A temporary table helps us store and process intermediate results. These temporary tables
are created and can be automatically deleted when they are no longer used. They are very
useful in places where we need to store temporary data.

18. What do you understand by Self Join?

Self-Join in SQL is used for joining a table with itself. Here, depending upon some
conditions, each row of the table is joined with itself and with other rows of the table.

19. Can you identify the employee who is having the third-highest salary from the
given Employee table (with salary-related data)?

20. Table is as follows:

ID C1 C2 C3
1 Red Yellow Blue
2 NULL Red Green
3 Yellow NULL Violet

Print the rows which have ‘Yellow’ in one of the columns C1, C2, or C3, but without using
OR.

Answer:
SELECT * FROM table
WHERE 'Yellow' IN (C1, C2, C3)

21. Can we save images in a database and if yes, how?

BLOB stands for Binary Large Object, which is a data type that is generally used to hold
images, audio & video files or some binary executables.

22. What is meant by an index?

Answer: An index is a schema object, which is created to search the data efficiently within
the table. Indexes are usually created on certain columns of the table, which are accessed
the most. Indexes can be clustered or non-clustered.

23. What are the parameters that we can pass through a stored procedure?

Answer: We can pass IN, OUT & INOUT parameters through a stored procedure and they
should be defined while declaring the procedure itself.

24. What is a trigger and what are its types?

Answer: A trigger is a stored program which is written in such a way that it gets executed
automatically when some event occurs. This event can be any DML or a DDL operation.

PL/SQL supports two types of triggers:

 Row Level
 Statement Level

25. How will you distinguish a global variable with a local variable in PL/SQL?

Answer: Global variable is the one, which is defined at the beginning of the program and
survives until the end. It can be accessed by any methods or procedures within the program,
while the access to the local variable is limited to the procedure or method where it is
declared.

26. How will you distinguish a global variable with a local variable in PL/SQL?

Answer: A package is a group of related database objects like stored procs, functions,
types, triggers, cursors, etc. that are stored in the Oracle database. It is a kind of library of
related objects which can be accessed by multiple applications if permitted.

PL/SQL Package structure consists of 2 parts: package specification & package body.

27. What is the difference between the RANK() and DENSE_RANK() functions?
Provide an example.

The difference between the results shown by these 2 functions occur in case there is a tie.
When multiple values in the set have same ranking, then the RANK() function will assign
non-consecutive ranks to the values resulting in gaps. When DENSE_RANK() is used
consecutive ranks are assigned to values in the set.
For example in the set  {525,525, 550, 575, 575, 600. RANK() will return {1, 1, 3, 4, 4, 6} .
Since values 2 and 5 are skipped there are gaps. DENSE_RANK() on the other hand will
return {1, 1, 2, 3, 3, 4}.Here ranks are not skipped.

28. What is the difference between SUBSTR & INSTR functions?

Answer:

 SUBSTR function returns the sub-part identified by numeric values from the provided
string.
o For Example, [SELECT SUBSTR (‘India is my country’, 1, 4) from dual] will
return “Indi”.
 INSTR will return the position number of the sub-string within the string.
o For Example, [SELECT INSTR (‘India is my country’, ‘a’) from dual] will
return 5.

29. Explain different types of index.

There are three types of index namely:

Unique Index:

This index does not allow the field to have duplicate values if the column is unique indexed.
If a primary key is defined, a unique index can be applied automatically.

Clustered Index:

This index reorders the physical order of the table and searches based on the basis of key
values. Each table can only have one clustered index.

Non-Clustered Index:

Non-Clustered Index does not alter the physical order of the table and maintains a logical
order of the data. Each table can have many nonclustered indexes.

30. What is the difference between DROP and TRUNCATE commands?

DROP command removes a table and it cannot be rolled back from the database whereas
TRUNCATE command removes all the rows from the table.

31.What are aggregate and scalar functions?

Aggregate functions are used to evaluate mathematical calculation and returns a single value.
These calculations are done from the columns in a table. For example- max(),count() are
calculated with respect to numeric.
Scalar functions return a single value based on the input value. For example – UCASE(),
NOW() are calculated with respect to string.

32. How can you fetch alternate records from a table?

You can fetch alternate records i.e both odd and even row numbers. For example- To display
even numbers, use the following command:

Select studentId from (Select rowno, studentId from student) where mod(rowno,2)=0

Now, to display odd numbers:

Select studentId from (Select rowno, studentId from student) where

33. How to get 3 Max salaries ?

34. Write an SQL query to fetch records that are present in one table but not in another
table.
Ans. Using MINUS- operator-

SELECT * FROM EmployeeSalary

SQL Query related questions: