By sorting the records in ascending ,and showing only name of those who have avg experienced of the-CodePudding

create table employees(
    id int (10),name varchar(20),salary int(20),designation varchar (20),
    expinyears int (10));



    insert into employees values ('101','rajesh','13000', 'system eng','3');
    insert into employees values ('102','pritam','5000', 'system eng','5');
    insert into employees values ('104','ramesh','40000', 'manager','10');
    insert into employees values ('105','john','30000', 'lead','15');
    insert into employees values ('106','anjali','20000', 'it analyst','10');
    insert into employees values ('103','sudeep','10000', 'lead','8');
    insert into employees values ('107','vicky','80000', 'lead','20');

From this table we have to display the names of employees who are having more than 5 years of experience over the average experience of all the employees and whose salary is > 25000... We have to display the result by sorting the records based on the id of employees in the asc order..

I have try this:-

SELECT name from employees 
where salary 
group by name
having sum(salary) > 25000;

But it shows the ramesh ,john & vicky

and the answer has to show "vicky" only...

CodePudding user response：

One way is using a scalar subquery

select name 
from employees 
where salary > 25000
   and expinyears > (select avg(expinyears) from employees)   5;

CodePudding user response：

No need for any group by or having clause. You can do as follows

select avg(expinyears)   5
into @avgexp
from employees;

select name
from employees 
where salary > 25000 and expinyears > @avgexp

or if you don't want to select into a variable you can also just do

select name
from employees 
where salary > 25000 and expinyears > (select avg(expinyears)   5 from employees)

CodePudding user response：

select name from employees where expinyears >= (select avg(expinyears) from employees where salary > 25000) 5 order by id asc;