SqlTutorial-1 : Lesson-4 : Class-1
Microsoft SQL Server Training:
Free Online-Learning Classes (OLC) for T-SQL
Advanced-DML:
Top(n) function, OUTPUT-Caluse, CASE-Clause

Microsoft SQL Server Training Online Learning Classes Advance DML Top-n OUTPUT CASE


INDEX
  1. Top(n) function
  2. OUTPUT - Clause
  3. CASE - Statement


1. Top(n) Function


The TOP(n)/TOP n clause retrieves first n rows of the result set.

(a) TOP(N) + SELECT

  1. You can select all rows using * with TOP clause

    SELECT TOP (3) * FROM Employee
  2. To select first 8 high budgets department.

    SELECT TOP 8 Dept_name
    FROM department
    ORDER BY budget DESC
  3. To get second Maximum salary

    SELECT  Top 1 Salary as 'Second Maximum'
    FROM (SELECT TOP (2) salary
          FROM Employee
          ORDER BY salary DESC)
    ORDER BY salary ASC

      ORDER BY clause of subquery and main query can be interchange.

(b) TOP(N) + UPDATE:

  1. To update salary of first employee.

    UPDATE TOP(1) Employee
    SET salary = salary + 30
  2. To update Maximum salaried employee.

    UPDATE Employee
    SET Salary = Salary + 30
    WHERE Salary IN (SELECT TOP (3) Salary
                 FROM Employee
                 ORDER BY Salary desc))

(c) TOP(N) + DELETE:

  1. To delete first employee.

    DELETE TOP (1)
    FROM Employee
  2. To delete maximum salaried employee.

    DELETE TOP(1)
    FROM Employee
    WHERE Salary IN (SELECT TOP (1) Salary
         FROM Employee
         ORDER BY Salary desc))

    TOP (N) + INSERT is not possible.


2. OUTPUT-Clause:
    To return effected rows by DML statements.

  • OUTPUT clause is extension to INSERT, DELETE and UPDATE commands.
  • It returns rows information those affected by INSERT,UPDATE or DELETE statement.
  • Its result you can store into variables or other tables for processing.
  • For INSERT and UPDATE operation use OUTPUT INSERTED.*
  • For DELETION operation use OUTPUT DELETED.*
  • is used to select all columns, if you want to select particular column then OUTPUT DELETED.Col1,

Syntax:

     [ OUTPUT {DELETED | INSERTED }.{* | column_name} 
     [ INTO { @table_variable | Table_itself } ]

Examples:

  1. OUTPUT clause with INSERT statement.

  2. -- Returns the rows those have been inserted.
    INSERT Table1
    OUTPUT INSERTED.*
    VALUES(104, 'JJJJ', 'female')
  3. OUTPUT clause with DELETE statement.

  4. -- Returns the rows that have been deleted.
    DELETE FROM Table1
    OUTPUT DELETED.*
    WHERE name = 'aaa'
  5. OUTPUT clause with UPDATE statement.

  6. -- Returns the rows those have been updated.
    UPDATE Table1
    SET Sex = 'male'
    OUTPUT INSERTED.*
    WHERE (Sex = 'female’)
  7. MOVE records from table1 to table2(same columns must be there)

    DELETE FROM Table1
    OUTPUT DELETED.*
    INTO Table2
    // OR //
    DELETE FROM Table1
    OUTPUT DELETED.id, DELETED.name, DELETED.sex
     INTO Table2(id,name,sex)
  8. Emptying all columns except one(here except ID column all columns get emptied)

  9. DELETE FROM Table2
    OUTPUT DELETED.id
    INTO Table2(id)


3. CASE Clause with DML:
    For conditional SEL-INS-DEL-UPDATE


Syntax 1:

For labels (similar to switch and based on only equality condition)

CASE input_expression
           WHEN label1 THEN result_expression
           [WHEN ...n ]
           [ELSE else_result_expression] 
END

Syntax 2:

Searched CASE function (similar to MULTIPLE IF and based on < & > = )

CASE
       WHEN conditional_expression_1 THEN result_expression_1
       [WHEN conditional_expression_2 THEN result_expression_2]...
       [ELSE else_result_expression]
END

A - Examples CASE with SELECT statement.

  1. Labels as conditional_expression (Syntax1 example)

  2. SELECT CASE 3                                           -- use labels as SELECT CASE @count
                WHEN 1 THEN 'Active'
                WHEN 2 THEN 'Inactive'
                WHEN 3 THEN 'Pending'
                ELSE 'Unknown'
             END
  3. Queries as conditional_expression. (Q: Find the grade of ‘aaa’ student)

  4. SELECT CASE
        WHEN ((SELECT     m1 + m2 + m3
                 FROM Student
                 WHERE name = 'aaa') / 3 BETWEEN 30 AND 45)
          THEN 'Third Class'
        WHEN ((SELECT      m1 + m2 + m3
                 FROM Student
                 WHERE name = 'aaa') / 3 BETWEEN 46 AND 59)
          THEN 'Second Class'
        WHEN ((SELECT      m1 + m2 + m3
                 FROM Student
                 WHERE name = 'aaa') / 3 > 60) THEN 'First Class'
        ELSE 'Unknown'
    END AS Grade
  5. Using built-in FUNCTIONS in conditional_expression

  6. SELECT  BillingNumber, BillingDate, BillingDueDate,
       CASE
            WHEN DATEDIFF(day, BillingDueDate, GETDATE()) > 30
                     THEN 'Over 30 days past due'
            WHEN DATEDIFF(day, BillingDueDate, GETDATE()) > 0
                      THEN '1 to 30 days past due'
            ELSE 'Current'
        END AS Status
    FROM Billings

B - Examples CASE with UPDATE

Interchange male & female for SEX column)

UPDATE table1
              SET Sex=CASE WHEN(sex='male') THEN 'female'
                                         WHEN(sex='female') THEN 'male'
                                         ELSE 'UNKNOWN'
END

C - Examples CASE with INSERT.

INSERT INTO table_Name(COL1,COL2)
Values(CASE1..........., CASE2................)

D - Examples CASE with DELETE

DELETE table_name
WHERE col1=(SELECT CASE statement.......)

1 comment: