This week's book giveaways are in the Cloud and AI/ML forums.
We're giving away four copies each of Cloud Native Patterns and Natural Language Processing and have the authors on-line!
See this thread and this one for details.
Win a copy of Cloud Native PatternsE this week in the Cloud forum
or Natural Language Processing in the AI/ML 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
  • Devaka Cooray
  • Liutauras Vilda
  • Jeanne Boyarsky
  • Bear Bibeault
Sheriffs:
  • Paul Clapham
  • Knute Snortum
  • Rob Spoor
Saloon Keepers:
  • Tim Moores
  • Ron McLeod
  • Piet Souris
  • Stephan van Hulst
  • Carey Brown
Bartenders:
  • Tim Holloway
  • Frits Walraven
  • Ganesh Patekar

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: 3752
62
Netbeans IDE Oracle Chrome
  • 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.
     
    It is sorta covered in the JavaRanch Style Guide.
    • Post Reply Bookmark Topic Watch Topic
    • New Topic
    Boost this thread!