TechFusion: SQL

Create a Table from Another table

<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="margin-bottom: .0001pt; margin: 0in;">
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.quackit.com/pix/sql_server/sql_server_2008/tutorial/create_a_table_2.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="http://www.quackit.com/pix/sql_server/sql_server_2008/tutorial/create_a_table_2.gif" width="320" /></a></div>
<span style="font-family: Verdana, sans-serif;"><br />
<b><u>Create a Table from Another table :-<o:p></o:p></u></b></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: Verdana, sans-serif;">            To create copy
of existing table use the following syntax<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">select</span><span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;"> <span style="color: grey;">*/<</span><span style="color: teal;">Columnn</span> <span style="color: teal;">List</span><span style="color: grey;">></span> <span style="color: blue;">into</span>  <span style="color: grey;"><</span><span style="color: teal;">New</span> <span style="color: blue;">Table</span> <span style="color: teal;">Name</span><span style="color: grey;">></span> <span style="color: blue;">From</span> <span style="color: grey;"><</span><span style="color: teal;">Old</span> <span style="color: blue;">Table</span> <span style="color: teal;">Name</span><span style="color: grey;">></span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">eg.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">folowing  table create the copy of the table student
form with the name stud<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">select</span><span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;"> <span style="color: grey;">*</span> <span style="color: blue;">into</span> <span style="color: teal;">tbl_copy</span> <span style="color: blue;">from</span> <span style="color: teal;">student<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">insert
table with only specefic columns  or
Conditions.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">select</span><span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">  <span style="color: grey;">*</span>  <span style="color: blue;">into</span> <span style="color: teal;">tbl_Copy1</span> <span style="color: blue;">from</span> <span style="color: teal;">tbl_Emp</span> <span style="color: blue;">where</span> <span style="color: teal;">FName</span><span style="color: grey;">=</span><span style="color: red;">'abhijit'</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">while
creating a table from another table if you dont <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">to
copy the data then specify a condition on the where clause which will be false<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">select</span><span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;"> <span style="color: grey;">*</span> <span style="color: blue;">into</span> <span style="color: teal;">student2</span> <span style="color: blue;">from</span> <span style="color: teal;">Student</span> <span style="color: blue;">where</span> 1<span style="color: grey;">=</span>0<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<b><u><span style="color: #c0504d; font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas; mso-themecolor: accent2;">Note<o:p></o:p></span></u></b></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">       While creating  a table from another table constraints
and  indexes available on existing table
will not be copy to new table<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">------------------------------------------------------------<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<b><u><span style="font-family: Verdana, sans-serif;">Inserting
Row from one table  to Another Table:-<o:p></o:p></span></u></b></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: Verdana, sans-serif;">         <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: Verdana, sans-serif;">You can insert rows from one tables to another provided those
tables have columns with same datatype and for this use the following syntax of
insert statement.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: green; font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">insert into <Table Name> <Select Statment><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">eg:<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<br />
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;">insert</span><span style="font-family: "Verdana","sans-serif"; font-size: 12.0pt; mso-bidi-font-family: Consolas;"> <span style="color: teal;">tbl_Copy</span><span style="color: grey;">(</span><span style="color: teal;">StudentId</span><span style="color: grey;">,</span><span style="color: teal;">FirstName</span> <span style="color: grey;">,</span><span style="color: teal;">LastName</span> <span style="color: grey;">)</span><span style="color: blue;">select</span> <span style="color: teal;">StudentId</span><span style="color: grey;">,</span><span style="color: teal;">FirstName</span><span style="color: grey;">,</span><span style="color: teal;">LastName</span> <span style="color: blue;">from</span> <span style="color: teal;">Student</span></span></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="http://www.quackit.com/pix/sql_server/sql_server_2008/tutorial/create_a_table_2.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://www.quackit.com/pix/sql_server/sql_server_2008/tutorial/create_a_table_2.gif" /></a></div>
<o:p></o:p></div>

Create a Table from Another table

Create a Table from Another table :- To create copy of existing table use the following syntax select */< Colum...

Bulit In Fuction I

<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="margin-bottom: .0001pt; margin: 0in;">
<b><u><span style="font-family: "Verdana","sans-serif";">Built in Functions:<o:p></o:p></span></u></b></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">Sql
server provides predefined function ,which are collectively called as built in
function<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">Bult
in fucnction are classified into  <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">1)Scalar
function.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">2)aggregate
function.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">3)Ranking
Function.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<u><span style="font-family: "Verdana","sans-serif";">1)Scaler
Function:- <o:p></o:p></span></u></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">           It is function are built in function
that can be operate on a single value at a time,scaler function are classified
into numeric function, string function,Date Function and conversion function.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<u><span style="font-family: "Verdana","sans-serif";">1)Numeric
Function:-</span></u><span style="font-family: "Verdana","sans-serif";"><o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">              The operate on numeric type of data,numeric
function avaliabli in sql server.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">a)<u>abs(n):-<o:p></o:p></u></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">    absolute value of n<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">abs</span><span style="color: grey;">(-</span>76.54<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">o/p-76.54<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">b)</span><u><span style="font-family: Consolas; font-size: 12.0pt;">radians(n):-<o:p></o:p></span></u></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;"> used to
convert given n .<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">RADIANS</span><span style="color: blue;"> </span><span style="color: grey;">(</span>30.00<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">o/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">      0.523598775598298790
<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<u><span style="font-family: Consolas; font-size: 12.0pt;">C)Degree(n):-<o:p></o:p></span></u></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">   Used to
convert given ‘n’ in radians to degree.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">DEGREES</span><span style="color: blue;"> </span><span style="color: grey;">(</span>0.5235<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">O/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">29.994340575098594000<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">d)</span><u><span style="font-family: Consolas; font-size: 12.0pt;"> Sin(n):-<o:p></o:p></span></u></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;"> used  to get trigonometry sin value of given in
the  radians.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">sin</span><span style="color: grey;">(</span><span style="color: magenta;">radians</span><span style="color: grey;">(</span>30.00<span style="color: grey;">))                            sin(30)=1/2=>0.5<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">O/P<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">0.5 <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">e)<u>Cos(n) :-</u><o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: Consolas;">used  to get trigonometry Cos value of given in
the  radians.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">Cos</span><span style="color: grey;">(</span><span style="color: magenta;">radians</span><span style="color: grey;">(</span>30.00<span style="color: grey;">))</span><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: Consolas;">o/p-<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: Consolas;">0.866025403784439<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<u><span style="font-family: Consolas;">f)Tan(n):-<o:p></o:p></span></u></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: Consolas;">used  to get trigonometry tan value of given in
the  radians.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">tan</span><span style="color: grey;">(</span><span style="color: magenta;">radians</span><span style="color: grey;">(</span>30.00<span style="color: grey;">)) </span><o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: Consolas;">o/p-<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: Consolas;">0.577350269189626<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<u><span style="font-family: "Verdana","sans-serif";">g)Power(m,n):-<o:p></o:p></span></u></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">to
get m to power n.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">POWER</span><span style="color: grey;">(</span>5<span style="color: grey;">,</span>3<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">o/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;"> 
125<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<u><span style="font-family: Consolas; font-size: 12.0pt;">h)Square(n):-</span></u><span style="font-family: Consolas; font-size: 12.0pt;"><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;"> to get n of
square.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">SQUARE</span><span style="color: blue;"> </span><span style="color: grey;">(</span>25<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">O/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">625<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<u><span style="font-family: "Verdana","sans-serif";">i)Sqrt(n):-<o:p></o:p></span></u></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<u><span style="font-family: "Verdana","sans-serif";"> </span></u><span style="font-family: "Verdana","sans-serif";">      for square root<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">SQRT</span><span style="color: blue;"> </span><span style="color: grey;">(</span>625<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">O/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">25<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<u><span style="font-family: Consolas; font-size: 12.0pt;">j)Floor(n):-<o:p></o:p></span></u></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">    used to
round the given ‘n’ nearest int less than or equal  n.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">FLOOR</span><span style="color: grey;">(</span>7.54<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">O/P-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">7<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<u><span style="font-family: Consolas; font-size: 12.0pt;">k)celling(n):-<o:p></o:p></span></u></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">  used to
round the given ‘n’ to nearest integer greather than or equal ‘n’.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">CEILING</span><span style="color: blue;"> </span><span style="color: grey;">(</span>7.54<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">o/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">8<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">l)<u>Round(n,p[,+t):-</u><o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">used to round the given ‘n’  to ‘p’ decimal  places when the 3<sup>rd</sup> argument ‘t’
is specifying as 1 than n values will be truncate to p decimal places.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">ROUND</span><span style="color: grey;">(</span>54.53641<span style="color: grey;">,</span>2<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">o/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">54.54000<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">M)<u>Log(n):-<o:p></o:p></u></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;"> Used to get
natural logarithm of given ‘n’<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">LOG</span><span style="color: blue;"> </span><span style="color: grey;">(</span>100<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">o/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">4.60517018598809     <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">    <o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<u><span style="font-family: Consolas; font-size: 12.0pt;">n)log10(n):-<o:p></o:p></span></u></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="font-family: Consolas; font-size: 12.0pt;">used to get base 10 logrithm value of given ‘n’.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">LOG10</span><span style="color: grey;">(</span>100<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">o/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">2.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<u><span style="color: grey; font-family: Consolas; font-size: 12.0pt;">o)Sign(n):-<o:p></o:p></span></u></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">this function is used to determine
whether the given ‘n’ is positive or negative or zero.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">When the given ‘n’ values is positive
then it return(+1),when it is negative then return (1),and when it is zero(0)
then return (0).<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">SIGN</span><span style="color: grey;">(</span>0<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">o/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">0<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<u><span style="color: grey; font-family: Consolas; font-size: 12.0pt;">p)Exp(n):-<o:p></o:p></span></u></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">used to get expontial value of given no
.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">EXP</span><span style="color: grey;">(</span>3<span style="color: grey;">)<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">o/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">20.0855369231877.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<u><span style="color: grey; font-family: Consolas; font-size: 12.0pt;">Q)pi():-<o:p></o:p></span></u></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">Return the constant pi value.<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: blue; font-family: Consolas; font-size: 12.0pt;">select</span><span style="font-family: Consolas; font-size: 12.0pt;"> <span style="color: magenta;">pi</span><span style="color: grey;">()<o:p></o:p></span></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">o/p-<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<span style="color: grey; font-family: Consolas; font-size: 12.0pt;">3.14159265358979<o:p></o:p></span></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt; text-indent: 0.5in;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div class="MsoNormal" style="margin-bottom: 0.0001pt;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">  If you have any query  then frankly 
leave mail me <b>dotnetbyabhipatil@gmail.com</b><o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";">For
more article  visit my blog<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif";"> <b>http://dotnetbyabhipatil.blogspot.in<o:p></o:p></b></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="MsoListParagraph">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
<br /></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in; tab-stops: 45.95pt;">
               <o:p></o:p></div>
</div>

Bulit In Fuction I

Built in Functions: Sql server provides predefined function ,which are collectively called as built in function Bult in fucnction ar...

Difference Between SQL Server,Oracle and DB2

<div dir="ltr" style="text-align: left;" trbidi="on">
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: Verdana, sans-serif; font-size: 13.5pt;"><br />
when we face any interview then mostly asked question on SQL Server are,<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="color: red; font-family: "Verdana","sans-serif"; font-size: 13.5pt;">Que: What is the Difference
between SQL Server and Oracle and DB2 ?<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-size: 13.5pt;"> </span><span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt;">  So
in this article I demonstration on the difference between them.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; mso-bidi-font-family: Verdana; mso-fareast-font-family: Verdana;">1)<span style="font-family: 'Times New Roman'; font-size: 7pt;">  </span></span><span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt;">Windows OS,DOTNET  and SQL Server are all product of the
MicroSoft and there is tight integration between those product that provide
fast access to the data from SQL Server on Windows Operationg System from
DOTNET Application when Compare into Oracle and DB2.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; mso-bidi-font-family: Verdana; mso-fareast-font-family: Verdana;">2)<span style="font-family: 'Times New Roman'; font-size: 7pt;">  </span></span><span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt;">SQL Server provide
Graphical User Interface to Perform Various operation on Database.GUI  makes it easy to work with database but
Oracle doesn’t provide  Support GUI.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpFirst">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">e.g. oracle doesn’t  provide 
GUI but Sql Server  provide  GUI of any syntax of command DB2 also Provide
GUI.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpLast">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; mso-bidi-font-family: Verdana; mso-fareast-font-family: Verdana;">3)<span style="font-family: 'Times New Roman'; font-size: 7pt;">  </span></span><span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt;">The License of
oracle and DB2 for only 10 User that pay 2 Lakhs and SQL Server  pay only 2 to 3 Lakhs for unlimited user  .ie.the cost of SQL Server low price than
Oracle.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; mso-bidi-font-family: Verdana; mso-fareast-font-family: Verdana;">4)<span style="font-family: 'Times New Roman'; font-size: 7pt;">  </span></span><span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt;">SQL Server comes as
bundle of Minimum 4 Product.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpFirst">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">a)<u>DataBase Engine</u>:-<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">                         Using to work with database.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">b)<u>Analysis Services</u> :-<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">                       Using the work with datawereHousing,data
Hiring. <o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">c) <u>Reporting Service:-</u><o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">                        to create Enterprise
Report.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">d)<u>Integration Services :-<o:p></o:p></u></span></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">                      Using  the integrate the data from one dataSource to
another like oracle and SQL Server.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle">
<br /></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">   
All these 4  product are
collectively called as  <span style="color: #c0504d; mso-themecolor: accent2;">MSBI</span>(Microsoft Business
Intelliegence).<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle">
<br /></div>
<div class="MsoListParagraphCxSpMiddle">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">But in case of Oracle and DB2 ,you
have to purchase them separately.<o:p></o:p></span></div>
<div class="MsoListParagraphCxSpMiddle">
<br /></div>
<div class="MsoListParagraphCxSpMiddle">
<br /></div>
<div class="MsoListParagraphCxSpLast">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; line-height: 115%;">                      <o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in; mso-list: l0 level1 lfo1; text-indent: -.25in;">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt; mso-bidi-font-family: Verdana; mso-fareast-font-family: Verdana;">5)<span style="font-family: 'Times New Roman'; font-size: 7pt;">  </span></span><span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt;">the drawback of the
SQL server  is it is available for only
windows OS and not for Others Operating System like-UNIX,LINUX and SOLARIS.<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt;">   If
you want to learn more Details  on MSBI
then read my next article,<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt;">  If
you have any query  then frankly  leave mail me <b>dotnetbyabhipatil@gmail.com</b><o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt;">For more article  visit my blog<o:p></o:p></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<span style="font-family: "Verdana","sans-serif"; font-size: 13.5pt;"> <b>http://dotnetbyabhipatil.blogspot.in<o:p></o:p></b></span></div>
<div style="margin-bottom: .0001pt; margin: 0in;">
<br /></div>
<div class="MsoListParagraph">
<br /></div>
<div style="margin-bottom: .0001pt; margin-bottom: 0in; margin-left: .5in; margin-right: 0in; margin-top: 0in;">
<br /></div>
<br />
<div style="margin-bottom: .0001pt; margin: 0in; tab-stops: 45.95pt;">
<span style="font-size: 13.5pt;">            <o:p></o:p></span></div>
</div>

Difference Between SQL Server,Oracle and DB2

when we face any interview then mostly asked question on SQL Server are, Que: What is the Difference between SQL Server and Oracle an...

Create a Table from Another table

Bulit In Fuction I

Difference Between SQL Server,Oracle and DB2

Rate me