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
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
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
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
A foreign key is a field (or collection of fields) in one table that uniquely identifies a row of
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:
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
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
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
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
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
DCL is used to control the visibility of data by granting database access and setting privileges
to create tables etc.
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
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.
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.
The database will move from one consistent state to another if the transaction succeeds
and remain in the original state if the transaction fails.
Every transaction should operate as if it is the only transaction in the system
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