CREATE TABLE ‘dbname’.’tablename’(‘COLUMN_NAME1’ INT NOT NULL, ‘COLUMN_NAME2’ type, PRIMARY KEY(‘id’);
Inner Join (Definition)
Combine the rows from two tables and create a result set containing only such records that are present in both the tables based on the joining condition (predicate).
Inner Join (Syntax)
SELECT tbl1.column1 TABLE1, tbl2.column1 TABLE2 FROM TABLE1 tbl1, TABLE2 tbl2 WHERE tbl2.tbl2_id = tbl1.tbl1_id
Outer Join (Definition)
Returns matching rows from both tables as well as any unmatched rows from one or both the tables (based on whether it is single outer or full outer join respectively).
Outer Join (Syntax)
SELECT dept.name DEPARTMENT, emp.name EMPLOYEE FROM DEPT dept, EMPLOYEE emp WHERE dept.id = emp.dept_id (+)
The (+) sign on the emp side of the predicate indicates that emp is the outer table here. The above SQL can be alternatively written as below (will yield the same result as above):
SELECT dept.name DEPARTMENT, emp.name EMPLOYEE FROM DEPT dept LEFT OUTER JOIN EMPLOYEE emp ON dept.id = emp.dept_id
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.
SELECT * FROM EMP1 UNION SELECT * FROM EMP2;
What is the difference between UNION and UNION ALL?
UNION and UNION ALL both unify for add two structurally similar data sets, but UNION operation returns only the unique records from the resulting data set whereas UNION ALL will return all the rows, even if one or more rows are duplicated to each other.
UNION ALL (Syntax)
SELECT * FROM EMPLOYEE WHERE ID = 5 UNION ALL SELECT * FROM EMPLOYEE WHERE ID = 5
What is the difference between WHERE clause and HAVING clause?
WHERE and HAVING both filters out records based on one or more conditions. The difference is, WHERE clause can only be applied on a static non-aggregated column whereas we will need to use HAVING for aggregated columns.
SELECT * FROM DEPT WHERE ID > 3id Name4 Sales5 Logistics
Next, suppose we want to see only those Departments where Average salary is greater than 80. Here the condition is associated with a non-static aggregated information which is “average of salary”. We will need to use HAVING clause here:
- SELECT dept.name DEPARTMENT, avg(emp.sal) AVG_SAL
- FROM DEPT dept, EMPLOYEE emp
- WHERE dept.id = emp.dept_id (+)
- GROUP BY dept.name
- HAVING AVG(emp.sal) > 80
- DEPARTMENT AVG_SAL
- Engineering 90
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.
- SELECT * FROM EMPLOYEE
- SELECT * FROM EMPLOYEE WHERE ID > 2
- SELECT * FROM EMPLOYEE WHERE ID IN (2, 3, 5)
- SELECT * FROM EMPLOYEE WHERE ID IN (1, 2, 4, 5)
What is Self Join and why is it required?
Self Join is the act of joining one table with itself.
Usage: Self Join is often very useful to convert a hierarchical structure into a flat structure
Self Join (syntax)
SELECT e.name EMPLOYEE, m.name MANAGER FROM EMPLOYEE e, EMPLOYEE m WHERE e.mgr_id = m.id (+)
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.
How to generate row number in SQL Without ROWNUM
Generating a row number. This method only works if there is at least one unique column in the table. This method will also work if there is no single unique column, but collection of columns that is unique.
- SELECT name, sal, (SELECT COUNT(*) FROM EMPLOYEE i WHERE o.name >= i.name) row_num
- FROM EMPLOYEE o
- order by row_num