L &T Infotech SQL Interview Question | SQL Intermediate Question 12

In this video we are going to discuss a very interesting SQL problem a sked in L and T interview.
DDL script:
create table employee
(
emp_name varchar(10),
dep_id int,
salary int
);
delete from employee;
insert into employee values
('Siva',1,30000),('Ravi',2,40000),('Prasad',1,50000),('Sai',2,20000);
#L&T #dataanalytics #interviewquestions #dataengineering #amazon #facebook #meta #instagram #netflix #google #ai #ml #sqldeveloper #sql #sqlserver #placement #college #reels

[@jjayeshpawar]

select dep_id, max(salary), min(salary)
from em
GROUP by dep_id

[@rajkumarrajan8059]

is it working, when i tried this query it throws an error

[@rajkumarrajan8059]

thereby used this query
SELECT
distinct DEP_ID
,MAX(SALARY) OVER (PARTITION BY DEP_ID ) AS [MAX_SALARY]
,MIN(SALARY) OVER (PARTITION BY DEP_ID ) AS [MIN_SALARY]
FROM EMPLOYEE

[@sidmoitra007]

@@rajkumarrajan8059
select dep_id, MAX(salary) as max_sal, Min(salary) as min_sal
From employee
Group by dep_id
working perfectly fine

[@daily_delightz]

This is the simplest and effective query, no need to conplicate things

[@rajkumarrajan8059]

SELECT
distinct DEP_ID
,MAX(SALARY) OVER (PARTITION BY DEP_ID ) AS [MAX_SALARY]
,MIN(SALARY) OVER (PARTITION BY DEP_ID ) AS [MIN_SALARY]
FROM EMPLOYEE

[@daily_delightz]

Instead of window function just use group by

[@Skd833]

select [dep_id],max([salary]) as higest_salary, min([salary]) as lowest_salary from [Sambit].[dbo].[emp]
group by [dep_id]

[@DEwithDhairy]

LnT PySpark Interview Question and Answer:
ruclips.net/video/Kuv_d5uybIU/видео.htmlsi=_1KLGDMhIPXCB52f

[@abhinavkumar2662]

We can check by use of Max and min..select Max(salary)from employee

[@Code-Con]

Yeah that's a better approach 👏

[@user-ew2nw1my7r]

Simple Query
select Dep_id,avg(salary) as avg_salary,min(salary) as Min_salary, Max(salary) as max_salary from table name
group by Dep_id
order by dep_id

[@rajm5349]

i have doubt what type of join is this , is it simple join , does it lead to cartesian product

[@Code-Con]

it's inner join

[@krishnakishore993]

Hi
Select dep_id,MAX(salary) as Highestsalary,MIN(salary) as Lowest _salary from employees
Group by dep_id
I got output from this query.
Is it write way or need to follow Cte and ranking,joins
Please let me know
Thanks 😊

[@Code-Con]

Absolutely a right approach 👏

[@Tech_with_Srini]

with cte as (
select empNo,eName,sal,deptno , max(sal) over (partition by deptno) as max_sal ,
min(sal) over (partition by deptno) as Min_sal
from emp2)
select c1.empNo,c1.eName,c1.deptno,c2.max_sal,c2.min_sal from cte c1 join cte c2
on c1.empno=c2.empno
where c1.sal= c2.max_sal or c1.sal=c2.min_sal
order by c1.deptno

[@jayavani2223]

SELECT D.dep_Name, MAX (salary) AS highest_salary, MIN(Salary) AS lowest_salary
FROM EMployees E
LEFT JOIN DEPARTMENTS D ON E.DEP_ID = D.DEPT_ID_DEP
GROUP BY D.DEP_NAME
Please let me know is it correct or wrong