Triggers

Create Trigger Name on Table
for insert as instead of insert
Begin
End
View: It is a virtual table

How many triggers are possible per table?
One Instead Of Trigger and any After Triggers.

1. How many a's in a given string

select LEN('vinoth')-Len(REPLACE('Vinoth','a',''))

2. Count of mails in a table:

select Mail,Count (Mail ) Count from (select right(EmailId,Len(EmailId)-
charindex('@',EmailId)) Mail from UserPersonalDetails) a group by
Mail;

3. Update M to F & F to M in single statement

update Emp set Gender= case when gender='M' then gender='F'
when gender='F' then gender='M'
else
gender
end

4. Print 1 to 100 with out loop
with a as
(select count=1
union all
select COUNT=Count+1 from a where COUNT<100)
select * from a

5. Delete Duplicate records

a. Table have identity column:
delete from Emp where ID in (Select Max(ID) from Emp group by
Name, Gender having Count(ID)>1)

b. Table don't have Identity column:
with TEMP as
(select ROW_NUMBER() over( order by ID) nId,ID,Name, Gender from
EMP)
delete from TEMP where nID in (Select Max(NID)from TEMP group by
ID,Name, Gender having Count(NID)>1)

6. Select Highest sal emp from Each Dept

Select E.DEPTNO,E.EMPNO, E.ENAME, A.MAXSAL FROM
EMP E INNER JOIN (select DEPTNO,MAX(Sal) Maxsal from
Emp group by DEPTNO) A ON E.SAL=A.Maxsal AND
E.DEPTNO=A.DEPTNO

7. Normalization

1NF-> Multiple same columns to same Column
2NF-> Divide table into 2 tables based on duplicates
3NF-> Divide table into multiple tables

8. Find 4th Highest sal from Emp table:

Select * from
(select Dense_RANK() over( order by salary desc) DR, * from
Employee) a where DR=4

9. select FirstName,Salary from Employee where Salary in
( select Min(Salary) sal from (select distinct top 4 Salary from Employee
order by Salary Desc) a