Some Query<\/b><\/p>\n
–Select Employee name with total number of phone number.<\/b><\/p>\n
<\/p>\n
Emplist(id,Name) values((1,a),(2,b),(3,c),(4,d))<\/p>\n
Emp_phone(id,phonenumber)values((1,5454)(1,45454),(2,6767),(3,34343),(3,454545),(7,34343),(11,56565))<\/p>\n
<\/p>\n
select m.name,m.id,COUNT(m_1.phone) as numphone from Emplist as m<\/p>\n
left outer join Emp_phone as m_1 on m.id =m_1.id<\/p>\n
group by m.name,m.id\u00a0 ;<\/p>\n
<\/p>\n
—-Swap the integer value without using third variable<\/b><\/p>\n
declare @var1 integer=2;<\/p>\n
declare @var2 int =1;<\/p>\n
set @var1 =@var1+@var2;– 3<\/p>\n
set @var2 =@var1-@var2 –2<\/p>\n
set @var1 =@var1-@var2; –1<\/p>\n
<\/p>\n
–set @var1 =@var2 ;<\/p>\n
select @var1,@var2;<\/p>\n
<\/p>\n
\u00a0—-Swap the string value <\/b><\/p>\n
<\/p>\n
declare @var3 varchar(max)=’ram’;<\/p>\n
declare @var4 varchar(max)=’ram1′;<\/p>\n
declare @var5 varchar(max);<\/p>\n
<\/p>\n
set @var5=@var3;–‘ram’<\/p>\n
set @var3=@var4;–ram1<\/p>\n
set @var4=@var5;<\/p>\n
select @var3,@var4;<\/p>\n
<\/p>\n
— IDENTITY_INSERT<\/b><\/p>\n
Allows explicit values to be inserted into the identity column of a table.<\/p>\n
SET IDENTITY_INSERT dbo.table1 off;<\/p>\n
If it is off then Cannot insert explicit value for identity column in table \u2018table1\u2019 when IDENTITY_INSERT is set to OFF.but in on it is vice versa i.e. insert value in identity column.<\/p>\n
note—for set statement detail describe in Msdn ‘http:\/\/msdn.microsoft.com\/en-us\/library\/ms190356.aspx<\/a>‘<\/strong><\/p>\n –cross join or cartesian join<\/b><\/p>\n select * from Emp_phone cross join Emplist;<\/p>\n or (old system-)<\/p>\n select * from Emp_phone ,Emplist;<\/p>\n id\u00a0\u00a0\u00a0 phone name\u00a0 id<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 999\u00a0\u00a0 amit_1 1<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 9925\u00a0 amit_1 1<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 5656\u00a0 amit_1\u00a0\u00a0 1<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 4545\u00a0 amit_1\u00a0\u00a0 1<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 5454\u00a0 amit_1\u00a0\u00a0 1<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 544541 amit_1\u00a0 1<\/p>\n 6\u00a0\u00a0\u00a0\u00a0 54513 amit_1\u00a0\u00a0 1<\/p>\n 11\u00a0\u00a0\u00a0 524845 amit_1\u00a0 1<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 999\u00a0\u00a0 st\u00a0\u00a0\u00a0 2<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 9925\u00a0 st\u00a0\u00a0\u00a0 2<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 5656\u00a0 st\u00a0\u00a0\u00a0 2<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 4545\u00a0 st\u00a0\u00a0\u00a0 2<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 5454\u00a0 st\u00a0\u00a0\u00a0 2<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 544541\u00a0\u00a0\u00a0\u00a0\u00a0 st\u00a0\u00a0\u00a0 2<\/p>\n 6\u00a0\u00a0\u00a0\u00a0 54513 st\u00a0\u00a0\u00a0 2<\/p>\n 11\u00a0\u00a0\u00a0 524845\u00a0\u00a0\u00a0\u00a0\u00a0 st\u00a0\u00a0\u00a0 2<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 999\u00a0\u00a0 st1\u00a0\u00a0 3<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 9925\u00a0 st1\u00a0\u00a0 3<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 5656\u00a0 st1\u00a0\u00a0 3<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 4545\u00a0 st1\u00a0\u00a0 3<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 5454\u00a0 st1\u00a0\u00a0 3<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 544541\u00a0\u00a0\u00a0\u00a0\u00a0 st1\u00a0\u00a0 3<\/p>\n 6\u00a0\u00a0\u00a0\u00a0 54513 st1\u00a0\u00a0 3<\/p>\n 11\u00a0\u00a0\u00a0 524845\u00a0\u00a0\u00a0\u00a0\u00a0 st1\u00a0\u00a0 3<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 999\u00a0\u00a0 st2\u00a0\u00a0 4<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 9925\u00a0 st2\u00a0\u00a0 4<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 5656\u00a0 st2\u00a0\u00a0 4<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 4545\u00a0 st2\u00a0\u00a0 4<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 5454\u00a0 st2\u00a0\u00a0 4<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 544541\u00a0\u00a0\u00a0\u00a0\u00a0 st2\u00a0\u00a0 4<\/p>\n 6\u00a0\u00a0\u00a0\u00a0 54513 st2\u00a0\u00a0 4<\/p>\n 11\u00a0\u00a0\u00a0 524845\u00a0\u00a0\u00a0\u00a0\u00a0 st2\u00a0\u00a0 4<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 999\u00a0\u00a0 st3\u00a0\u00a0 5<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 9925\u00a0 st3\u00a0\u00a0 5<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 5656\u00a0 st3\u00a0\u00a0 5<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 4545\u00a0 st3\u00a0\u00a0 5<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 5454\u00a0 st3\u00a0\u00a0 5<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 544541\u00a0\u00a0\u00a0\u00a0\u00a0 st3\u00a0\u00a0 5<\/p>\n 6\u00a0\u00a0\u00a0\u00a0 54513 st3\u00a0\u00a0 5<\/p>\n 11\u00a0\u00a0\u00a0 524845\u00a0\u00a0\u00a0\u00a0\u00a0 st3\u00a0\u00a0 5<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 999\u00a0\u00a0 mukesh\u00a0\u00a0\u00a0\u00a0\u00a0 6<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 9925\u00a0 mukesh\u00a0\u00a0\u00a0\u00a0\u00a0 6<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 5656\u00a0 mukesh\u00a0\u00a0\u00a0\u00a0\u00a0 6<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 4545\u00a0 mukesh\u00a0\u00a0\u00a0\u00a0\u00a0 6<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 5454\u00a0 mukesh\u00a0\u00a0\u00a0\u00a0\u00a0 6<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 544541\u00a0\u00a0\u00a0\u00a0\u00a0 mukesh\u00a0\u00a0\u00a0\u00a0\u00a0 6<\/p>\n 6\u00a0\u00a0\u00a0\u00a0 54513 mukesh\u00a0\u00a0\u00a0\u00a0\u00a0 6<\/p>\n 11\u00a0\u00a0\u00a0 524845\u00a0\u00a0\u00a0\u00a0\u00a0 mukesh\u00a0\u00a0\u00a0\u00a0\u00a0 6<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 999\u00a0\u00a0 amit\u00a0 7<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 9925\u00a0 amit\u00a0 7<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 5656\u00a0 amit\u00a0 7<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 4545\u00a0 amit\u00a0 7<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 5454\u00a0 amit\u00a0 7<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 544541\u00a0\u00a0\u00a0\u00a0\u00a0 amit\u00a0 7<\/p>\n 6\u00a0\u00a0\u00a0\u00a0 54513 amit\u00a0 7<\/p>\n 11\u00a0\u00a0\u00a0 524845\u00a0\u00a0\u00a0\u00a0\u00a0 amit\u00a0 7<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 999\u00a0\u00a0 amit7 8<\/p>\n 1\u00a0\u00a0\u00a0\u00a0 9925\u00a0 amit7 8<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 5656\u00a0 amit7 8<\/p>\n 3\u00a0\u00a0\u00a0\u00a0 4545\u00a0 amit7 8<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 5454\u00a0 amit7 8<\/p>\n 5\u00a0\u00a0\u00a0\u00a0 544541\u00a0\u00a0\u00a0\u00a0\u00a0 amit7 8<\/p>\n 6\u00a0\u00a0\u00a0\u00a0 54513 amit7 8<\/p>\n 11\u00a0\u00a0\u00a0 524845\u00a0\u00a0\u00a0\u00a0\u00a0 amit7 8<\/p>\n –Select for salary count more than 4999 and count zero if salary less than 4999.\u00a0<\/strong><\/p>\n create table #tmp(eid int not null ,ename varchar(25),salary int\u00a0 ,depid varchar(25) );<\/p>\n insert into<\/span><\/span> #tmp <\/span>values\u00a0 <\/span><\/span>(<\/span><\/span>22 <\/span>,<\/span><\/span>‘shyam’<\/span><\/span>,<\/span><\/span>3000<\/span>,<\/span><\/span>‘d5’<\/span><\/span>)<\/span><\/span>,(<\/span><\/span> 23 <\/span>,<\/span><\/span>‘mohan’<\/span><\/span>,<\/span><\/span> 5500\u00a0\u00a0 <\/span>,<\/span><\/span>‘d6’<\/span><\/span>\u00a0\u00a0\u00a0 <\/span>)<\/span><\/span>,(<\/span><\/span>44 <\/span>,<\/span><\/span>‘radha’<\/span><\/span>,<\/span><\/span>\u00a0 8000 <\/span>,<\/span><\/span>\u00a0 <\/span>‘d33’<\/span><\/span>\u00a0\u00a0 <\/span>)<\/span><\/span>,<\/span><\/span>(48 <\/span>,<\/span><\/span>‘abhi’<\/span><\/span>\u00a0 <\/span>,<\/span><\/span> 1000\u00a0 <\/span>,<\/span><\/span>‘d9’<\/span><\/span>);<\/span><\/span><\/p>\n select *<\/span><\/span>from<\/span><\/span> #tmp<\/span><\/p>\n select depid<\/span>,<\/span><\/span>\u00a0 <\/span>COUNT<\/span><\/span>(<\/span><\/span>case<\/span><\/span>when<\/span><\/span> salary <\/span>><\/span><\/span>4999 <\/span>then<\/span><\/span> 1 <\/span>end<\/span><\/span>)<\/span><\/span>as<\/span><\/span> Tcount <\/span>from<\/span><\/span> #tmp <\/span>group<\/span><\/span>by<\/span><\/span> depid<\/span><\/p>\n Difference between Sql server 2000 and 2005<\/b> Difference between Sql server 2005 and 2008<\/b><\/p>\n 11. <\/strong>SSIS is started using.\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0K. <\/strong>SSIS avails in this version<\/p>\n","protected":false},"excerpt":{"rendered":" Hints Specifies that query optimizer enforce a join strategy between two tables. Join hints are specified in the FROM clause of a query. Join hints enforce a join strategy between two tables. If a join hint is specified for any two tables, the query optimizer automatically enforces the join order for all joined tables in the query, based on the… Read more →<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-245","post","type-post","status-publish","format-standard","hentry","category-c-vb","xfolkentry"],"_links":{"self":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/245","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=245"}],"version-history":[{"count":0,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/245\/revisions"}],"wp:attachment":[{"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=245"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=245"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/mairwa.com\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=245"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
\n<\/b> \u00a0 \u00a0![\"Untitled\"](\"http:\/\/mairwa.com\/wordpress\/wp-content\/uploads\/2014\/08\/Untitled15.png\")
<\/a> \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 <\/b><\/p>\n![\"Untitled\"](\"http:\/\/mairwa.com\/wordpress\/wp-content\/uploads\/2014\/08\/Untitled16.png\")
<\/a><\/p>\n