1) What is the difference between inner and outer join? Explain with example.
- Inner join returns rows when there is at least one match in both tables
- Outer join will return matching rows from both tables as well as any unmatched rows from one or both the tables
2) What is the difference between JOIN and UNION?
- SQL JOIN allows us to “lookup” records on other table based on the given conditions between two tables.
- UNION operation allows us to add 2 similar data sets to create resulting data set that contains all the data from the source data sets. Union does not require any condition for joining.
3) What is the difference between UNION and UNION ALL?
- UNION operation returns only the unique records from the resulting data set
- UNION ALL will return all the rows, even if one or more rows are duplicated to each other.
4) What is the difference between WHERE clause and HAVING clause?
- WHERE clause can only be applied on a static non-aggregated column
- we will need to use HAVING for aggregated columns.
5) What is the difference among UNION, MINUS and INTERSECT?
- UNION combines the results from 2 tables and eliminates duplicate records from the result set.
- MINUS operator when used between 2 tables, gives us all the rows from the first table except the rows which are present in the second table.
- INTERSECT operator returns us only the matching or common rows between 2 result sets.
6) How can we transpose a table using SQL (changing rows to column or vice-versa) ?
- The usual way to do it in SQL is to use CASE statement or DECODE statement
7) How to generate row number in SQL Without ROWNUM
- SELECT name, sal, (SELECT COUNT(*) FROM EMPLOYEE i WHERE o.name >= i.name) row_num
FROM EMPLOYEE o
order by row_num
8) How to select first 5 records from a table?
sql server -> SELECT TOP 5 * FROM EMP;
Oracle -> SELECT * FROM EMP WHERE ROWNUM <= 5;
Generic -> SELECT name FROM EMPLOYEE o
WHERE (SELECT count(*) FROM EMPLOYEE i WHERE i.name < o.name) < 5
