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

[@DEwithDhairy]

LnT PySpark Interview Question and Answer:
th-cam.com/video/Kuv_d5uybIU/w-d-xo.htmlsi=_1KLGDMhIPXCB52f

[@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 👏

[@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

[@abhinavkumar2662]

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

[@Code-Con]

Yeah that's a better approach 👏