SQL Interview Questions

What is a PRIMARY KEY?

The PRIMARY KEY is the column(s) used to uniquely identify each row of a table.

What is a FOREIGN KEY?

A FOREIGN KEY is one or more columns whose values are based on the PRIMARY or CANDITATE KEY values from the database.

What is a UNIQUE KEY?

A UNIQUE KEY is one or more columns that must be unique for each row of the table.

What’s the difference between a primary key and a unique key?

Both primary key and unique enforce uniqueness of the column on which they are defined.

1. But by default primary key creates a clustered index on the column, where as unique creates a   nonclustered  index by default.
2. Primary key doesn’t allow NULLs, but unique key allows one NULL only.

What is Magic Table in SQL?

The insert and Delete commands are known as magic tables in SQL.

What is a Stored Procedure? State its advantage.

A stored procedure is a set of pre-compiled SQL commands (query statements), which are stored in the server.  As it is pre-compiled so it is faster than loose sql commands. It can execute more then one SQL commands once as they are bundled in a single entity.

We can use control statements within the stored procedure, which will allow us to repeat some SQL command. They are used to reduce network traffic.

What is the difference between UNION and UNION ALL?

UNION selects only distinct values whereas UNION ALL selects all values and not just distinct ones.

UNION: SELECT column_names FROM table_name1
SELECT column_names FROM table_name2

UNION All: SELECT column_names FROM table_name1
SELECT column_names FROM table_name2

What is ACID fundamental and what are transactions in SQL SERVER ?

A transaction is a sequence of  operations performed as a single logical unit of  work. A logical unit of  work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction:

1. Atomicity : A transaction must be an atomic unit of work; either all of its data modifications are performed or none of  them is performed.

2. Consistency : When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction's modifications to maintain all data integrity.

3. Isolation : A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state.

4. Durability : After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

                                                                     1  2  3
Get paid for clicking ads. 100% legit site which really pays.