Sunday, April 5, 2015

sql server interview questions

Here I will explain sql server interview questions and answers for experienced and freshers or interview questions and answers in sql server for freshers and experienced people with examples.

Please check below of sql server interview questions and answers for experienced and freshers

What is cursor in sql server with example?
Introduction:

Here I will explain what is cursor in 
sql server with example or simple cursor example in sql server and how to declare and use cursor in sql serverCursor in sql server is used to retrieve set of data from table, loop through each record row by row, and modify the values based on requirements.
Description:
In previous articles I explained While loop example in sql servernested while loop example in sql serverget only month and year from date in sql serversubstring function in SQL server and many articles relating to SQL server. Now I will explain cursor in sql server with example.

To use cursor in sql server that syntax will be like as shown below

Syntax to Create Cursor


DECLARE cursorname CURSOR
FOR selectstatement -- like SELECT OrderID,CustomerID FROM Orders
OPEN cursor
FETCH tablerow FROM cursor
Process Fetched Tablerow
CLOSE cursor
DEALLOCATE cursor
If you observe above syntax we have different steps in cursor

Step 1: Declare Cursor

First we need to declare cursor name that will be used in cursor execution

Step 2: Select Statement

Select statement is used to get data from table to process with cursor

Step 3: Open Cursor

This statement is used to open the cursor to process select statement result set

Step 4: Fetch Rows

Once cursor is opened, rows can be fetched from the cursor one by one

Step 5: Close Cursor

Once our process finished we need to close the cursor

Step 6: Deallocate Cursor

We need to deallocate cursor to delete cursor definition and release resources associated with the cursor

We will check this with example for that first create one table UserDetails in your database and insert some 100 or 200 records or install northwind database in your server and use orders table it contains more than 500 records

Example

Write cursor script like as shown below and run it.


DECLARE ex_cursor CURSOR
FOR SELECT OrderID,CustomerID FROM Orders
DECLARE @oid INT
DECLARE @cname NVARCHAR(50)
OPEN ex_cursor
FETCH NEXT FROM ex_cursor INTO @oid,@cname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT  (CAST(@oid AS VARCHAR(5)) + '-' + @cname)
FETCH NEXT FROM ex_cursor INTO @oid,@cname
END
CLOSE ex_cursor
DEALLOCATE ex_cursor
Output:

When we run above query our results like as shown below

Demo


What is SQL Injection attack and how to prevent it?
Introduction:

Here I will explain what is sql injection attacks and how to prevent it in 
asp.net website with example and how to prevent SQL injection attacks in asp.net using c#vb.net with example. SQL injection means injecting some SQL commands in SQL statements to hack your data or delete data or change your data in tables via web page input.

Description

In previous post I explained jQuery scroll to particular position of div when click on linkSQL Server remove duplicate records from tableSQL Server insert multiple rows with single insert statement
how to send mail with attachment in asp.net and many more articles related to asp.netSQLc#vb.net. Now I will explain what is sql injection attack in asp.net website with example.

First design one table countrydetails in your database like as shown below

Column Name
Data Type
Allow Nulls
ID
Int(set identity property=true)
No
name
Varchar(50)
no
value
Int
no
Once we create table we need to enter some dummy data for our application purpose
Now in your Default.aspx page write the following code


<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>SQL Injection Attacks in Asp.net Website</title>
</head>
<body>
<form id="form1" runat="server">
<div>
Enter Count:<asp:TextBox ID="txtSearch" runat="server" />
<asp:Button ID="btnsearch" Text="Search" runat="server" onclick="btnsearch_Click" />
<br /><br />
<asp:GridView ID="gvDetails" CellPadding="5" runat="server">
<HeaderStyle BackColor="#df5015" Font-Bold="true" ForeColor="White" />
</asp:GridView>
</div>
</form>
</body>
</html>
After completion of aspx page write the following code in codebehind

C# Code


using System;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnsearch_Click(object sender, EventArgs e)
{
DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("select Name,Total=value from countrydetails where value = "+txtSearch.Text+"", con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
}
}
VB.NET Code


Imports System.Data
Imports System.Data.SqlClient
Partial Class VBcode
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As ObjectByVal e As EventArgs)
End Sub
Protected Sub btnsearch_Click(ByVal sender As ObjectByVal e As EventArgs)
Dim dt As New DataTable()
Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("select Name,Total=value from countrydetails where value= = " + txtSearch.Text + "", con)
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
con.Close()
gvDetails.DataSource = dt
gvDetails.DataBind()
End Using
End Sub
End Class
When we run above code we will get output like as shown below

Demo

SQL Injection Example

Now I will explain how to inject SQL in our queries from our webpage input.
First in our textbox enter text like “10 or 1=1” as shown below and check the below output

It returns all the rows from table because our textbox input value converts query as like as shown below


select Name,Total=value from countrydetails where value =10 or 1=1

In above query it will check for value =10 as well as it will check for 1=1 means always true that’s the reason it will returns all the values from table this way they can inject values to change our queries and access all the values from table.

In another case if user enters value like “10; Drop TABLE countrydetails” in it will drop table from our database because our query will changed like this


select Name,Total=value from countrydetails where value =10; Drop TABLE countrydetails
This way they can inject SQL and get all the details or delete data or drop tables.

To avoid these SQL injection attacks always we need to use parameterized queries like as shown below


select Name,Total=value from countrydetails where value =@value
Example of C# Code


DataTable dt = new DataTable();
using (SqlConnection con = new SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB"))
{
con.Open();
SqlCommand cmd = new SqlCommand("select Name,Total=value from countrydetails where value =@value", con);
cmd.Parameters.AddWithValue("@value", txtSearch.Text);
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
con.Close();
gvDetails.DataSource = dt;
gvDetails.DataBind();
}
VB.NET Code


Dim dt As New DataTable()
Using con As New SqlConnection("Data Source=SureshDasari;Integrated Security=true;Initial Catalog=MySampleDB")
con.Open()
Dim cmd As New SqlCommand("select Name,Total=value from countrydetails where value =@value", con)
cmd.Parameters.AddWithValue("@value", txtSearch.Text)
Dim da As New SqlDataAdapter(cmd)
da.Fill(dt)
con.Close()
gvDetails.DataSource = dt
gvDetails.DataBind()
End Using



Introduction:

Here I will explain difference between UNION and UNION ALL in SQL serverUnion operator will return unique records from tables and Union All in SQL server will return all the records from the tables including duplicate values also.

Description:

In previous articles I explained SQL Server change identity column value,  replace multiple spaces with single space in sqlreseed identity column value in sql serverget only month and year from date in sql serversubstring function in SQL server and many articles relating to SQL server. Now I will explain difference between UNION and UNION ALL in SQL server.

Generally Union Operators are used to combine the result of two or more select queries into single result set.

SQL UNION Operator:

SQL Union Operator is used to combine the result of two or more select statement queries into single result set. The Union Operator is used to select only distinct values from two tables.

SQL Union Operator Syntax:

SELECT column1,column2 FROM table1
UNION
SELECT column1,column2 FROM table2
Here one more thing we need to remember that is we can use Union Operator for the tables which is having same column names and same data types otherwise it will throw error like this

All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Now I will explain with one example first design two tables in your tables like this

UserInfo

UserID
UserName
Location
     1
Suresh
Hyderabad
     2
Prasanthi
Hyderabad
     3
Mahesh
Vizag

After that create another table and give name as UserDetails

UserID
UserName
Location
     1
Suresh
Hyderabad
     2
Nagaraju
Bangalore
     3
Madhav
Nagpur

Now write the Union Operator Query to get all the user details from two tables like this

SELECT UserName,Location FROM UserInfo
UNION
SELECT UserName,Location FROM UserDetails
Resultant table will be like this
UserName
Location
Suresh
Hyderabad
Prasanthi
Hyderabad
Mahesh
Vizag
Nagaraju
Bangalore
Madhav
Nagpur

If you observe above resultant table it contains UserDetails with distinct records because Union Operator will return only distinct records. If we want all the records then we need to use UNION ALL Operator.

SQL UNION ALL Operator:

This operator is used in a situation like return all the records from the tables including duplicate values also.  

SQL UNION ALL Operator Syntax:

SELECT column1,column2 FROM table1
UNION ALL
SELECT column1,column2 FROM table2
Result table

UserName
Location
Suresh
Hyderabad
Suresh
Hyderabad
Prasanthi
Hyderabad
Mahesh
Vizag
Nagaraju
Bangalore
Madhav
Nagpur

The main difference between Union and Union ALL operator is

Union operator will return distinct values but Union ALL returns all the values including duplicate values.

















No comments:

Post a Comment