T-SQL

Database:

Permanent storage of data, collection of dB objects

DBMS:

Software application that interacts with user to analyze data

RDBMS:

Database with relational model. Most relational db uses SQL. (sql query language)

DDL- data definition language; create,alter,drop

DML-data manipulation language; insert, update,delete

DQL-data query language; only read data from the table; select

DCL-data control language-authorize users; grant, revoke, deny

TCL- transaction control language ; consistency of transactional data;

TSQL-Transact SQL

**consistency of transaction

*includes procedural programming inSQL

*variables, control sattements

Supports exception handling

Helped maintaining batches

Data types

Numerics- bigint,int,smallint,tinyint,bit,decimal,money,small money,numeric

Date and Time

Character strings

Unicode character strings

Binary strings

Subquery -used when main query needs some evaluated values. Executes before main query and return the value to main query.

if multiple data retrieved from sub query, use any, some or all before subquery to retrieve data. Inner query is independent.

Correlated subquery-Outer query is dependent on inner query and inner query is dependent on outer query as well.

Set Operators-union, union all, intersect, except

union- duplicate records will be removed

union all- duplicate records will not be removed

intersect- only common records will be shown

except-will remove the records from second table

Group functions- aggregate-count, max, min, avg, stdev

Group by- used to arrange identical data into groups. HAVING clause can be used to filter groups. Where will filter rows.

select Dept, sum(salary) totsal from emp group by Dept having sum(salary)>100000

DDL-create, alter, drop, truncate, enable, disable

Enable and disable for triggers- meta data will get affected

alter table tbl_students add Fees numeric(8,2)

alter table tbl_students alter column Fees numeric(10.2)

alter table tbl_students drop column Fees numeric(10.2)

alter table tblEmp add constraint emp_gender_chk check(Gender in ('M','F'))

Check Constraint

Gender char(1) constraint student_gender_chk check(Gender in ('M','F'))

constraint student_fees_chk check(Fees>=500)

Unique Constraint

constraint student_mob_un unique(MobileNo)

DML- can be rolled back

insert, update, delete (to  remove records by choosing records using where clause. data will be still in memory and can roll back. but with truncate, we cannot roll back)

Constraints- rules enforced on data columns on table. can apply on column or table level. can be applied by create table or alter table

NOT NULL, UNIQUE, CHECK, PRIMARY KEY, FOREIGN KEY

UNIQUE constraint will not accept duplicate data, but it will accept NULL.

*can add in mobil no or email id

PRIMARY KEY constraint- combination of NOT NULL key and UNIQUE

constraint student_studentid_pk primary key(StudentID)

FOREIGN KEY constraint-to enforces referential integrity-AN Employee can only be in a particular department if that particular department exists in departments table.

constraint student_course_fk foreign_key(Course) references tbl_Course(CourseId) on delete not null

constraint student_course_fk foreign_key(Course) references tbl_Course(CourseId) on delete cascade

constraint student_course_fk foreign_key(Course) references tbl_Course(CourseId) on update cascade

Indexes- to speed retrieval of data. physical indexing is implicitly applied with primary key and unique key

Unique Index- will automatically created when we assign any unique constraint in a column.

Cluster Index-physical index, data will be sorted on that particular basis, there can only one clustered index in a table. whenever we are adding a primary key in a table, that particular field is a clustered index. Means there can be only one CI in  a table.

Non Clustered index is  a logical index . Indexing will be done internally and result will be fetched. All unique key constraint fields are also non clustered index.

create nonclustered index Employees_FirstName_Ind

on tblEmployees(FirstName)

View: virtual table, provides restricted access to table, dml can be performed

Types of view:

 Simple view-A view based on only a single table, which doesn't contain GROUP BY clause and any functions.

Complex View: A view based on multiple tables, which contain GROUP BY clause and functions.

Inline View: A view based on a subquery in FROM Clause, that subquery creates a temporary table and simplifies the complex query.

Materialized View: A view that stores the definition as well as data. It creates replicas of data by storing it physically.

alter view empvw89 as

select EmpId, FirstName + ' ' + isnull(LastName,' ') as EmpName, Salary*12 as AnnualSal, Department

from tblEmployees

where Department=89

select * from tblEmployees

select * from empvw89

drop view empvw89

DML restrictions in view

If DISTINCT or Group function or group by clause is present, delete cant be possible

If DISTINCT or Group function or group by clause or contains expression is present, update cant be possible

If DISTINCT or Group function or group by clause or contains expression, not null column excluded is present, insert cant be possible

delete from empvw89 where EmpId=117

alter view empvw89 as

select EmpId, FirstName + ' ' + isnull(LastName,' ') as EmpName, Salary*12 as AnnualSal, Department

from tblEmployees

where Department=89

with check option

update empvw89 set Department=60 where EmpId=118

This cannot be done, as we have enabled with check option here and cannot update Departments other than 89.

To make view readonly

alter view empvw89 as

select EmpId, FirstName + ' ' + isnull(LastName,' ') as EmpName, Salary*12 as AnnualSal, Department

from tblEmployees

where Department=89

union

select EmpId, FirstName + ' ' + isnull(LastName,' ') as EmpName, Salary*12 as AnnualSal, Department

from tblEmployees

where 1=2

with check option

T-SQL (Transact SQL)

To deal with transactions. Includes procedural programming in sql. Supports exception handling to ensure consistency. variables and control statements are part of T-SQL.

begin

declare @var int;

declare @sal numeric(8,2);

declare @name varchar(20);

set @name='Anadi';

set @var=100;

set @sal=50000;

print @sal;

print @name;

print @var;

print @name + ' earns ' + cast(@sal as varchar);

end;

begin

declare @var int;

declare @sal numeric(8,2);

declare @name varchar(20);

select @name=FirstName, @sal=Salary

from tblEmployees where EmpId=101;

print @name + ' earns ' + cast(@sal as varchar);

end;

T SQL Conditional Statement

Imposes conditions on the execution of a Transact sql statement.

if condition

else

eg:

declare @sal numeric(8,2)

declare @grade char(1)

select @name = FirstName, @sal=Salary

from tbl_Employees

where EmpId=105;

if @sal >= 50000

begin

set @grade='A';

end

else if @sal >==35000

begin

set @grade='B';

end

else

begin

set @grade='C';

end

print @name + 'is in Grade' + @grade;

end

Loops/Iteration statements

sets  a condition for the repeated execution. Statements executed repeatedly as long as specified condition is true. Execution can be controlled by BREAK and CONTINUE keywords.

eg:

declare @sal numeric(8,2)

declare @grade char(1)

declare @name varchar(20);

declare @id int ==101;

while @id <=110

begin

Select @name=FirstName, @sal=Salary

from tbl_Employees

where EmpId=@id;

if @sal >= 50000

begin

set @grade='A';

end

else if @sal >==35000

begin

set @grade='B';

end

else

begin

set @grade='C';

end

print @name + 'is in Grade' + @grade;

set @id=@id+1;

end

Exception Handling

To handle runtime errors. Group of statements in try. if exception happens, control passed in catch block.

BEGIN TRY

select 1/0;

END TRY

BEGIN CATCH

SELECT

@@ERROR as ERROR,

ERROR_NUMBER() as Errornum,

ERROR_SEVERITY() as Errorsevere,

Error_State() as Errorstate,

ERROR_PROCEDURE() as errorproc,

ERROR_LINE() as errorline,

ERROR_MESSAGE as Errormessage;

END CATCH;

begin

BEGIN TRY

declare @sal numeric(8,2);

declare @name varchar(20);

set @name='Anadi';

set @sal=50000;

print @sal;

print @name;

print @name + ' earns ' + @sal;

END TRY

BEGIN CATCH

SELECT

@@ERROR as ERROR,

ERROR_NUMBER() as Errornum,

ERROR_SEVERITY() as Errorsevere,

Error_State() as Errorstste,

ERROR_PROCEDURE() as errorproc,

ERROR_LINE() as errorline,

ERROR_MESSAGE as Errormessage;

END CATCH;

end

TRANSACTIONS

-set of T-SQL statements executed together as a unit. COMMIT used to save changes. ROLLBACK to undo changes before saving. SAVEPOINT splits complete transaction in smaller parts for ROLLBACK. @@TRANCOUNT counts the number of transaction.

begin transaction

  delete from tbl_Employees where EmpId=108;

print @@trancount;

  select * from tbl_Employees;

rollback

commit

begin transaction

  save transaction s1

  delete from tbl_Employees where EmpId=108;

  save transaction s2

  delete from tbl_Employees where EmpId=109;

 rollback transaction s2

commit

commit can't be done to a particular savepoint , only rollback can be done to a particular savepoint.

Cursor

used to retrieve data from a result set one row at a time. it is used when we need to update records in a database table row by row.

@@FETCH_STATUS returns the status of last cursor fetch.

stages of cursor

Declare cursor

open

fetch

close

deallocate

begin

declare @empid int;

declare @name varchar(20);

declare @sal numeric(20);

declare empcur cursor for

select EmpId, FirstName, Salary from tbl_Employees where Department=30;

open empcur;

fetch next from empcur into @empid,@name,@sal;

WHILE @@FETCHSTATUS=0

begin

  if @sal>=50000

  begin

  set @sal=@sal+5;

  end

  else

  begin

  set @sal=@sal+10;

  end

update tbl_employees set Salary=@sal where EmpId=@empId;

print @name + 'now earns' + cast (@sal as varchar);

print @name;

print @sal;

fetch next from empcur into @empid,@name,@sal;

end

close empcur;

deallocate empcur;

end;

Stored Procedures

-database object which contains the set of logical T-SQL statements.

-increases performance of db

-reusable

-access can be controlled

-can access whenever required using EXEC or EXECUTE keyword

To create procedure

create procedure EmpSalIncrement as

begin

declare @empid int;

declare @name varchar(20);

declare @sal numeric(20);

declare empcur cursor for

select EmpId, FirstName, Salary from tbl_Employees where Department=30;

open empcur;

fetch next from empcur into @empid,@name,@sal;

WHILE @@FETCHSTATUS=0

begin

  if @sal>=50000

  begin

  set @sal=@sal+5;

  end

  else

  begin

  set @sal=@sal+10;

  end

update tbl_employees set Salary=@sal where EmpId=@empId;

print @name + 'now earns' + cast (@sal as varchar);

print @name;

print @sal;

fetch next from empcur into @empid,@name,@sal;

end

close empcur;

deallocate empcur;

end;

To alter procedure

PROCEDURE 1

alter procedure EmpSalIncrement  @deptid as int as

begin

declare @empid int;

declare @name varchar(20);

declare @sal numeric(20);

declare empcur cursor for

select EmpId, FirstName, Salary from tbl_Employees where Department=@deptid;

open empcur;

fetch next from empcur into @empid,@name,@sal;

WHILE @@FETCHSTATUS=0

begin

  if @sal>=50000

  begin

  set @sal=@sal+5;

  end

  else

  begin

  set @sal=@sal+10;

  end

update tbl_employees set Salary=@sal where EmpId=@empId;

print @name + 'now earns' + cast (@sal as varchar);

print @name;

print @sal;

fetch next from empcur into @empid,@name,@sal;

end

close empcur;

deallocate empcur;

end;

execute EmpSalIncrement 20

PROCEDURE 2

create proc SampleProc @id int, @name varchar(20) output,@sal numeric(8,2) output as

begin

  select @name=FirstName,@sal=Salary from tbl_Employees where EmpId=@id;

end;

begin

declare @empname varchar(20);

declare @salary numeric(8,2);

execute SampleProc 101, @empname output, @salary output;

print @empname;

print @salary;

end

STORED FUNCTIONS

it is a database object which contains the set of logical statements which must return a value. It can only have input parameters. It can be used in SELECT/WHERE/HAVING clause. It can't use exception handling.

create function TaxAmount(@amount numeric(10,2)) returns numeric(8,2) as

begin 

return @amount * 0.1;

end;

select EmpId, FirstName, Salary,dbo.TaxAmount(Salary) as Tax

from tbl_Employees

TRIGGER

A trigger is a special type of stored procedure that automatically runs when an event occurs in the database server. DML triggers run when a user tries to modify data through a data manipulation language (DML) event. DML events are INSERT, UPDATE, or DELETE statements on a table or view.Data definition language (DDL) triggers which fire in response to CREATE, ALTER, and DROP statements. Logon triggers which fire in response to LOGON events.

CREATE TRIGGER [schema_name.]trigger_name

ON table_name

AFTER  {[INSERT],[UPDATE],[DELETE]}

[NOT FOR REPLICATION]

AS

{sql_statements}

DML

After Trigger, Instead of Trigger

Magic Tables

Inserted, Deleted

If inserting one records, it will be there in the inserted table. Records which are getting deleted will be in deleted table.

There is no updated table. During updation, both inserted and deleted magic tables will be in action.

Create trigger EmpSalCheck on tblEmployees for update

as

begin

declare @oldsal numeric(8,2);

declare @newsal numeric(8,2);

select @oldsal=Salary from deleted;

select @newsal=Salary from inserted;

if(@oldsal>@newsal)

begin

print 'wrong';

rollback;

end;

end;

update tblEmployees set Salary=Salary+1

update tblEmployees set Salary=Salary-1

Create trigger EmpDelCheck on tblEmployees for delete

as

begin

declare @count int;

select @count=count(*) from deleted;

if @count > 1

begin

print 'Cannot delete more than one record at a time'

rollback;

end;

end;

delete from dbo.tblEmployees where EmpId in (101,118)

Instead of Trigger will be applicable on views only. If we are inserting any data on view, data will get inserted in table as well.

create view Empvu as
select e.EmpId, e.FirstName,e.Salary,d.DeptName from tblemployees e join tbl_Departments d
on e.Department=d.DeptId

select * from Empvu

create trigger trig_EmpVUInsert
on EmpVU
instead of insert as
begin

declare @empid int;

declare @name varchar(20);

declare @sal numeric(8,2);
declare @deptid int;
select @empid=EmpId,@name=FirstName,@sal=Salary,@deptid=d.DeptId from tbl_departments d join inserted
on d.DeptName=inserted.DeptName;
if @deptid is null
begin
print 'Invalid Department';
rollback;
end;
insert into tblEmployees(EmpId,FirstName,Salary,Department) values (@empid,@name,@sal,@deptid);
end;

insert into EmpVU values(121,'Ramu',30000,'Sales');
select * from EmpVU
select * from tblEmployees
select * from tbl_Departments