SQL Notes
True or false: You must also use theGROUP BYclause when using theHAVINGclause.
False – having clause can be used without group by
Group by vs Order by
In group by all non aggregate columns must be selected.
Group by is used to group identical data
order by is designed merely to put a data in a specific order.
It is generally not necessary unless using aggregate functions like sum, avg,count.
SELECT COUNT(EMPLOYEE_ID), SALARY FROM EMPLOYEE_PAY_TBL;
The result will display the first salary along with count.
AVGfunction does not consider null values
INSTR
The INSTR function is used to search a string of characters for a specific set of characters
and report the position of those characters. The syntax is as follows:
INSTR( COLUMN NAME, ‘ SET’,
[ START POSITION[ , OCCURRENCE] ]);
LTRIM(CHARACTER STRING[ ,’set’])
DECODE(COLUMN NAME,‘SEARCH1’,‘RETURN1’,[‘SEARCH2’,‘RETURN2’,‘DEFAULTVALUE’])
Coalesce:Function to check null in specified order and return not null values if any found
coalesce(column1,column2,column3... so on)
ASCII – returns acii set
Conversion functions
TO_NUMBER
CAST – Operator to convert datatypes into other data types.
Convert date to string
to_char(date_column,'month yy dd')
Natural join vs Inner join
Natural join – no need to specify column name
Inner join – column name in join is required
Not equal join,
If table a has 6 rows and table b has 8 rows then non equal join produces
6*(8-1) rows since it excludes the current row in the join instead of 6*8 rows
Subqueries
1.Order by clause cannot be used in subquery where as group by can be used to get achieve the same.
2. Between operator cannot be used with subquery
Correlated subquery – a subquery that depends on information specified from main query
Union vs Union ALL – former eliminates duplicate, later includes it.
Oracle – select random rows – use sample(10) where 10 is 10% of total rows.
Views – insert/update – the underlying data cannot involve joins or group by clause or union statement.
Create view cannot have order by clause – use group by instead.
