SQL For Me

Get Values from Multple Tables

SELECT table1.column1, table2.column2 FROM table1, table2, table3 WHERE table1.column1 = table2.column1 AND table1.column1 = table3.column1;

select dep.dep_id,emp.emp_id,salary.salary from dep,emp,salary where dep.dep_id=emp.dep_id and emp.emp_id=salary.sal_id

CASE

 A special scalar expression in SQL language is CASE expression. SQL CASE expression is used as a kind of IF-THEN-ELSE statement. It is similar to switch statement in modern programming language such as Java or C#. The syntax of the CASE statement is simple as follows :

CASE column_name  
  WHEN condition1 THEN result1  
  WHEN condition2 THEN result2  
  ...  
  ELSE result  
END  

The data type of the column_name after the CASE must be the same as the data type of the expression followed by the keyword THEN or ELSE. The ELSE part of the case expression is optional. If the ELSE part is omitted and all the conditions in the WHEN does not meet, the CASE expression will return NULL.
The case expression can be used in anywhere scalar expressions are allowed, including in WHERE and HAVING clause of the select statement.

Transaction

Mark the end of a successful implicit or explicit transaction.

If @@TRANCOUNT is greater than 1, the transaction will stay active. Each COMMIT TRANSACTION decrements @@TRANCOUNT by 1 until it reaches 1.

When @@TRANCOUNT is 1, COMMIT TRANSACTION makes all data modifications performed since the start of the transaction a permanent part of the database, frees the resources held by the transaction, and decrements @@TRANCOUNT to 0.

Placing COMMIT TRANSACTION or COMMIT WORK statements in a trigger is not recommended.

Example

begin transaction

begin

      select * from dep

      select * from emp

            begin transaction

                  select * from emp

                  Select @@trancount

            commit transaction

end

commit transaction

Select @@trancount

Output:

begin transaction

begin

      update dep set depname='IT'

            if @@rowcount>2

              begin

                rollback transaction

                raiserror('Not Allowed',16,2)

                return

              end

end

commit transaction