SQL Notes
Trigger
24/05/2011 13:36
CREATE TABLE StuDetails
(
Rollno int,
StuName varchar(100),
Address varchar(300),
City ...
Cursor
24/05/2011 13:36
Steps:-
1. Declare the Cursor
2. Open the Cursor
3. Fetch the Cursor
4. Close the cursor
5. Deallocate the cursor
DECLARE S_Cursor CURSOR FOR
SELECT * FROM TblEmpMaster
OPEN S_Cursor
FETCH NEXT FROM S_Cursor
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM S_Cursor ...
Groupby in SQL
24/05/2011 13:35
--Aggregate Functions
CREATE TABLE salary
(
empno varchar(10),
Dept varchar(50),
salary ...
Backup_Restore in SQL
24/05/2011 13:34
--Backup and Restore
--Backup
Backup Database DataBaseName to Disk='Path':
Backup database shan to disk='D:\shan2.BAK'
drop database shan
use shan
--Restore
Restore Database DataBaseName from Disk='Path'
Restore Database MTT from disk='D:\31.03.2006.BAK'
Default in SQL
24/05/2011 13:33
CREATE TABLE Ex_Default
(
EmpID int,
EmpName varchar(100) DEFAULT 'Shankar', --Preferred default definition
DOB ...
Send Email in SQL
24/05/2011 13:33
sp_send_dbmail [ [ @profile_name = ] 'profile_name' ]
[ , [ @recipients = ] 'recipients [ ; ...n ]' ]
[ , [ @copy_recipients = ] 'copy_recipient [ ; ...n ]' ]
[ , [ @blind_copy_recipients = ] 'blind_copy_recipient [ ; ...n ]'...
Constraint Validation in SQL
24/05/2011 13:32
CREATE TABLE Ex_Check
(
cust_id int CONSTRAINT CN_PKEY PRIMARY KEY,
cust_name char(50),
...
Views in SQL
24/05/2011 13:31
-- Views
view is a logical table.
select *
from dbo.TblEmpMaster
create view v_TblEmpMaster as
select * from dbo.TblEmpMaster
alter view v_TblEmpMaster as
select EmpID from dbo.TblEmpMaster
create view v_TblEmpMasterCn ...
Rule Validation in SQL
24/05/2011 13:31
CREATE RULE id_chk AS @id BETWEEN 0 and 10000;
GO
CREATE RULE Address_chk AS @Address in ('1000','2000','3000')
GO
CREATE TABLE cust_sample
(
cust_id int PRIMARY KEY,
...
Stored Procedure
24/05/2011 13:30
CREATE PROCEDURE [dbo].[INS_Emp_Master]
AS
BEGIN
SELECT *
FROM dbo.TblEmpMaster with(nolock)
END
GO
ALTER PROCEDURE [dbo].[INS_Emp_Master]
(
...