SQL Easy Learning

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...

Datepart (part,date), 2. Dateadd (datepart,date,number) 3. Datediff (datepart,start date,End date) SELECT GETDATE() 'Today' --0->1-1-1900 SELECT DATEADD(d,-1,GETDATE()) 'Yesterday' SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),0) 'First Day of Current Week' SELECT DATEADD(wk,DATEDIFF(wk,0,GETDATE()),6) 'Last Day of Current Week' SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),0) 'First Day of Last Week' SELECT DATEADD(wk,DATEDIFF(wk,7,GETDATE()),6) 'Last Day of Last Week' SELECT DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0) 'First Day of Current Month' SELECT DATEADD(ms,- 3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE())+1,0))) 'Last Day of Current Month' SELECT DATEADD(mm,-1,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0)) 'First Day of Last Month' SELECT DATEADD(ms,-3,DATEADD(mm,0,DATEADD(mm,DATEDIFF(mm,0,GETDATE()),0))) 'Last Day of Last Month' SELECT DATEADD(yy,DATEDIFF(yy,0,GETDATE()),0) 'First D...

--Make sure the database is using the simple recovery model. ALTER DATABASE ten SET RECOVERY SIMPLE ; GO -- Back up the full AdventureWorks2008R2 database. BACKUP DATABASE AdventureWorks2008R2 TO DISK = 'D:\Websites\DB Backup\Ten\ AdventureWorks2008R2.bak' WITH FORMAT ; GO --Create a differential database backup. BACKUP DATABASE AdventureWorks2008R2 TO DISK = 'D:\Websites\DB Backup\Ten\ AdventureWorks2008R2.bak'    WITH DIFFERENTIAL ; GO --Restore the full database backup (from backup set 1). RESTORE DATABASE TEN FROM DISK = 'D:\Websites\DB Backup\Tenx\65838_ Ten_25APR2013_23_04_54_ FULL.bak'     WITH FILE = 1 , NORECOVERY ; --Restore the differential backup (from backup set 2). RESTORE DATABASE TEN FROM DISK = 'D:\Websites\DB Backup\Tenx\65926_ Ten_26APR2013_10_42_13_ DIFF.bak'     WITH FILE = 1 , RECOVERY ; GO