finding highest and lowest salary in sql using co-o related query

sp_tables

select* from emp_23
alter table emp_23 add salary decimal(18,2)update emp_23 set age=4 where id =102
insert into emp_23 values ('sanjeev',5,'male',104 ,9999.32)
insert into emp_23 values ('sanjeev1',6,'male',104 ,19999.32)
insert into emp_23 values ('sanjeev2',7,'male',105 ,59999.32)
insert into emp_23 values ('sanjeev3',5,'male',104 ,79999.32)

record of table

name                age      sex      id       salary
mukesh123       3         male   103    2234.00
sanjeev             5          male   104   9999.32
sanjeev1           6           male   104   19999.32
sanjeev2           7          male     105   59999.32
sanjeev3           5          male      104   79999.32
sanjeev             5          male      104   9999.32
mukesh12        4            male      102 4234.00




 


finding 4 th highest salary using coreleted query

select     distinct salary from emp_23 e where 3=(select COUNT(distinct salary) as salary from emp_23 a where a.salary>e.salary)



select count(salary) as rokda,name,age from emp_23 where name='sanjeev' group by name,age having COUNT(name)>1
out put


rokda     name     age
2            sanjeev    5

delete and truncate both can be roll back

                        for delete
                            select * from e11       -run this script

                            id    name          ///record will display
                            2    mukesh
                            3     mks
                    
                    now run  this script one by one
                              
                           step1-run this                    begin tran aa    
                                    
                             step-2     run this                    delete e11
 
                                 now run this script     select * from e11
                                       you will see there is no record

                                       id    name         


                                                                     commit tran aa
                              step 3- run this        rollback tran aa

                    now run this script     select * from e11
                                       you will see there is  record

                             id    name          ///record will display
                            2    mukesh
                            3     mks


but if you commit transaction than no rollback is possible
                                  


            for truncate


                               select * from e11       -run this script

                            id    name          ///record will display
                            2    mukesh
                            3     mks
                    
                    now run  this script one by one
                              
                           step1-run this                    begin tran aa    
                                    
                             step-2     run this                    delete  truncate e11
 
                                 now run this script     select * from e11
                                       you will see there is no record

                                       id    name         


                                                                     commit tran aa
                              step 3- run this        rollback tran aa

                    now run this script     select * from e11
                                       you will see there is  record

                             id    name          ///record will display
                            2    mukesh
                            3     mks


but if you commit transaction than no rollback is possible
                                  

implementing same name funtion in Interface

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace
{WindowsFormsApplication1
public partial class Form1 : Form
{

{
InitializeComponent();
}public Form1()
public interface aa
{
int sum(int a,int b);
}public interface bb
{
int sum(int a, int b);
}class xx : aa,bb
{
int aa.sum(int a, int b)
{
return a + b;
} 
int bb.sum(int a, int b)
{
return a - b;
}
}
}
private void button1_Click(object sender, EventArgs e)
{
xx obj = new xx();
aa a = obj;
bb b = obj;
int p= a.sum(1, 2);
int k = b.sum(4, 2);
MessageBox.Show(p.ToString());
MessageBox.Show(k.ToString());
}

SQL Server - Difference between TSQL Functions CAST() and CONVERT()

SQL Server - Difference between TSQL Functions CAST() and CONVERT()

Both these functions are used to convert values from one datatype to another
But there are some differences between them

1 CAST is ANSI standard and CONVERT is specific to SQL Server
2 CAST can't be used for formating purposes.
But CONVERT can be used for formating purposes particularly on datetime and money datatype 

 

1.declare @d datetime

2.set @d='20001210 12:34:22'

3.select convert(varchar(10),@d,108)

4.declare @m money

5.set @m=23498

6.select convert(varchar(10),@m,1)

Results are

 

1.----------

2.12:34:22

3.  

4.----------

5.23,498.00

3 CAST can't simulate set dateformat option. But CONVERT can simulate it
The following example specifies that date value is in the format of dmy (style 103 is for dmy)

 

?

1.select convert(datetime,'19/12/2008 12:34:22',103)

Result is

 

 

1.-----------------------

2.2008-12-19 12:34:22.000

Also you should be very careful in using the both when you convert integer values to character datatype
Consider the following example

 

 

1.declare @i int

2.set @i=123456

3.select convert(char(5),@i),cast(@i as char(5))

The result is

 

 

1.----- -----

2.*     *

It is becuase the size of the variable is not enough to hold the number In this case you wont get any error.
You should in advance specify the enough length

view state in asp.net ...

Diffrence between url and uri

For non-technical purposes you can generally consider 'URL' and 'URI' equivalent. In fact, URL is a special case of URI containing information on how to access the resource in question. Thus, since every URL is a URI (but not vice versa), 'URI' is generally preferable to 'URL', unless the identifier is known to be a locator.
URL specifies the location of the resource and also the protocol to be used.
where as URI is a name for anything that points to a resource
example:-

http:/www.xyz.com/login.jsp --> URL
www.xyz.com --> URI

view state in asp.net

Encrption And Decryption Of A Connection String

For Encryption Of A Coonection String  Run this command on command prompt of administrator
After open command prompt type the following command aspnet_regiis.exe -pef "connectionStrings" "E:\website1"

After open web.config file in application and add sample db connection in connectionStrings section like this 

<connectionStrings> <add name="Constr" connectionString="Data Source= Yogi-pc /Family; Integrated Security=true;Initial Catalog= YourDatabaseName; uid=YourUserName; Password=yourpassword; " providerName="System.Data.SqlClient"/> </connectionStrings >

Example of declaring connectionStrings in web.config file like this

<connectionStrings> <add name="Constr" connectionString="Data Source=Yogi-pc /Family;Integrated Security=true;Initial Catalog=MySampleDB" providerName="System.Data.SqlClient"/> </connectionStrings >

now open your connection string look like this

<connectionStrings configProtectionProvider="RsaProtectedConfigurationProvider">

<EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"

xmlns="http://www.w3.org/2001/04/xmlenc#">

<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />

<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">

<EncryptedKey xmlns="http://www.w3.org/2001/04/xmlenc#">

<EncryptionMethod Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />

<KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">

<KeyName>Rsa Key</KeyName>

</KeyInfo>

<CipherData>

<CipherValue>ZNUbIEnOwlZzC8qbzHj5F2GS9gLYSkWCIgCJGkrgZAX8A+8oF</CipherValue>

</CipherData>

</EncryptedKey>

</KeyInfo>

<CipherData>

<CipherValue>tODWlPD0Q/B/mP14GQ/5tUxcjmhHcy9a0oPunV5osNrMQRzt</CipherValue>

</CipherData>

</EncryptedData>

</connectionStrings>

For Decryption Of Connection String Type this Command

 aspnet_regiis.exe -pdf "connectionStrings" "E:\website1"

date functions in sql.....

Declare @Date datetime set @Date = (SELECT GETDATE());print
-- Adding 5 days with Current Date @Date -- Print Current DateSELECT

DATEADD(month, 5,@Date ) AS NewTime-- Get Only YearSELECT DATEPART(year, GETDATE()) AS 'Year'-- Get Only MonthSELECT DATEPART(month, GETDATE()) AS 'Month'-- Get Only hourSELECT
DATEPART(hour, GETDATE()) AS 'Hour'-- Declare Two DateTime VariableDeclare @Date1 datetime Declare @Date2 datetime -- Set @Date1 with Current Dateset @Date1 = (SELECT GETDATE());-- Set @Date2 with 5 days more than @Date1set @Date2 = (SELECT DATEADD(day, 5,@Date1 ))-- Get The Date DifferenceSELECT


DATEDIFF(day, @Date1, @Date2) AS DifferenceOfDay-- Get Today SELECT DATENAME(dw, getdate()) AS 'Today Is'-- Get Mont nameSELECT




DATENAME(month, getdate()) AS 'Month'SELECT
 DAY-----------15MONTH()SELECT
MONTH(getdate()) AS 'Month' Month-----------8YEAR()SELECT YEAR(getdate()) AS 'Year' DAY(getdate()) AS 'DAY'

diffrence between server.transfer and response.redirect

Server.Transfer v/s Response.RedirectBoth “Server” and “Response” are objects of ASP.NET. Server.Transfer and Response.Redirect both are used to transfer a user from one page to another page. Both are used for the same purpose but still there are some differences are there between both that are as follows:

Syntactically both are different, if you want to transfer the user to a page named newpage.aspx then syntax for both methods will be, Response.Redirect(“newpage.aspx”) and Server.Transfer(“newpage.aspx”)

Response.Redirect involves a roundtrip to the server whereas Server.Transfer conserves server resources by avoiding the roundtrip. It just changes the focus of the webserver to a different page and transfers the page processing to a different page. Roundtrip means in case of Response.Redirect it first sends the request for the new page to the browser then browser sends the request for the new page to the webserver then after your page changes But in case of Server.Transfer it directly communicate with the server to change the page hence it saves a roundtrip in the whole process. If you are using Server.Transfer then you can directly access the values, controls and properties of the previous page which you can’t do with Response.Redirect. Suppose you are currently on the Page1.aspx and now you are transferring the user to the Page2.aspx using Response.Redirect then When the Page2 page is requested, Page1 has been flushed from the server’s memory and no information can be retrieved about it unless the developer explicitly saved the information using some technique like session, cookie, application, cache etc. But in case of Server.Transfer variables can stay in scope and Page2 can read properties directly from Page1 because it’s still in memory, as you know the Server.Transfer just changes the focus from page1 to page2 So in this case browser doesn’t know that any change is happen there that’s why with this method you can access the information about the previous page. Response.Redirect changes the URL in the browser’s address bar. So they can be bookmarked. Whereas Server.Transfer retains the original URL in the browser’s address bar. It just replaces the contents of the previous page with the new one. Actually in case of Server.Transfer it directly contact with the webserver for the new page request, it doesn’t involve the browser, so that browser doesn’t know that there is any change happen. But in case of Response.Redirect as you know it first send the request (for new page) to the browser then further processing will be performed, so here browser knows that yes there is some change in the browser window that’s why it changes the URL in the address bar. So, the matter “No change of address in the browser address bar” This is a good thing if you see it from security point of view but it creates problem in case if you refresh your page or in case you want to add bookmark of that page. In case of refresh and bookmark it will add perform both the action with the URL currently present in the address bar, but as you know Server.Transfer doesn’t changes the URL, so sometimes it creates problem. Response.Redirect can be used for both .aspx and html pages whereas Server.Transfer can be used only for .aspx pages and is specific to ASP and ASP.NET. With Response.Redirect you can redirect the user to the both type of pages .html or .aspx like below, Response.Redirect(“mypage.html”) OR Response.Redirect(“OtherPage.aspx”) But in case of Server.Transfer you can only work with .asp or .aspx page like below Server.Transfer(“mypage.asp”) OR Server.Transfer(“OtherPage.aspx”) Response.Redirect can be used to redirect a user to an external websites. Server.Transfer can be used only on sites running on the same server. You cannot use Server.Transfer to redirect the user to a page running on a different server. Suppose on some action on the webpage I want to redirect my user to the http://www.yahoo.com so with Response.Redirect you can redirect your user to the external site, but in case of Server.Transfer you can only work with the .asp or .aspx pages that are present in your site. Now the question is which to use and when to use? Mostly the Server.Transfer method is preferable to use because Server.Transfer is faster since there is one less roundtrip, but in some people say that Server.Transfer is not recommended since the operations typically flow through several different pages due to which you lose the correct URL of the page, but again all depends on your requirement. Server.Transfer also allows for more flexibility since you can use HTTPContext.Items to pass variables between pages so, use Server.Transfer when you need to pass context items. Otherwise use Response.Redirect so the user will always see the correct URL in the address bar.