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 server. Cursor 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.
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 server. Cursor 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 server, nested while loop example in sql server, get only month and year from date in sql server, substring function in SQL server and many articles relating to SQL server. Now I will explain cursor in sql server with example.
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.
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 link, SQL Server remove duplicate records from table, SQL Server insert multiple rows with single insert statement, how to send mail with attachment in asp.net and many more articles related to asp.net, SQL, c#, vb.net. Now I will explain what is sql injection attack in asp.net website with example.
In previous post I explained jQuery scroll to particular position of div when click on link, SQL Server remove duplicate records from table, SQL Server insert multiple rows with single insert statement, how to send mail with attachment in asp.net and many more articles related to asp.net, SQL, c#, 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
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 Object, ByVal e As EventArgs)
End Sub
Protected Sub btnsearch_Click(ByVal sender As Object, ByVal 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 server. Union 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 sql, reseed identity column value in sql server, get only month and year from date in sql server, substring 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.
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