PL/SQL(Oracle) Vs Transact-SQL(SQL Server)

This article presents to you a very easy way to understand PL/SQL as well as TSQL with the help of a questionnaire. 
The unique thing about it is that it keeps on comparing both of these database languages throughout the article. I am sure at the end, besides learning each of these languages, you will get a fair idea about where all they differ.

1. What are the basic DataTypes used in these languages?
PL/SQL: 
Character: 
char(size), nchar(size), varchar2(size) and nvarchar2(size)
Here "size" is the length of the variable.
Numeric: 
number(p, s), numeric(p, s), float, dec(p, s), decimal(p, s), integer, int, smallint and real
Here p: precision
         s: scale
that means number of digits before decimal = (p - s), and
number of digits after decimal = s.
Date/Time:   
Date and Timestamp.

T-SQL
Character: 
char(size), nchar(size), varchar(size) and nvarchar(size)
Here "size" is the length of the variable.
The default value of size is 1.
Numeric: 
smallint, int, bigint, decimal, tinyint, bit, money, float and real. 
Date/Time:   
date, datetimeoffset, datetime2, smalldatetime, timestamp and time.
Other data Types: 
table, xml and cursor.

2. What is the difference between Primary Key and Unique Key?

3. Difference between Delete and Truncate?
Truncate and Delete commands are used for removing rows from a table. They are very similar, still there are few basic differences in them.

	Truncate	Delete
Selectively deletes Records from Table? (ex. DELETE FROM Employee WHERE EmployeeId = 4)	No	Yes
DDL or DML?	DDL	DML
Retains constraints, indexes etc?	Yes	No
Activates associated Triggers?	No	Yes

5. What are Packages and what is their significance?

6. How we write a Procedure and Function in each of these languages?
We are demonstrating it with a simple and generic example. Please note that the differences are highlighted.

PL/SQL:
Procedure:

CREATE OR REPLACE PROCEDURE Get_EmpList(
                                               DId IN Department.Department_Id%TYPE,
                                               OutPutCursor OUT SYS_REFCURSOR)
IS
BEGIN
             SELECT Emp_Name 
             FROM Employee
             WHERE Department_ID  = DId;
END

Function:

CREATE OR REPLACE FUNCTION Get_EmpName(
                                              EId IN Employee.Employee_Id%Type)
RETURN Employee.Emp_Name%Type
IS
BEGIN
               RETURN SELECT  Emp_Name 
                                 FROM     Employee
                                 WHERE  Employee_Id = EId;
END

T-SQL
Procedure: 

CREATE PROCEDURE Get_EmpList
                                       @Did int
AS 
BEGIN
             SELECT Emp_Name 
             FROM Employee
             WHERE Department_ID  = @DId;
END

Function:

CREATE FUNCTION Get_EmpName
                             (@EId int)
RETURNS Varchar(50)
AS
BEGIN
           RETURN SELECT  Emp_Name 
                                 FROM     Employee
                                 WHERE  Employee_Id = @EId;
END

7. What are %TYPE and %ROWTYPE in PL/SQL? Do we have similar thing in TSQL?
%TYPE and %ROWTYPE are only there in the PL/SQL.
%TYPE is used to give the type of a table column. So every time you are not supposed to 
write the variables as int, varchar etc. You can make use of %TYPE as it's used in the
example above.    
%ROWTYPE is used to give the Type of a row.

6. What is Cursor and why we need them? How many types of Cursor are there?

7. What is the significance of "Commit" in each of these languages?

8. What are Triggers and how many types they have?

9. How does Table variables and Temp table differ?

10. What is a View?

11. What are Joins?
 Refer: http://akshaysnotion.blogspot.in/2012/06/sql-joins.html

12. What is a Correlated Subquery?
http://msdn.microsoft.com/en-us/library/ms187638(v=sql.105).aspx

13. How to deal with NULL in both these languages?
 PL/SQL: We use "NVL", "IS NULL" and "IS NOT NULL" to deal with NULL values.
"NVL" is used to replace NULL values with the value you need where ever required in the   
query.
See the below example:
          Select EmpName, NVL(Address, 'GURGAON') from Employee
 IS NULL/IS NOT NULL are used to check for NULL values
 See the below example:
          Select EmpName from Employee Where Address IS NULL 

 T-SQL: Similar to PL/SQL we use IS NULL and IS NOT NULL here (Remember that we can  
 never use "=NULL" because the NULL value is not equal to any other value)
 As a replacement of "NVL" we have "ISNULL" function.
 See the below example:
           Select EmpName, ISNULL(Address, 'GURGAON') from Employee

14. Explain the concept of Indexes in each of these languages?