Interview Questions with answers

Q1:What is Primary Key?

A1: The primary key is a field that uniquely identifies each record of the table. A table can have only one key declared as a primary key. If a primary key consist of more than one column,duplicate values are allowed in one of the columns,but the combination of values from all the columns in the primary key must be unique.Also,by default primary key creates clustered index.

Q2. What is the difference between a primary key and a unique key?

A2.  By default, a primary key creates a clustered index on the column
and unique keys create a nonclustered index. Primary keys do not allow NULL values, but unique keys
allow one NULL value. However, both primary and unique keys enforce uniqueness of the column on
which they are defined. You can use primary key constraints to enforce uniqueness as well as referential
integrity. Defining a primary key on a table helps to relate that table to other tables. These relationships
help to normalize a database. A table can have only one primary key.

Q3.What are the types of constraints?Explain each of them?

A3.

■ Check constraints

A CHECK constraint defines a condition for one or more columns in a table on INSERT and UPDATE operations.
This condition can be defined by any expression that returns TRUE or FALSE. If the condition returns TRUE,
the operation continues, but if the condition returns FALSE, the operation is automatically rolled back.

Ex:

CREATE TABLE NewEmployees (
EmployeeID int NOT NULL,
EmployeeName varchar(50) NOT NULL,
PostCode char(5) NOT NULL
CHECK (PostCode LIKE ‘[0-9][0-9][0-9][0-9][0-9]‘)
)
GO

■ Rules

You define check constraints within the table definition and cannot reuse them. Rulesprovide the same functionality as check constraints, except that you create them as aseparate object.

Because rules are not associated with a specific table or column when you create
them, they cannot reference columns or tables in their definition. Instead, you use
variables as placeholders. Rules provide the same features and complex comparisons
via AND, OR, and NOT as check constraints and allow pattern matching.
The following examples show the previous two check constraints implemented as
rules:
CREATE RULE EmailValidator
AS
@value like ‘%@%.[a-z][a-z][a-z]‘ or @value like ‘%@%.[a-z][a-z].[a-z][a-z]‘;

After defining a rule, you then bind it to columns or user-defined data types by using
the sp_bindrule system stored procedure.

■ Default constraint

You can define a DEFAULT definition for columns to avoid repetitive data entry. If a column has a DEFAULT
definition, this value will be supplied if you don’t provide a specific value for the column in the INSERT
statement. DEFAULT definitions are applied only in INSERT operations.
You can provide as a DEFAULT definition any expression that evaluates to a single scalar value with a data
type compatible with the data type of the column in which the DEFAULT definition is defined. This expression
can be
· A constant value
· Any system scalar function
· The result of a scalar user-defined function
· Any scalar expression made from any combination of the previous points, including mathematical
expressions

Ex:

CREATE TABLE NewCustomers(
CustomerID int NOT NULL
IDENTITY(1,1)
PRIMARY KEY,
CustomerName varchar(30) NOT NULL
CONSTRAINT Def_CustName
DEFAULT ‘To be entered’,
City varchar(30)
DEFAULT ‘London’,
CreaDate smalldatetime
DEFAULT Getdate(),
CreaUser nvarchar(128)
DEFAULT System_User)
GO
– Insert data into the NewCustomers table
– Providing values for CustomerName
– and City fields
INSERT NewCustomers (CustomerName, City)
VALUES (‘MyComp corp.’, ‘New York’)

■ Unique constaint

You can create a PRIMARY KEY to enforce uniqueness in a field or group of fields, but you can have only one
PRIMARY KEY per table.
If you require enforcing uniqueness in other columns, you can create a UNIQUE constraint.

Ex:

CREATE TABLE NewRegions (
RegionID int NOT NULL
UNIQUE NONCLUSTERED,
RegionDescription nchar (50) NOT NULL ,
)
GO

■ Primary key

Your choice of primary key constraint is critical in creating a sound structure for a
table. A primary key defines the column or combination of columns that allow a row
to be uniquely identified.

Ex:

You implement a primary key on the StateProvinceID column of the StateProvince
table as follows:
CREATE TABLE dbo.StateProvince
(StateProvinceID int IDENTITY(1,1) PRIMARY KEY,
StateProvince varchar(50) NOT NULL)

■ Foreign key

You use foreign key constraints to implement a concept called referential integrity.
Foreign keys ensure that the values that can be entered in a particular column exist in
a specified table. Users cannot enter values in this column that do not exist in the
specified table.

Ex:

Create Orders table and add foreign key constraint to Customers table CustomerID column.

CREATE TABLE Orders(
OrderID int
IDENTITY(1,1)
PRIMARY KEY,
CustomerID int NOT NULL
REFERENCES Customers(CustomerID),
OrderDate smalldatetime NOT NULL
DEFAULT CURRENT_TIMESTAMP)

Q4.What is a subquery?How it perform?

A4.A subquery is a nested select statement where the inner select statement is evaluated first. You can use
the results of the inner select statement as the input for another select statement. For example, you can use
the results of a subquery as a search condition that uses the IN ( ) function or EXISTS operator.

Ex:

USE northwind
SELECT * FROM Orders WHERE orderId IN
(SELECT orderId FROM [Order Details]
WHERE OrderId = 10248)

Q5.What is a correlated subquery?

A5.A correlated subquery is a subquery where the inner query is evaluated once for every value returned by
the outer query. Generally a query which has a subquery will execute the subquery once and substitute the
resulting value or values into the WHERE clause of the outer query. In queries that include a correlated
subquery (also known as a repeating subquery), the subquery depends on the outer query for its values.
For example, using the northwind database, if you want to find out the price of all the books ordered, you
can use a correlated subquery. Price information about each book is in the [Order Details] table, and the
Orders table has all the books ordered.

Ex:
SELECT O.OrderID, O.OrderDate,
(SELECT MAX (Od.UnitPrice) FROM [Order Details] AS Od
WHERE Od.OrderID = O.orderid) AS MaxUnitPrice
FROM Orders AS O

Q6.What is Index in SQL Server?

A6.A SQL Server index is constructed as a B-tree, which enables SQL Server to
search very large volumes of data without affecting the performance from one
query to the next.An index is useful only if it can help find data quickly regardless of the volume of data
stored.

Q7.What are the benefits of Indexes?

A7.SQL Server queries can benefit from the existence of suitable indexes in the following cases:
· Exact-match queries— When searching for rows with specific key values. These are queries with a
WHERE clause to restrict the query to specific values for every key column.
· Range queries— When solving queries that search for a range of values in a column.
· Filtering for values in the foreign key to solve a join operation— When using a JOIN predicate to
search for rows in a table based on keys from a second table.
· Hash and merge join operations— In some cases, having an index can speed up the execution of a
JOIN algorithm, because the data is exactly in the order that the JOIN algorithm uses.
· Covering a query— To avoid a full-table scan, when a narrow index contains all the required data.
· Avoiding duplicates— To check for the existence of suitable indexes in an INSERT or UPDATE
operation in an attempt to avoid duplicates.
· Sorting data— To produce an ordered output when using the ORDER BY clause.

Q8.Tell about Stored Procedures?

A8.A stored procedure is a database object that comprises one or more Transact-SQL statements. The main
difference between a stored procedure and a set of statements is that a stored procedure can be reused just
by calling its name. Therefore, if you want to rerun the code, you don’t have to execute the whole set of
statements that compose the stored procedure one by one.

Q9.What are the advantages of Stored Procedures?

A9.

· They are precompiled statements

· They optimize network traffic

· They can be used as a security mechanism

· They allow modular programming

· They can be set to execute automatically when SQL Server starts

· They can use parameters

Q10. Is it possible to force a query to use a specific Index?

A10.

Yes, it is possible by using index hints.
SELECT fname, lname
FROM emps (INDEX (indx_firstname, indx_lastname))

Q11. What is ACID Properties ?

A11.

• ACID property decides the quality of the transaction,If any transaction that violate this property that’s not consider as a quality transaction.
• ACID is an acronym for four interrelated properties: atomicity, consistency,
  isolation, and durability.
• Atomicity

If the transaction is completed it’s execution then at the end , either
all of the transaction is successful, or all of the transaction fails. If a partial data is written to disk, the atomic property is violated.

• Consistency

Once the transaction has start it’s transaction then the data is consistent before and after the transaction complete. It means the transaction should maintain it’s consistency. SQL Server must guarantee that the data modifications are applied consistently. If the transaction is canceled, the data should go back
to the same consistent state it was in at the beginning of the transaction.

• Isolation

This isolation level must implies that the transaction must either visible to the data in its previous state or the transaction must wait until the changes from other transactions are committed.

• Durability

After every transaction completes, its changes to the data are stay permanent, apart from the
event of a system failure. In other words, when a client application receives notification that a
transaction has completed its work successfully, it is guaranteed that the data is changed permanently.

Q12. Tell about isolation properties in SQL Server?

A12. Isolation properties are decides the way transactions are needed to behave during the time of execution. An isolation level determines the degree of isolation of data between concurrent transactions.

There are four types of isolation properties are available we can choose any of these properties based on our requirements.

• READ COMMITTED

Specifies that shared locks are held only while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in no repeatable reads or phantom data. This option is the SQL Server default.

• READ UNCOMMITTED

Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data. Values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This is the least restrictive of the four isolation levels.

• REPEATABLE READ

Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction.

• SERIALIZABLE

Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels.

Q13. What is NORMALIZATION?

A13.

To normalizing the logical database design using formal methods to split the data into multiple and number of  related tables.The number of  small tables join together and produce the required details is the characteristic of a normalized database.

Reasonable normalization also improves the performance query. When useful indexes are available, and efficient joins between tables are used boost up performance .

Some of the benefits of normalization include:

• Faster sorting and index creation.
• A larger number of clustered indexes.
• Narrower and more compact indexes.
• Fewer indexes per table, which improves the performance of INSERT, UPDATE, and DELETE statements.
• Fewer null values and less opportunity for inconsistency, which increase database compactness.

As normalization increases, so do the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance. Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables.

Q14. What is DENORMALIZATION?

A14. Some times more normalization in a database also reduce the performance of  the process,Such as in OLTP applications. Denormalization is the process of attempting to optimize the read performance of a database by adding redundant data or by grouping data. In some cases, denormalization helps cover up the inefficiencies inherent in relational database software. A relational normalized database imposes a heavy access load over physical storage of data even if it is well tuned for high performance.

Q15. DBMS Vs RDBMS

A15. A DBMS has to be persistent, that is it should be accessible when the program created the data ceases to exist or even the application that created the data restarted. A DBMS also has to provide some uniform methods independent of a specific application for accessing the information that is stored.

RDBMS is a Relational Data Base Management System Relational DBMS. This adds the additional condition that the system supports a tabular structure for the data, with enforced relationships between the tables. This excludes the databases that don’t support a tabular structure or don’t enforce relationships between tables.

–To be continue…