Thank you for the resources, they are very helpful

6 years passed from the video posted in May 2014. And today in May 2020 I still found it very helpful. Great job done for explanation and preparation. It is the best tutorial I have ever met in video, plus it has links to the slides and code. BRAVO! TOP JOB!

When it comes to SQL learning ..Your Channel is the only thing comes to my mind. Really appreciate your hard-work.

it's 2024 and this video is still helpful for me. Thank you so much kudvenkat

Hi Venkat , I really appreciate your effort in creating all these tutorials.It helped me to get 2 job offers.Your explanations and examples are very vivid.Thanks for your time and effort.Keep going !! I have been recommending your channel to many friends and will keep doing it.

Last part where you showed the difference with row_number, it was a great finisher indeed. My concept got clear about the ranking functions. Thanks a lot @kudvenkat

I got first job by following your videos for learning.... and its been 2 years experience and still following your videos so that can get better opportunity. I am sure its gonna work again...XD

the way you explain any concept is simply amazing! thank you so much!

This is just a beauty, I really loved the new Dense_Rank Function, kudvenkat you are the man. Thanks a million.

Venkat, so grateful to your instructions! Your tutorials are comprehensive and inspiring, and get me away from fears coding the queries. I have followed 6 parts and will be keeping on learning. Thank you again!

Thank You Venkat for a detailed explanation, we can also use the row number when we have duplicate values using the below query with Result as ( select salary,row_number() over (order by salary desc) as "Row Number" from (select distinct Salary from Employees) DisEmp ) select * from Result where Result.[Row Number] = 3

Watching this vedio after 9 years, still this question is asked in every interview ... Thank you for explaining in such a giod way 😊

Your way of explanation is amazing and unique Thank you so much because you made my skills much polished from zero to hero. I was unable to prepare anything but you made me the most confident one. Thanks once again

Your explanation is very precise, I love the way u first write sub query first and then the outer query..it just fits in mind quickly. One more thing others uses rownumber function , coz they first make it distinct rows and accordingly top number is given for req. Salary.

Iam Raja Sekhar recently i watched your vedios about SQL for beginner's it's very useful to me. Full explanation it's amazing ,it's easy to understand .thanks lot for ur helping the viewer by giving free knowledge to us .once thanks for alot.

Kud Venkat is Good Venkat. Thanks for all the efforts you have put in to make these videos.

THANKS ALOT CLEARED CONFUSION.... BEST PART OF YOUR VIDEO IS GIVEN COMPLETE DETAILS FROM TABLE CREATION TO EXECUTION IN DESCRIPTION WHICH SAVES TIME

This is very useful video, I got this question in the interview > How to find the 75th highest salary record in an employee table where there are 1000 records.

more simpler and generic query would be:- select salary from employee e1 where n=(select count(distinct e2.sal) from employee e2 where e2.sal>=e1.sal);

You're a genius. I've been searching for this. Your explanation is everything. Thanks for sharing

thank you. another way for nth salary is to use limit select salary from (select salary from employee order by salary desc limit n ) as newTable order by salary limit 1;

Thankyou Venkat . You can make anyone to get offer letter with nice packages . I am so confident today just because of you

>select ename,sal from emp where sal = ( select max(sal) from emp where level=&levelno connect by prior sal>sal group by level;

Superb tutorial, as usual. Just an alternative though: SELECT MIN(salary) FROM dbo.Employees WHERE salary IN(SELECT DISTINCT TOP N salary FROM dbo.Employees ORDER BY salary DESC)

Best SQL Server course I ever had.. Thanks Venkat

To add; for the CTE we would not want to use RANK as opposed to DENSE_RANK because RANK would skip rankings for duplicate salary values. For example if we were to use RANK we would skip over rank 3 because there are two values for 70,000 in salary and searching for the 3rd highest salary would yield nothing

We can also use this method SELECT name, salary FROM employee e1 WHERE N-1= (SELECT COUNT(DISTINCT salary) FROM employee e2 WHERE e2.salary > e1.salary);

simply superb.. i got lot of confusions before while these kind of queries... now got it.. really super and Thankyou so much...

best video of this nature and ive watched a few today. Well done sir

Amazing Teaching Technique I Learned within min My All doubts Clear within min

I watched many lecture but finally i understand subquery concept

Thanks Venkat, Excellent You are awesome dear, not only this one but all the other videos specially the SQL are fabulous and very much informative and very easy to understand. Your way of teaching is very much understandable and easy. We have learnt a lot from your video series on all the programming fields C#, SQL Server etc. Be blessed and thanks a lot.

OMG why did I just discover your channel... I have so much to catch up on.... *Goes on to watch all your vids*

Hi Venkat, I have recently come across your videos. these videos are actually gold mines. Thank you very much for explaining is such a simple way.

Thank you, you are providing good SQL interview question & Answer ,It's was helped to me, i have crack the interview.

-- Return the 2nd highest salary in the employee table- (even we can use NOT IN) Select Max(salary) From employee Where salary NOT IN (select Max(salary) from employee) Correct me if am wrong !

Thinking about it, I'd prefer to use DISTINCT w/ LIMIT.. OFFSET with the sub-query instead of writing two ORDER BY statements. And a sub-query's function here would be to pull the data related to that particular salary.

Still useful in 2021. Very well explained. Thank you🙏

If you use a group by Salary then the row_number() works. WITH Result AS ( SELECT ROW_NUMBER() OVER(ORDER BY Salary DESC) AS RID ,Rate FROM Employee GROUP BY Salary --- You need this line ) SELECT Salary FROM Result Where RID = 8 -- your number here

I really liked your way of explaining the query ...appreciate thanks

Still helpful, 2022. My favorite teacher by far.

I have four questions can you help with this please 1. What’s the largest dataset you have worked upon in the past? 2. How can you find an abstract book from personal library? 3. How do you find a record is from which table? 4. What is a query plan?

We can also use Min(Salary) instead of Top 1 while using sub query but both will give same result.... Something like below Select MIN(Salary) from (Select distinct top 15 (N-can be any number) Salary from Employee Order by Salary desc) Result

You can get the result in a more simple way by using LIMIT keyword, For example getting top 5 high salaries (SELECT * FROM employees ORDER BY salary DESC LIMIT 5)

I was given these two tables:- (A) month, category, primeflag, shippedunits (B) month, primeflag, shipcost I was asked what assumptions would you make? If you wanted to write a select into statement for the following table what would you write:- month, category, primeflag, shippedunits, shippedcost

Hello Venkat, I am thank full to you that you create a great tutorial and yes this is the only tutorial which lead me to a great job. The efforts you made are very helpful for many peoples. You Rocks Man.

Simplest example*** _________________________ Suppose for 2nd Highest Salary 1) Select max (sal) from employee where sal NOT IN (select max (sal) from employee) 2) select * from employee E1 where 2= (select count(distinct(E2.sal))from employee E2 where E2.sal >=E1.sal

your videos are very effective n helpful for beginners...I follow your tutorials to study IT.Thank you.

By Referring your videos this also can be done by offset. to Find 4th highest. select distinct salary from tblEmployees order by salary desc offset 3 rows fetch next 1 rows only

I became fan of you... You explain everything so nicely...

we can actually do this using row_number() SELECT salary FROM ( SELECT salary, ROW_NUMBER() OVER(ORDER BY salary DESC) as rownum FROM ( SELECT DISTINCT salary from Employees ORDER BY salary DESC ) AS DISSAL ) AS OuterGrid WHERE OuterGrid.rownum = @NthPosition

I revisited this video to refresh my mind. I enjoyed watching this video and recommend others to watch. Topic has been explained in crisp and clear. Thanks Guru Venkat

I enjoyed watching this video and I recommend others to watch since it has been clearly expressed concept Very helpful in interview and also in real life

A very simple sub query would be, Select * from Employees where SALARY = (SELECT MIN(SALARY) from (select DISTINCT TOP 2 salary from Employees ORDER BY SALARY DESC) as result )

We can use rows between unbounded precedings and current To handle duplicate values

Hi Venkat. Can you please make a video on 1). finding growth percentage for each item over the months [ex. column name can be: month, item, quantity] and 2). find the personid who appeared for the appointment 3 time continuously [ex. column name can be: date, person_id, status]

what an explanation! hats off... you got very good communication

Hi Venkat. Am thanking you for offering these tutorials, ur providing a very good explanation, really it's helps a lot! i am sharing one of the interview question which i was faced! Can you plz make a video session for clear explanation ---How to avoid Divide by zero error ? also i wanted to show the null value results as NULL is that possible ? Create table tblcompute ( Numerator int not null, Denominator int not null, Result as (Numerator / Denominator) ) Go insert into tblcompute values (3,0) when we issue a select querry Select * from tblcompute Msg 8134, Level 16, State 1, Line 1 Divide by zero error encountered. even when we add new rows also getting the same error insert into tblcompute values (10,2) Finally i come up with these querys! Select * from tblcompute where Result !=0 SELECT Numerator , Denominator, NULLif(Result, 0) as Results from tblcompute SELECT Numerator , Denominator, NULLif(Result, null) as Results from tblcompute Can you plz give me an in depth explanation for this scenario !

Thanks guys.. your videos are awesome.. U also helped me to land SQL developer job..

In another video someone explained 2nd highest salary like this: select max(salary) from salary_table where salary not in (select max(salary) from salary_table) this works only for 2nd highest.

Best teacher . Please make more videos . Thank you so much sir. Very helpful videos . Nice explanation.

select sal from (select sal, dense_rank() over (order by sal desc) as a from emp) where a = n* n* is the nth no.

Thank You very much Sir. Your videos are better than some paid courses.

you are awesome ...👍👍🙏🙏 respect man..just because your way of explaination is to step by step which becomes easy to understand thanx man..

Hats off to the way you explain...thank you very much Venkat.

Thanks Kudvenkat for your clear description.

Bro, you have explained it so well. Perfect!!

Clear explanation, thank you, have a pleasant day sir

Hi Venkat, I am thank full to you that you create a great tutorial and yes this is the only tutorial which lead me to a great job .. Can you please tel how we can swap row date in single table using only select statement , This is one of interviewer question . Ex -- in table have 5 row . 1 - ranga , 2- Venkat , 3 - Manoj ,4 - Ranga 5- Venkat , we need to swap where ever we find ranga and next to that venkat we need to swap output like venkat next ranga .

It is simply wow....this is the first time I understood this answer. :-)

Great Sir, a true professional as well as a proficient person with awesome convincing skill (y)

with cte as ( select dense_rank() over(order by salary desc) as Rank#, * from employees ) select distinct salary from cte where Rank# = 3 ^This makes for the quickest approach to me, coding wise. Not sure on performance...

Venkat is the man, too bad we do not hear him any more.

If anybody use SSMS 2012, Result word does not work with CTE, Please try to Use another CTE name because "result" is reserved word. If you use older version SSMS, No problem with Result word

Thank you! This helped me with understanding CTEs as well.

using ROW_NUMBER() we can achieve the result by adding Group By clause : WITH RESULT AS ( SELECT SALARY, ROW_NUMBER() OVER (ORDER BY SALARY DESC) AS ROWNUMBER FROM EMPLOYEES GROUP BY SALARY ) SELECT SALARY FROM RESULT WHERE ROWNUMBER = 3 Correct me if I'm wrong.

You can get the highest salary by ordering DESC by salary, and doing a LIMIT N,1 and you use the offset to order, hence you don't need a sub-query. This assumes that you use a valid position, you can calculate the total rows also, and show a warning if N is invalid, because the row will return NULL :) And for duplicates you can just use DISTINCT

Ur superb man ., the way you explained abt dense rank its awesome

Excellent.... Ek Number... Concept Cleared....

Hi Venkat , You are really wonderful , Thanks for helping us , May God bless you.

Can you please do SAS tutorial....I havnt found a single youtube tutorial that helped me. I find your tutorials very clear and to the point.Please upload SAS ...Please...:)

Wow thre functionalities explained with example clearly. DENSERANK, CTE, ROW_NUM. Thanks a lot

Good. BTW, it's highly recommended to use semicolon before WITH clause, then you won't need GO. Especially if you are writing a multiple stage query. Also, using Dense_rank is more preferable - in case, say, you need to write a function, in which N is a variable. You can't do it with Top (like - Top @salary_no). Or, better to say - it's not that simple. The problem is that Dense_rank is much slower.

Very helpful and very clearly explained! Thank you for making this video. Please keep up the good work!

Select max(Sal) from Emp where Sal not in (select Sal from emp where rownum

This video is 100% on point and saved my time ! Thanks a lot :D

Awesome teaching technique. Very useful channel. Keep up the good work.

Explained in most decent way !

Hi I have a question There are 5 teams (a,b,c,d&e) each team will only one match against each other eg if a and b have played then they will not play again. Result table will be like this A B A C A D A E B C B D B E C D C E D E Write a SQL query to show the following output

Awesome man ,crystal clear and crisp !!!

Sir, Kindly make video on indexes, I am asked in interviews about materialized views, Kind of indexes used, stored procedure practically, Kindly make videos on Realtime scenarios approaches... Will be helpful Thank you

You are the best one. Keep Going bro.

Thanks for your video tutorials which helps me lot really. I recently was asked questions below . can you please post some videos on this 1) what is fill factor 2) what is dirty read

We can use row_number function as well with partition by..

Simple elegant and Great explanation..

How do we calculate the N highest salary using a correlated subquery without using top/limit syntax? Also, I would appreciate it if you can explain the correlated query. Thank you.

Hi Venkat. Thanks for the video. I have a doubt here. For finding the 2nd highest salary, you have first order by Sal Desc in subquery and then in main query you have order by Sal Asc. Instead of 2 steps why dnt we directly Order by Sal Asc in sub query? Can we do that?

-- Find the second highest salary: --Option 1 using a Common Table Expression (CTE): With cte_Ranking_From_Highest AS (SELECT EmployeeId, EmployeeName, AnnualSalary, DENSE_RANK() OVER(ORDER BY AnnualSalary DESC) AS Ranking FROM Employee ) SELECT EmployeeId, EmployeeName, AnnualSalary, Ranking FROM cte_Ranking_From_Highest WHERE Ranking = 2 --Option 2: Subquery: Note, You can Use DENSE_RANK(). SELECT EmployeeId, EmployeeName, AnnualSalary, Ranking FROM (SELECT EmployeeId, EmployeeName, AnnualSalary, DENSE_RANK() OVER(ORDER BY AnnualSalary DESC) AS Ranking FROM Employee) AS t WHERE Ranking = 2 Notes: Common Table Expression (CTE) is an in-memory function that doesn't require any cleanup as would temp tables. Subqueries are not optimized in SQL Server like Joins are, so I would use a CTE instead as it is an in-memory function. The reason why the best choice function is DENSE_RANK() is because if you have two salaries that are the same, it will still rank them appropriately.

You are Awesome sir, u r great in your explanation

sir,these all tutorials are really nice... HATS OFF!!!