Sql System Functions Examples

string functions  (23 functions)
---------------------------------------------------------------

select upper('prasad')

select lower('PRASAD')

select ltrim('   prasad')

select rtrim('prasad    ')

select substring('prasad',1,3)

select replace('prasad','p','si')

select reverse('prasad')

select len('prasad')

select ascii('P')

select char(78)

select charindex('prasad',97)

select difference(5,8)

select left('prasad',1)

select right('prasad',2)

select len('prasad')

select nchar(102)

select patindex('prasad','s')

select quotename('PRASAD')

select replicate('prasad',5)

select empname from tblemp where soundex(empname)=soundex('prasad')

select space(5)

select str('20.4')

select str('20.6')

select stuff('prasad',3,1,'a')

select substring('prasad',4,6)

select unicode('prasad')

----------------------------------------------------------------------
datetime functions
-----------------------------------------------------------------------
select getdate()

select day(getdate())

select dateadd(yy,5,getdate())

select dateadd(dd,5,getdate())

select dateadd(mm,5,getdate())

select datename(dw,getdate())

select datename(yy,getdate())

select datename(mm,getdate())

select datename(dd,getdate())

select datediff(dd,2011-06-10,getdate())

select datediff(dd,getdate(),2011-06-10)

select datediff(mm,2011-06-10,getdate())

select datediff(yy,2008,getdate())

select datepart(dd,getdate())

select datepart(mm,getdate())

select datepart(yy,getdate())

select getutcdate()

select month(2005-05-06)

select year(getdate())

-------------------------------------------------------------------
agrigate functions
-------------------------------------------------------------------

select avg(5895/565)

select binary_checksum(1001001000)

select checksum(256456+455454)

select checksum_agg(54545)

select count(4544)

select count_big(544)

select grouping(salary) from tblemp

select max(salary) from tblemp

select min(salary) from tblemp

select stdev(salary) from tblemp

select stdevp(salary) from tblemp

select sum(salary) from tblemp

select var(salary) from tblemp

select varp(salary) from tblemp

-----------------------------------------------------------------------
Configuration Functions
------------------------------------------------------------------------

select @@datefirst

select @@Dbts

select @@langid

select @@language

select @@lock_timeout

select @@max_connections

select @@max_precision

select @@nestlevel

select @@options

select @@remserver

select @@servername

select @@servicename

select @@spid

select @@textsize

select @@version

----------------------------------------------------------------------------------
Cursor Functions
----------------------------------------------------------------------------------

select @@cursor_rows

select cursor_status()

select @@fetch_status

----------------------------------------------------------------------------------
mathematical functions
-----------------------------------------------------------------------------------
select abs(536.4)

select acos(.665)

select asin(.4545)

select atan(4545.556)

select atn2(4545.556,445.554)

select ceiling(2454.445)

select cos(0)

select cot(120)

select degrees(45)

select exp(5+5)

select floor(556.1)

select log(10)

select log10(20)

select pi()

select power(2,5)

select radians(55)

select rand()

select round(55,6)

select sign(90)

select sqrt(2)

select square(2)

select tan(90)

-------------------------------------------------------------------------------------
MetaData Functions
-------------------------------------------------------------------------------------
select col_length('empname','empid') from tblemp

select col_name(4,4) from tblemp






insert into tblemp values('ram','40000','ccc','ccc')

select * from tblemp

select max(salary) from tblemp

select * from tblemp where salary=(select max(salary) from tblemp)

select * from tblemp where salary=(select max(salary) from tblemp)

select * from tblemp distinct where top 2 salary=(select * from tblemp order by salalry desc)  //error

select * from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp)) //2nd higest salary

select * from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp))))

select * from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp))

create view emp1
as
select empid,empname,salary from tblemp


exec dbo.udf_tblemp


var s=from n in dc.tblMasterUsers join o in dc.tblOrganizations on n.UserID equals 0.UserID select new{n.UserID,n.FirstName,o.OrgID,o.Orgname};

How to copy table structure and data into another database using query

select * into destinationdb..destinationtable from sourcedb..sourcetable

How to restore and backup database in sqlserver using query

For BackUp:
backup database mydb to disk = 'c:\mydb.bak'

For Restore:

restore database mydb from disk = 'c:\mydb.bak'

how to Insert data of one database table to another database table

INSERT INTO Database2..Table1 SELECT * FROM Database1..Table1

how to change the database name in sqlserver using query

ALTER DATABASE databasename MODIFY NAME = newdatabasename

Another Way

sp_renamedb 'olddbname','newdbname'

how to make nvarchar id auto incerement function in sqlserver

create function idincr() returns nvarchar(50)

as begin

declare @id int, @cid nvarchar(50), @did int

set @id=(select count(*) from tblcust)

if(@id>0)

begin

set @did= (select max(cast(substring(custid,2,len(custid))as int))from tblcust)+1;

set @cid='A'+cast(@did as nvarchar(50))

end

else 

set @cid='A1'

return @cid

end

Go

select dbo.idincr()

Go

Keybord ASCII Values in JavaScript

<script type="text/javascript">
document.onkeyup = KeyCheck;

function KeyCheck()
{
   var KeyID = event.keyCode;
   switch(KeyID)
   {
      case 16:
      document.Form1.KeyName.value = "Shift";
      break; 
      case 17:
      document.Form1.KeyName.value = "Ctrl";
      break;
      case 18:
      document.Form1.KeyName.value = "Alt";
      break;
      case 19:
      document.Form1.KeyName.value = "Pause";
      break;
      case 37:
      document.Form1.KeyName.value = "Arrow Left";
      break;
      case 38:
      document.Form1.KeyName.value = "Arrow Up";
      break;
      case 39:
      document.Form1.KeyName.value = "Arrow Right";
      break;
      case 40:
      document.Form1.KeyName.value = "Arrow Down";
      break;
   }
}</script>

Casing in .Net(NameSpaces,Classes,Properties etc...)

Pascal case

The first letter in the identifier and the first letter of each subsequent concatenated word are capitalized.

Example:

BackColor, DataSet

Camel case

The first letter of an identifier is lowercase and the first letter of each subsequent concatenated word is capitalized.

Example:

numberOfDays, isValid

Uppercase

All letters in the identifier are capitalized.

Example:

ID, PI

1).  Private Variables: _strFirstName, _dsetEmployees

2).  Local Variables: strFirstName, dsetEmployees

3).  Namespace:(Pascal) System.Web.UI, System.Windows.Forms

4).  Class Naming:(Pascal) FileStream, Button

5).  Interface Naming:(Pascal) IServiceProvider, IFormatable

6).  Parameter Naming:(Camel) pTypeName, pNumberOfItems

7).  Method Naming:(Pascal) RemoveAll(), GetCharAt()

7).  Method Parameters:(Camel) void SayHello(string name)....
8).  Property / Enumerations Naming:(Pascal) BackColor, NumberOfItems

9).  Event Naming:(Pascal) public delegate void MouseEventHandler(object sender, MouseEventArgs e);

10).  Exception Naming: catch (Exception ex){ }

11).  Constant Naming: AP_WIN, CONST

BlogSpot for more information

http://www.lovethedot.net/?expref=next-blog

 LOVE THE DOT

Some Coding Standards in .net

1.    Use Meaningful, descriptive words to name variables. Do not use abbreviations.

    Good:
    string address
    int salary

     Not Good:

           string name

       string addr

      int sal

2. Do not use single character variable names like i, n, s etc. Use names like index, temp

     variable 'i' is used for for iterations in loops

        Ex:  for ( int i = 0; i < count; i++ ){....}

3.  Do not use underscores (_) for local variable names. But member variables must be prefixed with underscore (_)

4.  Prefix boolean variables, properties and methods with “is” or similar prefixes.

      Ex: private bool _isFinished

5.  File name should match with class name.

       Ex:  for the class HelloWorld, the file name should be helloworld.cs (or, helloworld.vb) 

6.  Keep private member variables, properties and methods in the top of the file and public members in the bottom.  

7. Use String.Empty instead of “”

      Ex: txtMobile= String.Empty;

8.   8. Avoid having very large files. If a single file has more than 1000 lines of code, it is a good candidate for refactoring. Split them logically into two or more classes.

9. Use StringBuilder class instead of String when you have to manipulate string objects in a loop. The String object works in weird way in .NET. Each time you append a string, it is actually discarding the old string object and recreating a new object, which is a relatively expensive operations.

Trigger firing in SqlServer2005

trigger in sql server2005
create trigger database name on tblname
for insert
as
set nocount on
print 'insert'
go

query
insert into tblbinary values(2,3,4)
output



insert

(1 row(s) affected)

DATA LIST IN ASP.NET

findcontrol of datalist control in asp.net

Source:
<body>
    <form id="form1" runat="server">
    <div>
        <asp:DataList ID="DataList1" runat="server"
            onitemcommand="DataList1_ItemCommand">
        <ItemTemplate>
        <table border="1">
        <tr>
        <td>
        <%#Eval("id") %>
        </td>
        <td>
        <%#Eval("name") %>
        </td>
        <td>
        <%#Eval("adress") %>
        </td>
        <td>
        <%#Eval("mobil") %>
        </td>
        </tr>
        </table>
        </ItemTemplate>
            <FooterTemplate>
                <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
                </asp:DropDownList>
                <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
                <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>
                <asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>
                <asp:Button ID="Button1" runat="server" Text="add" CommandName="hhh" />
            </FooterTemplate>
        </asp:DataList>
 
    </div>
    </form>
</body>

C# code:



protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            da = new SqlDataAdapter("select * from details", con);
            ds = new DataSet();
            da.Fill(ds);
            DataList1.DataSource = ds;
            DataList1.DataBind();
         }
    }
    protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
    {
        if (e.Item.ItemType == ListItemType.Footer)
        {
                TextBox t1 = (TextBox)e.Item.FindControl("TextBox1");
                TextBox t2 = (TextBox)e.Item.FindControl("TextBox2");
                TextBox t3 = (TextBox)e.Item.FindControl("TextBox3");
                TextBox t4 = (TextBox)e.Item.FindControl("TextBox4");
             
                da = new SqlDataAdapter("insert into details values('" + t1.SelectedValue.ToString() + "','" + t2.Text + "','" + t3.Text + "','" + t4.Text + "')", con);
                ds = new DataSet();
                da.Fill(ds);
                this.Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "alert('Inserted Successfully')", true);
         }
    }

SQL QUERIES

how to change the database name in sqlserver using query

ALTER DATABASE databasename MODIFY NAME = newdatabasename

how to Insert data of one database table to another database table

INSERT INTO Database2..Table1 SELECT * FROM Database1..Table1

How to restore and backup database in sqlserver using query

For BackUp:
backup database mydb to disk = 'c:\mydb.bak'

For Restore:

restore database mydb from disk = 'c:\mydb.bak'

How to copy table structure and data into another database using query

select * into destinationdb..destinationtable from sourcedb..sourcetable

how to restore database from sqlserver2008 to sqlserver2005

Right click on database of sqlserver2008 that u want to restore-->
Tasks-->
GenerateScripts-->
Next-->
Select Particular database that u want to restore-->
Next-->
Next-->
Select Tables and UserDefined Functions-->
Next-->
Select Tables and Functions-->
Next-->
Select Script to New QueryWindow-->
Next-->
Finish.

It gives a long Query. Copy It, Then execute in Sqlserver2005.
It's working...

SQL all type of functions

string functions  (23 functions)
---------------------------------------------------------------

select upper('prasad')

select lower('PRASAD')

select ltrim('   prasad')

select rtrim('prasad    ')

select substring('prasad',1,3)

select replace('prasad','p','si')

select reverse('prasad')

select len('prasad')

select ascii('p')

select char(78)

select charindex('prasad',97)

select difference(5,8)

select left('prasad',1)

select right('prasad',2)

select len('prasad')

select nchar(102)

select patindex('prasad','s')

select quotename('PRASAD')

select replicate('prasad',5)

select empname from tblemp where soundex(empname)=soundex('prasad')

select space(5)

select str('20.4')

select str('20.6')

select stuff('prasad',r,s,d)

select substring('prasad',4,6)

select unicode('prasad')

----------------------------------------------------------------------
datetime functions
-----------------------------------------------------------------------
select getdate()

select day(getdate())

select dateadd(yy,5,getdate())

select dateadd(dd,5,getdate())

select dateadd(mm,5,getdate())

select datename(dw,getdate())

select datename(dy,getdate())

select datename(mm,getdate())

select datename(dd,getdate())

select datediff(dd,2011-06-10,getdate())

select datediff(dd,getdate(),2011-06-10)

select datediff(mm,2011-06-10,getdate())

select datediff(yy,2008,getdate())

select datepart(dd,getdate())

select datepart(mm,getdate())

select datepart(yy,getdate())

select getutcdate()

select month(2005-05-06)

select year(getdate())

-------------------------------------------------------------------
agrigate functions
-------------------------------------------------------------------

select avg(5895/565)

select binary_checksum(1001001000)

select checksum(256456+455454)

select checksum_agg(54545)

select count(4544)

select count_big(544)

select grouping(salary) from tblemp

select max(salary) from tblemp

select min(salary) from tblemp

select stdev(salary) from tblemp

select stdevp(salary) from tblemp

select sum(salary) from tblemp

select var(salary) from tblemp

select varp(salary) from tblemp

-----------------------------------------------------------------------
Configuration Functions
------------------------------------------------------------------------

select @@datefirst

select @@Dbts

select @@langid

select @@language

select @@lock_timeout

select @@max_connections

select @@max_precision

select @@nestlevel

select @@options

select @@remserver

select @@servername

select @@servicename

select @@spid

select @@textsize

select @@version

----------------------------------------------------------------------------------
Cursor Functions
----------------------------------------------------------------------------------

select @@cursor_rows

select cursor_status()

select @@fetch_status

----------------------------------------------------------------------------------
mathematical functions
-----------------------------------------------------------------------------------
select abs(536.4)

select acos(.665)

select asin(.4545)

select atan(4545.556)

select atn2(4545.556,445.554)

select ceiling(2454.445)

select cos(0)

select cot(120)

select degrees(45)

select exp(5+5)

select floor(556.1)

select log(10)

select log10(20)

select pi()

select power(2,5)

select radians(55)

select rand()

select round(55,6)

select sign(90)

select sqrt(2)

select square(2)

select tan(90)

-------------------------------------------------------------------------------------
MetaData Functions
-------------------------------------------------------------------------------------
select col_length('empname','empid') from tblemp

select col_name(4,4) from tblemp






insert into tblemp values('ram','40000','ccc','ccc')

select * from tblemp

select max(salary) from tblemp

select * from tblemp where salary=(select max(salary) from tblemp)

select * from tblemp where salary=(select max(salary) from tblemp)

using distinct key word find max or min salary
select min(salary) from (select distinct top 3 salary from tblpp order by salary desc)tblpp
another type
select min(salary) from tblpp where salary in (select distinct top 3 salary from tblpp order by salary desc)
another way
select max(salary) from tblpp where salary =(select max(salary) from tblpp where salary <(select max(salary) from tblpp))



using alter command add column to table
alter table tblpp add address nvarchar(50)
alter table tblpp add salary nvarchar(50)
sp_renamedb olddbname,new dbname

select * from tblemp distinct where top 2 salary=(select * from tblemp order by salalry desc)  //error

select * from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp)) //2nd higest salary

select * from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp))))

select * from tblemp where salary=(select max(salary) from tblemp where salary<(select max(salary) from tblemp))

create view emp1
as
select empid,empname,salary from tblemp


exec dbo.udf_tblemp


var s=from n in dc.tblMasterUsers join o in dc.tblOrganizations on n.UserID equals 0.UserID select new{n.UserID,n.FirstName,o.OrgID,o.Orgname};

Time Increment in c# Using Timer Control

HTML CODE

<asp:ScriptManager ID="ScriptManager1" runat="server">
                </asp:ScriptManager>                
                 <asp:UpdatePanel ID="UpdatePanel1" runat="server">

                    <ContentTemplate>
                        <asp:Timer ID="Timer2" runat="server" Interval="1000" ontick="Timer2_Tick">
                        </asp:Timer>
                        <asp:Label ID="lblhrs" runat="server"></asp:Label>
                        <asp:Label ID="lblmin" runat="server"></asp:Label>
                        <asp:Label ID="lblsec" runat="server"></asp:Label>
                    </ContentTemplate>
                </asp:UpdatePanel>

C#  CODE

using System;

using System.Collections;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Drawing;

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

{

    static int m = 1;

    static int s = 1;

    static int h = 1;

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            lblmin.Text = "00";

            lblsec.Text = "00";

            lblhrs.Text = "00";

        }

    }

    protected void Timer1_Tick(object sender, EventArgs e)

    {

        UpdatePanel1.Visible = true;

        //hours

        if (m >= 60)

        {

            lblhrs.Text = h.ToString();

            h++;

        }

        //minuts

        if (s == 60)

        {

            if (m >= 10)

            {

                lblmin.Text = ":" + m.ToString();

            }

            else

            {

                lblmin.Text = ":" + "0" + m.ToString();

            }

            s = 0;

            if (s >= 10)

            {

                lblsec.Text = ":" + s.ToString();

            }

            else

            {

                lblsec.Text = ":" + "0" + s.ToString();

            }

            m++;

        }

        //seconds

        if (s >= 10)

        {

            lblsec.Text = ":" + s.ToString();

        }

        else

        {

            lblsec.Text = ":" + "0" + s.ToString();

        }

        s++;

    }

}