Win a copy of JDBC Workbook this week in the JDBC and Relational Databases forum
or A Day in Code in the A Day in Code forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Paul Clapham
  • Jeanne Boyarsky
  • Junilu Lacar
  • Henry Wong
Sheriffs:
  • Ron McLeod
  • Devaka Cooray
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Frits Walraven
  • Tim Holloway
  • Carey Brown
Bartenders:
  • Piet Souris
  • salvin francis
  • fred rosenberger

Efficient Query

 
Ranch Hand
Posts: 391
1
MySQL Database PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


Now I want to write a query that gives me the highest paid male employee in each department.

I get What I was looking for
EID ENAME DID GENDER SALARY
222 Mohammad latif 1 M 15000
888 Ahmed Rehman 2 M 19000

But I want to display dept_name of employee rather than employees department id.
So I wrote this one

This gives me
EID ENAME DEPT_NAME GENDER SALARY
222 Mohammad latif CS M 15000
888 Ahmed Rehman IT M 19000

Above Query Have two joins , So i don`t think its efficient way.So i write this query.


So how one should go about writing a query that is efficient.
 
Mahtab Alam
Ranch Hand
Posts: 391
1
MySQL Database PHP Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One more question , can you tell me one query that have outer query and one correlated subquery and that correlated subquery uses group by clause .
So group by clause in inner subquery uses outer query`s column as grouping column.
Something like this

select col1,col2,..
from tab1 t1
where col1 = ( select max(col3)
from tab2 t2
group by t1.col1
)
 
Sheriff
Posts: 3837
66
Netbeans IDE Oracle Firefox Browser
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Writing efficient queries is a very broad topic. If you're concerned about Oracle, I'd suggest to read Oracle's Performance guides:
  • Oracle® Database Performance Tuning Guide
  • Oracle® Database 2 Day + Performance Tuning Guide

  • If you haven't read it by now, start with the Concepts guide. You'll need it to be able to follow the other guides.

    One thing that stands out in this particular case is the lack of constraints. Constraints, such as primary key, unique and foreign key constraints, give the database additional information about the data and allow better optimization of the queries.

    Generally speaking, the query which uses less joins is probably more efficient. Oracle does a lot of optimizations, though, and it is just possible that the two queries would perform about the same. There might be a difference, however, if the department name isn't unique, in which case the two queries would give different results! So, in your case, the query with two joins might also be incorrect. (I would prefer the department ID over the name for comparison in this query. ID just feels better for identification, which is what you need here. Name is just a name, and there might be situations in which names become non-unique, this is much less probable with IDs.)

    (Also note that your sample data contains too few rows for any performance issues to actually come up. Incidentally, a promotion for GenRocket, a test data generator, has just ended in our Testing forum; this tool would help in generating the amount of data you'd need. I'm mentioning this just so that you're aware of it, I don't want you to actually post scripts that would insert thousands of rows. )

    Mahtab Alam wrote:One more question , can you tell me one query that have outer query and one correlated subquery and that correlated subquery uses group by clause .
    So group by clause in inner subquery uses outer query`s column as grouping column.


    I've though we've already discussed this thoroughly here. It's exactly the same question. If it's still unclear to you, please let's continue the discussion in the other thread.
     
    Mahtab Alam
    Ranch Hand
    Posts: 391
    1
    MySQL Database PHP Java
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator


    ORA-32035: unreferenced query name defined in WITH clause

    I read somewhere that with ,with clause you have to reference all the views you created.
     
    A "dutch baby" is not a baby. But this tiny ad is baby sized:
    Devious Experiments for a Truly Passive Greenhouse!
    https://www.kickstarter.com/projects/paulwheaton/greenhouse-1
      Bookmark Topic Watch Topic
    • New Topic