SQL Tutorial..

                                                                                     AUTHOR

                                                                 THAMARAI SELVAN .M


create table thamarai( sno int,name varchar(20),age int,native varchar(20),salary float)
drop table nmts
insert into thamarai values(1,'muthu','16','kumari','10000')
insert into thamarai values(2,'wwwww','17','tuticorin','1500')
insert into thamarai values(3,'kumar','18','kallai','2000')
insert into thamarai values(4,'thamarai','21','tvmalai','3000')
insert into thamarai values(5,'siva raman','19','tvmalai','2500')
insert into thamarai values(6,'kamal','20','kallai','10000')
insert into thamarai(salary) values('20000')
select * from thamarai
select * from thamarai where age<=16
select * from thamarai where age <17
select *from thamarai where age=16
alter table thamarai alter column dob varchar(20)
update thamarai set dob='1/10/1990' where name='vicky'
sp_rename 'thamarai','nmts'
select * from thamarai
insert into thamarai values(7,'gwwwww','20','thiruvallur','12000')
insert into thamarai values(8,'ethirawwwwwj','21','thiruvallur','15000')
========================================================================
ARITHMETIC
==========
print 100+1000

or

select 2000+1000 as 'addition'

====================================================================

OPERATORS
************

RELATIONAL
==========

select * from thamarai where salary <=10000
select * from thamarai where salary >10000
NOT EQUAL
=========
select * from thamarai where salary !=10000
select * from thamarai where salary <>10000
LOGICAL OPERATOR
================
select * from thamarai where name='kamal' and sno=7
select * from thamarai where name='kamal'
select * from thamarai where name='kamal' or job='designer'
BETWEEN
========
select salary,name from thamarai where salary between 5000 and 10000

In
====
select * from thamarai where name in ('thamarai','muthu')
select * from thamarai where sno in (1,2)

NOT IN
======
select * from thamarai where sno not in (1,2)

LIKE OPERATOR
=============

select * from thamarai where name like 's%'
select * from thamarai where name like 'v%'
select * from thamarai where name like '_______i'

INTO STATEMENT
===============
\\ for copy the current table field into the another table.

DISTINCT
========
\\ is used for delete the duplicate values.

eg:
===
select distinct * from thamarai
=========================================================================
FUNCTIONS
=========
TYPES:
======
1.single row function.

2.Aggregate function or group function.

SINGLE ROW FUNCTION:
====================
1.Numeric function
2.String function
3.Date and time function.
4.System function
5.Calculation result
6.conversion function(cast,convert)
7.other function
=========================================================================
1.NUMERIC FUNCTION:
===================
Eg:
===
select cos(0) as 'cos value'
    (or)

print sin(30)
    (or)

print 'sin value is='+str(sin(30))
==========================================================================
2.CONVERSION FUNCTION:
======================
select 'cos value is='+convert(varchar(20),cos(0))
// because to print the statement ,so we convert into variant.

select 'tan value is='+cast(tan(45) as varchar(20))
==========================================================================
ROUNDING:::
===========
print round(123.458,2)
// add 2 digits i.e 123.450//

print 'round value is='+convert(varchar(20),round(123.45,1))
// add 5 digits 123.50//

print 'round value is='+convert(varchar(20),round(123.45,0))
//its rounding the value is 123//

print 'floor value is='+str(floor(123.45))

print 'ceiling value is='+str(floor(-123.45))
===========================================================================
STRING FUNCTION::
=================
print 'ascii value is='+str(ascii('A'))
 // A-Z=65 t0 90//

print 'ascii value is='+str(ascii('a'))

print 'char='+char(65)
// code purpose i.e 65=A

print 'uppercase letter='+upper('welcome to orange')
// upper case purpose i.e welcome=WELCOME

print'length is='+convert(varchar(20),len('computer'))
// find the length of the given word.

print 'left string='+left('thamarai',6)
// we want only 6 letter word left to right so use "6"

print 'right string='+right('thamarai',2)
// we want only 2 letter from right to left "2"

print 'replace value='+replace('selva','kumar','thamarai')
=========================================================================
CURSOR PROCESSING::
===================
declare => Defines a cursor
open    => open a cursor
Fetch   => Retrieves a row from the cursor
close   => closes the cursor
Deallocate => Deletes the cursor

Eg::
====

declare nmts cursor static
for
select sno,name,job,salary from thamarai
open nmts
fetch next from nmts
fetch prior from nmts
fetch last from nmts
fetch absolute 5 from nmts
fetch relative -2 from nmts
fetch first from nmts
close nmts
deallocate nmts
=========================================================================
DATE AND TIME FUNCTION:::
=========================

print getdate()

select getdate()'todays date'

select day('02/1/2011')

select month(getdate())

select month('01/2/2011')

select year(getdate())

DATE ADD::
==========
// current date + user defined value

// current date +2
select dateadd(dd,2,getdate())

select dateadd(mm,5,'04/12/2011')
//month +2

DATE PART::
===========
// date part is used to get the current date,mm,yyyy,ss,hh,qq.
select datepart (dd,getdate())

select datepart (mm,getdate())

DATE NAME::
===========
// date name is used to get the date,year, and month.
select datename(ss,getdate())

DATE DIFFERENCE::
=================
//date difference is used to find the age.or find the diff between prv years.
select datediff(yy,'06/27/1990',getdate())'Age'

==================================================================
SYSTEM FUNCTION::
=================
select host_name()

select user_name()

select user_id()
===================================================================
OTHER FUNCTION::
================
select isdate(getdate())

select isdate(2)
=================================================================
GROUP FUNCTION OR AGGREGATE FUNCTION::
======================================
count=> count the rows

Avg  => get the average value

sum => add the values

max=> Identity the max values

min => Identity the min values

COUNT::
=======
select count(name)from thamarai

select count(job)from thamarai

select count(*)no from thamarai

select * from thamarai

AVg::
=====
select avg(salary)'average salary' from thamarai

SUM::
=====
select sum(bonus)'total bonus' from thamarai

MAX & MIN::
===========
select max(salary) as'maximum salary',
min(salary) as 'minimum salary' from thamarai
==================================================================
VIEWS::
=======
1.Updatable view.

2.Non updatable view

UPdatable view::
=================
Syntax::
========
create view <view name> as query

(Eg)
create view nmts as select * from thamarai
query::
=======
eg::
=====
select * from nmts
============================================

create proc show @age varchar(20) as
declare @age varchar(20),@dob varchar(20)
select @age=age from thamarai where dob=@dob
print'age:'+@age
exec show