SQL Basics

SELF JOIN

• A self join is a join in which a table is joined with itself

Ex :when we want to get the manager name who is an employee then in the join condition left side would be the first table (employee e) and right side would be the second table (employee m) in the join condition.

Note: It would take manger_id from the employee (m) table and would look for the employee_id in the  employee table (e)

Select  id,e.employee_name,m.employee_name
from employee e
Join employee m
on e.manger_id=m.employee_id

UNION ALL vs UNION

UNION ALL
Would return duplicate value and would not be sorted

UNION

• Would not return duplicate value and would not be sorted
• UNION has to perform distinct sort to remove duplicates, which makes it slower than union all

NOTE:

• For UNION and UNION ALL to work the number, data types and the order of the columns in the select statement should be the same

• As long as data types are same they would try to combine  and would try to convert implicit conversions.when the conversion works it would display it
• ORDER BY can be used to sort in UNION ALL. however we have to specify ORDER BY in the second column

Select email from employee
UNION ALL
Select name from dup_employee

DIFFERENCE between UNION and JOIN

• UNION combines the result of two or more select queries into a single result set which includes all the rows from all the queries in that UNION 
• UNION combines rows from 2 or more tables.

• JOIN refers data from 2 or more tables based on logical relationship between the table
• JOIN combines columns from 2 or more table

GROUP BY

• Used to group a selected set of rows into a selected set of summary rows by the values of one or more columns or expressions.
• It is always used in conjunction with one or more aggregate functions

AGGREGATE FUNCTIONS

• In SQL, aggregate functions are :sum(),min(),max(), avg()
• They can be used without group by clause
• Aggregate function has to be applied on the column.
• We can only apply aggregate function on numerical column

Select SUM (salary) from employee

Note: Now if we use city column in the select query then we have to specify GROUP BY clause
Ex

Select city, SUM(salary) from employee
Group by city

WHERE VS HAVING

• WHERE filters rows before aggregation(GROUPING) 
• HAVING filters groups after the aggregation are performed.
• WHERE clause can be used with select,insert,update statements.HAVING clause can only be used with select statement
• Aggregate functions cannot be used in the WHERE clause, unless it is  the sub query contained in a HAVING clause,aggregate functions can be used in the having clause

DERIVED TABLE

• Are available in the context of current select query.
• It would not be available later

CTE

• Can be thought as a temporary result set that is defined within the execution scope of a single SELECT, INSERT,UPDATE,  DELETE or CREATE VIEW statement.
• CTE is similar to a derived table that is not stored in an object and lasts only for the duration of the query

NOTE: The column in the select query and the outer column in the with clause count should be same
.We can rename the column

SUB QUERY

• Is simply a select statement that returns a single value and can be nested inside a SELECT, UPDATE,INSERT,DELETE statement. 
• It is possible to nest a sub query inside another sub query .
• The columns from the table that is present only inside a sub query,  cannot be used in the SELECT list of the OUTER QUERY
• Any time we want a sub query we open and close the parenthesis ( ) and are called as INNER QUERIES
• We can use a sub query inside a select list

SELECT name, ( select SUM(sold) from tblproducts 
where productId= tblproducts.Id) as qtysold from products order by name