What is the difference between UNION and UNION ALL?

UNION eliminates duplicate records whereas UNION ALL retains duplicates.

What is the difference between a full outer join and a cross join?

For sets of A and B rows, a cross join (aka Cartesian join) fetches a result of A * B rows whereas a full outer join pulls a return of at most A + B rows.

How could you retrieve the manager's name of an employee within the EMPLOYEE table?

SELECT E.EMP_ID   AS EMPLOYEE_ID,
       E.EMP_NAME AS EMPLOYEE_NM,
       M.EMP_NAME AS MNGR_NM
FROM   EMP_MNGR E
       LEFT OUTER JOIN EMP_MNGR M
                    ON E.MNGR_ID = M.EMP_ID
ORDER  BY E.EMP_ID;

And the EMPLOYEE table goes as below :
EMP_ID    EMP_NAME    MNGR_ID
1001    Roger Scott   
2500    Mike Larsen    1001
2538    Marry Gartner  2500
2567    David Rice     2538
2570    Ben Tenesion   2567
2590    Joseph Robert  2570
3000    Adam McNally   1001

How could you find out all the table names having a specific column name in SQL?

SELECT COLUMN_NAME, TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%<Column Name>%'

or

SELECT SO.NAME, SC.NAME
FROM SYSOBJECTS SO INNER JOIN SYSCOLUMNS SC
ON SO.ID = SC.ID
WHERE SC.NAME = '<Column Name>'

What is the difference between "IN" and "BETWEEN"?

"BETWEEN" requires a range of values whereas "IN" demands a list of values to operate on.

How could you delete duplicate rows?

DELETE FROM <Table Name>
WHERE rowid not in ( SELECT MIN (rowid)
FROM <Table Name>
GROUP BY column1, column2, column3, ... );

What is the generic or full syntax of SELECT statement?

SELECT select_list
[ INTO new_table ]
FROM table_source
[ WHERE search_condition ]
[ GROUP BY group_by_expression ]
[ HAVING search_condition ]
[ ORDER BY order_expression [ ASC | DESC ] ]

What is the difference between Clustered and Non-Clustered Index?

Clustered Index
 - describes the order wherein the records are physically stored
 - only one per table
 - faster to read as data is physically stored in index order
 - to enhance insert & update, clustered indexes should be set on a field that is normally incremental i.e. Id or Timestamp

Non Clustered Index
 - defines a logical order that does not match the physical order on disk
 - can be used many times per table
 - quicker for insert and update operations than a clustered index

How do you find nth highest salary from an employee table?

SELECT Min (emp_sal)
FROM   employee
WHERE  emp_sal IN (SELECT DISTINCT TOP n emp_sal
                   FROM   employee
                   ORDER  BY emp_sal DESC)