2) SQL Joins can be classified into Equi join and Non Equi join. 1) SQL Equi joins It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join. For example: You can get the information about a customer who purchased a product and the quantity of product. 2) SQL Non equi joins It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=
STORED PROCEDURE VS FUNCTION
IN functions we can not alter database records., can not use statements like insert, update, or delete. However we can perform the same functions in a stored procedure, so use stored procedure when the statements involve these kind of operations. Unlike stored procedures functions can be used to inline in SQL statements, other functions or in procedures so use it when you need to compute some value that will be later used in some SQL statement. Both stored procedure and function can be used to achieve the same goal.
3) Select top 3 rows/ select top row/ select row with maximum value/frequency
Suppose the table named, STREET is like below.
55 2ndstreet 555 57
59 2ndstreet 555 61
63 2ndstreet 555 22
65 1ststreet 555 21
67 2ndstreet 555 69
73 2ndstreet 555 75
77 3rdstreet 555 79
81 2ndstreet 555 83
105 4thstreet 555 107
133 2ndstreet 555 135
This Query gives the maxim/max frequently occuring column.
select * from (select street_name,count(street_name) from street group by street_name order by count(street_name) desc) where rownum=1;