1. Registered User
Join Date
Jan 2013
Posts
5

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.

Thank You

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
So what have you tried
What isn't working
What are you stuck on

3. Registered User
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. Registered User
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?

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by SlalithaS
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

6. Registered User
Join Date
Nov 2003
Posts
2,985
Originally Posted by healdem
set up a SQL DB, could be MySQL, or SQL Server
No need to setup: SQL Fiddle

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

8. Registered User
Join Date
Sep 2009
Location
San Sebastian, Spain
Posts
880
This could also be done inline without views as a subquery.

#### Posting Permissions

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