Find those companies whose employees earn a higher salary, on average, than the average salary at “Wipro”

0
votes

I have tried one sql but not working

select cname,avg(salary) from Company as co,Works as wo where co.cid=wo.cid and wo.salary > (select avg(salary) from Company as c,Works as w where c.cid=w.cid and c.cname='Wipro');

Employee (EID, EName, City)

Works (EID, CID, Salary)

Company (CID, CName, City)

create table Employee(eid int primary key,ename varchar(6),city varchar(6))

create table Works(eid int,cid int primary key,salary int)

create table Company(cid int,cname varchar(6),city varchar(6))

alter table Works add foreign key(eid) references Employee(eid)

alter table Works add foreign key(cid) references Company(cid)

4
mysqlsql

4 Answers

1
votes

Learn to use proper, explicit, standard JOIN syntax.

That said, you are close. You just need a HAVING clause:

select c.cname, avg(w.salary)
from Company c join
     Works w
     on c.cid = w.cid 
group by c.cname
having avg(w.salary) > (select avg(w2.salary)
                        from Company c2 join
                             Works w2
                             on c2.cid = w2.cid 
                        where c2.cname = 'Wipro'
                       );

Notes:

  • Never use commas in the FROM clause.
  • You should have a GROUP BY when you are using AVG().
  • Conditions on summarized values should be in the HAVING clause.
0
votes

Try this:

Select cname
FROM
(select cname,cid,avg(salary) as avg_sal
from works w 
join company c
on w.cid = c.cid
group by w.cid,w.cname
)a,
(
select avg(salary) as avg_wipro
from works w
join company c
on w.cid = c.cid
and ccname='Wipro'
)b
WHERE a.avg_sal > b.avg_wipro
0
votes
SELECT t1.CName, 
       t2.AvgSalary
FROM Company t1
INNER JOIN (SELECT AVG(Salary) AS AvgSalary, 
            CID
            FROM Works
            GROUP BY CID) t2 ON t1.CID = t2.CID
WHERE t2.AvgSalary > (SELECT AVG(Salary)
                      FROM blog.Works t1a
                      INNER JOIN blog.Company t2a ON t1a.cid = t2a.cid 
                      WHERE t2a.cname = 'Wipro');
0
votes

When there's a GROUP BY then you can use the aggregate functions like a SUM in the HAVING clause.

SELECT 
co.cname AS company_name, 
AVG(wo.salary) AS avg_salary 
FROM Company AS co 
JOIN Works AS wo ON wo.cid = co.cid 
GROUP BY co.cname 
HAVING AVG(wo.salary) > (
       SELECT AVG(salary) 
       FROM Company AS c 
       JOIN Works AS w ON w.cid = c.cid 
       WHERE c.cname = 'Wipro'
      );

You can try it here