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

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.

-- Returns the rows those have been inserted.
INSERT Table1
OUTPUT INSERTED.*
VALUES(104, 'JJJJ', 'female')

2. OUTPUT clause with DELETE statement.

-- Returns the rows that have been deleted.
DELETE FROM Table1
OUTPUT DELETED.*
WHERE name = 'aaa'

3. OUTPUT clause with UPDATE statement.

-- Returns the rows those have been updated.
UPDATE Table1
SET Sex = 'male'
OUTPUT INSERTED.*
WHERE (Sex = 'female’)

4. 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)

5. Emptying all columns except one(here except ID column all columns get emptied)

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)

SELECT CASE 3                                           -- use labels as SELECT CASE @count
            WHEN 1 THEN 'Active'
            WHEN 2 THEN 'Inactive'
            WHEN 3 THEN 'Pending'
            ELSE 'Unknown'
         END

2. Queries as conditional_expression. (Q: Find the grade of ‘aaa’ student)

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

3. Using built-in FUNCTIONS in conditional_expression

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.......)