Vikash Chauhan: DATA BASE

What is Indexing in Database?

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space

An index is used to quickly locate the data record(s) from which the required data is read. In other words, the index is only used to locate data records in the table and not to return data.

Type of Indexing in Database

Non-clustered

The index in the back of a book is an example of a non-clustered index. A non-clustered index has the indexed columns and a pointer or bookmark pointing to the actual row.

Clustered

A printed phone directory is a great example of a clustered index. Each entry in the directory represents one row of the table. A table can have only one clustered index.

Find The Third Highest Salary
/*----------------------------------------*/
Select MIN(EmpSal) From Employee WHERE EmpSal IN (Select top 3 EmpSal From Employee ORDER BY EmpSal DESC)

Creating Insert stored Processor
/*---------------------------------------*/

CREATE PROCEDURE insertproc

(@fname varchar(50),@lname varchar(50),@mobile varchar(50))
AS
BEGIN
insert into info (fname,lname,mobile) values (@fname,@lname,@mobile);
END

Create Insert Processors in MS SQL Server
/*-----------------------------------------------------------*/

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[SpSaveCard]
@loginid varchar(50),
@totalqty numeric(18,0),
@totalamount numeric(18,2),
@hallmark varchar(50),
@OrderList varchar(500),
@chain numeric(18,2),
@qty numeric(18,0),
@totalprice numeric(18,2)

AS

declare @orderdetailid numeric(18,0)
declare @msg int
set @msg = 0
begin tran

INSERT INTO MLJ_OrderDetail(loginid,totalqty,totalamount) VALUES (@loginid,@totalqty,@totalamount)

select @orderdetailid=@@identity

select @msg = @@error

if @msg=0
begin

SET NOCOUNT ON

DECLARE @OrderID varchar(10), @Pos int

SET @OrderList = LTRIM(RTRIM(@OrderList))+ ','
SET @Pos = CHARINDEX(',', @OrderList, 1)

IF REPLACE(@OrderList, ',', '') <> ''
BEGIN
WHILE @Pos > 0
BEGIN
SET @OrderID = LTRIM(RTRIM(LEFT(@OrderList, @Pos - 1)))
IF @OrderID <> ''
BEGIN
if @msg=0
begin
insert into MLJ_OrderInfo(orderdetailid,chainid,hallmark,chain,qty,totalprice)values(@orderdetailid,CAST(@OrderID AS numeric(18,0)),@hallmark,@chain,@qty,@totalprice)
select @msg = @@error
end
END
SET @OrderList = RIGHT(@OrderList, LEN(@OrderList) - @Pos)
SET @Pos = CHARINDEX(',', @OrderList, 1)
END
END
end

If @msg = 0
Begin
commit tran
End
Else
Begin
rollback tran
End

RETURN

INNER JOIN in SQL SERVER 2005

/*----------------------------------------------*/

select emp.ename,dept.dname
from emp,dept
where emp.empid=dept.empid

Create View from two tables in SQL Server 2005
/*--------------------------------------------------------------*/

CREATE VIEW my_view
AS
SELECT ename pname
FROM emp dept

Create before delete Trigger in Oracle 10g
/*------------------------------------------------------*/

create or replace trigger back1 before delete on emp
Declare
vempno emp.empno%TYPE;
vename emp.ename%TYPE;
vsal emp.sal%TYPE;
Begin
--select empno into vempno from emp;
--select ename into vename from emp;
--select sal into vsal from emp;
vempno := :old.empno;
vename := :old.ename;
vsal := :old.sal;
insert into backemp values(vempno,vename,vsal);
end;
Create after update Trigger in Oracle 10g
/*----------------------------------------------------*/

create trigger usertrigger
after update or delete on user1
for each row
declare
oper varchar2(20);
begin
if updating then
oper:='update';
end if;
if deleting then
oper:='delete';
end if;
insert into user2 values(:old.uno,:old.uname,:old.usal);
end;
Create after update Trigger in Oracle 10g

/*-------------------------------------------------------*/

create trigger emptrigger

after update or delete on emp

for each row

declare

oper varchar2(20);

begin

if updating then

oper:='update';

end if;

if deleting then

oper:='delete';

end if;

insert into emptest values(:old.empno,:old.ename,:old.sal);

end;

/*-------------------Complex Store Processor----------------*/

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[SpVenueInfo]

@getcityid numeric(18,0),

@querytype int

if @querytype=1

begin

Select distinct companyname,

(case when typeid=1 then 'Not Specified' else contactperson end)contactperson,

(case when typeid=1 then 'Not Specified' else contactno end)contactno,

address,cityname,capacity,(case when typeid=1 then 'Not Specified' else isnull(servicesoffered,'Not Specified') end)servicesoffered,

(case when typeid=1 then 'Not Specified' else isnull(email,'Not Specified') end)email,

(case when typeid=1 then 'Not Specified' else isnull('<a href="'+ website +'" target="_blank">'+ website +'</a>','Not Specified') end)website,

(case when typeid=1 then 'images/images/venue_20.jpg' else isnull(url,'images/images/venue_20.jpg') end)url,

areaid,minrange,maxrange,loginid,typeid,keywords,status,cityid,commonid,areaname,

(case when map is null then 'No Map' else '<a href="showmap.aspx?mapid='+convert(varchar,commonid)+'&tablename=tbl_banquetinfo">View Map</a>' end)map,priority FROM View_UpdateVenue WHERE status=1 AND cityid=@getcityid ORDER BY companyname,priority,typeid desc

end

else

begin

Select distinct companyname,

(case when typeid=1 then 'Not Specified' else contactperson end)contactperson,

(case when typeid=1 then 'Not Specified' else contactno end)contactno,

address,cityname,capacity,(case when typeid=1 then 'Not Specified' else isnull(servicesoffered,'Not Specified') end)servicesoffered,

(case when typeid=1 then 'Not Specified' else isnull(email,'Not Specified') end)email,

(case when typeid=1 then 'Not Specified' else isnull('<a href="'+ website +'" target="_blank">'+ website +'</a>','Not Specified') end)website,

(case when typeid=1 then 'images/images/venue_20.jpg' else isnull(url,'images/images/venue_20.jpg') end)url,

subcategoryid,areaid,minrange,maxrange,loginid,typeid,keywords,status,cityid,commonid,areaname,

end

RETURN

/*------Sencond last gold Rate-------*/

SELECT MIN(goldrate)FROM MLJ_BillProduct

WHERE goldrate IN (SELECT DISTINCT TOP 3 goldrate FROM MLJ_BillProduct ORDER BY goldrate DESC )

/*------T-SQL(Transat SQL)-------*/

/*-------------------------------*/

IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1

PRINT 'It is the weekend.'

ELSE

PRINT 'It is a weekday.'

-------------------------------------------------

IF DATEPART(dw, GETDATE()) = 7 OR DATEPART(dw, GETDATE()) = 1

BEGIN

PRINT 'It is the weekend.'

PRINT 'Get some rest in weekend!'

END

ELSE

BEGIN

PRINT 'It is a weekday.'

PRINT 'Get to work in weekday!'

END

-----------------------------------------------------------

IF (SELECT CountryName FROM CountryMast WHERE ID=2)='USA'

BEGIN

PRINT 'Country is USA.'

END

ELSE

BEGIN

PRINT 'Country is not USA'

END

-----------------------------------------------------------

ALTER PROCEDURE TEST

@ID INT,

@CountryName VARCHAR(50)

IF (SELECT CountryName FROM CountryMast WHERE ID=@ID AND CountryName=@CountryName)='USA'

BEGIN

PRINT 'Country is USA.'

END

ELSE

BEGIN

PRINT 'Country is not USA'

END

/*-Exec Procedure with parameter--*/

EXEC TEST'2','USA'

------------------------------------------------------------

ALTER PROCEDURE FINDCOUNTRY

@ID INT

DECLARE @CName VARCHAR(50)

SET @CName=(SELECT CountryName FROM CountryMast WHERE ID=@ID )

IF @CName!=''

BEGIN

PRINT @CName

END

ELSE

BEGIN

PRINT 'COUNTRY NAME NOT EXIST'

END

/*-Exec Procedure with parameter--*/

EXEC FINDCOUNTRY'16'

-----------------------------------------------------------

DECLARE @i INT

SET @i = 0

WHILE @i < 5

BEGIN

PRINT 'Hello world.'

PRINT @i

SET @i = @i + 1

END

--------------------------------------------------------------

-- begin transaction

BEGIN TRAN

BEGIN TRY

-- execute each statement

INSERT INTO CountryMast(CountryName) VALUES ('India')

INSERT INTO CountryMast(CountryName) VALUES ('123456')

-- commit the transaction

COMMIT TRAN

END TRY

BEGIN CATCH

-- rollback the transaction because of error

ROLLBACK TRAN

END CATCH

/*---Full Exam of Trigger-------*/

/*------------------------------*/

CREATE TABLE Employee_Test

(

Emp_ID INT Identity,

Emp_name Varchar(100),

Emp_Sal Decimal (10,2)

)

---------------------------------

INSERT INTO Employee_Test VALUES ('Anees',1000);

INSERT INTO Employee_Test VALUES ('Rick',1200);

INSERT INTO Employee_Test VALUES ('John',1100);

INSERT INTO Employee_Test VALUES ('Stephen',1300);

INSERT INTO Employee_Test VALUES ('Maria',1400);

---------------------------------------------------

CREATE TABLE Employee_Test_Audit_Delete

(

Emp_ID int,

Emp_name varchar(100),

Emp_Sal decimal (10,2),

Audit_Action varchar(100),

Audit_Timestamp datetime

)

----------------------------------------------------

CREATE TRIGGER trgAfterInsert ON [dbo].[Employee_Test]

FOR INSERT

declare @empid int;

declare @empname varchar(100);

declare @empsal decimal(18,2);

declare @audit_action varchar(100)

select @empid =i.Emp_ID from inserted i;

select @empname=i.Emp_Name from inserted i;

select @empsal=i.Emp_Sal from inserted i;

set @audit_action='Inserted Record -- After Insert Trigger.';

insert into Employee_Test_Audit

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER INSERT trigger fired.'

------------------------------------------------------

insert into Employee_Test values('Chris',1500);

------------------------------------------------------

ALTER TRIGGER trgAfterUpdate ON [dbo].[Employee_Test]

FOR UPDATE

declare @empid int;

declare @empname varchar(100);

declare @empsal decimal(18,2);

declare @audit_action varchar(100);

select @empid=i.Emp_ID from inserted i;

select @empname=i.Emp_Name from inserted i;

select @empsal=i.Emp_Sal from inserted i;

if update(Emp_Name)

set @audit_action='Updated Record -- After Update Trigger.';

if update(Emp_Sal)

set @audit_action='Updated Record -- After Update Trigger.';

insert into Employee_Test_Audit_Update

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER UPDATE Trigger fired.'

----------------------------------------------------------------------

update Employee_Test set Emp_Sal='2000' where Emp_ID=1

------------------------------------------------------------------

CREATE TRIGGER trgAfterDelete ON [dbo].[Employee_Test]

AFTER DELETE

declare @empid int;

declare @empname varchar(100);

declare @empsal decimal(10,2);

declare @audit_action varchar(100);

select @empid=d.Emp_ID from deleted d;

select @empname=d.Emp_Name from deleted d;

select @empsal=d.Emp_Sal from deleted d;

set @audit_action='Deleted -- After Delete Trigger.';

insert into Employee_Test_Audit_Delete

(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@empid,@empname,@empsal,@audit_action,getdate());

PRINT 'AFTER DELETE TRIGGER fired.'

--------------------------------------------------------

delete Employee_Test where Emp_ID=4

-----------------------------------------------------------

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Employee_Test]

INSTEAD OF DELETE

declare @emp_id int;

declare @emp_name varchar(100);

declare @emp_sal int;

select @emp_id=d.Emp_ID from deleted d;

select @emp_name=d.Emp_Name from deleted d;

select @emp_sal=d.Emp_Sal from deleted d;

BEGIN

if(@emp_sal>1200)

begin

RAISERROR('Cannot delete where salary > 1200',16,1);

ROLLBACK;

end

else

begin

delete from Employee_Test where Emp_ID=@emp_id;

COMMIT;

insert into Employee_Test_Audit_Delete(Emp_ID,Emp_Name,Emp_Sal,Audit_Action,Audit_Timestamp)

values(@emp_id,@emp_name,@emp_sal,'Deleted -- Instead Of Delete Trigger.',getdate());

PRINT 'Record Deleted -- Instead Of Delete Trigger.'

end

END

-----------------------------------------------------------

delete Employee_Test where Emp_ID=4

----------------------------------------------------------

/* Select Date as Month-Year from Table*/

/*------------------------------------------*/

SELECT AcademicSessionId,AcademicSessionName, CONVERT(CHAR(4), FromYear, 100) + CONVERT(CHAR(4), FromYear, 120)AS FromYear,CONVERT(CHAR(4), ToYear, 100) + CONVERT(CHAR(4), ToYear, 120)AS ToYear,Description,IsActive FROM [WEM_AcademicSessionMast]where IsActive=1 AND [Description]!='0' ORDER BY [AcademicSessionName]

CREATE TRIGGER FOR Update table After Inserting Data

ALTER TRIGGER WEM_UpdateAccountEIILMTotalBalance ON WEM_AccountEIILM

AFTER INSERT

DECLARE @SBHTAccountId numeric(18,0)

DECLARE @CreditAmount numeric(18,0)

DECLARE @DebitAmount numeric(18,0)

DECLARE @TotalBalance numeric(18,0)

SET @TotalBalance=0

DECLARE @TotalBalance1 numeric(18,0)

SET @TotalBalance=0

BEGIN

SET @SBHTAccountId=(SELECT SBHTAccountId FROM INSERTED i);

SET @CreditAmount=(SELECT CreditAmount FROM INSERTED i);

SET @DebitAmount=(SELECT DebitAmount FROM INSERTED i);

SET @TotalBalance1=(SELECT TotalBalance FROM WEM_AccountEIILM WHERE SBHTAccountId=@SBHTAccountId);

SET @TotalBalance=(SELECT TotalBalance FROM WEM_AccountEIILM WHERE SBHTAccountId=@SBHTAccountId-1);

IF @CreditAmount !=0

BEGIN

SET @TotalBalance=(@TotalBalance+@CreditAmount);

END

IF @DebitAmount !=0

BEGIN

SET @TotalBalance=(@TotalBalance-@DebitAmount);

END

UPDATE WEM_AccountEIILM SET TotalBalance=@TotalBalance WHERE SBHTAccountId=@SBHTAccountId;

END

-------For Execution----------

INSERT INTO WEM_AccountEIILM VALUES('8 Feb 2012','Credit',10000,0,0,1);

While loop in Store Procedure

ALTER PROCEDURE [dbo].[SumStreamSBHTFee]

@CIIPId numeric(18,0)

DECLARE @intFlag INT

SET @intFlag = 1

DECLARE @Count numeric (18,0)

SET @Count=0

DECLARE @SUM numeric(18,0)

SET @SUM=0

DECLARE @StreamSBHTId numeric(18,0)

DECLARE @StreamSBHTFee numeric(18,0)

SET @StreamSBHTFee=0

SET @Count=(SELECT COUNT(CIIPId) FROM View_WEM_CentreCourseSBHT WHERE CIIPId=@CIIPId);

WHILE (@intFlag <=@Count)

BEGIN

SELECT StreamSBHTFee FROM View_WEM_CentreCourseSBHT WHERE CIIPId=@CIIPId

PRINT @StreamSBHTFee

---PRINT @intFlag

SET @intFlag = @intFlag + 1

END

Sting Function in Store Procedure

ALTER PROCEDURE [dbo].[WEM_SpInsertStudentFeeEIILM]

@StudentAmountEIILMId numeric(18,0),

@TStudentId varchar(500)

DECLARE @IsActive int

SET @IsActive=1

DECLARE @flag int

SET @flag=1

DECLARE @ch char(50)

DECLARE @len int

SET @len=LEN(@TStudentId);

WHILE(@flag<=@len)

BEGIN

SET @ch= (SUBSTRING(@TStudentId,@flag,1));

IF( @ch !=',')

INSERT INTO WEM_StudentFeeEIILM (StudentId,StudentAmountEIILMId,IsActive) VALUES (@ch,@StudentAmountEIILMId,@IsActive);

SET @flag=@flag+1

END

/*---------Sum of Two Columns in a Table------------*/

select image2,CAST(dweight1 AS decimal(10,3))+CAST(dweight2 AS decimal(10,3))+CAST(dweight3 AS decimal(10,3))+CAST(dweight4 AS decimal(10,3))+CAST(dweight5 AS decimal(10,3))+CAST(sweight1 AS decimal(10,3))+CAST(sweight2 AS decimal(10,3))+CAST(sweight3 AS decimal(10,3))+CAST(sweight4 AS decimal(10,3)) as diamondweight,diamondcode,subcategoryname from MLCD_View_DiamondInfo where diamondid=21

1 Export data to existing EXCEL file from SQL Server table

insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=D:\testing.xls;',

'SELECT * FROM [SheetName$]') select * from SQLServerTable

2 Export data from Excel to new SQL Server table

select *

into SQLServerTable FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=D:\testing.xls;HDR=YES',

'SELECT * FROM [Sheet1$]')

3 Export data from Excel to existing SQL Server table

Insert into SQLServerTable Select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;Database=D:\testing.xls;HDR=YES',

'SELECT * FROM [SheetName$]')

4 If you dont want to create an EXCEL file in advance and want to export data to it, use

EXEC sp_makewebtask

@outputfile = 'd:\testing.xls',

@query = 'Select * from Database_name..SQLServerTable',

@colheaders =1,

@FixedFont=0,@lastupdated=0,@resultstitle='Testing details'

(Now you can find the file with data in tabular format)

5 To export data to new EXCEL file with heading(column names), create the following procedure

create procedure proc_generate_excel_with_columns

(

@db_name varchar(100),

@table_name varchar(100),

@file_name varchar(100)

)

--Generate column names as a recordset

declare @columns varchar(8000), @sql varchar(8000), @data_file varchar(100)

select

@columns=coalesce(@columns+',','')+column_name+' as '+column_name

from

information_schema.columns

where

table_name=@table_name

select @columns=''''''+replace(replace(@columns,' as ',''''' as '),',',',''''')

--Create a dummy file to have actual data

select @data_file=substring(@file_name,1,len(@file_name)-charindex('\',reverse(@file_name)))+'\data_file.xls'

--Generate column names in the passed EXCEL file

set @sql='exec master..xp_cmdshell ''bcp " select * from (select '+@columns+') as t" queryout "'+@file_name+'" -c'''

exec(@sql)

--Generate data in the dummy file

set @sql='exec master..xp_cmdshell ''bcp "select * from '+@db_name+'..'+@table_name+'" queryout "'+@data_file+'" -c'''

exec(@sql)

--Copy dummy file to passed EXCEL file

set @sql= 'exec master..xp_cmdshell ''type '+@data_file+' >> "'+@file_name+'"'''

exec(@sql)

--Delete dummy file

set @sql= 'exec master..xp_cmdshell ''del '+@data_file+''''

exec(@sql)

After creating the procedure, execute it by supplying database name, table name and file path

EXEC proc_generate_excel_with_columns 'your dbname', 'your table name','your file path'

---Insert Trigger at WEM_CountryMast table

----------------------------------------------

CREATE TRIGGER AfterInsertCountry ON [dbo].[WEM_CountryMast]

FOR INSERT

DECLARE @CountryId int;

DECLARE @CountryName varchar(50);

DECLARE @Description varchar(500);

DECLARE @IsActive int;

SELECT @CountryId=c.CountryId from INSERTED C;

SELECT @CountryName=c.CountryName from INSERTED C;

SELECT @Description=c.Description from INSERTED C;

SELECT @IsActive=c.IsActive from INSERTED C;

INSERT INTO WEM_DemoCountryMast (CountryId,CountryName,Description,IsActive) VALUES (@CountryId,@CountryName,@Description,@IsActive);

---Insert some values in WEM_CountryMast table

----------------------------------------------
INSERT INTO WEM_CountryMast VALUES ('Japan','Test',1)

--Crate Duplicate table---

--------------------------

SELECT * INTO MLKF_OrderInfo_DUP FROM MLKF_OrderInfo

--Reset Identity of Table--

---------------------------

DBCC CHECKIDENT(MLKF_OrderInfo_DUP,RESEED,1)

---Delete Trigger----

---------------------

CREATE TRIGGER DeleteOrderDetail ON MLKF_OrderDetail

FOR DELETE

DECLARE @orderdetailid numeric(18,0);

SELECT @orderdetailid=o.orderdetailid FROM DELETED o;

DELETE MLKF_OrderDetail_DUP WHERE @orderdetailid=@orderdetailid;

---CREATE DATABASE BACKUP---

----------------------------

BACKUP DATABASE test

TO DISK ='F:\test.bak'

---RESTORE DATABASE BACKUP---

----------------------------

RESTORE DATABASE test

FROM DISK='F:\test.bak'

WITH REPLACE

---Restore Database When Path is Different---

---------------------------------------------

CREATE DATABASE ablazeNew

RESTORE DATABASE ablazeNew

FROM DISK='F:\AllDataScript\ablazeNew.bak'

WITH MOVE 'ablazeNew_Data' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ablazeNew_Data.mdf',

move 'ablazeNew_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ablazeNew_log.ldf', replace

---Verify the backup file---

----------------------------

RESTORE VERIFYONLY

FROM DISK='F:\AllDataScript\ablze.bak'

---Reset AutoIncrement in Table---

----------------------------------

DBCC CHECKIDENT (DateTest,RESEED,1)

Converted datatype of varchar to datetime

-----------------------------------------

declare @mincust numeric(18,0),@maxcust numeric(18,0);

declare @t table (currentdate date);

select @mincust=min(customerid) from MLAS_CustomerInfo

select @maxcust=max(customerid) from MLAS_CustomerInfo

while (@mincust<=@maxcust)

BEGIN

update MLAS_CustomerInfo

set currentDate=(select substring(currentDate,1,2)+'/'+ substring(currentDate,4,2)+'/'+convert(date,substring(currentDate,7,4)) where Customerid=@mincust)

where Customerid=@mincust

set @mincust=@mincust+1;

END

select * from @t order by currentdate desc;

----------------Delete all table,Store Procedure,Views---------------------

---------------------------------------------------------------------------

SELECT 'DROP PROCEDURE '+ s.NAME+ '.' + p.NAME FROM SYS.PROCEDURES p ,SYS.SCHEMAS s

WHERE p.SCHEMA_ID = s.SCHEMA_ID

/*Recover data BackUp*/

/*----Creating all database backups Using Cursor-----*/

DECLARE @name VARCHAR(50) -- database name

DECLARE @path VARCHAR(256) -- path for backup files

DECLARE @fileName VARCHAR(256) -- filename for backup

DECLARE @fileDate VARCHAR(20) -- used for file name

SET @path = 'C:\Backup\'

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR FOR

SELECT name

FROM MASTER.dbo.sysdatabases

WHERE name NOT IN ('master','model','msdb','tempdb')

OPEN db_cursor

FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0

BEGIN

SET @fileName = @path + @name + '_' + @fileDate + '.BAK'

BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name

END

CLOSE db_cursor

DEALLOCATE db_cursor

********************************************************************************

*** Update in table ***

update emp

set name='vikash',lastname ='chauhan'

where address ='raj'

insert into MVE_adminInfo values ('vikash','123456','vikash','1','vikash@gmail.com','901','yes');

insert into emp values('ram','kumar','kavi nagar')

delete emp where id=1

******** Searcha Table in DataBase*****

use WEmission

SELECT *

FROM sys.Tables

WHERE name = 'WEM_CountryMast'

******* Add Value in Table**********

ALTER TABLE VIKASH

ADD class varchar(20)

******* Drop Value From Table**********

ALTER TABLE VIKASH

DROP COLUMN class

*******Change varchar Value From Table**********

ALTER TABLE VIKASH

ALTER COLUMN citydescription int

**********How to Create Table**************

CREATE TABLE Persons

(

P_Id int NOT NULL AUTO_INCREMENT,

LastName varchar(255) NOT NULL,

FirstName varchar(255),

Address varchar(255),

City varchar(255),

PRIMARY KEY (P_Id)

)

SELECT GETDATE() AS CurrentDateTime

SELECT countryname ,(orderprice+ISNULL(recevieprice,0))

FROM country

SELECT countryname ,(orderprice+COALESCE(recevieprice,0))

FROM country

SELECT AVG(orderprice) AS OrderAverage FROM country

SELECT countryname FROM country

WHERE orderprice>(SELECT AVG(orderprice) FROM country)

SELECT COUNT(countryname) FROM country

SELECT COUNT(*) FROM country

SELECT COUNT(DISTINCT countryname) FROM country

SELECT min(countryname) FROM country

SELECT MIN(orderprice) AS SmallestOrderPrice FROM country

SELECT SUM(OrderPrice) AS OrderTotal FROM country

SELECT countryname,SUM(OrderPrice) FROM country

GROUP BY countryname

Add two value of two duplicate column

SELECT countryname,SUM(OrderPrice) FROM country

GROUP BY countryname

SELECT countryname FROM country

GROUP BY countryname

SELECT countryname,SUM(OrderPrice) FROM country

GROUP BY countryname

HAVING SUM(OrderPrice)<2000

SELECT LEN(countryname) as countryname FROM country

How to Execute Of Store Procedure

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

-- =============================================

-- Inserted Data in WEM_SpInsertState

-- =============================================

ALTER PROCEDURE [dbo].[WEM_SpInsertState]

@StateName varchar(50),

@Description varchar(500),

@IsActive int,

@CountryId numeric(18,0)

BEGIN

INSERT INTO WEM_StateMast (StateName,Description,IsActive,CountryId)VALUES(@StateName,@Description,@IsActive,@CountryId);

END

EXEC WEM_SpInsertState @StateName="Mp",@Description="yes",@isactive=1,@CountryId="2"

======================================================================

Image Upload with path , Name in My Sql Server

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.IO;

using System.Data;

public partial class Default3 : System.Web.UI.Page

{

clsDB objDB = new clsDB();

string sql = "";

DataSet dsCommon = new DataSet();

protected void Page_Load(object sender, EventArgs e)

{

fillGrid();

}

protected void btnSubmit_Click(object sender, EventArgs e)

{

//Get Filename from fileupload control

string filename = Path.GetFileName(fileuploadimages.PostedFile.FileName);

//Save images into Images folder

fileuploadimages.SaveAs(Server.MapPath("Images/" + "filename"));

//Getting dbconnection from web.config connectionstring

sql = "insert into imagepath(ImageName,ImagePath) values('" + filename + "','" + "Image/" + filename + "')";

objDB.executeQuery(sql);

gvImages.DataBind();

lblMsg.Text = "Images Saved successfully";

//Response.Redirect("~/Default.aspx");

}

protected void fillGrid()

{

sql = "Select * from imagepath";

dsCommon = objDB.openDataset(sql);

gvImages.DataSource = dsCommon;

Session["dsCommon"] = dsCommon;

gvImages.DataBind();

}

<title>Save Images In Folder and Display Images in Gridview from folder</title>

.Gridview

{

font-family:Verdana;

font-size:10pt;

font-weight:normal;

color:black;

}

</style>

</head>

<body>

<div>

<asp:FileUpload ID="fileuploadimages" runat="server" />

<br />

<asp:Button ID="btnSubmit" runat="server" Text="Submit" onclick="btnSubmit_Click" />

</div>

<div>

<asp:GridView runat="server" ID="gvImages" AutoGenerateColumns="false" CssClass="Gridview" HeaderStyle-BackColor="#61A6F8" >

<asp:BoundField DataField="ID" HeaderText="ID" />

<asp:BoundField DataField="ImageName" HeaderText="ImageName" />

<asp:ImageField HeaderText="Image" DataImageUrlField="ImagePath" />

</Columns>

</asp:GridView>

<asp:Label ID="lblMsg" runat="server" CssClass="txtvalidation" ForeColor="Red"></asp:Label>

</div>

</form>

</body>

</ht

APP CODE

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Data;

using System.Data.Odbc;

using System.Configuration;

/// <summary>

/// Summary description for clsDB

/// </summary>

public class clsDB

{

string connStr;

OdbcConnection conLocal;

public clsDB()

{

// TODO: Add constructor logic here

}

public void conn()

{

connStr = ConfigurationManager.AppSettings["ConStr"].ToString();

conLocal = new OdbcConnection(connStr);

}

public void executeQuery(String sSQL)

{

conn();

OdbcCommand command;

if (conLocal.State == ConnectionState.Closed)

{

conLocal.Open();

}

command = new OdbcCommand(sSQL, conLocal);

command.ExecuteNonQuery();

if (conLocal.State == ConnectionState.Open)

{

conLocal.Close();

}

public int executeScaler(String sSQL)

{

conn();

OdbcCommand command;

int id = 0;

if (conLocal.State == ConnectionState.Closed)

{

conLocal.Open();

}

command = new OdbcCommand(sSQL, conLocal);

id = Convert.ToInt32(command.ExecuteScalar());

if (conLocal.State == ConnectionState.Open)

{

conLocal.Close();

}

return id;

}

public DataSet openDataset(String sSQL)

{

conn();

DataSet ds = new DataSet();

OdbcDataAdapter adap;

adap = new OdbcDataAdapter(sSQL, conLocal);

adap.Fill(ds);

return ds;

}

</appSettings>

=======================================================================

Select, Insert, Update, Delete Using Stored Procedure in SQL Server 2008

Creating Table

CREATE TABLE employee(

id INTEGER NOT NULL PRIMARY KEY,

first_name VARCHAR(10),

last_name VARCHAR(10),

salary DECIMAL(10,2),

city VARCHAR(20),

)

Now insert some values in the table and using select statement to select a table.

INSERT INTO employee VALUES (2, 'Monu', 'Rathor',4789,'Agra');

INSERT INTO employee VALUES (4, 'Rahul' , 'Saxena', 5567,'London');

INSERT INTO employee VALUES (5, 'prabhat', 'kumar', 4467,'Bombay');

INSERT INTO employee VALUES (6, 'ramu', 'kksingh', 3456, 'jk');

select * from employee

Table looks like this.

Stored procedure for Select, insert, update, delete

Here, we create a stored procedure for select,insert,update,delete statements to select the data from the table.

Alter PROCEDURE MasterInsertUpdateDelete

(

@id INTEGER,

@first_name VARCHAR(10),

@last_name VARCHAR(10),

@salary DECIMAL(10,2),

@city VARCHAR(20),

@StatementType nvarchar(20) = ''

)

BEGIN

IF @StatementType = 'Insert'

BEGIN

insert into employee (id,first_name,last_name,salary,city) values( @id, @first_name, @last_name, @salary, @city)

END

IF @StatementType = 'Select'

BEGIN

select * from employee

END

IF @StatementType = 'Update'

BEGIN

UPDATE employee SET

First_name = @first_name, last_name = @last_name, salary = @salary,

city = @city

WHERE id = @id

END

else IF @StatementType = 'Delete'

BEGIN

DELETE FROM employee WHERE id = @id

END

end

Now press F5 to execute the stored procedure.

Now open object explorer and select storeprocedure MasterInsertUpdateDelete.

Stored Procedure to Check Insert

StatementType = 'Insert'

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

Figure3

Now for insert we fill the data in required field.

StatementType=insert

Figure4

Click on the ok Button. and check in the employee table with following inserted data.

Figure5

Stored Procedure to Check update

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Update'

Figure6

Click on the ok Button. and check in the employee table with following updated data where id is 7.

Figure7

Stored Procedure to Check Delete

MasterInsertUpdateDelete -> right click select execute stored procedure...

Execute procedure window will be open.

StatementType = 'Delete'

Figure8

we delete record from table which has id=2

Click on the ok Button. and check in the employee table with following deleted data where id is 2.

SELECT name,salary,

CASE

WHEN salary <= 2000 THEN 'low'

WHEN salary > 2000 AND salary <= 3000 THEN 'average'

WHEN salary > 3000 THEN 'high'

END AS salary_level

FROM employees

ORDER BY salary ASC

Vikash Chauhan

Pages

DATA BASE

Select, Insert, Update, Delete Using Stored Procedure in SQL Server 2008

Total Pageviews