How to use wildcard character in a SQL query


What is the wildcard character in a SQL query?


SQL wildcards can be used when searching for data in a database. SQL wildcards are used with the LIKE operator, which acts as a comparison when searching the database. We will use the '%' and '_' wildcards in SQL Server. Wildcards are used in SQL to match a string pattern

 Percent sign (%)

   select * from Employee where name like 'b%'
   It will show record of name which start with b


   select * from Employee where name like '%b'
    It will show record of name which end with b

  
   select * from Employee where name like '%st%' --
   It will Pick up the record from middel ,last or first


   Dash(_):

  
   select * from Employee where user_id like '_eha'
   It will show record of user_id which end with last three matching characters
  
   select * from Employee where user_id like '_e_a'
  
   select * from Employee where user_id like '_e_a'
  
   select * from Employee where user_id like '[lnr]%'
  
   select * from Employee where user_id like '[!lnr]%' --not like
  
   select * from Employee where user_id like '[a-n]%'


Read More

Frequently ask question in Sql interview

Frequently ask question in Sql interview


Find the highest salary in sql



SELECT [Name],
       [Age],
       [Address],
       [Salary],
  FROM [salarydetails]




Name Age     Address         Salary
Jhon    21        UK                10000.00
Sham  21        India             100000.00
Allen  21       US                  90000.00
Shyam 21       America        60000.00
Seema 21       Rashia           50000.00


Find maxium salary

select max(salary) as maxiumsalary from salarydetails

Output:

    maxiumsalary
    100000.00

Note: (here  maximumsalary is virtual column means, i am aliasing the column name now it  will look like maximumslary column in output)

Find the average of  salary using AVG function

select avg(salary) as avgsalary from salarydetails

Output:
Output:

avgsalary
62000.000000

Find the 2nd highest salary

select max(salary) as secondhighest from salarydetails where  salary not in(select MAX(salary) from salarydetails)

Output:

secondhighest
90000.00


Find the 3rd  highest salary


select min (salary) from (select top 3* from salarydetails)m


Thanks for reading my blog

Read More

Sql Server Stored Procedure


SQL SERVER – Stored Procedure WITH ENCRYPTION


One of the ways to keep stored procedure, function or view schema hidden is using the WITH ENCRYPTION statements. In this way, access to objects' DDL script can be locked, so that unauthorized users cannot see them:


create database procedurepractical
use procedurepractical
create table employee
( id int primary key,
  Name nvarchar(20),
  Class nvarchar(20),
  Dept nvarchar(20)
)
insert into employee values(1,'Sam','B.tech','IT')
insert into employee values(2,'Rahul','B.tech','CS')
insert into employee values(3,'Mahesh','B.tech','Mechnical')
insert into employee values(4,'Ethen Hunt','B.tech','EC')
insert into employee values(5,'Ram','B.tech','ME')
insert into employee values(6,'Jhon','B.tech','IT')

select * from employee

Create proc employeeproc
As
 begin
    select Name,Class from employee
 end

--------------------------------------------------------------------------------       --------------------------------------------------------------------------------
TO create procedure we can type CREATE PROC or CREATE PROCEDURE it depends on you
To execute store procedure just type the name or Execute ProcuderName or EXEC procedure name



select * from employee
 
create proc getemployee_by_parameter
@Class nvarchar(20),
@Dept nvarchar(20)
as
  begin
       Select Name,Class,Dept from employee where Class= @Class and Dept=@Dept

  End


   select * from employee
   --To execute we two way
  getemployee_by_parameter 'B.tech','IT'
  getemployee_by_parameter  @Class='B.tech',@Dept='ME'

 IF i want to view the text or definition of the store procedure to use following queries
 Right clik on store procedure go for script store procedure as--->Create to-->New query Window
 Two above procedure are the UDP
 Another way to view the

 sp_helptext getemployee_by_parameter(store_procedure name)

 sp_helptext getemployee_by_parameter

To change store procedure implementation

  sp_helptext employeeproc

  Create proc employeeproc
As

 begin
    select Name,Class from employee order by id
 end


 After execute above procedure we got error -There is already an object named 'employeeproc' in the database.
 
Now we would not be change store_procedure name just a way to do that
  TO change the definition of the sp we use ALTER


  Alter proc employeeproc
As

 begin
    select Name,Class from employee order by id
 end

 --TO delete procedure (Drop procedure procedureName)

 Drop proc employeeproc

 To encrypt the store procedure---> we use WITH ENCRYPTION

 sp_helptext getemployee_by_parameter


 Alter proc getemployee_by_parameter

@Class nvarchar(20),
@Dept nvarchar(20)
With encryption
as
 begin
       Select Name,Class,Dept from employee where Class= @Class and Dept=@Dept
  End
 

  Once we perform Stored Procedure WITH ENCRYPTION we can't see            the SP text


Read More

What are properties of a transaction(ACID)


What are properties of a transaction(ACID)?

ACID stands for atomicity, consistency, isolation, and durability.
These all process done during the Sql transaction.


Atomicity:
 Atomicity means the entire transaction must complete.A transaction consists of many steps. When all the steps in a transaction gets completed, it will get reflected in DB or if any step fails, all the transactions are rolled back.

Consistency:
These rules may be broken during the time  of a transaction. The database will move from one consistent state to another, if the transaction succeeds and remain in the original state, if the transaction fails.

Isolation:
Every transaction should operate as if it is the only transaction in the system.
It means No views into components of uncommitted transactions.

 Durability:
Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.


Read More

Change select and edit top 1000 rows command in MS SQL



If you use SQL Server Management Studio, you know how annoying it is when you try to select records from a table and the UI limits you to the Top 1000 records. Well there is a way to change it!!!!


Go to Tools > Options > SQL Server Object Explorer > Commands and change the values as you see fit.


1àOpen SQL Server under tool section click on option





Now, it will open another window click on  SQL Server Object Explorer.

2àChange the new value for Value for Edit Top  and Change the Value for Select     Top. In the below example we have set the value as 5000 for both options.




3àClick OK to save the changes.

4àRight click the table to see the changes.






Read More

C# code for inserting data into sql database using stored procedure


Stored Procedures in Asp.Net C# with example


The below example will help you how to call stored procedure in asp.net and insert data in database using store procedure.



 C# code for inserting data into sql database using stored procedure


STEP 1:Create table and Store Procdure in SQL server

Create table user_registration
(
 UserID int identity(1,1),
 Name varchar(50),
 UserAddress varchar(50),
 Gender varchar(6),
 UserPassword varchar(50)
)


Create procedure user_regprocedure

 @UName varchar(50),
 @UAddress varchar(50),
 @Gender varchar(6),
 @U_Password varchar(50)
  AS
  BEGIN
     INSERT INTO user_registration VALUES (@UName,@UAddress,@Gender,@U_Password)
  END

STEP 2:Add a asp.net webform.

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Registration.aspx.cs" Inherits="Registration" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
        <div>
            <table style="bordersolid 1px blackpadding45pxpositionrelativetop50px;" align="center">
                <tr>
                    <td>Name:
                    </td>
                    <td>
                        <asp:TextBox ID="txt_name" runat="server" Width="200px" Height="20px"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>Address :
                    </td>
                    <td>
                        <asp:TextBox ID="txt_address" runat="server" Width="200px" Height="20px"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td>Gender:
                    </td>
                    <td>
                        <asp:DropDownList ID="DropDownList1" runat="server" Width="205px" Height="25px">
                            <asp:ListItem Value="">Please Select</asp:ListItem>
                            <asp:ListItem>Male </asp:ListItem>
                            <asp:ListItem>Female</asp:ListItem>
                        </asp:DropDownList>
                    </td>
                </tr>
                <tr>
                    <td>Password:
                    </td>
                    <td>
                        <asp:TextBox ID="txt_pwd" runat="server" TextMode="Password" Width="200px" Height="20px"></asp:TextBox>
                    </td>
                </tr>
                <tr>
                    <td></td>
                </tr>
                <tr>
                    <td colspan="2" align="center">
                        <asp:Button ID="btnRegiser" runat="server" Text="Register" OnClick="btnRegiser_Click" />
                    </td>
                </tr>
            </table>
        </div>
    </form>
</body>
</html>

STEP 3:Put this code in "Registration.aspx.cs" file



using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

    public partial class Registration : System.Web.UI.Page
    {
        SqlConnection con;
        SqlCommand cmd;
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btnRegiser_Click(object sender, EventArgs e)
        {
            con = new SqlConnection(@"initial catalog=project; data source=SANJAY\SQL2012; integrated security=true");
            con.Open();
            SqlCommand cmd = new SqlCommand("user_regprocedure", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@UName", txt_name.Text);
            cmd.Parameters.AddWithValue("@UAddress", txt_address.Text);
            cmd.Parameters.AddWithValue("@Gender", DropDownList1.SelectedItem.Value);
            cmd.Parameters.AddWithValue("@U_Password", txt_pwd.Text);
            cmd.ExecuteNonQuery();
            con.Close();
            lbl_msg.Text = "Record Inserted";
        }
    }




Read More