This is the perfect video anyone will be looking for.. like while we see a question what all ways we can do a question is what everyone need as some people ask for different approaches. Thank you please do a lot like these videos.
You the only destination for Datastage developer. I have cleared lots of interviews seeing your videos only. Please add the script for those questions. So that it will be helpful for practice. Thanks🙏
Excellent coverage! Looking forward to plenty more queries -- I genuinely love this! Here's hoping that you can create more and more training series -- Basic, Intermediate and Advanced plus super-duper Complex examples! Dhanyawad! AWESOME!
I just subscribed this channel after seeing this video.. Wonderful explanation.. I would suggest to make similar kind of scenarios questions videos playlist for PL/SQL also.
Thank you for the video. That is how, step by step, the tasks should be solved! However, solution #4 is not correct because the number of employees with the same salary can be more than 2. Universal solution: SELECT EmployeeID ,FullName ,DeptID ,E.Salary FROM Employees E INNER JOIN (SELECT Salary FROM Employees GROUP BY Salary HAVING Count (Salary) >1) MS ON E.Salary = MS.Salary
I think more efficient approach - rather than joins or multiple subqueries is CTE with window function like rank or avg or max partitioned by dept etc.
2nd question can be solved using co related sub query. Select e.employeeID, e.DeptID, e.Salary from emp e where e.sal < (select avg(sal) from emp e2 where E1.deptno=e.deptno);
Hey there, God bless your efforts. I have come through the term concurrence in sql, what does it actually means? Thanks for taking time to clarfifying this to me.
Is the solution for Question 3 - "Employees with Salary less than the Department's Average Salary - But Higher than the Average of ANY OTHER Department's Salary" - compatible with all RDBMS ? " > ANY " is supported in all RDBMS ? Can someone help with other solutions. Thank You
--SHORT QUERY WITH CTE AS( SELECT EMPID , DEPTID , SALARY ,ROW_NUMBER()OVER(PARTITION BY DEPDID ORDEER BY SALARY) AS RANK) FROM EMP) SELECT * FROM CTE WHERE RANK = 1
Simpler solution for 9th one: SELECT * FROM (WITH cte AS (SELECT e.deptid AS EMPDEPT, 0.90 * AVG(salary) AS AVGSAL FROM employees e GROUP BY e.deptid ) SELECT *, ROW_NUMBER() OVER(PARTITION BY deptid) AS rownum FROM employees e1 JOIN cte c ON e1.deptid = EMPDEPT AND e1.salary > AVGSAL ) temp WHERE rownum >= 2;
Need more tips and pointers to solve SQL Complex queries. Check out our latest video below -
ua-cam.com/video/YKHFhZJDw-o/v-deo.html
This is the perfect video anyone will be looking for.. like while we see a question what all ways we can do a question is what everyone need as some people ask for different approaches. Thank you please do a lot like these videos.
Thank You
I thought sql was tough until I watch this video. Thanks for the wonderful explanation.
Thank you
Excellent explanations. Thank you for sharing
Thank you
You the only destination for Datastage developer. I have cleared lots of interviews seeing your videos only. Please add the script for those questions. So that it will be helpful for practice.
Thanks🙏
Thank you. The scripts are available here -
know-star.blogspot.com/2022/09/how-to-write-complex-sql-queries.html?m=1
Very useful information. Very helpful for our preparation. Please do more videos like this. Thankyou
Thank you 🙏
Thanx for the great examples and clear explanations ❤
Glad it was helpful!
Wow , great video as usual and questions were also good. Thanks a lot 🙏
Thank you 🙏
Excellent coverage! Looking forward to plenty more queries -- I genuinely love this! Here's hoping that you can create more and more training series -- Basic, Intermediate and Advanced plus super-duper Complex examples! Dhanyawad! AWESOME!
Thank you!
I just subscribed this channel after seeing this video.. Wonderful explanation..
I would suggest to make similar kind of scenarios questions videos playlist for PL/SQL also.
Sure. Glad the video was helpful.
Thank you very much for your effort. I wish you success in your life
Thank you 🙏
Great video. Explaining how to think logically is very helpful.
Glad it was helpful!
Thank you for the video. That is how, step by step, the tasks should be solved!
However, solution #4 is not correct because the number of employees with the same salary can be more than 2. Universal solution:
SELECT EmployeeID
,FullName
,DeptID
,E.Salary
FROM Employees E INNER JOIN (SELECT Salary
FROM Employees
GROUP BY Salary
HAVING Count (Salary) >1) MS ON E.Salary = MS.Salary
Thanks for sharing this. This is a great solution.
Amazing content. Please post more of such detailed videos!
More videos coming soon 👍
I think more efficient approach - rather than joins or multiple subqueries is CTE with window function like rank or avg or max partitioned by dept etc.
Gr8 vdo thnx for ur efforts
Thank You !
2nd question can be solved using co related sub query.
Select e.employeeID, e.DeptID, e.Salary from emp e where e.sal < (select avg(sal) from emp e2 where E1.deptno=e.deptno);
Really very helpful.
Thank you 🙏
Hey there,
God bless your efforts.
I have come through the term concurrence in sql, what does it actually means?
Thanks for taking time to clarfifying this to me.
Good explanation thank you
Thank you 🙏
Superb!
Thank you 🙏
great video, you've gained a subscriber, but it will be great to have the scripts so i can tag along, the links in the description isn't going through
Thank you. You can find the sql statements and dataset here - know-star.blogspot.com/2022/09/how-to-write-complex-sql-queries.html?m=1
wow great keep it up, i will be grateful if you could tell how to get this database to my sqlserver so i can practice after watching
sincerly
Thank you.The dataset and sql links are in the description.
Good
Thank you
I'm getting "Cannot insert explicit value for identity column in table 'Employees' when IDENTITY_INSERT is set to OFF." ERROR
In the syntax of creating the table how you refer the managerId by using empID. I think there is a wrong syntax.
in your screen query not visible properly becouse of latest ouput.
All queries are provided in the link in the description box. Hope it helps.
can you pls give me the data set script so that we can try practice
You can find the practice dataset and sql queries here -
know-star.blogspot.com/2022/09/how-to-write-complex-sql-queries.html
Mam, how can I get better in SQL server?
Practice SQL queries and try writing them in your own.
Is the solution for Question 3 - "Employees with Salary less than the Department's Average Salary - But Higher than the Average of ANY OTHER Department's Salary" - compatible with all RDBMS ? " > ANY " is supported in all RDBMS ? Can someone help with other solutions. Thank You
Ma'am please reduce size of characters.. can't see full query at a time
Will keep in mind. Thank you for bringing this to our attention.
--SHORT QUERY
WITH CTE AS( SELECT EMPID , DEPTID , SALARY ,ROW_NUMBER()OVER(PARTITION BY DEPDID ORDEER BY SALARY) AS RANK) FROM EMP)
SELECT * FROM CTE
WHERE RANK = 1
wow
Thank you
Waise agar Hindi me ki hoti sure like Jayada subscribe hota but English hi without style bolo
hi make a video 15 to 20 minutes
Sure, we will be making medium length videos as well 👍
10th problem statement is poorly worded.
Bhai samajh aarhi toh padhlo warna dusre channel follow krlo...
Inka channel hai inki marzi...
Madam tum log style me ku bolte ho jra acche se bolo n
your script showing error
Simpler solution for 9th one:
SELECT * FROM
(WITH cte AS
(SELECT
e.deptid AS EMPDEPT,
0.90 * AVG(salary) AS AVGSAL
FROM employees e
GROUP BY e.deptid
)
SELECT *, ROW_NUMBER() OVER(PARTITION BY deptid) AS rownum
FROM employees e1 JOIN cte c ON e1.deptid = EMPDEPT AND e1.salary > AVGSAL
) temp
WHERE rownum >= 2;