SQL interview questions and answers for freshers

What is SQL?

SQL is a Structured Query Language designed for inserting and modifying a relational 

database management system.

 What is the difference between the “DELETE” and “TRUNCATE” commands?

1. The DELETE command is used to remove rows from a table based on a WHERE 

condition whereas TRUNCATE removes all rows from a table. 

2. So we can use a where clause with DELETE to filter and delete specific records 

whereas we cannot use a Where clause with TRUNCATE. 

3. DELETE is executed using a row lock, each row in the table is locked for deletion 

whereas TRUNCATE is executed using a table lock and the entire table is locked for 

removal of all records. 

4. DELETE is a DML command whereas TRUNCATE is a DDL command. 

5. DELETE retains the identity of the column value whereas, in TRUNCATE, the Identify 

the column is reset to its seed value if the table contains an identity column. 

6. To use Delete you need DELETE permission on the table whereas to use Truncate on 

a table you need at least ALTER permission on the table. 

7. DELETE uses more transaction space than the TRUNCATE statement whereas 

Truncate uses less transaction space than the DELETE statement. 

8. DELETE can be used with indexed views whereas TRUNCATE cannot be used with 

indexed views. 

9. The DELETE statement removes rows one at a time and records an entry in the 

the transaction log for each deleted row whereas TRUNCATE TABLE removes the data 

by deallocating the data pages used to store the table data and recording only the page 

deallocations in the transaction log. 

10. Delete activates a trigger because the operation is logged individually whereas 

TRUNCATE TABLE can’t activate a trigger because the operation does not log 

individual row deletions.

What is the difference between the “WHERE” clause and the “HAVING” clause?

1. WHERE clause can be used with a Select, Update and Delete Statement Clause but 

the HAVING clause can be used only with a Select statement. 

2. We can’t use aggregate functions in the WHERE clause unless it is in a sub-query 

contained in a HAVING clause whereas we can use an aggregate function in the 

HAVING clause. We can use a column name in the HAVING clause but the column 

must be contained in the group by clause. 

3. WHERE is used before the GROUP BY clause whereas a HAVING clause is used to 

impose a condition on the GROUP Function and is used after the GROUP BY clause in 

the query. 

4. A WHERE clause applies to each and every row whereas a HAVING clause applies to 

summarized rows (summarized with GROUP BY). 

5. In the WHERE clause the data that is fetched from memory depending on a 

condition whereas in HAVING the completed data is first fetched and then separated 

depending on the condition.

What is the difference between “Primary Key” and “Unique Key”?

1. We can have only one Primary Key in a table whereas we can have more than one 

Unique Key in a table. 

2. The Primary Key cannot have a NULL value whereas a Unique Key may have only 

one null value. 

3. By default, a Primary Key is a Clustered Index whereas, by default, a Unique Key is a 

unique non-clustered index. 

4. A Primary Key supports an Auto Increment value whereas a Unique Key doesn’t 

support an Auto Increment value.

What are super, primary, candidate, and foreign keys?

Ans: A super key is a set of attributes of a relation schema upon which all attributes of the 

schema are functionally dependent. No two rows can have the same value of super key 

attributes.

A Candidate key is a minimal super key, i.e., no proper subset of Candidate key attributes 

can be a super key.

A Primary Key is one of the candidate keys. One of the candidate keys is selected as the most 

important and becomes the primary key. There cannot be more than one primary key in a 

table.

A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of 

another table.

What is the difference between primary key and unique constraints?

Ans: Primary key cannot have a NULL value, the unique constraints can have NULL values. 

There is only one primary key in a table, but there can be multiple uniques constraints.

 What is database normalization?

Ans: It is a process of analyzing the given relation schemas based on their functional 

dependencies and primary keys to achieving the following desirable properties:

1)Minimizing Redundancy

2) Minimizing the Insertion, Deletion, And Update Anomalies

Relation schemas that do not meet the properties are decomposed into smaller relation 

schemas that could meet desirable properties.

What are the differences between DDL, DML, and DCL in SQL?

Ans: Following are some details of the three.

DDL stands for Data Definition Language. SQL queries like CREATE, ALTER, DROP and 

RENAME come under this.

DML stands for Data Manipulation Language. SQL queries like SELECT, INSERT and 

UPDATE come under this.

DCL stands for Data Control Language. SQL queries like GRANT and REVOKE come under 

this.

What is the difference between having and where clause?

Ans: HAVING is used to specify a condition for a group or an aggregate function used in 

the select statement. The WHERE clause selects before grouping. The HAVING clause selects 

rows after grouping. Unlike the HAVING clause, the WHERE clause cannot contain aggregate 

functions. 

What is Join? 

Ans: An SQL Join is used to combine data from two or more tables, based on a common 

field between them. For example, consider the following two tables.

What is a view in SQL? How to create one

Ans: A view is a virtual table based on the result-set of an SQL statement. We can create 

using create view syntax.

CREATE VIEW view_name AS

SELECT column_name(s)

FROM table_name

WHERE condition

What are the uses of view?

1. Views can represent a subset of the data contained in a table; consequently, a view can 

limit the degree of exposure of the underlying tables to the outer world: a given user may 

have permission to query the view, while denied access to the rest of the base table.

What is a Trigger?

Ans: A Trigger is a code that is associated with insert, update or delete operations. The code 

is executed automatically whenever the associated query is executed on a table. Triggers 

can be useful to maintain integrity in the database.

What is a stored procedure?

Ans: A stored procedure is like a function that contains a set of operations compiled 

together. It contains a set of operations that are commonly used in an application to do 

some common database tasks.

What is the difference between Trigger and Stored Procedure?

Ans: Unlike Stored Procedures, Triggers cannot be called directly. They can only be 

associated with queries.

 What is a transaction? What are ACID properties?

Ans: A Database Transaction is a set of database operations that must be treated as a whole, 

means either all operations are executed or none of them.

An example can be a bank transaction from one account to another account. Either both debit 

and credit operations must be executed or none of them.

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee 

that database transactions are processed reliably.

 What are indexes? 

Ans: A database index is a data structure that improves the speed of data retrieval 

operations on a database table at the cost of additional writes and the use of more storage 

space to maintain the extra copy of data.

Data can be stored only in one order on a disk. To support faster access according to 

different values, a faster search like a binary search for different values is desired, For this 

purpose, indexes are created on tables. These indexes need extra space on the disk, but they 

allow faster search according to different frequently searched values.

What are clustered and non-clustered Indexes?

Ans: Clustered indexes are the index according to which data is physically stored on a disk. 

Therefore, only one clustered index can be created on a given database table.

Non-clustered indexes don’t define the physical ordering of data, but logical ordering. 

Typically, a tree is created whose leaf point to disk records. B-Tree or B+ tree are used for 

this purpose.

What are the different types of SQL or different commands in SQL?

Ans: Frequently asked SQL Interview Questions

1. DDL – Data Definition Language.DDL is used to define the structure that holds the data. 

2. DML– Data Manipulation Language

DML is used for the manipulation of the data itself. Typical operations are Insert, Delete, Update 

and retrieving the data from the table 

3.DCL–DataControlLanguage 

DCL is used to control the visibility of data by granting database access and setting privileges 

to create tables etc.

4.TCL-TransactionControl Language

It contains

 What are the Advantages of SQL?

1. SQL is not a proprietary language used by specific database vendors. Almost every 

major DBMS supports SQL, so learning this one language will enable programmers to 

interact with any database like ORACLE, SQL, MYSQL, etc.

2. SQL is easy to learn. The statements are all made up of descriptive English words, and 

there aren’t that many of them.

3. SQL is actually a very powerful language and by using its language elements you can 

perform very complex and sophisticated database operations

what is a field in a database?

A field is an area within a record reserved for a specific piece of data. 

Examples: Employee Name, Employee ID, etc

What is a Record in a database?

A record is the collection of values/fields of a specific entity: i.e. an Employee, Salary, etc. 

 What is a Table in a database?

A table is a collection of records of a specific type. For example, employee table, salary 

table etc.

What is a database transaction?

Database transactions take the database from one consistent state to another. At the end of the 

transaction the system must be in the prior state if the transaction fails or the status of the 

the system should reflect the successful completion of the transaction goes through.

What are the properties of a transaction?

Properties of the transaction can be summarized as ACID Properties. 

1. Atomicity

A transaction consists of many steps. When all the steps in a transaction get completed, it 

will get reflected in DB or if any step fails, all the transactions are rolled back.

2. Consistency

The database will move from one consistent state to another if the transaction succeeds 

and remain in the original state if the transaction fails.

3. Isolation

Every transaction should operate as if it is the only transaction in the system

4. Durability

Once a transaction has been completed successfully, the updated rows/records must be 

available for all other transactions on a permanent basis

 What is a Database Lock?

Database lock tells a transaction if the data item in question is currently being used by 

other transactions.