SQL Server : T-SQL Basics

                                                                                T-SQL

                                                               

T-SQL  programming is a procedural language extension to SQL which is known as PL/SQL in Oracle.

It extends SQL by adding programming structures.

It has syntax and rules that determine how programming statements work together.

We can controle the program flow by using conditional statements like IF and While Loop.

T-SQL provide try catch mechanism to handle runtime errors.

Re-usability of code is available by defining objects such as procedures and functions.

SQL commands can be embedded inside the programs.

Programming blocks can be of 2 types

Anonymous Block:

These blocks will not be stored permanently in the database i.e once we logoff from the database then these blocks are completely destroyed. Here block means a group of code or set of instructions, these blocks doesn't contain any name that's y these blocks can be called as un-named blocks.

Named Block:

These blocks can be called as subprograms which can be stored permanently in the database as an object, these blocks having some name so these blocks can be Called as named blocks.

Data types:

                Data type identifies what type of data we are going to store in our column of a table. The data types which are using in SQL same data types are supported by T-SQL.

Variables:

* A variable is a temp memory area where we can maintain some data.

Local Variables:

* These are declared and used in a particular batch.

Global Variables:

These are predefined variables.

                * We can use in any batch.

                                Ex: @@servername

                                      @@identity

                                      @@error  (0 for no errors)

                                      @@rowcount ( No of rows effected by the recently executed st)

                                      @@fetch_status   -- Cursors

Declaring Variable:

                While declaring a variable it has to be preceded with @symbol

Ex:

                Declare @variable as datatype ....

Declare @x int

Declare @sal as money

Declare @ename varchar(20),@job varchar(40)

Assigning Values to variables: Values can be assigned by using a SET statement

                 Syn: set @variable=value

Ex:          set @X=10

                set @ename='scott'

Printing Values:

If we want to print the values we can use the Print statement

            Print @Variable

Print @x

Print @ename

Comments in T-SQL:

1. Single Line Comment (--)

2. Multi Line Comments (/*---------------*/)

WAP to find the sum of 2 numbers?

           DECLARE @a INT

      ,@b INT
      ,@c INT

          SET @a = 5
          SET @b = 5
          SET @c = @a + @b

PRINT 'The sum of a&b is:' + Convert(VARCHAR @c)

WAP to find largest of 2 numbers?

        DECLARE @a INT
 ,@b INT

        SET @a = 10
        SET @b = 6

       IF (@a > @b)
 PRINT ' B is big'
     ELSE
 PRINT 'A is big'

WAP to display wheter rows are there or not in emp table?

     DECLARE @a INT

     SELECT @a = count(*)
     FROM emp

   IF (@a > 0)
 PRINT 'Table will have records'
   ELSE
 PRINT 'No records in the table'

WAP to find the first 10 n's using while?

     DECLARE @n INT

     SET @n = 1

     WHILE (@n <= 10)
     BEGIN
 PRINT @n

 SET @n = @n + 1
     END

WAP to print all even numbers from 1 to 10

     DECLARE @n INT

     SET @n = 1

    WHILE (@n <= 10)

    BEGIN
 IF (n % 2 = 0)
  PRINT @n

 SET 2 n = @n + 1
    END