SQLSERVER
===========
SQL stands for Structured Query Language
In 1980's Microsoft and sybase team together to create sql server.It run only on IBM's machine.os/2 operating system plat form.
After the release of version 6.5 microsoft and sybase sepailed and sql server made it's progress into the market it a RDBMS [Relational Data Base Managemant System].
The database architecture introduced by sql server 7.0 was very modern .
Sql server 2000 keeps the database architecture of sql server 7.0 as its foundation and it provides additional features thus making it fully web enabled ,scabled ,reliable,and easily programmable.
Edition of sql server :-
================
Sql server 2000 standed edition
Sql server 2000 enterprise edition
Sqlserver2000 personal edition
Sql server 2000 desktop edition
Sql server 2000 windows edition
Tables:-
=======
Tables are database objects that contains all the data present in a database .A table is a collection of rows and column format similar to a spread sheet.A database contain many tables.It is a possible to have as many as two billion tables per database and 1024 column per tables .The total size of the table is limited only by the amount of byte per row 8060 byte.
SQL SERVER TABLES:-
====================
All the sql server tables posses contain common features of rdbms such as
1.Each table must have a unique name
2.Each row in the table must be unique and describe one set of related information about the subject of the table.
3.Each column or field describes a single characteristic of an name must be unique within the tables
DESINGING SQL SERVER TABLES:-
================================
When we design a database we decide the type of tables that are needed what type of data the goes in each tables who has access to each tables and so on as we create and work with tables.
Certain points one should keeps in the mind while creating table
1.Type of data the table will contain
2.Column in the table and datatype [and length if required] for each column,column or field describes a single characteristic of an entity column name must be unique with in the table
3.Which column accepts null values
4.name of the column
5.Wheather and where to use default values
DATATYPE:-
============
Integer ===> int(4 bytes),
Numeric ===> (9 bytes)
float, character ===> char(any value),varchar(any value ,decimal,datetime,currency.
To enter an sql server:-
===================
start ===> programs ===> microsoft sql server 7.0 ===> query analyzer
CREATING TABLES:-
====================
A tables can be created using
2.Transact sql statement
CREATING DATABASE :-
======================
Syntax:-
=======
create database <database name>
Ex:-
===
create database karthi
**********************************************************************
CREATING TABLES:-
===================
Syntax:-
=======
create table <table name> ( column name) <datatype>,(column name)<datatype>.........(column n) <datatype>
EX:-
====
create table karthi (sno int,name varchar(20),age int,place varchar(20))
TO SEE THE STRUCTURE OF THE TABLE :-
=======================================
SYNTAX:-
=========
sp_help <table name>
Ex:-
===
sp_help karthi
TO DROP TABLE:-
================
SYNTAX:-
=========
drop table <table name>
Ex:-
====
drop table karthi
TO ADD THE RECORDS TO THE TABLE:-
=================================
SYNTAX:-
=========
insert into <table name> values((column values))
Ex:-
===
insert into karthi values(1,"karthi",21,"nallur")
TO VIEW THE RECORDS:-
======================
syntax:-
=======
select *from <table name>
Ex:-
====
select *from karthi
MODIFYING TABLE:-
===================
After a table create we can change the option that were defined for the tables when it was orginally created .
column can be added,modify,deleted.
ALTER COLUMN:-
===============
SYNTAX:-
=========
alter table <table name> alter column <column name > <new datatype>
Ex:-
====
alter table karthi alter column id varchar(10)
ADD COLUMN:-
==============
SYNTAX:-
========
alter table <table name> add <column name> <datatype>
Ex:-
===
alter table karthi add salary int
DROP COLUMN:-
================
SYNTAX:-
=========
alter table <table name> drop column <column name>
Ex:-
====
alter table karthi drop column age
UPDATING ROWS:-
==================
SYNTAX:-
=========
update <table name> set column name = <values> where condition
EX:-
====
update karthi set name="sona" where id=1
DELETING ROWS:-
=================
SYNTAX:-
=========
delete from <table name> where condition
EX:-
====
====
delete from karthi where id=1
FILTERING ROWS FROM TABLES:-
===============================
There are two types.
1.Horizontal
2.Vertical
1.Horizontal:-
============
select *from karthi where id=1
2.Vertical:-
==========
select name from karthi
CONSTANTS IN QUERY RESULT SETS:-
=================================
SYNTAX:-
=========
select <column name>+"&&&&&&"+<column name>from <table name>
EX:-
=====
select name+"&&&&&&"+place from karthi
ASSIGNING RESULT SET COLUMN NAME:-
====================================
SYNTAX:-
=========
select <orginial column name > as duplicate column name from table name.
EX:-
====
select name as studentname from karthi
COMPUTED VALUES IN THE SELECTED LIST:-
========================================
SYNTAX:-
==========
select <column name> <columnname >from <table name>
EX:-
====
select name,id*0.2 as discount from karthi
ELEMINATING THE DUPLICATE WITH DISTINCT:-
===========================================
syntax:-
======
select distinct <column name> from <table name>
EX:-
====
select distinct name from karthi
USING REALTIONAL OPERATORS AND WHILD =======================================
CHARACTERS:-
==============
operators:-
========
[=,<,>,<>,<=,>=]
whild character:-
============
'_' ===> a single character
'%' ===> a string of the length
[] ===> a single character within the range enclosed in the brackets.
[^] ===>any single character not within the range enclosed in the brackets.
EX:-
=====
select *from karthi where id =2
select * from karthi where id>103
select *from karthi where id>=103
select*from karthi where id<103
select *from karthi where id<=103
select *from karthi where id<>103
USING BETWEEN OPERATORS:-
==========================
Expression [not] between & <start exp> and <end exp>
EX:-
====
select *from karthi where id between 101and 102
select *from karthi where id not between 101and 102
Logical operators:-
==============
The logical operator are and,or,not.
And,Or are used to search condition
Not clause not negartes the search condition
select *from karthi where id >103
select *from karthi where id=101 or id<=103
select *from karthi where id <=103 and id<103
SORTING THE ROW WITH ORDER BY:-
=================================
asc ===> ascending
desc ===> descending
select *from karthi order by id asc
select *from karthi order by id desc
1.USING IN:-
==========
select *from karthi where id in(2,4)
select *from karthi where id not in(2,4)
select *from karthi where name like 's [a-b] %'
select *from karthi where name like 's [^a-b] %'
select *from karthi where name like 'pradep'
select * from karthi where name like's_%'
QUERY:-
=======
Combine data from multiple table
=========
Combine data from multiple tables
2.SUBQUERIES:-
==============
sub query is encapsulation of one query inside another query
3.JOINS:-
========
Joins combine column from multiple data tables.
===================================================
UNIONS:-
========
union operator combines the result of two or more selected statement into a single result set.
syntax:-
=====
select statement union (all) select statement
create table mickey (id int,name varchar(30))
insert into mickey values(3,"navin")
select *from mickey
ex:-
===
select id,name from karthi union select id,name from mickey
USING SUB QUERIES:-
=================
we can use one selected statement to return records
that will be used by another select statement.
The encapsulation query is called parent query & the inner
query is called sub query.
syntax:-
=====
select(columnname)from <table>where (column name)in(select column name from (table where column)=criterial))
ex:-
==
select name from mickey where id in(select id from karthi where id=4)
USING EXISTS AND NOT EXISTS (sub query):-
====================================
the sub query does not actually return any data.It return a value of true or false
SYNTAX:-
========
where(not)exists(subquery)(exist)
ex:-
===
select *from karthi where exists (select name from karthi where karthi.id=balaji.id)
select *from karthi where not exists (select name from karthi where karthi.id=balaji.id)
===================================================
JOIN:-
====
Joins are used to retrieve data from two or more tables based relationship between tables.
syntax:-
=====
select<short name.cloumn name list of columns>from <table as a table >_alias a join table b as
table alias b on table alais a <cloumn field >=table_alias_a<column field>)
EX:-
==== select a.name,b.id from karthi as a join nive as b on a.id=b.id
there are three types of joins :-
=====================
1.inner join
2.outer join
3.self join
1.inner join:-
=========
record from two tables are combined and added to a query result only if the rows from both the tables are matched based on a column.
ex:-
===
select a.name,b.id from mickey as a inner join karthi as b on a.id=b.id
there are two types :-
===============
1.equi join
2.natural join
1.equi join:-
========
It is a join in which the column values begin compared are equal.
EX:-
===
select a.name,b.id from mickey as a inner join karthi as b on a.id=b.id
2.natural join:-
===========
It is one in which the column values begin compared 2 or more tables but the join column is displayed only once.
create table sun(id int,name varchar(20))
insert into sun values(1,"karthi")
create table moon (id int,place varchar(20))
insert into moon values(1,"pp")
select *from moon
ex:-
==
select name,place from sun,moon where sun.id=moon.id
outer join:-
=======
It return all rows from at least one of the table mentioned in the from clause as long as those rows meet any where or having search condition of the select statement
there are three types of outer joins
1.left outer join
2.right outer join
3.full outer join
1.left outer join:-
===========
left outer join return all the records from the left tables
are only matching records from the right rable.
ex:-
==
select a.id,a.name,b.id from karthi as a left outer join mickey as b on a.id=b.id
2.right outer join:-
============
The right outer join pulls all the records from the second table in the table join regardles of whether there is matching data in the first table
EX:-
===
select a.id ,a.name,b.id from karthi as a right outer join mickey as b on a.id=b.id
full join:-
======
A full join lists all the records from both the tables,regardles of whether there are matching records in the tables or not.
EX:-
===
select a.id,a.name,b.id from karthi as a full join mickey as b on a.id=b.id
self join:-
======
self join is a type of inner joins.It is used to find records in a table that are related to another records in the same tables.
A table is joined to itself in a self join.
EX:-
===
select a.id,a.name,b.id from karthi as a join mickey as b on a.id=b.id
chapter-6
=======
constraint:-
========
A constraint is a property that can be placed on a column or set of column in a tables,in order to limit the data that is entered in the column or set of cloumns.
creating and modify primarykey:-
=======================
syntax:-
======
create table table name <column _defination>primary key
EX:-
===
create table suresh (id int primary key,name varchar(10),age int)
insert into suresh values(1,"pradeep",19)
insert into suresh values(2,"mahes",20)
sp_help suresh
added to an existing tables:-
====================
syntax:-
======
alter table <table _name> add constraint <constraint _name >primary key <column name>
ex:-
==
create table yadav (id int,name varchar(15),age int)
insert into yadav values(3,"mickey",15)
select*from yadav
alter table yadav alter column id int not null
alter table yadav add constraint ns primary key (id)
select * from yadav
insert into yadav values (4,"roja",23)
sp_help yadav
drop constraint :-
============
syntax:-
======
alter table <table name>drop constraint <constraint name>
ex:-
==
alter table yadav drop constraint ns
foreign key:-
=========
a foreign key is a combination of columns used to create a link between the data present in two tables.
syntax:-
======
create table pass(ticket_no int ,pnr_no int foreign key references reservation (pnr_no))
ex:-
====
create table pass (id int references yadav (id),name varchar(20),age int)
insert into pass values (2,"balaji",23)
select *from yadav
sp_help pass
drop table pass
check constraint
============
a check constraint is used to enforce domain integrity the values stored in a column
ex
=
create table karthi(id int check(id<=3),name varchar(20),age int)
insert into karthi values(4,"balaji",22)
select *from karthi
sp_help karthi
not null constraint:-
==============
not null constraint means that a column cannot have a null values
EX:-
===
create table karthi(id int not null,name varchar(20),age int)
insert into karthi values(" ", "arun",23)
select * from karthi
UNIQUE CONSTRAINT:-
===================
A unique constraint specifices that all value in a given column must be unique a table can have mulitple unique constraint a column that allow null values
ex:-
create table mahes(id int unique,name varchar(20),age int)
insert into mahes values(1,"mahes",18)
insert into mahes values(2,"murali",28)
select * from mahes
sp_help mahes
drop table mahes
default definition:
============
every column in a record must have a value even if that value is null it should not be left blank.
ex:-
==
create table murali1(id int ,name varchar(20),age int default 20 )
insert into murali1 values(1,"mahes",18)
insert into murali1 values(2,"murali",18)
if we are not giving the id values means use this method
insert into murali1 (id,name)values(1,"sathish")
select *from murali1
drop table murali1
identity properties:-
=============
the starting value set by this property is 1.this property can be set at one of table creation
syntax:-
======
column name datatype identity(seed,increment)
where:-
=====
column name of the column to which identity property is attached
seed:-
===
the starting or initial values for the identity column
increment:-
=======
the stop value used to generate the next value for the column this can be negative also.
ex:-
==
create table hema (id int identity(1,1),name varchar(20),salary int)
insert into hema values("jothi",2100)
select *from hema
drop table hema
rules:-
===
select *from karthi
syntax:-
======
create rule <rule name>as @ <column name>condition
sp_help ks
ex:
===
create rule ks as @age >18
binding rule to the table:-
=================
syntax:-
=====
exec sp_bindrule<rule name><table name>.<column name>
ex:-
==
exec sp_bindrule ks,'karthi.age'
insert into karthi values(10,"kalai",19)
select *from karthi
unbinding rule from the table:-
=====================
syntax:-
=====
exec sp_unbindrule <table name>.<column name>
ex:-
===
exec sp_unbindrule'karthi.age'
drop the rule from table:-
=================
syntax:-
=====
drop rule <rule name>
ex:-
===
drop rule ks
=====================================================================
view:-
====
the simulated tables support the same functionality as physical tables. the data can be read added and updated in the simulated tables.these simulated tables are called as views.
a view is an altemate way of working at data from one or more tables in the database a view acts as a filter on the tables addressed in the query.
syntax:-
=====
create view <view name>select <statement>
ex:-
==
create view try as select id,name from karthi
select *from try
insert into try values(4,"sa")
indexed view:-
==========
indexed view has been introduced with sql `server
2000 and its support only by its enterprise edition
if the view references any user defined functions.it must use the shemabinding option
index:-
====
index is a named database structure that is used by sql server to locate a row of a table very quickly
indexs are generally created on column that are frequently used in the query
types of index:-
===========
1.clustered index
2.non-clustered index
3.unique index
4.composite index
A column can be selected for index based on the following criteria
1. the column is used for search very frequently
2. the column is used for sorting the data
clustered index:
==========
A clustered index determines the stoarage order of data in a table clustered index is ideal for pirmary key on column that is often used for a range search.
syntax:-
======
create clustered index <index name>on <table name><column name>
ex:-
===
create clustered index sona on karthi(age)
insert into karthi values(5,"balaji",10)
select *from karthi
non-clustered index:-
==============
A non-clustered index specifies a logical ordering only.therefore a table can have mulitiple (249)
non-clustered index
the data is stored in one place .the index in another place ,with inters pointers to the storage location of the data .
syntax:-
=======
create non clustered index index name on <table name><column name>
ex:-
==
create nonclustered index balaji on karthi (id)
select *from karthi
unique index:-
==========-
A unique index ensures that the indexed column no duplicate values .in case of multi-column unique indexes.The index ensures that each combination of values in the indexed
column is unique
creating a primary key or unique constraint automatically creates a unique index on the specified column in the table
syntax:-
=====
create unique index name on <table name> <cloumn name>
ex:-
==
create unique index pradeep on karthi(id)
insert into karthi values (6,"raj",34)
select *from karthi
composite index:-
=============
A composite index consits of two or more column index together the maximum
number of column that can be combined is 16 and the allowable size of 900 bytes.
the column order is very important in creating a composite index
syntax:-
=====
create index index name on <table name>(column name1,column name2)
ex:-
===
create index mahi on karthi(id,age)
insert into karthi values(6,"mathi",12)
select *from karthi
viewing indexs:-
============
To view the indexs on a table.
syntax:-
=====
sp_help index <table name>
ex:-
===
sp_help index karthi
sql server procedures:
================
it is used to perform a particular work and return a value if necessary.
ex:-
==
create procedure empproc as
delete from karthi where id=6
select *from karthi
exec empproc``````````````````
ex:-
==
create procedure empanitha @a int as
begin
select * from karthi where id =@a
end
exec empanitha 4
if-else statemanet:-
=============
ex:-
===
create procedure kavi @a int,@b int as
begin
if(@a>@b)
select"a greater number is=",@a
else
select"b greater number is=",@b
end
exec kavi 50,56
ex:-
===
create procedure jaya @a int,@b int,@c int as
begin
select @c=@a+@b
if(@c>100)
print"sucess"
else
print"failure"
end
exec jaya 56,23,23
do -while statemant:
==============
ex:-
==
create procedure kalai @i int as
print"the number from 1 to 100 are"
while (@i<=100)
begin
print @i
set @i=@i+1
continue
end
exec kalai 1
break statement:-
============
exist out from the loop
create procedure rr @i int as
print"the number from 1 to 10 are"
while(@i<=10)
begin
print @i
break
set @i=@i+1
continue
print "ok bye"
end
exec rr 1
goto statement:-
===========
control move from one statement to another statement.
ex:-
===
create procedure sp @i int as
print"the number from 1 to 10 are"
while(@i<=10)
begin
print @i
set @i=@i+1
goto hai
continue
hai:
print"hai friends"
end
exec sp 1
TRIGGER
=======
It is a database object created by an user and it is invoked automatically when any modification done in a table.
Three types of triggers-
1.insert trigger
2.update trigger
3.delete trigger
1.insert trigger:-
===========
create table yahoo(id int,name varchar(20),age int)
insert into yahoo values(1,"mangai",23)
ex:-
==
create trigger pp on yahoo for insert as
select *from yahoo
then insert one or more records into the table it will automatically run the select
statement .
drop trigger pp
insert into yahoo values(5,"sam",23)
2.update trigger:-
===========
create trigger pp on yahoo for update as
select id,name from yahoo
if we update any records the select satement is automatically executed.
update yahoo set name="jothi" where id=5
drop trigger pp
3.delete trigger:-
============
create trigger pp on yahoo for delete as
select *from yahoo
delete from yahoo where id=5
if we delete any record then select statement is automatically executed.
drop trigger pp
we can also combine:-
================
-- insert and update
-- insert and delete
1.insert and update:-
=============
create trigger dd on yahoo for insert as
select*from yahoo
update yahoo set name="mangai" where id=1
insert into yahoo values (5,"sam",23)
drop trigger dd
2.insert and delete:-
==============
create trigger df on mickey for insert as
select *from mickey
delete from mickey where id=5
insert into mickey values(6,"sakthi")
drop trigger df
TRIGGER WITH PROCEDURE
========================
insert trigger:-
=========
create trigger kk on yahoo for insert as
if (select age from inserted)<5
begin
print"age cannot be lessthan 5"
rollback transaction
end
insert into yahoo values(3,"dsd",1)
insert into yahoo values(8,"dfdssd",34)
drop trigger kk
delete from yahoo where id=3
update trigger:-
==========
create trigger kk on yahoo for update as
if update(name)
begin
print"you cannot change the name"
rollback transaction
end
update yahoo set name="mathi" where id=5
drop trigger kk
delete trigger:-
==========
create trigger kk on mickey for delete as
if(select id from deleted)>5
begin
print"you should not delete the records whose id greater than 5"
rollback transaction
end
delete from mickey where id=6
drop trigger bt
select * from mickey
drop a trigger:-
==========
syntax:-
=====
drop trigger <trigger name>
ex:-
==
drop trigger jay
to execute:
=======
exec sp_help trigger <table name>
exec sp_help trigger yahoo
CURSOR
=======
cursor is a database object used by application to mainpulate the data by row instead of sets.using cursor mulitple operations can be performed row by row against the result set with or without returning to the original table.cursor used to refer memory area .
creating a cursor:-
=============
b declare <cursor name>cursor
for
<select statement>
ex:-
==
declare hai cursor for select *from yahoo
to open a cursor:
============
syntax:-
=====
open<cursor name>
ex:-
==
open hai
to fetch row in a cursor:
==================
syntax:-
=====
fetch<cursor name>
ex:-
==
fetch hai
to close an opened cursor:
=====================
syntax:-
======
close<cursor name>
ex:-
===
close hai
to remove or delete a cursor:
====================
syntax:-
=====
deallocate <cursor name>
ex:-
==
deallocate hai
fetching and scrolling a cursor:-
======================
when a cursor is opened the current row position in the cursor is at the first row.
sql cursors can fetch one row at a time .the rows from a cursor is called fetching
there are various operations:-
====================
1.fetch first
2.fetch next
3.fetch prior
4.fetch last
5.fetch absolute
6.fetch relative
Another way to create cursor:-
=====================
syntax:-
=====
declare cursor name cursor for sql statement a scroll-option -is used to move the cursor up and down so we can fetch any records.
ex:-
===
declare hai cursor scroll for select*from yahoo
open hai
fetch operation:-
============
five fetch operation can performed only when the cursor is declared as scroll type
syntax:-
======
fetch <fetch type>from<cursor name>
create table csc (id int,name varchar(15),age int)
insert into csc values (6,"rweyr",17)
delete from csc where id=6
select*from csc
declare cur1 cursor scroll for select *from csc order by id
open cur1
fetch first from hai
fetch next from hai
fetch prior from hai
fetch last from hai
fetch absolute 3 from hai
fetch relative 3 from hai
fetch relative -3 from hai
close cur1
fetch status:-
=========
@@fetch _status=0,-1
there must be no row to fetch
fetch first from cur1
while @@fetch_status=0
begin
fetch next from cur1
end
Locking mechanism:-
===============
only theory concept:-
==============
It is used to prevent the table datas from another user access
other user access:-
=============
Once a lock is applied to a table other users cannot use a same table for their own transactions.
Three types :-
=========
1.Shared locks:-
===========
when a table user share lock means other user can only view the data but he/she cannot insert update or delete a record in a table.
2.Update locks:-
===========
only update work is done by other user in a table rather than insert/delete cannot be done in a update lock table
3.Exclusive lock:-
============
here all the options such as insert /delete /update can be done by other user in a table
DATA CONSISTENCY:-
==================
two types of transaction
1.Implicit transaction
2.Explicity transaction
1.Implicity transaction:-
================
These transaction are automatically controlled by sql server. if the statement cause error.it is automatically committed
2.Explicit transaction:-
===============
explicit transaction is a grouping of sql statements marked by,begin transaction and commit transaction
tcl statement
=========
1.begin transaction:-
==============
mark the starting point of a transaction.
2.commit transaction:-
===============
marks the end of a program and makes the changes with in the transaction a permanent part of a database.
3.rollback transaction(save point):
========================
rollback a transaction to the starting point or to the specified save point.
4.save transaction:-
==============
save a new save point with in a transaction
5.nested transaction:-
===============
A transaction within another transaction @@trancount is used to determine if there are any open transaction and how many level deep they are nested.
ex:-
==
declare @@rowcount int
begin transaction
delete csc where id=6
if (@@ rowcount) >1
begin
rollback transaction
print"more than on name with same id"
print"deletion cancelled"
end
else
commit transaction
print"deletion sucessful commited"
ex:2
====
begin transaction
delete csc where id=6
save transaction e1
delete csc where id=2
save transaction e2
select *from csc
rollback transaction e2
select *from csc
rollback transaction e1
select *from csc
commit transaction
select *from csc
NUMERIC FUNCTION:-
===================
select abs(-190)
select cos(90)
select sin(45)
select cot(45)
select tan(180)
select exp(2)
select log(3.14)
select pi()
select sqrt(81)
select ceiling(123)
select floor(1234)
select power(2,3)
