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