Results 1 to 8 of 8

Thread: Queries

  1. #1
    Join Date
    Jan 2013
    Posts
    5

    Unanswered: Queries

    Employee table

    EMPNAME - STREET- CITY
    Raj - Mg- Delhi
    Tom - Fl -Delhi
    Sam - Jkl- Goa
    Bob - Jai- Amritsar
    Manic -Ss- Chennai
    Lali -Qp- Kolkata
    Zomy - Ss- Chennai
    Joy - Jkl- Goa
    Carol - Qp- Kolkata
    Fizy- Mg- Delhi
    Syam- Mg- Delhi
    Ramu- Jj- kolkata

    Manager table

    Empname - Managername
    Raj - Bob
    Tom - Lali
    Sam -Joy
    Manik - Bob
    Zomy - Joy
    Carol- Lali
    Syam- lali


    Work table

    Empname- Companyname- Salary
    Tom- HCL - 50000
    Raj- Wipro - 45000
    Sam- UHG - 50000
    Carol- HCL - 15000
    Manic- Wipro - 25000
    Zomy- UHG - 5000
    Fixy- Infosys- 6000
    Bob- Wipro - 75000
    Lali- HCL - 90000
    Joy- UHG- 95000



    Company table

    Companyname- City
    HCL - Delhi
    Wipro- Goa
    UHG- Amritsar
    HCL- Chennai
    UHG- Chennai
    Wipro- Kolkata
    Infosys- Delhi
    Infosys- Kolkata
    HCL - Bangalore
    Wipro - delhi

    Queries:
    1) Find all employees who earn more than the average salary of all the employees of their company.
    2) Find the company that has the most employees.
    3) Find the company that has the smallest payroll.
    4) Find those companies whose employees earn a higher salary on average than the average salary at wipro.

    Please help me with these queries.

    Thank You

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So what have you tried
    What isn't working
    What are you stuck on
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    5
    For the 1 question is this right?
    select W1.empname from work W1 where W1.salary>( avg(W2.salary) from work W2 group by cmpnyname where W1.cmpnyname =W2.cmpnyname);

  4. #4
    Join Date
    Jan 2013
    Posts
    5
    For the 1 question:

    I know how to take the average of the company's salary. But how do I compare the employee's salary to his company's average salary?

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by SlalithaS View Post
    For the 1 question is this right?
    select W1.empname from work W1 where W1.salary>( avg(W2.salary) from work W2 group by cmpnyname where W1.cmpnyname =W2.cmpnyname);
    why don't you try it
    set up a SQL DB, could be MySQL, or SQL Server or even Access
    insert your test data
    then run your queries against that test data and see if it works. if it doesn't refine the queries till they do work
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2003
    Posts
    2,934
    Provided Answers: 12
    Quote Originally Posted by healdem View Post
    set up a SQL DB, could be MySQL, or SQL Server
    No need to setup: SQL Fiddle
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  7. #7
    Join Date
    Jan 2013
    Posts
    5
    Never mind.
    I got the queries. It is based on the concept of 'views'.
    Thank You.

  8. #8
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    This could also be done inline without views as a subquery.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •