- *steps-for-installing-of-coldfusion
- *resolving-issues-in-sql-server-dreamweaver
- *fusebox-version-2.0
- *coldfusion-interview-questions
- *pagination-in-coldfusion
- *case-sensitive-password-logins-in cf
- *password-encrypt-and-decrypt in cf
- *creating-user-authentication-login in cf
- *coldfusion-security-checklist
- *scopes-in-cfc
Tuesday, August 19, 2008
coldfusion examples
Wednesday, August 6, 2008
javascript popup window
<html>
<head>
<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript">
function openIt(theAdd,fdid){
open
(theAdd,fdid,'width=600,height=300,toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,copyhistory=no,r
esizable=no');
}
</SCRIPT>
</head>
<body>
<a href="javascript:openIt('http://shariffdotnet.blogspot.com','helpLinkImport');"><img src="Images/help.gif" width="22"
height="22" alt="Help " title="Help" border="0"> </a>
</body>
</html>
<head>
<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript">
function openIt(theAdd,fdid){
open
(theAdd,fdid,'width=600,height=300,toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,copyhistory=no,r
esizable=no');
}
</SCRIPT>
</head>
<body>
<a href="javascript:openIt('http://shariffdotnet.blogspot.com','helpLinkImport');"><img src="Images/help.gif" width="22"
height="22" alt="Help " title="Help" border="0"> </a>
</body>
</html>
Setting, storing, and retrieving cookie data with javascript
Cookies -- those scrumptious little bits of precious data.
What is a cookie?
A cookie is a text file containing information about a user pertinent to a website. They are important as http is a stateless protocol. (This means servers cannot remember things about their users) (This is a good thing this means no-one can track your internet use accross servers). This does create some problems for people who use secure access, if someone were to leave the site they would have to revalidate everytime they go back to it, additionally the server won't remember anything about your prefrences, or settings. Cookies were created to circumvent that problem they give each computer a memory of sites and preferences.
Cookies are made up of several things
1. A name-value pair which stores whatever data you want to save.
2. An expire date, after which time the entry will be deleted.
3. The web domain and path that the entry should be associated with.
Setting a cookie is just like declaring a variable
document.cookie = "value=yes; expires=Wed, 13 Mar 2003 00:00:01 UTC; path=/";
alert(document.cookie);
This will display your cookie name and cookie value.
OK now we have the basics, now we set and retieve cookies usefully
<script>
function set_Cookie(name, value, days)
{
if (days)
{
var date = new Date();
date.setTime(date.getTime()+(days*24*60*60*1000));
var expires = "; expires="+date.toGMTString();
}
else var expires = "";
document.cookie = name+"="+value+expires+"; path=/";
}
function get_Cookie(name)
{
var CValue="";
var cook = document.cookie.split(';');
var nameIs = name + "=";
for(var i=0; i < cook.length; i++)
{
var spc = cook[i];
while (spc.charAt(0)==' ')
spc = spc.substring(1, spc.length); file://delete spaces
if (spc.indexOf(nameIs) == 0)
{
CValue = spc.substring(nameIs.length, spc.length);
alert(CValue);
return CValue;
}
}
return null;
}
</script>
What do we do with this?
we can use it to store data fairly easily.
first we have the option to give a name and a value to the cookie (if days aren't specified it becomes a session cookie).
so lets say we want to remember someones name from a form we have a form
<form name="cForm">
Name
<br />
<input type="text" name="txt1" value="" />
<br />
<input type="button" value="Set Cookie"
onclick="set_Cookie('imacookie',document.forms[0].txt1.value,30);" />
<input type="button" value="Get Cookie"
onclick="get_Cookie('imacookie');" />
</form>
Name
That will retrieve one peice of data, the name.
How dow we get more stuff stored in a cookie?
Thats the easiest part of all, all we have to do is associate it with another name, our cookie will store all sorts of data.
<form name="cForm2">
Name
<br />
<input type="text" name="txt1" value="type" />
<input type="text" name="txt2" value="stuff" />
<input type="text" name="txt3" value="in" />
<input type="text" name="txt4" value="these" />
<br />
<input type="button" onclick="set_Cookie('imacookie',document.cForm2.txt1.value,30);
set_Cookie('imacookie2',document.cForm2.txt2.value,30);
set_Cookie('imacookie3',document.cForm2.txt3.value,30);
set_Cookie('imacookie4',document.cForm2.txt4.value,30);" />
<input type="button" value="Get Cookie"
onclick="get_Cookie('imacookie');
get_Cookie('imacookie2');
get_Cookie('imacookie3');
get_Cookie('imacookie4');" />
</form>
Name
One final thing to do... learn how to delete a cookie.
It is amazingly simple, All we have to do is call the set_Cookie method we already created, and put in a negative expire date.
set_Cookie("imacookie", "", -1);
What is a cookie?
A cookie is a text file containing information about a user pertinent to a website. They are important as http is a stateless protocol. (This means servers cannot remember things about their users) (This is a good thing this means no-one can track your internet use accross servers). This does create some problems for people who use secure access, if someone were to leave the site they would have to revalidate everytime they go back to it, additionally the server won't remember anything about your prefrences, or settings. Cookies were created to circumvent that problem they give each computer a memory of sites and preferences.
Cookies are made up of several things
1. A name-value pair which stores whatever data you want to save.
2. An expire date, after which time the entry will be deleted.
3. The web domain and path that the entry should be associated with.
Setting a cookie is just like declaring a variable
document.cookie = "value=yes; expires=Wed, 13 Mar 2003 00:00:01 UTC; path=/";
alert(document.cookie);
This will display your cookie name and cookie value.
OK now we have the basics, now we set and retieve cookies usefully
<script>
function set_Cookie(name, value, days)
{
if (days)
{
var date = new Date();
date.setTime(date.getTime()+(days*24*60*60*1000));
var expires = "; expires="+date.toGMTString();
}
else var expires = "";
document.cookie = name+"="+value+expires+"; path=/";
}
function get_Cookie(name)
{
var CValue="";
var cook = document.cookie.split(';');
var nameIs = name + "=";
for(var i=0; i < cook.length; i++)
{
var spc = cook[i];
while (spc.charAt(0)==' ')
spc = spc.substring(1, spc.length); file://delete spaces
if (spc.indexOf(nameIs) == 0)
{
CValue = spc.substring(nameIs.length, spc.length);
alert(CValue);
return CValue;
}
}
return null;
}
</script>
What do we do with this?
we can use it to store data fairly easily.
first we have the option to give a name and a value to the cookie (if days aren't specified it becomes a session cookie).
so lets say we want to remember someones name from a form we have a form
<form name="cForm">
Name
<br />
<input type="text" name="txt1" value="" />
<br />
<input type="button" value="Set Cookie"
onclick="set_Cookie('imacookie',document.forms[0].txt1.value,30);" />
<input type="button" value="Get Cookie"
onclick="get_Cookie('imacookie');" />
</form>
Name
That will retrieve one peice of data, the name.
How dow we get more stuff stored in a cookie?
Thats the easiest part of all, all we have to do is associate it with another name, our cookie will store all sorts of data.
<form name="cForm2">
Name
<br />
<input type="text" name="txt1" value="type" />
<input type="text" name="txt2" value="stuff" />
<input type="text" name="txt3" value="in" />
<input type="text" name="txt4" value="these" />
<br />
<input type="button" onclick="set_Cookie('imacookie',document.cForm2.txt1.value,30);
set_Cookie('imacookie2',document.cForm2.txt2.value,30);
set_Cookie('imacookie3',document.cForm2.txt3.value,30);
set_Cookie('imacookie4',document.cForm2.txt4.value,30);" />
<input type="button" value="Get Cookie"
onclick="get_Cookie('imacookie');
get_Cookie('imacookie2');
get_Cookie('imacookie3');
get_Cookie('imacookie4');" />
</form>
Name
One final thing to do... learn how to delete a cookie.
It is amazingly simple, All we have to do is call the set_Cookie method we already created, and put in a negative expire date.
set_Cookie("imacookie", "", -1);
Sunday, August 3, 2008
Dynamic Html and CSS menu
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>menu</title>
<style type="text/css">
.MainDivStyle
{
background-color:#66ccff;
width:200px;
position:absolute;
}
.td
{
width:200px;
}
.inDiv
{
font-weight:bolder;
cursor:pointer;
}
.outDiv
{
font-weight:normal;
}
</style>
<script language="javascript" type="text/javascript">
/* This function recives a given div id and the desired height as arguments in deed it must be like "600px"
for example, it changes the div height according to the hght variable */
function ExpandOrCollapse(divID,hght)
{
document.getElementById(divID).style.height = hght;
}
/*This function is done for the sub elements in order to change their font to bold and
personalise the cursor as pointer, see inDiv css class above
*/
function indiv(DivID)
{
document.getElementById(DivID).className= "inDiv";
}
/*This function is done for the sub elements in order to change their font to bold and
personalise the cursor as pointer, see inDiv css class above
*/
function outdiv(DivID)
{
document.getElementById(DivID).className= "outDiv";
}
</script>
</head>
<body onload="ExpandOrCollapse('element1','20px');ExpandOrCollapse('element2','20px');ExpandOrCollapse('element3','20px');ExpandOrCollapse('element4','20px');">
<center>
<table>
<tr>
<td class="td">
<div id="element1" class="MainDivStyle" onmouseout="ExpandOrCollapse('element1','20px');"
onmouseover="ExpandOrCollapse('element1','110px');">
<center>
<span style="color: White; font-size: large">Element1</span></center>
<div id="element11" onclick="alert('Element11')" onmouseover="indiv('element11');" onmouseout="outdiv('element11');">
<center>
<span style="color: White; font-size: large">Element 11</span></center>
</div>
<div id="element12" onclick="alert('Element12')" onmouseover="indiv('element12');" onmouseout="outdiv('element12');">
<center>
<span style="color: White; font-size: large">Element 12</span></center>
</div>
<div id="element13" onclick="alert('Element13')" onmouseover="indiv('element13');" onmouseout="outdiv('element13');">
<center>
<span style="color: White; font-size: large">Element 13</span></center>
</div>
<div id="element14" onclick="alert('Element14')" onmouseover="indiv('element14');" onmouseout="outdiv('element14');">
<center>
<span style="color: White; font-size: large">Element 14</span></center>
</div>
</div>
</td>
<td class="td">
<div id="element2" class="MainDivStyle" onmouseout="ExpandOrCollapse('element2','20px');"
onmouseover="ExpandOrCollapse('element2','110px');">
<center>
<span style="color: White; font-size: large">Element2</span></center>
<div id="element21" onclick="alert('Element21')" onmouseover="indiv('element21');" onmouseout="outdiv('element21');">
<center>
<span style="color: White; font-size: large">Element 21</span></center>
</div>
<div id="element22" onclick="alert('Element22')" onmouseover="indiv('element22');" onmouseout="outdiv('element22');">
<center>
<span style="color: White; font-size: large">Element 22</span></center>
</div>
<div id="element23" onclick="alert('Element23')" onmouseover="indiv('element23');" onmouseout="outdiv('element23');">
<center>
<span style="color: White; font-size: large">Element 23</span></center>
</div>
<div id="element24" onclick="alert('Element24')" onmouseover="indiv('element24');" onmouseout="outdiv('element24');">
<center>
<span style="color: White; font-size: large">Element 24</span></center>
</div>
</div>
</td>
<td class="td">
<div id="element3" class="MainDivStyle" onmouseout="ExpandOrCollapse('element3','20px');"
onmouseover="ExpandOrCollapse('element3','110px');">
<center>
<span style="color: White; font-size: large">Element3</span></center>
<div id="element31" onclick="alert('Element31')" onmouseover="indiv('element31');" onmouseout="outdiv('element31');">
<center>
<span style="color: White; font-size: large">Element 31</span></center>
</div>
<div id="element32" onclick="alert('Element32')" onmouseover="indiv('element32');" onmouseout="outdiv('element32');">
<center>
<span style="color: White; font-size: large">Element 32</span></center>
</div>
<div id="element33" onclick="alert('Element33')" onmouseover="indiv('element33');" onmouseout="outdiv('element33');">
<center>
<span style="color: White; font-size: large">Element 33</span></center>
</div>
<div id="element34" onclick="alert('Element34')" onmouseover="indiv('element34);" onmouseout="outdiv('element34');">
<center>
<span style="color: White; font-size: large">Element 34</span></center>
</div>
</div>
</td>
<td class="td">
<div id="element4" class="MainDivStyle" onmouseout="ExpandOrCollapse('element4','20px');"
onmouseover="ExpandOrCollapse('element4','110px');">
<center>
<span style="color: White; font-size: large">Element4</span></center>
<div id="element41" onclick="alert('Element41')" onmouseover="indiv('element41');" onmouseout="outdiv('element41');" >
<center>
<span style="color: White; font-size: large">Element 41</span></center>
</div>
<div id="element42" onclick="alert('Element42')" onmouseover="indiv('element42');" onmouseout="outdiv('element42');">
<center>
<span style="color: White; font-size: large">Element 42</span></center>
</div>
<div id="element43" onclick="alert('Element43')" onmouseover="indiv('element43');" onmouseout="outdiv('element43');">
<center>
<span style="color: White; font-size: large">Element 43</span></center>
</div>
<div id="element44" onclick="alert('Element44')" onmouseover="indiv('element44');" onmouseout="outdiv('element44');">
<center>
<span style="color: White; font-size: large">Element 44</span></center>
</div>
</div>
</td>
</tr>
</table>
</center>
</body>
</html>
Horizontal Dropdown Menu using Javascript
EXAMPLE 1
HTML Code
<b>
<ul id="sddm">
<li><a href="#"
onmouseover="mopen('m1')"
onmouseout="mclosetime()">Home</a>
<div id="m1"
onmouseover="mcancelclosetime()"
onmouseout="mclosetime()">
<a href="#">HTML Drop Down</a>
<a href="#">DHTML Menu</a>
<a href="#">JavaScript DropDown</a>
<a href="#">Cascading Menu</a>
<a href="#">CSS Horizontal Menu</a>
</div>
</li>
<li><a href="#"
onmouseover="mopen('m2')"
onmouseout="mclosetime()">Download</a>
<div id="m2"
onmouseover="mcancelclosetime()"
onmouseout="mclosetime()">
<a href="#">ASP Dropdown</a>
<a href="#">Pulldown menu</a>
<a href="#">AJAX Drop Submenu</a>
<a href="#">DIV Cascading Menu</a>
</div>
</li>
<li><a href="#">Order</a></li>
<li><a href="#">Help</a></li>
<li><a href="#">Contact</a></li>
</ul>
<div style="clear:both"></div>
</b>
CSS Code:-
<b>
#sddm
{ margin: 0;
padding: 0;
z-index: 30}
#sddm li
{ margin: 0;
padding: 0;
list-style: none;
float: left;
font: bold 11px arial}
#sddm li a
{ display: block;
margin: 0 1px 0 0;
padding: 4px 10px;
width: 60px;
background: #5970B2;
color: #FFF;
text-align: center;
text-decoration: none}
#sddm li a:hover
{ background: #49A3FF}
#sddm div
{ position: absolute;
visibility: hidden;
margin: 0;
padding: 0;
background: #EAEBD8;
border: 1px solid #5970B2}
#sddm div a
{ position: relative;
display: block;
margin: 0;
padding: 5px 10px;
width: auto;
white-space: nowrap;
text-align: left;
text-decoration: none;
background: #EAEBD8;
color: #2875DE;
font: 11px arial}
#sddm div a:hover
{ background: #49A3FF;
color: #FFF}
</b>
JavaScript Code:-
<b>
var timeout = 500;
var closetimer = 0;
var ddmenuitem = 0;
// open hidden layer
function mopen(id)
{
// cancel close timer
mcancelclosetime();
// close old layer
if(ddmenuitem) ddmenuitem.style.visibility = 'hidden';
// get new layer and show it
ddmenuitem = document.getElementById(id);
ddmenuitem.style.visibility = 'visible';
}
// close showed layer
function mclose()
{
if(ddmenuitem) ddmenuitem.style.visibility = 'hidden';
}
// go close timer
function mclosetime()
{
closetimer = window.setTimeout(mclose, timeout);
}
// cancel close timer
function mcancelclosetime()
{
if(closetimer)
{
window.clearTimeout(closetimer);
closetimer = null;
}
}
// close layer when click-out
document.onclick = mclose;
</b>
HTML Code
<b>
<ul id="sddm">
<li><a href="#"
onmouseover="mopen('m1')"
onmouseout="mclosetime()">Home</a>
<div id="m1"
onmouseover="mcancelclosetime()"
onmouseout="mclosetime()">
<a href="#">HTML Drop Down</a>
<a href="#">DHTML Menu</a>
<a href="#">JavaScript DropDown</a>
<a href="#">Cascading Menu</a>
<a href="#">CSS Horizontal Menu</a>
</div>
</li>
<li><a href="#"
onmouseover="mopen('m2')"
onmouseout="mclosetime()">Download</a>
<div id="m2"
onmouseover="mcancelclosetime()"
onmouseout="mclosetime()">
<a href="#">ASP Dropdown</a>
<a href="#">Pulldown menu</a>
<a href="#">AJAX Drop Submenu</a>
<a href="#">DIV Cascading Menu</a>
</div>
</li>
<li><a href="#">Order</a></li>
<li><a href="#">Help</a></li>
<li><a href="#">Contact</a></li>
</ul>
<div style="clear:both"></div>
</b>
CSS Code:-
<b>
#sddm
{ margin: 0;
padding: 0;
z-index: 30}
#sddm li
{ margin: 0;
padding: 0;
list-style: none;
float: left;
font: bold 11px arial}
#sddm li a
{ display: block;
margin: 0 1px 0 0;
padding: 4px 10px;
width: 60px;
background: #5970B2;
color: #FFF;
text-align: center;
text-decoration: none}
#sddm li a:hover
{ background: #49A3FF}
#sddm div
{ position: absolute;
visibility: hidden;
margin: 0;
padding: 0;
background: #EAEBD8;
border: 1px solid #5970B2}
#sddm div a
{ position: relative;
display: block;
margin: 0;
padding: 5px 10px;
width: auto;
white-space: nowrap;
text-align: left;
text-decoration: none;
background: #EAEBD8;
color: #2875DE;
font: 11px arial}
#sddm div a:hover
{ background: #49A3FF;
color: #FFF}
</b>
JavaScript Code:-
<b>
var timeout = 500;
var closetimer = 0;
var ddmenuitem = 0;
// open hidden layer
function mopen(id)
{
// cancel close timer
mcancelclosetime();
// close old layer
if(ddmenuitem) ddmenuitem.style.visibility = 'hidden';
// get new layer and show it
ddmenuitem = document.getElementById(id);
ddmenuitem.style.visibility = 'visible';
}
// close showed layer
function mclose()
{
if(ddmenuitem) ddmenuitem.style.visibility = 'hidden';
}
// go close timer
function mclosetime()
{
closetimer = window.setTimeout(mclose, timeout);
}
// cancel close timer
function mcancelclosetime()
{
if(closetimer)
{
window.clearTimeout(closetimer);
closetimer = null;
}
}
// close layer when click-out
document.onclick = mclose;
</b>
Sql Queries
use samples
select * from information_schema.tables
use pubs
select * from emp_details
select * from emp_address
delete emp_details where emp_id=1
insert into emp_details values(1,'raja')
joins :
inner join :
select * from emp_details inner join emp_address on
emp_details.emp_id=emp_address.emp_id
outer join :
left outer join
select * from emp_details left outer join emp_address on
emp_details.emp_id=emp_address.emp_id
Right outer join:
select * from emp_details right outer join emp_address on
emp_details.emp_id=emp_address.emp_id
Full join :
select * from emp_details full join emp_address on
emp_details.emp_id=emp_address.emp_id
Cross join:
select * from emp_details cross join emp_address
Delete and Truncate :
Delete :
Select * from emp_details
begin transaction
delete emp_details where emp_id=3
select * from emp_details
truncate table emp_details
create table stock_details(stock_id int identity(1,10),stock_name varchar(50))
select * from stock_details
insert into stock_details values('supplygoods')
insert into stock_details values('maintenance')
insert into stock_details values('purchase')
insert into stock_details values('realgoods')
insert into stock_details values('manufacturing')
insert into stock_details values('suppliermerchant')
select * from stock_details
delete stock_details where stock_id=41
delete stock_details
truncate table stock_details
rollback
DML : Insert ,update,Delete ,Select
DDL : Create ,Alter,Drop
DCL : Grant,Revoke,Deny
TCL : Commit,RollBack,Savepoint,SetTransaction
self Join;
select * from emp_details
insert into emp_details values(4,'ganesh')
insert into emp_details values(5,'ravi')
insert into emp_details values(7,'sivam')
insert into emp_details values(8,'ragam')
select * from emp_details
select t1.emp_id,t1.empname,t2.empname from emp_details t1,emp_details t2
where t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 inner join emp_details t2
on t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 left outer join emp_details t2
on t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 right outer join emp_details t2
on t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 full join emp_details t2
on t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 cross join emp_details t
use master
select * from syslanguages
select @@language
set language us_english
select * from information_schema.tables
Top 10 new in sql server 2005
Integrated common language runtime (CLR) support. Stored procedures and functions can be written using your favorite programming language.
Native XML support.
XCopy support for moving databases from one location to another.
Robust and reliable installation using either graphical user interface (GUI) or silent modes.
Automated servicing and patching.
Deep integration with Visual Studio to provide access to rich data controls like the DataGridView, DataNavigator, and DataConnector.
Support for databases up to 4GB.
Support for disconnected systems with SQL Server Service Broker and Replication.
SQL Server Express Manager, a new, easy-to-use database management and query analysis tool.
Easier deployment via Visual Studio ClickOnce (available in Beta 3).
Stored Procedure :
use pubs
select * from emp_details
Create Procedure for without Arguments:
***************************************
create procedure emp_procedure
as
select * from emp_details
exec emp_procedure
alter procedure emp_procedure
as select empname from emp_details
exec emp_procedure
drop procedure emp_procedure
Create Procedure for with Arguments :
************************************
create procedure emp_procedure
@empid bigint,
@empname varchar(50)
as
select * from emp_details where emp_id=@empid
exec emp_procedure 3,'raj'
drop procedure emp_procedure
create procedure emp_procedure
@empid bigint ,
@empname varchar(50)
as
insert into emp_details(emp_id,empname)values(@empid,@empname)
exec emp_procedure 5,'mani'
select * from emp_details
constraints :
Create table emps(empid bigint constraint pk_emp primary key,emp_name varchar(50),empaddress varchar(50))
insert into emps values(101,'ravi','chennai')
insert into emps values(102,'raja','trichy')
select * from emps
alter table emps drop constraint pk_emp
insert into emps values(101,'sivam','bangalore')
select * from emps
alter table emps add constraint pk_emp check(empid is not null)
use pubs
select * from emp_details
Create Procedure for without Arguments:
***************************************
create procedure emp_procedure
as
select * from emp_details
exec emp_procedure
alter procedure emp_procedure
as select empname from emp_details
exec emp_procedure
drop procedure emp_procedure
Create Procedure for with Arguments :
************************************
create procedure emp_procedure
@empid bigint,
@empname varchar(50)
as
select * from emp_details where emp_id=@empid
exec emp_procedure 3,'raj'
drop procedure emp_procedure
create procedure emp_procedure
@empid bigint ,
@empname varchar(50)
as
insert into emp_details(emp_id,empname)values(@empid,@empname)
exec emp_procedure 5,'mani'
select * from emp_details
constraints :
Create table emps(empid bigint constraint pk_emp primary key,emp_name varchar(50),empaddress varchar(50))
insert into emps values(101,'ravi','chennai')
insert into emps values(102,'raja','trichy')
select * from emps
alter table emps drop constraint pk_emp
insert into emps values(101,'sivam','bangalore')
select * from emps
alter table emps add constraint pk_emp check(empid is not null)
select * from emps
select * from emp_details
select * from emp_address
select emp_details.emp_id,empname from emp_details,emp_address
where emp_details.emp_id=emp_address.emp_id
select emp_details.emp_id,empname ,empaddress from emp_address,emp_details
select * from emp_details
begin transaction
delete emp_details where emp_id=7
commit
select * from emp_details
begin transaction
rollback
select * from emp_details
select * from emp_address
select * from emp_details where emp_id between 1 and 4
views :
select * from authors
create view view_authors as select * from authors where au_fname='Johnson'
select * from view_authors
drop view view_authors
select * from emp_details
create view empview as select * from emp_details
select * from empview
insert into empview values(5,'sivam')
select * from empview
select * from emp_details
delete empview where emp_id=5
select * from empview
alter view empview as select empname from emp_details
select * from empview
alter view empview as select * from emp_details
select * from empview
update empview set emp_id=5 where empname='ganesh'
select * from empview
drop view empview
select * from emp_details
create view empview with encryption as select * from emp_details where emp_id=5
select * from empview
select * from information_schema.views
drop view empview
create view empview with schemabinding as select emp_id,empname from dbo.emp_details
select * from empview
select * from information_schema.views
alter view empview as select emp_id from emp_details
select * from empview
select * from emp_details
------------------------------------------------------------------------------------------------------------------------------------------------
Copy one table to another Table :
select * into empdetails from emp_details
------------------------------------------------------------------------------------------------------------------------------------------------
Difference between Drop and Truncate Table
To Find duplicate Records:
Select emp_id from emp_details group by emp_id having count(*)>1
Self join :
select a.emp_id ,b.empname from emp_details a,emp_details b
where a.emp_id=b.emp_id
To find duplicate Records:
select * from emp_details where emp_id in(select emp_id from emp_details group by emp_id having count(*)>1)
To Rename DataBase
Exec sp_renamedb ‘OldDataBasename’,’NewDataBasename’
use pubs
select * from emp_details
create table empdetails INTO select * from emp_details where 1=2
drop table empdetails
select * into empdetails from emp_details
select * from empdetails
select * from emp_address
select * into empaddress from emp_address
select * from empaddress
select * from emp_details
insert into emp_details values(2,'ravimani')
select emp_id from emp_details group by emp_id having count(*)>1
Stored Procedure:
drop procedure emp_procedure
create procedure emp_procedure
as
select * from emp_details
exec emp_procedure
drop procedure emp_procedure
Self join:
select * from emp_details
select a.emp_id ,b.empname from emp_details a,emp_details b
where a.emp_id=b.emp_id
exec sp_tables
exec sp_columns pubs
exec sp_who2
To find Duplicate Records
select * from emp_details
insert into emp_details values(59,'ganesh')
select * from emp_details where emp_id in(select emp_id from emp_details group by emp_id having count(*)>1)
select * from emp_details
select top 1 emp_id from(select distinct TOP emp_id from emp_details order by emp_id desc) a order by emp_id
select emp_details.emp_id ,empname from emp_details,emp_address
use shariff
exec sp_renamedb 'pasha','shariff'
exec sp_renamedb 'shariff','pasha'
select * from information_schema.tables
use pubs
select * from emp_details
select * from emp_address
delete emp_details where emp_id=1
insert into emp_details values(1,'raja')
joins :
inner join :
select * from emp_details inner join emp_address on
emp_details.emp_id=emp_address.emp_id
outer join :
left outer join
select * from emp_details left outer join emp_address on
emp_details.emp_id=emp_address.emp_id
Right outer join:
select * from emp_details right outer join emp_address on
emp_details.emp_id=emp_address.emp_id
Full join :
select * from emp_details full join emp_address on
emp_details.emp_id=emp_address.emp_id
Cross join:
select * from emp_details cross join emp_address
Delete and Truncate :
Delete :
Select * from emp_details
begin transaction
delete emp_details where emp_id=3
select * from emp_details
truncate table emp_details
create table stock_details(stock_id int identity(1,10),stock_name varchar(50))
select * from stock_details
insert into stock_details values('supplygoods')
insert into stock_details values('maintenance')
insert into stock_details values('purchase')
insert into stock_details values('realgoods')
insert into stock_details values('manufacturing')
insert into stock_details values('suppliermerchant')
select * from stock_details
delete stock_details where stock_id=41
delete stock_details
truncate table stock_details
rollback
DML : Insert ,update,Delete ,Select
DDL : Create ,Alter,Drop
DCL : Grant,Revoke,Deny
TCL : Commit,RollBack,Savepoint,SetTransaction
self Join;
select * from emp_details
insert into emp_details values(4,'ganesh')
insert into emp_details values(5,'ravi')
insert into emp_details values(7,'sivam')
insert into emp_details values(8,'ragam')
select * from emp_details
select t1.emp_id,t1.empname,t2.empname from emp_details t1,emp_details t2
where t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 inner join emp_details t2
on t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 left outer join emp_details t2
on t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 right outer join emp_details t2
on t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 full join emp_details t2
on t1.emp_id=t2.emp_id
select t1.emp_id,t1.empname,t2.empname from emp_details t1 cross join emp_details t
use master
select * from syslanguages
select @@language
set language us_english
select * from information_schema.tables
Top 10 new in sql server 2005
Integrated common language runtime (CLR) support. Stored procedures and functions can be written using your favorite programming language.
Native XML support.
XCopy support for moving databases from one location to another.
Robust and reliable installation using either graphical user interface (GUI) or silent modes.
Automated servicing and patching.
Deep integration with Visual Studio to provide access to rich data controls like the DataGridView, DataNavigator, and DataConnector.
Support for databases up to 4GB.
Support for disconnected systems with SQL Server Service Broker and Replication.
SQL Server Express Manager, a new, easy-to-use database management and query analysis tool.
Easier deployment via Visual Studio ClickOnce (available in Beta 3).
Stored Procedure :
use pubs
select * from emp_details
Create Procedure for without Arguments:
***************************************
create procedure emp_procedure
as
select * from emp_details
exec emp_procedure
alter procedure emp_procedure
as select empname from emp_details
exec emp_procedure
drop procedure emp_procedure
Create Procedure for with Arguments :
************************************
create procedure emp_procedure
@empid bigint,
@empname varchar(50)
as
select * from emp_details where emp_id=@empid
exec emp_procedure 3,'raj'
drop procedure emp_procedure
create procedure emp_procedure
@empid bigint ,
@empname varchar(50)
as
insert into emp_details(emp_id,empname)values(@empid,@empname)
exec emp_procedure 5,'mani'
select * from emp_details
constraints :
Create table emps(empid bigint constraint pk_emp primary key,emp_name varchar(50),empaddress varchar(50))
insert into emps values(101,'ravi','chennai')
insert into emps values(102,'raja','trichy')
select * from emps
alter table emps drop constraint pk_emp
insert into emps values(101,'sivam','bangalore')
select * from emps
alter table emps add constraint pk_emp check(empid is not null)
use pubs
select * from emp_details
Create Procedure for without Arguments:
***************************************
create procedure emp_procedure
as
select * from emp_details
exec emp_procedure
alter procedure emp_procedure
as select empname from emp_details
exec emp_procedure
drop procedure emp_procedure
Create Procedure for with Arguments :
************************************
create procedure emp_procedure
@empid bigint,
@empname varchar(50)
as
select * from emp_details where emp_id=@empid
exec emp_procedure 3,'raj'
drop procedure emp_procedure
create procedure emp_procedure
@empid bigint ,
@empname varchar(50)
as
insert into emp_details(emp_id,empname)values(@empid,@empname)
exec emp_procedure 5,'mani'
select * from emp_details
constraints :
Create table emps(empid bigint constraint pk_emp primary key,emp_name varchar(50),empaddress varchar(50))
insert into emps values(101,'ravi','chennai')
insert into emps values(102,'raja','trichy')
select * from emps
alter table emps drop constraint pk_emp
insert into emps values(101,'sivam','bangalore')
select * from emps
alter table emps add constraint pk_emp check(empid is not null)
select * from emps
select * from emp_details
select * from emp_address
select emp_details.emp_id,empname from emp_details,emp_address
where emp_details.emp_id=emp_address.emp_id
select emp_details.emp_id,empname ,empaddress from emp_address,emp_details
select * from emp_details
begin transaction
delete emp_details where emp_id=7
commit
select * from emp_details
begin transaction
rollback
select * from emp_details
select * from emp_address
select * from emp_details where emp_id between 1 and 4
views :
select * from authors
create view view_authors as select * from authors where au_fname='Johnson'
select * from view_authors
drop view view_authors
select * from emp_details
create view empview as select * from emp_details
select * from empview
insert into empview values(5,'sivam')
select * from empview
select * from emp_details
delete empview where emp_id=5
select * from empview
alter view empview as select empname from emp_details
select * from empview
alter view empview as select * from emp_details
select * from empview
update empview set emp_id=5 where empname='ganesh'
select * from empview
drop view empview
select * from emp_details
create view empview with encryption as select * from emp_details where emp_id=5
select * from empview
select * from information_schema.views
drop view empview
create view empview with schemabinding as select emp_id,empname from dbo.emp_details
select * from empview
select * from information_schema.views
alter view empview as select emp_id from emp_details
select * from empview
select * from emp_details
------------------------------------------------------------------------------------------------------------------------------------------------
Copy one table to another Table :
select * into empdetails from emp_details
------------------------------------------------------------------------------------------------------------------------------------------------
Difference between Drop and Truncate Table
To Find duplicate Records:
Select emp_id from emp_details group by emp_id having count(*)>1
Self join :
select a.emp_id ,b.empname from emp_details a,emp_details b
where a.emp_id=b.emp_id
To find duplicate Records:
select * from emp_details where emp_id in(select emp_id from emp_details group by emp_id having count(*)>1)
To Rename DataBase
Exec sp_renamedb ‘OldDataBasename’,’NewDataBasename’
use pubs
select * from emp_details
create table empdetails INTO select * from emp_details where 1=2
drop table empdetails
select * into empdetails from emp_details
select * from empdetails
select * from emp_address
select * into empaddress from emp_address
select * from empaddress
select * from emp_details
insert into emp_details values(2,'ravimani')
select emp_id from emp_details group by emp_id having count(*)>1
Stored Procedure:
drop procedure emp_procedure
create procedure emp_procedure
as
select * from emp_details
exec emp_procedure
drop procedure emp_procedure
Self join:
select * from emp_details
select a.emp_id ,b.empname from emp_details a,emp_details b
where a.emp_id=b.emp_id
exec sp_tables
exec sp_columns pubs
exec sp_who2
To find Duplicate Records
select * from emp_details
insert into emp_details values(59,'ganesh')
select * from emp_details where emp_id in(select emp_id from emp_details group by emp_id having count(*)>1)
select * from emp_details
select top 1 emp_id from(select distinct TOP emp_id from emp_details order by emp_id desc) a order by emp_id
select emp_details.emp_id ,empname from emp_details,emp_address
use shariff
exec sp_renamedb 'pasha','shariff'
exec sp_renamedb 'shariff','pasha'
Subscribe to:
Posts (Atom)