MOST FREQUENTLY ASKED SQL INTERVIEW QUESTIONS AND ANSWER
Table of Contents
ToggleQ1 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
column is reset to its seed value if the table contains any 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 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
transaction log for each deleted row whereas TRUNCATE TABLE removes the data
by deallocating the data pages used to store the table data and records 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
Q2 What is the difference between “Stored Procedure” and “Function”?
1 A procedure can have both input and output parameters, but a function can only
have input parameters.
2. Inside a procedure we can use DML (INSERT/UPDATE/DELETE) statements. But
inside a function we can’t use DML statements.
3. We can’t utilize a Stored Procedure in a Select statement. But we can use a function
in a Select statement.
4. We can use a Try-Catch Block in a Stored Procedure but inside a function we can’t
use a Try-Catch block.
5. We can use transaction management in a procedure but we can’t in a function.
6. We can’t join a Stored Procedure but we can join functions.
7. Stored Procedures cannot be used in the SQL statements anywhere in the
WHERE/HAVING/SELECT section. But we can use a function anywhere.
8. A procedure can return 0 or n values (max 1024). But a function can return only 1
value that is mandatory.
9. A procedure can’t be called from a function but we can call a function from a
procedure
q3 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.
Q4 What is the difference between a “Local Temporary Table” and “Global Temporary Table
A Local Temporary Table is created by giving it a prefix of # whereas a Global
Temporary Table is created by giving it a prefix of ##.
2. A Local Temporary Table cannot be shared among multiple users whereas a Global
Temporary Table can be shared among multiple users.
3. A Local Temporary Table is only available to the current DB connection for the
current user and are cleared when the connection is closed whereas a Global
Temporary Table is available to any connection once created. They are cleared when
the last connection is closed.
Q5 What are super, primary, candidate and foreign keys?
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 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 most
important and becomes the primary key. There cannot be more that one primary keys in a
table.
Foreign key is a field (or collection of fields) in one table that uniquely identifies a row of
another table.
Q 6.What is database normalization?
It is a process of analyzing the given relation schemas based on their functional
dependencies and primary keys to achieve 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.
Q7. What is SQL?
SQL is Structured Query Language designed for inserting and modifying in a relational database management system
Q8 What are the differences between DDL, DML and DCL in SQL?
Following are some details of 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.
Q7 What is Identity?
Identity (or AutoNumber) is a column that automatically generates numeric values. A
start and increment value can be set, but most DBA leave these at 1. A GUID column also
generates numbers; the value of this cannot be controlled. Identity/GUID columns do not
need to be indexed.
Q 8 What is a view in SQL? How to create one
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
Q 9 What is a Trigger?
A Trigger is a code that 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 database.
Q 10 What is a stored procedure?
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.
Q11 What is the difference between Trigger and Stored Procedure?
Unlike Stored Procedures, Triggers cannot be called directly. They can only be associated with queries.
Q 12 What is a transaction? What are ACID properties?
: A Database Transaction is a set of database operations that must be treated as whole,
means either all operations are executed or none of them.
An example can be 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
13 What are indexes?
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 disk. To support faster access according to
different values, faster search like binary search for different values is desired, For this
purpose, indexes are created on tables. These indexes need extra space on disk, but they
allow faster search according to different frequently searched values
14 What are clustered and non-clustered Indexes?
Clustered indexes is the index according to which data is physically stored on disk.
Therefore, only one clustered index can be created on a given database table.
Non-clustered indexes don’t define 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
15 What are the different type of SQL or different commands in SQL?
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 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 like granting database access and set privileges
to create table etc.
4.TCL-TransactionControl Language
It contains
16 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
17 What is a Composite Key ?
A Composite primary key is a type of candidate key, which represents a set of columns
whose values uniquely identify every row in a table.
For example – if “Employee_ID” and “Employee Name” in a table is combined to uniquely
identifies a row its called a Composite Key.
18 Define SQL Insert Statement ?
SQL INSERT statement is used to add rows to a table. For a full row insert , SQL Query
should start with “insert into “ statement followed by table name and values command,
followed by the values that need to be inserted into the table. Insert can be used in several
ways:
1. To insert a single complete row
2. To insert a single partial row
20 Define SQL Delete Statement ?
SQL Delete is used to delete a row or set of rows specified in the filter condition.
The basic format of an SQL DELETE statement is, DELETE FROM command followed by
table name followed by filter condition that determines which rows should be updated.
22 Define Join and explain different type of joins?
In order to avoid data duplication, data is stored in related tables . Join keyword is used to
fetch data from related table. Join return rows when there is at least one match in both
tables . Type of joins are
Right Join
Return all rows from the right table, even if there are no matches in the left table .
Outer Join
Left Join
Return all rows from the left table, even if there are no matches in the right table .
Full Join
Return rows when there is a match in one of the tables .
23 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 conditiona
24 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.
25 What is a primary key?
A primary key is a column whose values uniquely identify every row in a table. Primary
key values can never be reused. If a row is deleted from the table, its primary key may not
be assigned to any new rows in the future. To define a field as primary key, following
conditions had to be met :
1. No two rows can have the same primary key value.
2. Every row must have a primary key value
3. Primary key field cannot be null
4. Values in primary key columns can never be modified or updated
26 What is a Composite Key ?
A Composite primary key is a type of candidate key, which represents a set of columns
whose values uniquely identify every row in a table.
For example – if “Employee_ID” and “Employee Name” in a table is combined to uniquely
identifies a row its called a Composite Key.
27 What is a Composite Primary Key ?
A Composite primary key is a set of columns whose values uniquely identify every row in a
table. What it means is that, table which contains composite primary key will be indexed
based on columns specified in the primary key. This key will be referred in Foreign Key
tables.
For example – if combined effect of columns, “Employee_ID” and “Employee Name” in a
table is required to uniquely identifies a row, its called a Composite Primary Key. In this
case, both the columns will be represented as primary key.