1. What is the command used to fetch first 5 characters of the string?
There are many ways to fetch first 5 characters of the string -
a) Select SUBSTRING(StudentName,1,5) as studentname from student
b) Select RIGHT(Studentname,5) as studentname from student
2. How to create a Foreign key
a). CREATE TABLE Orders (
OrderID int NOT NULL PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
b). ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
c) ALTER TABLE Orders ADD CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
3. Handle Exception in SQL
BEGIN TRY
DECLARE @Mynum INT
---- Divide by zero Error
SET @Mynum = 10/0
PRINT 'This will not execute'
END TRY
BEGIN CATCH
SELECT @@Error, @@ROWCOUNT
SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_SEVERITY() AS ErrorSeverity, ERROR_STATE() AS ErrorState, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage;
END CATCH;
GO
4. RaiseError()
The RAISERROR statement allows you to generate your own error messages and return these messages back to the application using the same format as a system error or warning message generated by SQL Server Database Engine.
Syntax : RAISERROR ( { message_id | message_text | @local_variable }
{ ,severity ,state }
[ ,argument [ ,...n ] ] )
[ WITH option [ ,...n ] ];
<br/>
5. SQL Transactions
BEGIN TRANSACTION T1
INSERT INTO Customer VALUES (10, 'Code_10', 'Ramesh')
INSERT INTO Customer VALUES (11, 'Code_11', 'Suresh')
BEGIN TRANSACTION T2
INSERT INTO Customer VALUES (12, 'Code_12', 'Priyanka')
INSERT INTO Customer VALUES (13, 'Code_13', 'Preety')
PRINT @@TRANCOUNT --** Here TRANCOUNT value 2**
COMMIT TRANSACTION T2 -- This does not physically commit
PRINT @@TRANCOUNT -- **Here TRANCOUNT value 1**
COMMIT TRANSACTION T1 -- This does a physically commit
PRINT @@TRANCOUNT --** Here TRANCOUNT value 0 **