Solved

How Select Statement Work

Posted on 2006-07-04
9
1,088 Views
Last Modified: 2013-12-11
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
Comment
Question by:loay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 48

Expert Comment

by:schwertner
ID: 17036648
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 Comment

by:loay
ID: 17040231
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
 
LVL 48

Expert Comment

by:schwertner
ID: 17040414
SELECT sal
FROM salary
WHERe rownum = 2
ORDER BY sal;
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 48

Accepted Solution

by:
schwertner earned 20 total points
ID: 17040420
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 Comment

by:loay
ID: 17041069
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
 
LVL 4

Expert Comment

by:sudhi022299
ID: 17041226
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
 
LVL 1

Expert Comment

by:Tareq_Asebie
ID: 17130315
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sybase and replication server 13 80
update using pipeline function 3 44
UTL_FILE invalid file operation 5 57
Password_rules_securitty.. 12 46
Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question