My Solution using MYSQL Workbench: -- Part-1 Solution: select Department, salary,Emp from ( select d.DepartmentName as Department, e.FirstName as Emp, e.salary as Salary, dense_rank() over(partition by d.DepartmentName order by salary desc) as rnk from employees e join departments d on e.DepartmentID = d.DepartmentID ) as rnk_table where rnk<=3; -- Part 2 Solution: SELECT AVG(Salary) AS AverageSalary FROM Employees WHERE DateHired >= DATE_SUB(CURDATE(), INTERVAL 5 YEAR); -------- Part 3 Solution: SELECT * FROM Employees WHERE Salary < ( SELECT AVG(Salary) FROM Employees WHERE DateHired >= DATE_SUB(CURDATE(), INTERVAL 5 YEAR) );

The following are my solutions in MS SQL: --Question 1 select authorName,count(BookID) as 'Books Written' from authors au join books b on au.AuthorID=b.AuthorID group by authorName --Question 2 select au.AuthorID,au.AuthorName ,b.BookID,b.BookTitle,b.PublicationYear from authors au join books b on au.AuthorID=b.AuthorID where year(PublicationYear)=year(getdate())-1

Thank you for uploading such questions.I am benefitted by these questions.

IN SQL SERVER, -- How to find the highest salary? select MAX(Salary) as Highest_Salary from Employees_Data; select TOP 1 Salary as Highest_Salary from Employees_Data order by Salary DESC; select distinct Salary as HighestSalary from ( select ID, FirstName+', '+LastName as EmployeeName, Gender, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as drnk from Employees_Data ) as temp where temp.drnk = 1 -- How to find 2nd highest salary? select MAX(Salary) from Employees_Data where Salary < (select MAX(Salary) from Employees_Data) select top 1 Salary from ( select distinct TOP 2 Salary from Employees_Data order by Salary DESC ) as temp order by Salary asc; select distinct Salary as HighestSalary from ( select ID, FirstName+', '+LastName as EmployeeName, Gender, Salary, DENSE_RANK() OVER (ORDER BY Salary DESC) as drnk from Employees_Data ) as temp where temp.drnk = 2 -- How to find nth highest salary using a Co-Related Sub-Query? select * from Employees_Data as e1 where 2 = (select COUNT(distinct Salary) from Employees_Data as e2 where e2.Salary >= e1.Salary)

IN SQL SERVER, -- with pivot operator select month as MonthName, [Electronics], [Clothing] from ( select month, category, amount from sales_data ) as sourcedata PIVOT ( SUM(amount) FOR category IN ([Electronics], [Clothing] ) ) as pivoteddata; -- without pivot operator select month as MonthName, SUM(CASE WHEN category = 'Electronics' THEN amount ELSE NULL END) as Electronics, SUM(CASE WHEN category = 'Clothing' THEN amount ELSE NULL END) as Clothing from sales_data group by month; thanks for the table script...saves a lot of time and makes my job bit easier :)

in sql server, for 2nd question ;with cte_employees as ( select EmployeeID, FirstName+' '+LastName as EmployeeName, Salary, DateHired from Employees ) select AVG(Salary) as Average_Salary from cte_employees where DateHired >= DATEADD(YEAR, -5, GETDATE());

Thanks for your explanation. At video time (7:54), i mean while generating odd numbers from 1 to 10. We are getting 11 also, how to ignore it?

Hi Sir, Thanks for your explanation. I have doubt in Nth highest salary question. Why are we using order by twice in outer query. Then regarding offset, at first we gave 0 to find first high salary, then we need to use offset value 1 to find second highest salary right. Why you are using 2 to find second highest salary. If understand wrongly, please correct me.

thank you sir for explaining step by step😊😊

Great Video, Just went through your channel and the idea of tackling interview questions like this is great. Thanks, you just got a sub💯

1 st question answer: WITH RankedEmployees AS ( SELECT employee_id, salary, department, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank FROM employee_data ) SELECT employee_id, salary, department FROM RankedEmployees WHERE rank <= 3 ORDER BY department, rank;

MY answer 2 qst :SELECT AVG(salary) AS avg_salary_hired_last_5_years FROM employee_data WHERE hire_year BETWEEN 2019 AND 2024;

with cte as( select *,rank() over (partition by student_name order by marks desc) as rn from students) select student_name, sum(case when rn <=2 then marks else 0 end) as marks from cte group by student_name;

These questions asked for 2- 3 years experience candidate or fresher

WITH RECURSIVE CTE AS ( SELECT 1 AS order_id UNION SELECT order_id+1 FROM CTE WHERE order_id<20 ) SELECT order_id FROM CTE WHERE order_id NOT IN ( SELECT order_id FROM orders )

Why we use left join for 2nd question in weak in use of join cases so explain in detail please

Zoom in on Important Things Is Good Feature, Use it in Videos while editing

MY ANSWER FOR 1ST QUE AND TQ BRO select distinct(EE.Salary),concat(EE.FirstName,'',EE.LastName) as Full_name,DD.DepartmentName FROM department DD Join employee EE on DD.DepartmentID=EE.DepartmentID group by concat(EE.FirstName,'',EE.LastName),DD.DepartmentName,EE.Salary order by DD.DepartmentName, EE.Salary DESC LIMIT 9;

Can we use where year(publicationyear) = Adddate(curdate(), interval -1, year) in the where condition? Please correct me if am wrong

Very useful for practice scenarios

i completed postgresql recently, i don't know how to practice and where to implement?

Good explanation

This query I am using Select p.productName From product p Left join orders o on p.productid =o.productid Where o.orderid is null But I am getting no rows returned

nice job keep it up kindly update more queryyyyyy

nice explanation keep posted new one to help crack the interview

Why didn't you take a.author id= b.book id?

Which ide you using for sql

Nicely explained 😃👍👍👌👌👌👌👌

Instead of dense rank, we could use row number function, which helps better

Great job bro keep it up

select P.ProductName,sum(O.Quantity * P.ProductPrice) as Total_Revenue from Products P left join Orders1 O on O.OrderID = P.ProductID group by P.ProductName Use Multiply bro for Qunatity * ProductPrice

Please upload more query 🎉🎉

Ye question daily use me hote hai...?

Bro aap output ki jagah questions likh diya kro jo new h unke liye direct output smjhna thoda tough h

can u provide scripts for table and insert -> CHECK COMMENT

with final as (select coalesce(order_id - lag(order_id) over (partition by id order by id),0) as diff , row_number() over (partition by id order by id) as row_num from input_table) select row_num as Order_id from final where diff=2

select month, sum(if(category="electronics",amount,null)) as electronics, sum(if(category="clothing",amount,null)) as clothing from sales_data group by 1 order by 1;

select student_name, sum(marks) as marks from ( select *, dense_rank() over(partition by student_name order by marks desc) as rnk from students ) as m where rnk <3 group by 1

Informative.

WITH RECURSIVE CTE AS ( SELECT MIN(ORDER_ID) AS cnt FROM orders UNION SELECT cnt+1 FROM cte WHERE cnt < (SELECT max(order_id) FROM orders) ) SELECT cnt AS order_id FROM cte WHERE cnt NOT IN (SELECT order_id FROM orders)

select month, sum(case when category = 'clothing' then amount end )as clothing, sum(case when category = 'electronics' then amount end )as electronics from sales_data group by month

SELECT * FROM SALES_DATA SELECT MONTH,MAX(CASE WHEN CATEGORY='ELECTRONICS'THEN AMOUNT END) AS ELECTRONICS ,MAX(CASE WHEN CATEGORY='CLOTHING' THEN AMOUNT END) AS CLOTHING FROM SALES_DATA GROUP BY MONTH ORDER BY MONTH

plss dnt stop making videos

Hi, In first question while finding the top 3 employees within each department, dense_rank() would be appropriate one because it will handle tie values without skipping the ranking rather than row_number IMO. Thank you for the constant motivation through SQL Questions ! Looking for more ...

Without using crosstab, can do with group by, will the company accept it

fantastic