Thank you for the resources, they are very helpful
@Csharp-video-tutorialsBlogspot8 жыл бұрын
+Abel Mengistu Thanks a lot for stopping by to provide your feedback. This means a lot to me. I am really glad you found the video tuorials useful. All the Pragim Tech video tutorials are organised in to playlists, which help you find the videos you are looking for easily kzfaq.infoplaylists?view=1&sort=dd You can also order DVDs or download all the videos and slides for offline viewing using the link below www.pragimtech.com/kudvenkat_dvd.aspx Image version of the slides and text Version of the videos are on my blog csharp-video-tutorials.blogspot.com Tips to effectively use our free channel. kzfaq.info/get/bejne/r51oY7Cozb-bYKc.html To receive email alerts, when new videos are uploaded, please subscribe to my youtube channel. kzfaq.info If you like these videos, please click the THUMBS UP button below the video. We like to see these free video tutorials helping others as well. Please share the link with your friends and family who you think would also benefit from them. Thanks Venkat
@techspace56426 жыл бұрын
Another way to nth highest salary is this : Select min(Salary) from (Select distinct top 7 Salary from Employee order by Salary desc) Result :: :: Like my comment if my query works
@danieltaye65665 жыл бұрын
@@techspace5642 It doesn't. 'cause min pulls the minimum by value not by order
@brackishdreams Жыл бұрын
Thank you so much
@mangorez04 жыл бұрын
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!
@arunkaiser Жыл бұрын
I am from 2023 still found it useful
@TheSourav19867 ай бұрын
@@arunkaiser Now I am fin 2024 still found relevant
@kripamishra10017 ай бұрын
Still in 2024...
@rohitpatil19937 жыл бұрын
When it comes to SQL learning ..Your Channel is the only thing comes to my mind. Really appreciate your hard-work.
@codewithcreativecoder88347 ай бұрын
it's 2024 and this video is still helpful for me. Thank you so much kudvenkat
@gokulm594Ай бұрын
yep still in big 4 they ask these type of questions haha
@mirrapriya8 жыл бұрын
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.
@Csharp-video-tutorialsBlogspot8 жыл бұрын
+mirrapriya Wow....Congratulations. Very happy for you. I am honored, when you say these videos helped you to get the job. Thank you very much for taking time to share the good news. Good luck and all the very best with your new role.
@sirsyed35028 жыл бұрын
+kudvenkat hi sir hope u well i have a question i have a website name web1 and database my website is live and hosted on domain i have another website web2 also live n hosted both database are different my task is that perform some search in web2 and the the result will be as the stored question and answer in web1 database it goes on web1 database n shown it on web2 page to retrieve the search result as google search technique its crawler or google cse technique please kindly guide me
@shirshodatta28863 жыл бұрын
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
@shantilal_suthar7 жыл бұрын
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
@prasadm11176 жыл бұрын
Bro please help me wr r u Staying I'm looking for job for SQL Server DBA
@rituagrawal39506 жыл бұрын
Shantilal Suthar hi
@TableauTip5 жыл бұрын
kzfaq.info/love/MyjwXfdnf1ezjJyUDHXrsA
@hanifhani15 жыл бұрын
same here
@1256dimple7 жыл бұрын
the way you explain any concept is simply amazing! thank you so much!
@ga78535 жыл бұрын
This is just a beauty, I really loved the new Dense_Rank Function, kudvenkat you are the man. Thanks a million.
@sarahlee18775 жыл бұрын
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!
@Csharp-video-tutorialsBlogspot5 жыл бұрын
Hello Sarah - Thank you very much for taking time to give feedback. This means a lot. I am very glad you found the videos useful. Good luck and all the very best with your programming career.
@pgadige9 жыл бұрын
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
@tauhidhussain86292 жыл бұрын
great 👍😊
@poojagore258211 ай бұрын
Watching this vedio after 9 years, still this question is asked in every interview ... Thank you for explaining in such a giod way 😊
@humanitylover792 Жыл бұрын
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
@shanks24a36 жыл бұрын
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.
@rajasekharreddy478 Жыл бұрын
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.
@letmeexcel59433 жыл бұрын
Kud Venkat is Good Venkat. Thanks for all the efforts you have put in to make these videos.
@vishnuvamshi46452 жыл бұрын
THANKS ALOT CLEARED CONFUSION.... BEST PART OF YOUR VIDEO IS GIVEN COMPLETE DETAILS FROM TABLE CREATION TO EXECUTION IN DESCRIPTION WHICH SAVES TIME
@rajeshmanivannan53133 жыл бұрын
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.
@1MYNAMEISKHAN8 жыл бұрын
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);
@nonoobott86022 жыл бұрын
You're a genius. I've been searching for this. Your explanation is everything. Thanks for sharing
@KathakathanKarya Жыл бұрын
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;
@sonikagoyal94795 жыл бұрын
Thankyou Venkat . You can make anyone to get offer letter with nice packages . I am so confident today just because of you
@Csharp-video-tutorialsBlogspot5 жыл бұрын
Wow! That's great news Sonika. Will be delighted to know if you made it. Thank you for sharing.
@sagarpanda6458 жыл бұрын
>select ename,sal from emp where sal = ( select max(sal) from emp where level=&levelno connect by prior sal>sal group by level;
@MrJonnis137 жыл бұрын
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)
@spicytuna086 жыл бұрын
i don't think you need desc operator. min() will find the minimum.
@ahmadganteng74354 жыл бұрын
Best SQL Server course I ever had.. Thanks Venkat
@MrTrojis7 жыл бұрын
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
@saisai-yo4nv3 жыл бұрын
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);
@poomanivenugopal31932 жыл бұрын
simply superb.. i got lot of confusions before while these kind of queries... now got it.. really super and Thankyou so much...
@JymVer Жыл бұрын
best video of this nature and ive watched a few today. Well done sir
@beautyofnature67092 жыл бұрын
Amazing Teaching Technique I Learned within min My All doubts Clear within min
@Dk-gl7rq2 жыл бұрын
I watched many lecture but finally i understand subquery concept
@KhalidAfridi110 жыл бұрын
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.
@nawaltarekmatarid88805 жыл бұрын
OMG why did I just discover your channel... I have so much to catch up on.... *Goes on to watch all your vids*
@himanshurustagi73515 жыл бұрын
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.
@SunilChintu-sh2ct5 ай бұрын
Thank you, you are providing good SQL interview question & Answer ,It's was helped to me, i have crack the interview.
@poornaps6 жыл бұрын
-- 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 !
@ganeshmhaske495 жыл бұрын
Yes, it's correct. Just a little correction put TOP 1 in-front of Max(salary) o.w it will return all the salaries which are less than top max one.
@NancyMancarious8 ай бұрын
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.
@gardeningforeveryone85853 жыл бұрын
Still useful in 2021. Very well explained. Thank you🙏
@fcanapa4 ай бұрын
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
@AbhishekAbhi-mc1dv5 жыл бұрын
I really liked your way of explaining the query ...appreciate thanks
@yamaya5136 Жыл бұрын
Still helpful, 2022. My favorite teacher by far.
@delmensah35444 жыл бұрын
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?
@vijayvenkat963 жыл бұрын
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
@shamsularifeen1667 жыл бұрын
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)
@meghabhatnagar37847 жыл бұрын
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
@AbdulWahab-li9ri8 жыл бұрын
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.
@chamelion1red8 жыл бұрын
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
@tarapanthi96154 жыл бұрын
your videos are very effective n helpful for beginners...I follow your tutorials to study IT.Thank you.
@arvindkumartegampure41708 жыл бұрын
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
@riyankabhowmick6977 жыл бұрын
I became fan of you... You explain everything so nicely...
@narendrakumarbiswal20233 жыл бұрын
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
@krismaly630010 жыл бұрын
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
@krismaly630010 жыл бұрын
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
@yaarkhakf8 жыл бұрын
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 )
@ilyasnaghiyev70388 жыл бұрын
WHAT IF HIGHEST SALARY DUPLICATED 10 TIMES?THEN TOP 2 WILL BE STILL HIGHEST SALARY.
@yaarkhakf8 жыл бұрын
No it won't because of the 'distinct' keyword.
@gokukanishka2 жыл бұрын
We can use rows between unbounded precedings and current To handle duplicate values
@deeptigautam97132 жыл бұрын
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]
@venureddy82184 жыл бұрын
what an explanation! hats off... you got very good communication
@varma87748 жыл бұрын
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 !
@aravinds38083 жыл бұрын
Thanks guys.. your videos are awesome.. U also helped me to land SQL developer job..
@gshrdy54156 жыл бұрын
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.
@miteshjain564 жыл бұрын
Best teacher . Please make more videos . Thank you so much sir. Very helpful videos . Nice explanation.
@sabyasachisahu76638 жыл бұрын
select sal from (select sal, dense_rank() over (order by sal desc) as a from emp) where a = n* n* is the nth no.
@ayandey47423 жыл бұрын
Thank You very much Sir. Your videos are better than some paid courses.
@vishaljaiswar39134 жыл бұрын
you are awesome ...👍👍🙏🙏 respect man..just because your way of explaination is to step by step which becomes easy to understand thanx man..
@nitincsawant10 жыл бұрын
Hats off to the way you explain...thank you very much Venkat.
@shekharmishra99038 жыл бұрын
Thanks Kudvenkat for your clear description.
@geoffluw3 жыл бұрын
Bro, you have explained it so well. Perfect!!
@hopeislife16514 жыл бұрын
Clear explanation, thank you, have a pleasant day sir
@Krranganathakr8 жыл бұрын
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 .
@MasterjiClassy6 жыл бұрын
It is simply wow....this is the first time I understood this answer. :-)
@vijaydubey17348 жыл бұрын
Great Sir, a true professional as well as a proficient person with awesome convincing skill (y)
@sonderis5 жыл бұрын
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...
@danieltaye65665 жыл бұрын
I think it doesn't work. 'cause there is no 'salary' column in the cte
@sonderis5 жыл бұрын
@@danieltaye6566 The cte contains all columns from employees in addition to the dense rank. see the asterisk?
@danieltaye65665 жыл бұрын
@sonderis got it thanks buddy.
@ga78535 жыл бұрын
Venkat is the man, too bad we do not hear him any more.
@muktarhussain71969 жыл бұрын
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
@adrolee16 жыл бұрын
Thank you! This helped me with understanding CTEs as well.
@aritrachatterjee4874 жыл бұрын
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.
@robertuniqid5 жыл бұрын
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
@jamjalajagadeesh50983 жыл бұрын
Ur superb man ., the way you explained abt dense rank its awesome
@dattakashiwale4 жыл бұрын
Excellent.... Ek Number... Concept Cleared....
@rashmi9338 жыл бұрын
Hi Venkat , You are really wonderful , Thanks for helping us , May God bless you.
@LearningwithIzaan6 жыл бұрын
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...:)
@krismaly9 жыл бұрын
Wow thre functionalities explained with example clearly. DENSERANK, CTE, ROW_NUM. Thanks a lot
@igalye7 жыл бұрын
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.
@UdayKumar-lt1nu3 жыл бұрын
Please write a that query in simple way
@ryanmariner4 жыл бұрын
Very helpful and very clearly explained! Thank you for making this video. Please keep up the good work!
@Jaffa13 жыл бұрын
Select max(Sal) from Emp where Sal not in (select Sal from emp where rownum
@LetCode966665 жыл бұрын
This video is 100% on point and saved my time ! Thanks a lot :D
@balrandhawa71284 жыл бұрын
Awesome teaching technique. Very useful channel. Keep up the good work.
@neel90978 жыл бұрын
Explained in most decent way !
@nazishsiddiqui35585 жыл бұрын
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
@sanjarmatin62276 жыл бұрын
Awesome man ,crystal clear and crisp !!!
@bikashpadhy94622 жыл бұрын
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
@amkytube52423 жыл бұрын
You are the best one. Keep Going bro.
@sabarinathkr76037 жыл бұрын
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
@faridjafri53853 жыл бұрын
We can use row_number function as well with partition by..
@rajeshgaikar75356 жыл бұрын
Simple elegant and Great explanation..
@kritiverma13423 жыл бұрын
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.
@saylishah67087 жыл бұрын
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?
@netsurfers93573 жыл бұрын
-- 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.
@nagp35113 жыл бұрын
You are Awesome sir, u r great in your explanation
@vipulzambare532910 жыл бұрын
sir,these all tutorials are really nice... HATS OFF!!!