• Status: Solved
• Priority: Medium
• Security: Public
• Views: 1104

# How Select Statement Work

Hi experts,,

Can any one help me to understand the mechanism of select statement or how to start thinking of solving a select statement.. for example how to think of a select statement that brings the second heights salary in the company ... really i need to understand how select statement works please i need help here

Thanx

0
loay
1 Solution

Commented:
You have to think using SETS.
Imagine a table as set of rows and columns.
You can select SUBSET of rows and/or columns.
You can join set from one table with set from other table using different techniques - joins, Union, Minus set operations.
0

Author Commented:
Thanks ,,but I want the internal mechanism of select as I mentioned earlier for example how to start thinking of a select statement to bring the second largest salary in a company .. To solve such selects you need to understand what is going on inside the select itself

0

Commented:
SELECT sal
FROM salary
WHERe rownum = 2
ORDER BY sal;
0

Commented:
No, you do not need to know what happens behind the scene.
Only some rules.

sal is the salary column
salary is the table that contains salaries
rownum is pseudocolumn that ordered the result set
order by means sort values of the column sal

In fact I did an error:

SELECT sal
FROM salary
WHERe rownum = 2
ORDER BY sal DESC;

DESC means descending.
0

Author Commented:
thanx experts u r really great people but its not the query what i want i want to understand how select works because it will help me understand alot of things like explain plane and all the behind scien happend there
0

Commented:
You need to really stop worrying about the internals and start concentrating on writing queries. The best thing to start is the HR schema that comes with oracle. Once you feel that you can write queries, then as mentioned above, start thinking in terms of *sets*.

0

Commented:
there is many function used in SQL statement  like :

aggregate function
1- sum
2- count
3- max
4- min
5- avg

string function
1- lower
2- upper
3- substr
4- instr

you will write like this statement

select  max(sal) from emp
where sal <>(select max(sal) from emp)

change the name of column as the same name in your table

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.