SQL Interview Question - Solution (Part

SQL Interview Question - Solution (Part - XV) |

Рет қаралды 1,146

16 күн бұрын

#sql #education #sqlfunctions #dataanalyst #dataengineers
#MeanLifeStudies #sqlinterview #datascience #interview #dataanalystinterview
Here are My profiles that will definitely help your preparation for data analyst or data engineer roles.
Medium: / mahendraee204
Github: github.com/mahendra204
Do you want to clear your doubts about becoming a data analyst/data engineer or any guidance you require? don't hesitate to book a call at Topmate with lower prices. The link is mentioned below.
Topmate.io :- Topmate.io/mahendra204
Here are table creation and insertion statements:
-------------------------------------------------------------------------------
create table employee (
empid int,
empname varchar(50),
mgrid int,
salary int
);
insert into employee values
(1, 'mahendra', 2, 25000),
(2, 'mahi', 5, 20000),
(3, 'sam', null, 45000),
(4, 'dev', 1, 20000),
(5, 'dash', 3, 97000),
(6, 'pratik', 6, 30000),
(7, 'singh', 2, 40000),
(8, 'rao', 5, 80000);

Пікірлер: 8

@Vaibha293 13 күн бұрын

amazing bro..

@MeanLifeStudies 13 күн бұрын

Thank You.

@VARUNTEJA73 14 күн бұрын

with cte as( select distinct e.empid,t.mgrid,e.empname as mngname,t.empname as empname,e.salary+t.salary as totalsal from employee e join employee t on e.empid=t.mgrid where e.salary

@dasubabuch1596 14 күн бұрын

Hi Sir, I tried with hierarchical query for this problem. Can you please check it once? with t as ( select empid,empname, prior empname as managername,salary,level from employee start with mgrid = 3 connect by empid = prior mgrid ),t1 as ( select avg(salary) as av_salary from t ) select av_salary, empname||'-'||managername as emp_mgr_pair from t1, t where managername is not null;

@mrsantho 13 күн бұрын

with cte1 as( select e.*,e1.empname as managername, e1.salary as managersalary, (e.salary + e1.salary)/2 as averagesalary from employeet1 e join employeet1 e1 on e.mgrid=e1.empid ), cte2 as( select concat(empname,':',managername) as emp_mgr_pair, averagesalary as salary, dense_rank() over(order by averagesalary desc) as rn from cte1 ) select emp_mgr_pair,salary from cte2 where rn=2;

@maheshnagisetty4485 14 күн бұрын

select mgr_emp,avg_sal from ( select *,rank() over(order by avg_sal desc) as rn from ( select concat(e.empname, ':', m.empname) as mgr_emp, (e.salary+m.salary)/2 avg_sal from employee as e join employee as m on e.mgrid=m.empid )as a ) as b where rn=2

@Naveen-uz4hw 13 күн бұрын

select concat(manager_name,':',b.empname) as emp_mgr_name ,(manager_salary+salary)/2 as salary from (select empid as manager_id ,empname as manager_name ,salary as manager_salary from employee where mgrid is null)a join employee b on a.manager_id=b.mgrid Can you please validate this solution

@MeanLifeStudies 13 күн бұрын

Yes. It is correct. But you are making it too complex. Kindly understand if a manager is not null for Sam then? I mean for Sam is any other manager then?