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