Here in this tutorial the advanced conepts of sql server are explained in detailed manner :::
CONTENTS
CONTENTS
1. Foreign key references
2. Predefined funcitons in sql
3.Aggregate functions
4. subquery
5. Functions
6. STORED PROCEDURES
7. INDEXES AND CURSORS
8. CURSORS
9. TRANSACTIONS
10. TRANSACTION STORED PROCEDURE
1. Foreign key references
- Referential integrity means one table depend on the another table.
- The foreign key should be assigned to the column of the table where there is primary key for that column is assigned in the another table. Ie., the column should be same
create table items(itemno int primary key,
itemname varchar(13) not null, itemqty int not null,
itemprice float not null, check (itemprice>0))
create table customers(cusno int primary key,
custname varchar(23),itemno int references items(itemno),qty int)
here referring table is items and referring column is itemno
syntax 2:
create table ac_master(acno int primary key,
name varchar(13) not null, address varchar(14) not null,
balance float not null)
create table tr_master(acno int,
amt int,constraint fk_acno foreign key(acno)
references ac_master(acno))
To delete the parent records directly without deleting the child records:
On delete cascade
create table tr_master1(acno int,
amt int,constraint fk_acno1 foreign key(acno)
references ac_master(acno)on delete cascade)
Group by having condition
select * from stu group by sno
select * from stu group by sno having sname='jj'
2. Predefined funcitons in sql
Upper(char)
select upper(Accname) from accdetails
lower(char)
select lower(Accname) from accdetails
replace
select accno,accname,creationusername ,
replace('10','aaa','nandu') from accdetails
where accname='aaa'
length
3.Aggregate functions
Sum( ): select sum(sal) from stu
Avg( ):select avg(sal) from emp
Count :select count(sal) from emp
Count(*):select count(*) from sal
Max() : select max(sal) from emp
Min( ): select min(sal) from emp
Abs(): select abs(-10)
Floor():select floor(17.98)
Variance:select variance(sal) from emp
Exp(n):exponential select exp(3)
Mod(min): remainder of division select mod(10,2)
Powe(m,n):select power(10,2)
Round: select round(1234.45454)
Sign(): select sign(15)
Cos()
Tan()
Sin()
Sinh()
Cosh()
Sqrt()
4. subquery
Select * from emp where sal>(select avg(sal) from emp)
select * from flight_details where income=(select max(income)from flight_details where dest=’delhi
select * from pilot_master where pno=(select pno from flight_details where income=
(select max(income) from flight_details where dest=’delhi ’) and dest=’delhi
Select * from stu_info where science>(select avg(science) from stu_info and name like ‘n%’
Select * from stu_info where name=(select name from stu_info where (science+maths+social)>250)
5. Functions
Two types:
1.system defined :select max(sal)
2.user defined
Return a value
Create function maxoftwo(@n1 int,@n2 int)
Returns int
As
Begin
Declare @big int
if @n1 > @n2
set @big = @n1
else
set @big = @n2
return @big
end
to run a function: select master.dbo.maxoftwo(10,20)
6. STORED PROCEDURES
TWO KINDS
INPUT PARAMETERS—takes values from function/procedure
OUTPUT PARAMETERS—return a value from storedprocedure
Syntax:INSERT
Create procedure sp_student_insert
As
Begin
Insert into student values(2,’nandu’,12,23,34)
End
For execution
Execute sp_student_insert
Or exec sp_student_insert
update
update stu set marks=100 where sno=3
Display:
Create procedure sp_student_display
As
Begin
Select * from student
End
Delete:
Create procedure sp_stu_delete
As
Begin
Delete from stu where sno-90
End
Commands used in stored procedure:
Declare @ abc int
Set @abc=10
Print @abc
1. Input paramets query:
Create procedure sp_student_inputparamets
@sno int,@sname varchar(30),@marks float
As
Begin
Insert into student values(@sno,@sname,@marks)
End
exec sp_students_inputparameters 100,’nan’,56,34
Drop procedure
Drop procedure <procedure name>
Ex., drop procedure sp_stu_input
2. OUTPUT PARAMETERS
Create procedure sp_stu_count
@count int output
--here must mention output to
--returnn a value from stored procedure
As
Begin
Select @count=count(*) from student
End
To call stored procedure
Declare @count int
Exec sp_stu_count @count=@count output
Print @count
7. INDEXES AND CURSORS
Index is a technique for record searching
We can create only one cluster index for one table
1.clustered—
It is segregated grouping to find records
Only one clustered index for one table
search based on single column
2.nonclustered—search is based on more than one column
Create clustered index index_eno on emp(eno)
Create clustered index index_accno on accdetails(accno)
Create nonclustered index index_accname on accdetails(accname)
For one column both clustered and nonclustered cannot be applied Clustered:
Em-db-table-rightclick-design table-sno right click-index/keys
-new sno-asc-create clustered index
Nonclusterd:
Table-rightclick-sname-rightclick-index/keys-new-sname-don’t tick creat clustered index because it is
Nonclustered index-close
Note:
Within one table clustered and non clustered index will not work
Any one for one table
8. CURSORS
It is a dataset which can hold more data
Setps:
1. create a cursor
2. open a cursor
3. fetch a cursor
4. close the cursor
5. deallocate the cursor
syntax:
1. declare <cursorname> cursor for <selectstatement>
2. open <cursorname> into <local variable>
3. 0-fetch succeded
4. 1-failed and record missed
5. 2-fetch failed and end of record set
6. close <cursor name>
7. deallocate<cursorname>
--local variables created
declare @sno int
declare @ sname varchar(10)
--creation of cursor
declare cursorstudent cursor for
select sno,sname from stu
--open cursor
open cursorstudent
--fetching records
fetch next from cursorstudent into
@sno,@sname
print @sno
print @sname
while @@fetch_status=0
begin
fetch next from cursorstudent int @sno,@sname
print @sno
print @sname
end
--closing cursor
close cursorstudent
--deallocating cursor
deallocate cursorstudent
9. TRANSACTIONS
Transactions are all about autommicity
i.e.,data consistancy.smallest grouping of statements
steps for transaction:
1. begin transaction
2. commit a transaction
3. rollback a transaction
4. save a transaction
begin trans <transactionaname>
commi trans <transactionname>
rollback trans <transacionname>
save trans savepointname
create table account(accno int primarykey identity(1,1))
--identity to increment the value
begin tran begintrans
insert into account default value--1
insert into account default value--2
insert into account default value--3
save tran savepointA--4
insert into account default value--5
insert into account default value--6
insert into account default value--7
save tran savepointB--8
insert into account default value--9
insert into account default value--10
insert into account default value--11
---first run above only
rollback tran savepointB--here 6records are opened
--or
rollback tran savepointA--here 3 records are opened
--or
rollback tran begintrans--0 records because no transactions
--or
commit tran begintrans--here 9 records
10. TRANSACTION STORED PROCEDURE
Create table account_details(accountno Int identity(1,1),name varchar(20),accounttype varchar(20),bal float)
Insert into account_details(1,’aaa’,’savings’,1000)
Insert into account_details(2,’aaa’,’savings’,1000)
Select * from accoutn_details
--create procedure
create procedure sp_acc_transfer(@savingsaccno int, @currentaccno int)
as
begin
begin transaction t1
save transaction savepoint
update account_details set bal=bal-30
where accountno=@savingsaccno
if(@@rowcount=0)
rollback transaction savepoint begin
update account_details set bal=bal+90
where accno=@currebt accno
if(@@rowcount=0)
begin
print @@rowcount
rollback transaction savepoint begin
end
else
commit transaction t1
end
exec sp_account_transfer 1,2
select * from account_holder
print @@ rowcounts
--------------THE END-----------
