SQL

Following are some of the important SQL Server Interview Questions and Answers

What is DBMS?

Database Management System or DBMS is an application for managing data stored in a database.It lets users work with the stored data and provides facilities to retrieve,insert,update and delete data in the database.

What is RDBMS?

RDMBS is a software system which is used to manage the database.In RDBMS data is stored using collection of tables.Tables consists of columns and rows.

What is Primary key?

Primary key is a group of columns in a table which uniquely identifies a row.Primary key values cannot be NULL.Two rows never have the same primary key value.A table can have only ONE primary key.his primary key can consist of single or multiple columns.

Primary key can be a single column or consist of multiple columns.A table can have only ONE primary key.We can create a table with a primary key as:

CREATE TABLE Employee(
Name varchar(100),
ID int,
PRIMARY KEY (ID)
);

What is Unique Key?

Unique Key is a group of fields in a table which identifies row.Difference between primary key and unique key is that unique key can have a null value.

What is Join?

Join is used fetch rows from multiple tables using a common column.Column defined in the first table acts as the primary key while the column defined in the other table acts the foreign key.

What is Inner join?

Inner join return rows when both the tables have matching column values.

What is Right Join?

Right join return common rows in the tables and all rows of Right hand side table in the join.

What is Left Join?

Left join return common rows in the tables and all rows of Left hand side table in the join.

What is Full Join?

Full join returns rows from both the tables.

What is a View?

A view is a virtual table as it doesn’t exist physically unlike actual tables.It can consist of data from multiple tables.
We can filter and sort data when returning the view data.

What is a Stored Procedure?

Stored Procedure is a collection precompiled  T-SQL statements which are stored in the database and can be executed using the stored procedure name.Since they are stored in the SQL Server they reduce network load.Stored procedures also makes the application more secure since application don’t need to send the SQL to the SQL Server and can just execute the Stored Procedure.

What is a function in SQL Server?

Like a Stored Procedure ,function is also a group of T-SQL statements.But the difference is that functions can return a value while stored procedures can not return a value.There are two types of functions:

• UDF or User Defined Functions  Custom functions created by developer
• Inbuilt Functions  Provided by SQL Server

What is a Index?

Index helps in faster retrieval of data from the table.Index is like an index of a book.Like an index in a book Index is used to get to the actual row containing the data.This results in faster execution of query when selecting rows.

What is a Clustered Index?

Clustered Index physically rearranges the data in a table.This is the reason that a table can have only one Clustered Index.

What is a Non-Clustered Index?

NonClustered Index does not physically rearranges the data in a table.This is the reason that a table can have multiple NonClustered Indexes.

What is a Cursor?

A cursor is used to traverse the rows in a table.A cursor points to a single row at a time.So it used when we need to traverse all the rows in a table.

Define Query?

A query is request to the RDBMS to retrieve the matching rows for a table or group of tables.

What is SQL?

SQL stands for Structured Query Language.It is the language supported by all the RDBMS such as Oracle and SQL Server.

What is a Sub Query?

A subquery is a query within another query. The outer query is called as main query, and inner query is called subquery.

Follow on:

Often we need to find all the tables in our database containing column with specific names.As we don’t know which tables contains the columns so we find ourselves manually searching for the columns in the tables.This can be a time consuming and error prone approach.

To find all tables containing columns with a specific name or pattern we can query the following SQL Server system tables

• sys.columns
• sys.tables

System tables are the tables which are supposed to be used by SQL server components.These  tables mostly start with  sys.

sys.columns This table contains a row for every column of each table in the database.The important columns in this table are

• name             Name of the database object(table) to which this column belongs
• object_id      Id of the database object(table in our case) to which this column belongs

sys.tables This table contains a row for each table in the database.The important columns in this table are

• name   Name of the table
• id        Id of the table

Following will return all the tables which contains a column containing User as a part of its name

SELECT columns.name AS ColumnName, tables.name AS TableName
FROM sys.columns columns
JOIN sys.tables tables ON columns.object_id = tables.object_id
WHERE columns.name LIKE '%User%'

object_id is an object identification number of an object.We match the table and column using this identification number.

Information_schema views

Another way we can find the column with a specific name or pattern is by using  Information_schema views.These views contains the metadata about the objects in the database.

Using Information_schema views we can find all the tables containing a column with a specific name as:

SELECT TABLE_NAME ,COLUMN_NAME  FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE '%User%'

 

Follow on:

A common requirement is to convert rows from CSV values.If we have string such as ‘ORANGES,APPLES,BANANAS’ then we can create a result set from these
values.

ORANGES

APPLES

BANANAS

Here we will see how to do it using a custom SQL Server function.But before that it will be helpful if we understand some of the useful SQL Server functions which we will be using in our function.

CHARINDEX is a function which searches for the occurrence of a string inside another string and returns the index of the search string if found or 0 if the substring is not contained in the main string.

CHARINDEX ( MainExpression ,SearchExpression , indexToStartSearch)

Following SQL will search for the occurrence of string “sample” in the main string and will return its position.The following will return the value 16.

DECLARE @searchString varchar(50)
SET @searchString='This is just a sample string value'
PRINT CHARINDEX('sample', @searchString, 0)

CHARINDEX will return 0 if the searched string is not found.The below SQL will return 0 as the value ‘info’ is not contained in the main string

DECLARE @searchString varchar(50)
SET @searchString='This is just a sample string value'
PRINT CHARINDEX('info', @searchString, 0)

SUBSTRING is a function which returns a portion of a string.The start location and the length of the substring to return is specified as the function arguments.

SUBSTRING ( expression ,startIndex , lengthOfTheSubstring )

startIndex is the index in the expression from which substring is retreived
lengthOfTheSubstring specifies the length of the substring.

For example the below function will return the value “STRING” since we specified the index as 4 and length as 6.

SELECT SUBSTRING('SQLSTRING', 4, 6)

STUFF function is used to delete a substring from a string and then to insert a new string in its place.
STUFF( expression, startIndex, length, newString )

startIndex is the index in the expression from where to remove the substring
length is the number of characters to delete
newString is the string to replace the deleted string.

The following will return the value ‘SQL SAMPLE STRING’

SELECT STUFF( 'SQLSTRING', 1, 3, 'SQL SAMPLE ' )

Function to Convert Comma Separated Values to Rows

We have implemented a custom using the above functions which will convert the comma separated values to rows.It accepts a parameter of type string and return the rows.

CREATE FUNCTION dbo.SplitCSVs (@CSVString varchar(1000))
RETURNS @SeparatedValues TABLE (Value VARCHAR(100))
AS
BEGIN
DECLARE @CommaPosition INT
WHILE (CHARINDEX(',', @CSVString, 0) > 0)
BEGIN
SET @CommaPosition = CHARINDEX(',', @CSVString, 0)
INSERT INTO @SeparatedValues (Value)
SELECT SUBSTRING(@CSVString, 0, @CommaPosition)
SET @CSVString = STUFF(@CSVString, 1, @CommaPosition, '')
END
INSERT INTO @SeparatedValues (Value)
SELECT @CSVString
RETURN
END

Now we can just pass the comma separated string to our function and it will return the result set based on the string values.

If we execute the below SQL

select * FROM [dbo].[SplitCSVs]('SAMPLE VALUE1,SAMPLE VALUE2,SAMPLE VALUE3')

then we will get the following result set

Follow on:

Temporary tables are like normal tables , but these are used to store the result temporarily.We can perform the same operations on the temporary tables as normal tables such as select,delete,update.Unlike normal tables temporary tables are automatically deleted when the user session ends or we manually destroy the table.These tables are stored in the tempdb database.

In SQL Server there are two types of temporary tables

Local Temporary tables

The name of local temporary table start with the pound(#) sign.These are automatically destroyed when the session or the connection which created the table is closed.

Global Temporary tables

The name of global temporary table start with the double pound (##) sign.Unlike local temporary tables once a global temporary table has been created it is destroyed only when the last connection is closed.

In the following example there are two tables defined in the database Users table and UserInfo table.UserInfo table contains the personal details about the user while the Users table contains the account information about the user.

UserInfo table

User table

If we require a temporary result set which contains data from both the tables then we can define a temporary table

CREATE TABLE #UserDetails
(UserName varchar(50),
FirstName varchar(50),
LastName varchar(50),
Email varchar(50))

For populating the temporary table with the  data from both the tables we can use a  INSERT INTO statement as

INSERT INTO #UserDetails
SELECT us.UserName,usi.FIRSTNAME,usi.LASTNAME,us.Email
FROM [dbo].[Users] us
JOIN [dbo].[UserInfo] usi ON us.UserName=usi.UserName

 

Follow on:

Function is a group of T-SQL statements used for defining reusable logic.Unlike stored procedure function always returns some value.The return value can be of different data types such as a table or a single scalar value.

Also the only way to return a value from a function is through the return value as functions don’t have out parameters as is in the case of stored procedures.

We can broadly classify functions depending upon the values they operate upon and the value which is returned

• Rowset functions   Returns resultset that can be used in place of a table in a select statement.
• Aggregate functions  Operates on a set of values and returns a single value.
• Scalar functions   Operates on a single value and returns  a single value.

Depending whether they are inbuilt or not functions can be classified as:

• Inbuilt Functions               These are provided by SQL Server.
• User Defined Functions   Depending on the requirement we can implement user defined functions.

Some of the examples of inbuilt functions are:

Aggregate functions

These  performs an operation on a set of values and returns a single value

Function	Use
AVG	Returns the Average value in group
MIN	Returns the minimum value in group
MAX	Returns the maximum value in group
SUM	Returns the total of items in group
COUNT	Returns the numbers of items in group

Following StudentsInfo table stores the Details about the students such as their marks.

We can use different aggregate functions to get aggregated data about the Students in the StudentsInfo table.

SELECT SUM(MARKS) as TOTAL,AVG(MARKS) AVERAGE,MAX(MARKS) HIGHEST ,MIN(MARKS) LEAST,COUNT(MARKS) [NUMBER OF STUDENTS]
FROM dbo.StudentsInfo

Scalar function

Operates  on a single value and returns a single value

Function	Use
CAST	Converts  expression of one type to another data type.
CONVERT	Converts  expression of one type to another data type. This is specific to SQL Server
LOWER	Converts a string value to lowercase
UPPER	Converts a string value to uppercase
LTRIM	Removes spaces from the beginning of a string
RTRIM	Removes spaces from the end of a string

Rowset functions

Returns a resultset that can be used in a select statement.

Function	Use
OPENQUERY	Performs Query on a Linked Server
OPENXML	Provides rowset from a XML document

User Defined Functions

When creating a user defined function we first identify the type of value that will be returned by the function:

• Scalar Function     Function which returns a scalar value.
• Table  Valued Function     Function which returns a resultset.

We can create functions in SQL Server using the Create Function statement.Following is the syntax for creating a user defined function.

CREATE FUNCTION FuntionName
( Parameter as Datatype)
Returns Type
As

Function Body

We will create a scalar function and a table valued function for querying the StudentsInfo table.StudentsInfo table contains data about the students.

Scalar Function

We create the FnGetStudentsSummary scalar function which will return the details about the number of the countries to which students belong .

ALTER FUNCTION dbo.FnGetStudentsSummary
(
@country varchar(50)=''
)
RETURNS int
AS
BEGIN
DECLARE @COUNT INT
IF @country<>''
SELECT @COUNT=COUNT(*) FROM [dbo].[StudentsInfo]
WHERE [COUNTRY]=@country
ELSE
SELECT @COUNT=COUNT(distinct([COUNTRY])) FROM [dbo].[StudentsInfo]

RETURN (@COUNT)
END
GO

The function FnGetStudentsSummary will return the country details of the users.It expects one input parameter.@country is a default parameter.
If we do not supply any value for this parameter then we will get the distinct countries in the table.For example if we execute the following query:

SELECT dbo.FnGetStudentsSummary('India') AS 'TOTAL COUNTRY'

then we will get the result as 2 as the number of students from India are 2 .

If we want the number of different countries to which Students belong then we can use the following query.To use the functions default parameter value we specify the word DEFAULT.

SELECT dbo.FnGetStudentsSummary(DEFAULT) AS 'TOTAL COUNTRY'

This will display the value 3 as there 3 different countries in the StudentsInfo table.

To get the different countries we use the default parameter value.

TABLE VALUED FUNCTION

Table valued functions can return a result set.The value returned by a table valued function can be directly used in a select statement.

To get the data for a specific user we will create a table valued function.The name of a user consists of two columns.In the function we are concatenating the values of the FIRSTNAME and LASTNAME columns.

Create FUNCTION dbo.FnGetStudentDetailById(@id int)
RETURNS TABLE
AS
RETURN

SELECT UPPER(FIRSTNAME)+' '+UPPER(LASTNAME) AS NAME,UPPER(COUNTRY) AS COUNTRY FROM StudentsInfo WHERE ID=@id

We can use the FnGetStudentDetailById() function in a select statement similarly to how we use a table:

SELECT * FROM FnGetStudentDetailById(1)

This will display the name and country of the student with Id “1”:

Name                     Country

AMIT KUMAR     INDIA

Follow on: