SQL

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

SQL CONSTRAINTS

Constraints:- Used to enforce data integrity ie.. to  implement certain rules/restrictions on

database
so as to maintain quality data.
DataIntegrity is achived by using following constraints.
Data Integrity follows under Four Categories :

--1-Entity Integrity
Entity integrity defines a row as a unique entity for a particular table
[UNIQUE CONSTRAINT,  PRIMARY KEY CONSTRAINT]

--2-Referential Integrity
Referential integrity ensures that key values are consistent across tables.
through FOREIGN KEY and CHECK constraints


--3-Domain Integrity
Domain integrity is the validity of entries for a given column.
You can enforce domain integrity by restricting
the type (through data types),
the format (through CHECK constraints and rules),
the range of possible values (through FOREIGN KEY constraints, CHECK constraints,

DEFAULT definitions, NOT NULL definitions, and rules).

--4-User Defined Integrity


There are 5 types of constraints
NOT NULL CONSTRAINT
UNIQUE CONSTRAINT
CHECK CONSTRAINT
PRIMARY KEY CONSTRAINT
FOREIGN KEY CONSTRAINT

Constraints can be applied on table level and column level.

select * from employee
select * from Department
 

alter table employee add constraint pk_emp_empId primary key(EmpId)
alter table Department add constraint fk_dep_EmpId  foreign key(EmpId) references

employee(empid)

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

--On a Broad classification, there are two types of joins.
--
--1-Inner Join
--2-Outer Join
--
--Inner Join is again classified into three types of joins
--
--Equi Join
--Natural Join
--Cross Join

1-------INNER JOIN

--Equi-Join or Inner Equi-Join
Equi join  uses only equality comparisons in the join-predicate.
Using of any comparisions other than equality '=' disqualifies a Equijoin

select * from Employee e inner join Department d  on e.DepartmentNo = d.DepartmentNo
--where e.EmpId=5    Can be further filterd with a where condition

-- Natural Join or Inner Natural Join
Natural Join is same that of Equi Join but the resultant table give only one Identical Column

where as Equi JOin
resultant table consist of two identical columns(one from one table and one from another table)

select e.EmpId,e.DepartmentNo,e.EmpName,d.Category  from Employee e inner join

Department d
on e.DepartmentNo = d.DepartmentNo
--where e.EmpId=5  Can be further filterd with a where condition

-- Cross Join
--This Corss Join produces the cross product of two or more tables.<no Filtration with On or

where Clauses>
Employee= 6 records
DepartMent= 4 records
CrossJoin yields 6*4 = 24 records with the columns of Employee and Department tables

select * from Employee
select * from Department
select * from Employee e cross Join Department d where e.empId=5
--Cross Join cannot be used with ON CLAUSE only Where clause is acceptable
or
select * from Employee , Department



-------OUTER JOIN

--Left Outer Join
--Right Outer Join
--Full Outer Join

--Left Outer Join
A Left Outer Join retrives
all the rows
that satisfies the on condition
plus
Unmatched rows 'from' the left-hand side table.
select * from employee
select * from department

select e.empName as EmployeeName,d.Category as Category from employee e
left outer join department d on e.DepartmentNo =d.DepartmentNo

--where  Category='A-Category'  can be used with where clause for further filtration


--Right Outer JOin

A right Outer Join retrives all the rows that matches the on condition
plus
Unmatched rows 'from' the right hand side table

select * from employee
select * from department
select e.empName as [Employee Name],d.Category as Category,e.departmentNo  from

employee e
left outer join department d on e.DepartmentNo =d.DepartmentNo

select e.empName as [Employee Name],d.Category as Category,d.departmentNo from

employee e
Right outer join department d on e.DepartmentNo =d.DepartmentNo


--FullJoin
Full Join retrives all the records that matches the on clause condition and unmatched rows from

both the tables

select e.empName as [Employee Name],d.Category as Category,d.departmentNo from

employee e
Full outer join department d on e.DepartmentNo =d.DepartmentNo
--where d.departmentNo=2 can be used with where condition for further filtration

=============
--Cursor:-
--What is Cursor?
Cursor is a database object used by applications to manipulate data in a set on a row-by-row

basis

--Use of cursor:-
 A cursor is used to manage attributes of data returned from a data source (a result set).
 

select * from Employee

eg:

declare empCursor cursor -- FAST_FORWARD
for select * from employee
declare @EmpId  int,@EmpName varchar(50),@EmpDepartment varchar(50),@DepartmentNo

int
open empCursor
fetch next from empCursor into @EmpId,@EmpName,@EmpDepartment,@DepartmentNo
while @@fetch_status=0
begin
print 'EmpId:- ' +cast(@EmpId as varchar(30)) +' @EmpName:- '+@EmpName+'

@EmpDepartment:- '+@EmpDepartment

fetch next from empCursor into @EmpId,@EmpName,@EmpDepartment,@DepartmentNo
end
close empCursor
deallocate empCursor

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

--Triggers (DML on tables,views :DDL on database and server)
create table emp
(empNo int primary key,
empName varchar(50),
Adress varchar(100),
emlSal money)

select * from emp
insert into emp values ( 001,'raju','hyd',5000)

create trigger empTrigger
on emp
for insert
as


begin
 update emp set empName='manoj'
 end

go

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

Sql functions

=============================
'String Functions'
'LEFT,LTRIM,RTRIM'
declare @sqlVariable1 as varchar(50)
set @sqlVariable1 =' manojreddy '
set @sqlVariable1=ltrim(rtrim(@sqlVariable1))
print left(@sqlVariable1 ,5)
print right(@sqlVariable1 ,5)

'SUBSTRING'
SYNTAX:-SUBSTRING(string, starting_character_number, number_of_characters_to_return)

declare @sqlVariable1 as varchar(50)
SET @sqlVariable1='SQLSERVER2005'
print SUBSTRING(@sqlVariable1,1,3)

'REVERSE Function'
declare @sqlVariable1 as varchar(50)
SET @sqlVariable1='SQLSERVER2005'
print REVERSE(@sqlVariable1)

'CHARINDEX  RETURNS THE POSITION OF THE CHARACTER WHICH WE

MENTION AS THE SEARCH VALUE'
SYNTAX:-CHARINDEX(search value, string, starting search location)

declare @sqlVariable1 as varchar(50)
SET @sqlVariable1='SQL,SERVER,2005,'
PRINT

CHARINDEX(',',@sqlVariable1,CHARINDEX(',',@sqlVariable1,CHARINDEX(',',@sqlVariabl

e1,2)+1)+1)
PRINT CHARINDEX('SERVER',@sqlVariable1) 'THIS RETURNS THE 'S'POSITION IN

@sqlVariable1'

'REPLACE '
declare @sqlVariable1 as varchar(50)
SET @sqlVariable1='SQL,SERVER,2005,'
SET @sqlVariable1 =REPLACE (@sqlVariable1,',',' ')
PRINT @sqlVariable1

'STUFF Function
The STUFF function inserts a set of characters into a given string at a given position. The

syntax is:
view sourceprint?1.STUFF(string to manipulate, starting position, length, characters to insert'

declare @sqlVariable1 as varchar(50)
SET @sqlVariable1='SQL,SERVER,2005,'
PRINT STUFF (@sqlVariable1,2,10,'EQUENTIAL')

'REPLICATE FUNCTION'
PRINT REPLICATE('MANOJ ',5000)

declare @sqlVariable1 as varchar(50)
SET @sqlVariable1='SQL,SERVER,2005,'
PRINT REPLACE(@sqlVariable1,',',(SPACE(2)))

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

'system functions'
CASE

SELECT * FROM EMPLOYEE

SELECT DISTINCT EMPiD,eMPnAME = CASE
WHEN EMPNAME LIKE '%AN%' THEN 'unlimited'
WHEN EMPNAME LIKE '%RA%' THEN '100K to 250K'
WHEN EMPNAME LIKE '%NG%' THEN '20K to 40K'
WHEN EMPNAME LIKE '%VA%' THEN '50K to 65K'
ELSE 'unknown'
END
FROM   
EMPLOYEE
update employee set empDepartment = null where departmentNo=2

select * from employee

 select coalesce(empDepartment,'') from employee
select isnull(empDepartment,'') from employee


select coalesce(empDepartment,'nothing') from employee

----NULLIF
select nullif(empDepartment,'Electrical') from employee

-----------------
'CAST AND  CONVERT FUNCTIONS'
DECLARE @TEMP  AS DATETIME
SET @TEMP=GETDATE()
PRINT @TEMP
PRINT CAST(@TEMP AS VARCHAR(11))
PRINT CONVERT(VARCHAR(20),@TEMP,105) 'DD-MM-YYYY'
PRINT CONVERT(VARCHAR(20),@TEMP,104) 'DD.MM.YYYY'
PRINT CONVERT(VARCHAR(20),@TEMP,103) 'DD/MM/YYYY'
PRINT CONVERT(VARCHAR(20),@TEMP,102) 'YYYY-MM-DD'
PRINT CONVERT(VARCHAR(20),@TEMP,101) 'MM/DD/YYYY'

'@@IDENTITY'
SELECT IDENT_CURRENT(EMPid) FROM EMPLOYEE


=======

'Functions'
Syntax

CREATE FUNCTION (
[=DEFAULT],…
RETURNS [WITH ENCRYPTION]
AS
BEGIN

RETURN
END

example 1:

ALTER function Fn_EmpDept (@empno INT) Returns varchar(40)
As
Begin
Declare @emName varchar(20), @empDept varchar(20) ,@empName_Dept varchar(40)
Select @emName = EmpName, @empDept =EmpDepartment from employee where

empId=@empno
set @empName_Dept =  isnull(@emName,'')+'_'+ isnull(@empDept,'unAssigned') 
Return @empName_Dept
End

'calling the above function'
select dbo.Fn_EmpDept(1)


Select EmpName, EmpDepartment from employee where empId=2
select isnull(empName,'')+ isnull(empDepartment,'unAssigned') as name,EMPiD from employee
set @empName_Dept = isnull(@emName,'')+ isnull(@empDept,'unAssigned')


EXAMPLE 2:-
'CREATING A FUCNTION THAT RETURNS A TABLES'
CREATE FUNCTION FN_RETURNTABLE(@EMPID INT) RETURNS TABLE
AS
RETURN SELECT * FROM EMPLOYEE WHERE EMPID=@EMPID

'CALLING A FUNCTION THAT RETURNS TABLE'
SELECT * FROM FN_RETURNTABLE(2)

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

'Working with View'
<-- A view is a virtual table that consists of columns from one or more tables.
<-- Though it is similar to a table, it is stored in the database.
<-- It is a query stored as an object.
<-- Hence, a view is an object that derives its data from one or more tables.
<--These tables are referred to as base or underlying tables.
<--Once you have defined a view, you can reference it like any other table in a database.

'Syntax for creating a view'
Create view View_Name
as
select * from  country

select * from view_name

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

'Sub Queries and Corelated sub queries'
_______________________________________

SUB QUERY:

'Here the output of the inner query  is being fed as input to outer query for furthe processing'
select distinct * from employee where DepartmentNo in ( Select DepartmentNo from

Department where Category like 'A%')

CORELATED SUBQUERY:(doubt)
'Here for each record of outer query executes with inner query statment'
 select * from employee e where 2=(select count(EmpName) from employee where

employee.EmpName in('Manoj','Ranga'))