SQL Server Interview Questions and Answers for Mid Level

 1.Write the script to create Table.

Answer:

CREATE TABLE Test
(
ID int,
LastName varchar(255),
FirstName varchar(255),

Address varchar(100),

);

2. What is Primary Key Constraint?

Answer:

The PRIMARY KEY constraint uniquely identifies each record in a database table. It must contain unique values and must not contain null values. Most of the tables should have the Primary key but there exists only one Primary Key for one table. Primary key does not contain null value. Primary Key has Unique Cluster Index by default.

3. What is Foreign Key Constraint?

Answer:

A FOREIGN KEY in one table points to a PRIMARY KEY in another table. There can exist multiple Foreign Key and Foreign Key may have null values. The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column because it has to be one of the values contained in the table it points to.

 Reference

4. What is Unique Constraint?

Answer:

The UNIQUE constraint uniquely identifies each record in a database table. Unique Constraint and Primary Key both provides the uniqueness of data in database table. There can be multiple Unique Key in Table but only One Primary Key. Primary Key itself has automatically defined Unique Constraint on it. Unique key can contain maximum of one null value where as primary does not.

5. How Union and Union All works in SQL server?

Answer:

Union gives the record only once if it occurs several times but Union All gives the record as several time that occurs in the table. Means that Union gives the distinct record where as Union All allows duplicate.

e.g.        Table 1

ID	Student Name
1	Ram
2	Shyam
3	Hari

Table 2

ID	Student Name
1	Ram
2	Shyam
3	Hari
1	Ram
3	Shyam

Union Result

ID	Student Name
1	Ram
2	Shyam
3	Hari

Union All Result

ID	Student Name
1	Ram
2	Shyam
3	Hari
1	Ram
3	Shyam

6. What are SQL Join?

Answer:

Inner Join: If we need Common records from both the tables we use Inner Join.

Outer Join: when we need the common and uncommon record from both the table outer join is used. An outer join is of two type on the basis of the unmatching record of which table is needed.

Left Outer Join: This gives all the record of the Left table and common record of the right table. Right Outer Join: This gives all the record of the Right table and common record of the left table.

Full Join: It gives all the record of both tables.

 7. Suppose you have the script of one database for one client and you need to create the same database for another client for same project deployment. How you ensure there is an exact same database, there is no mismatch of the table, table name, columns and constraints in both the Database?

Answer:

By checking the Number of Tables, Number of Columns, Number of Constraints, Table Name etc we can ensure that there is no mismatch of database creation if there is anything missed we can find out what is missing with the help of it.

Query for Number of Table Count

select COUNT(* ) from INFORMATION_SCHEMA.TABLES

Query for Number of Column count

select COUNT(* ) from INFORMATION_SCHEMA.COLUMNS

Query for Number of Constraint Count

select COUNT(* ) from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

e.g.

If there is one table creation missing then running following below query in both database and comparing each other, we can find out which table is missing. This will give the name list of table in the database.

select TABLE_NAME from INFORMATION_SCHEMA.TABLES

8. How many types of Function exists in SQL server explain with example?

Answer:

There are two types of SQL Function

1. SQL Aggregate Functions

It returns a single value, calculated from values in a column.

Some of the Aggregate SQL Function are

AVG() – Returns the average value

COUNT() – Returns the number of rows

FIRST() – Returns the first value

LAST() – Returns the last value

MAX() – Returns the largest value

MIN() – Returns the smallest value

SUM() – Returns the sum

   2. SQL Scalar functions

SQL Scalar functions return a single value, based on the input value.

Some of the SQL Scalar Functions are

UCASE() – Converts a field to upper case

LCASE() – Converts a field to lower case

MID() – Extract characters from a text field

LEN() – Returns the length of a text field

ROUND() – Rounds a numeric field to the number of decimals specified

NOW() – Returns the current system date and time

FORMAT() – Formats how a field is to be displayed

9. What is Difference Between Store Procedure and Function?

Answer:

– A Stored Procedure can return zero, single or multiple values. But, a function must return a single value.

– A Stored Procedure can have both input and output parameters whereas Functions can have only input parameters.

– Functions allow only a SELECT command whereas a Stored Procedure allows SELECT and other DML commands (INSERT, UPDATE and DELETE).

– A Stored Procedure can call a function, but a function can’t call a Stored Procedure.

– A Stored Procedure can use the try-catch block to handle exceptions. But try-catch is not allowed in functions.

– SQL transactions can be used in Stored Procedures but not in Functions.

10. What is SQL Aliases?

Answer:

Aliases are used to give database Table Name or Table Column Name as Temporary Name. It is used to make column Name more readable.

SELECT ColumnName AS AliasName
FROM TableName;