Brush up your knowledge on SQL

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

fullstackdeveloper007/SQLQueries

ErrorLooks like something went wrong!

About

Resources

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Languages

Error
Looks like something went wrong!

Packages