Thanks a lot for the kind words. I have a small request, I am working on this new channel and will really appreciate if you watch and subscribe to it. th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
This amazing video you made 6 years back from now and even today it is very relevant and helpfull. You have such an amazing explanation talent, why you dont made video on SQL queries regularly!!! .
Excellent !!!. Thank you very much for deep explanation. Solution of exercises 1: select min(salary) over (partition by job_id) as min_sal ,a.* from emp a; 2: select e.* ,(salary+salary*.10) as Bonus from ( select lag(hire_date,1,null) over (partition by department_id order by hire_date ) as old_emp, a.* from emp a) e where e.old_emp is null; 3:select count(*) over (partition by manager_id ) as cntofReportee,a.* from emp_bk a 4:select a.*, avg(salary) over (partition by department_id) from emp a where (months_between(sysdate ,hire_date) /12)> =12; I am confused about order by ,when to use and when not .Please clear my doubt.
I will review them real quick and will let you know :), for order by try practicing an example using lag and lead function it shall clear all your doubts :)
Hello In your video ...at the point where you are explaining cumulative sum, you said order by doesn't matter but in fact it matters a lot if you don't specify order by clause it will not provide the cumulative sum of salaries instead it will give sum of salaries department wise. Other than that it was a very helpful video thanks a lot man...keep up good work...
Thank you for the video. Really helpful to understand OLAP basics. On a light note, when you said at 18:35 "to come up with an easier answer to find the money spent by company as salary to employees", well, instead of cumulative, we can just use SELECT SUM(SALARY) FROM EMPLOYEE; and if we want department wise, we can use the same with GROUP BY clause. I believe there is a fine line where we should use all these OLAP functions or aggregate functions.
Correction -- @12:05 you said Previous value -- so in LEAD() it should be Next row not previous value. if its a LAG() I can understand. Hope this is useful to the viewers.
select*from ( select lag(hire_date) over(partition by department_id order by hire_date) as old_emp,a.*, (salary+(0.1*salary)) as new_salary from employees a order by employee_id) where old_emp is null order by employee_id
Thanks for the kind words, I have a small request I am working on a new youtube channel " An Indian Abroad" . I would really appreciate if you watch and subscribe to it. th-cam.com/video/HBQHekM1U2c/w-d-xo.html
I am glad shaik abdul arif I could be of any help. I have a small request, I am working on this new channel please do watch share and subscribe to it. Your support is my motivation. th-cam.com/video/Oz4TbQwrrOY/w-d-xo.html
Thanks Mahesh for the kind words. I have a small request, I am working on this new channel please do watch and subscribe to it. Your support is my motivation. th-cam.com/video/JobONiVWA9Q/w-d-xo.html
--Q1 select * from ( select min(salary) over (partition by job_id) as min_sal ,a.* from employees a ) A where salary = min_sal; --Q2: select e.* ,(salary*1.10) as Bonus from ( select dense_rank() over (partition by department_id order by hire_date nulls last ) as old_emp_rank , a.* from employees a order by a.employee_id,department_id desc) e where e.old_emp_rank = 1 ; --Q3: select count(*) over (partition by manager_id ) as cntofReportee,a.* from employees a; --4: select a.*, avg(salary) over (partition by department_id) from employees a where (months_between(sysdate ,hire_date) /12)> =12;
Thanks Raj for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
Thanks amrita for the kind words,I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. th-cam.com/video/GnVn3mPBRz4/w-d-xo.html
1 : select * from ( select e.*,min(e.salary) over(partition by e.job_id) as min_salary from employees e order by e.job_id ) where salary = min_salary; 2: select a.*,((a.salary * 10)/100) as bonus from ( select e.*,lag(e.hire_date) over(partition by e.department_id order by e.hire_date) as oldest_emp from employees e) a where a.oldest_emp is null;
Excellent Sir i am working on production environment i had faced so many times this difficulties bu after watching this i definetely improved my knowledge. Thanks you so much please make more videos about debugging and give some shortcuts while using plsql developers
Thanks durwesh for the kind words, I will certainly work on more videos.Meanwhile I have a small request, I am working on this new channel please do watch and subscribe to it. Your support is my motivation. th-cam.com/video/Oz4TbQwrrOY/w-d-xo.html
Find most recent employee Select * from emp where hiredate in ( select max(hiredate) from emp group by deptno); - this is done in Sub Query. No need to introduce Self join with join condition.
Yes correct , but what if we have 2 employees in same department hired on same date ...for example in this scenario department 80 hired 2 employees on same day so it should show 2 employees with analytical function as recent joinee but it shows only one .
Hi Shivkanya , in this scenario it is good to use the dense_rank function , below query can be used :- select * from (select e.* , dense_rank() over(partition by deptno order by hiredate desc) as recent_joinee from EMP e) where recent_joinee=1; even if 10 people joined on the same date , there rank will be same
Answer 1:- select * from( select a.*, min(a.salary) over(partition by job_id ) min_sal from employees a order by employee_id ) where min_sal in salary Answer 2:- select b.*,((b.salary*10)/100) as bonus from( select lead(hire_date) over(partition by department_id order by hire_date desc) oldest_joinee, a.* from employees a order by employee_id )b where oldest_joinee is null
Hi Vivek, I was looking the vedio for the analytic function vedio no 19 and it was really good and help full. I jst want to ask you that you have not uploaded the next analytic vedio that might be helpful. I was searching that but still I am not getting it in your channel.. If possible please upload it for which i will get notification. Really i am very much interested to get more and more knowledge on analytic function and much more about oracle... If you will publish the vedio class wise or pattern wise for every concept then i would be a great help for the people like me those who are very much interested to get knowledge.. Please requesting you to upload the remaining vedio for analytic function for which it would be helpful.
sure Ranaj, I will try to make some more, if you have specific question let me know . I am working on this new youtube channel"An Indian Abroad", please support me by watching subscribing and sharing. th-cam.com/users/AnIndianAbroadd
Thank you for the excellent explanation, I would like to ask you if the following query solve the fourth point in the exercise ? select b.* , avg(salary) over (partition by job_id order by hire_date range 4380 preceding) as Average_Salary from EMPLOYEES b ;
Thanks for the kind words.I will review the query soon. I have a small request I am working on this new youtube channel "An Indian Abroad" it will really help me if you watch and subscribe to it. th-cam.com/video/HBQHekM1U2c/w-d-xo.html
You can use this query to find the recent joinee: SELECT department_id , max (hire_date) recent_hiredate , min (employee_id) keep (dense_rank first order by hire_date desc) recent_employee_id FROM employees GROUP BY department_id ORDER BY department_id; for a more detailed resultset, use this SELECT department_id , MAX(hire_date) recent_hiredate , MAX(employee_id) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_emp_id , MAX(last_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_last_name , MAX(first_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_first_name FROM employees GROUP BY department_id ORDER BY department_id;
Thanks manish for the kind words, I have a small request. I am working on this new TH-cam channel.I would really appreciate if you watch and subscribe to it. Your support is my motivation. th-cam.com/video/0sLU8YaYr4w/w-d-xo.html
So the main advantage is to avoid joins in your query, right. Now, I have a DataWarehouse 3rd normal form with 2000 tables, will AF be an advantage for me?
3rd normal in DW , your data would be highly normalized. If you fine yourself using self join often you can consider analytical functions. But with 3NF you mayn't get too much help with analytical functions.
Thanks for the kind words, I have not posted the answers but I will try and post soon. I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it. th-cam.com/video/HBQHekM1U2c/w-d-xo.html
Hi bro I am fresher 2021 passed out student and I finished Oracle developer certification.And how to became a SQL developer what are the ways to finding jobs?please reply bro...
Hi Gunal, The job market is hot so you can find a job if you bring some "Value" Try learning SQL, Excel and one ETL or reporting tool, Polish your communication skills and apply at linkedln for every single job. All the very best
Can you please let us know that how to install the same set of data and table in personal laptop? This video is so knowledgeable and it helped a lot in order to Analytical functions!! Would like to appreciate your efforts!!!
Thanks a lot for the kind words :), I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad". th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
Thanks Abhishek for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
hi your videos have good stuff, thanks for providing it in your channel. i have moderate knowledge in sql,plsql' but i don,t know how real time applications are build. will you please make a video in it.
ques: select oldest employee for each designation and give them 10% BONUS? SELECT ID,NAME, ROLE_ID, SALARY , CREATED_DATE, OLDEST, CASE WHEN OLDEST IS NULL THEN (SALARY*(10/100)) WHEN OLDEST IS NOT NULL THEN 0 END AS "BONUS" FROM ( select a.id,a.name, a.role_id,a.salary, a.created_date, LAG(created_date) over (partition by role_id order by created_date) as "OLDEST" from employees a) order by id; Please let me know if the above is right
Thanks a lot for the kind words. I would really appreciate if you help me in my new Initiative "An Indian Abroad" by subscribing viewing and sharing. th-cam.com/users/AnIndianAbroadd
you need to specify order by clause in almost every analytical function other wise it will give you an error. Please help me in building my new channel by subscribing sharing and watching. th-cam.com/users/AnIndianAbroadd
Please check this video it should help . th-cam.com/video/-DDBt48G2ug/w-d-xo.html I have a small request I am working on this new youtube channel "An Indian Abroad" It will really help me if you watch and subscribe to it th-cam.com/users/AnIndianAbroadd
Do provide feedback. 1.select min(salary) over(partition by job_id) as min_sal , a.* from employees a order by a.job_id; 2.select employee_id,department_id, hire_date,salary , salary * 1.1 from (select a.*,rank() over(partition by department_id order by hire_date ) as old_emp from employees a order by a.department_id,a.hire_date) where old_emp=1; 3. select count(employee_id) over(partition by manager_id) as count_emp , a.* from employees a order by a.manager_id; 4.select avg(salary) over(partition by job_id) ,a.* from employees a where add_months(trunc(sysdate) , -12*12) > a.hire_date
2.select * from (select lag(hiredate) over (partition by deptno order by hiredate) old_emp,sal*.10 bonous_sal,e.* from emp e) where old_emp is null try this
Yup you are right Varun, In fact in this video I have shown 3 ways of writing this query along with a lot of different queries th-cam.com/video/oiN8rfrWMwU/w-d-xo.html
Hi.., the explanation is clear. But can you please tell on sum(salary) using order by employee gives sum of last two employees like window function. Similarly i am confused on window as avg is done three rows where we require only for two rows Please explain Does order by clause has these effects?
SELECT COUNT(*) over (partition by MANAGER_ID),EMPLOYEE_ID, FIRST_NAME, LAST_NAME FROM Employees order by EMPLOYEE_ID ; is this the correct answer for question #3 ?
One question - For cumulative salary you didn't mention the significance of "Order By" in over() clause, which is most imp. This would explain how sum function provides cumulative sum.
Thanks sameer, I will review what I have mentioned. I have a small request I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it. th-cam.com/video/11DK-oyRql0/w-d-xo.html
thank u for ur useful video...in the sample scenario u asked to , Select the details of employees with minimum salary on their designation...i got the query as follows ---- select a.*,min(salary) over (partition by job_id) from employees a order by job_id;but it still retrieves multiple JOB_IDs with all salary ....BUt it need to the minimum salary according to each designation...how to correct it??pls calrify
I will take a look at the query Fathima and will get back to you soon, you haven't specified the order by clause along with partition by but that shouldn't cause any issues. Meanwhile I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it. th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
Hi ..nice video about analyitcal functions..thank you...instead of "lead(hire_date)" can'nt we use "max(hire_date)" after partitioning by as below : select MAX(hire_date) OVER(PARTITION BY department_id) ,a.* from employee a
HI Sateesh in this particular case max can be used, but max and lead are very different functions in general. Let's say I am trying to analyze how my aircarft flies throughout the day. Source|Dest|dept|arrival|aircraft_type Delhi|Mumbai|7:00|9:00|787 Chennai|Hyderabad|14:00|16:00|787 Mumbai|Chennai|10:00|12:00|787 Hyderabad|Delhi|17:00|19:00|787 I can use lead function here to identify my aircraft positions which can't be done using max. I hope this clarifies the difference. I need a small help I am working on this new youtube channel and would really appreciate if you watch and subscribe to it. th-cam.com/video/11DK-oyRql0/w-d-xo.html
@@TechCoach - But your logic of using LEAD will fail if there are multiple people with the same hire date in the same department. I used RANK() which I think works better here because the same RANK is assigned to multiple rows if they have the same value for the concerned column.
Hi great video on analytical functions, just got one issue. In the video, at 22:18 you said that it is going to compute the average salary of Neena by taking average of three salaries, whereas the function is row 2 preceding, should not it be calculating the average salaries only using only 2 prior salaries, the salary of Neena and only one before her? Please give clarification.
Hi Aditya, why don't you try running the statement in oracle once? I think that will clarify all your doubts. If you still have any questions please do let me know. I will be happy to help :)
@@TechCoach Sure! Thanks for your reply. Also, if you have time please make an extended video on this topic, explaining everything, every function. Thanks
@@AdityaVermaopenheartedme you can check asktom on google, there is a course on udemy that explains analytical functions in detail as well. I will try posting some practice scenarios in the next couple of days.
also i tried 2nd question from ur assignment (oldest employee department wise) using min(hire_date) ... output comes with multiple different date for each department.
@@TechCoach It will give me the total count of the rows of the table but I need distinct rows, can you tell me how can I implement that? I need a simplified version of this command : select count(*) from (select distinct(*) from employees)
sum(salary) : it should give total sum of salary department wise, for cumulative sum i think we add 'rows unbounded preceding' in analytical function, can you please clear my doubt.
Great Question Ashutosh. First Explanation: once you have used partition by department that means the sum function will be applied on individual departments, now here order by is of vital importance actually because of it the 1st row doesn't have any preceding row so we will get just its salary sum, the 2nd row will have 1 preceding row so it will have the sum of 1st and itself and so on. Try running the query on your own it will give you the correct values. Rows unbounded preceding : yes you are right there are multiple ways to achieve the same result you can use Rows unbounded preceding clause instead of partition by departments to obtain the same result, In Rows unbounded preceding the order by clause is used to describe the window instead of ordering. sum(salary) over (order by dept_no rows unbounded preceding) cum_sum will give you cum_sum of salary department_wise, Because here it will continue to do cum_sum as long as it find the same dept_no, As soon the dept_no changes it will again cum_sum for that dept.So here order by is acting as a windowing function instead of ordering. I hope this clarifies your doubt, I have a small request I am working on this new youtube channel "An Indian Abroad " and it will really help me if you watch and subscribe to it. th-cam.com/video/HBQHekM1U2c/w-d-xo.html Please share it with your friends as well, your support is my motivation.
Answer for homework: 1.select minimum_salary over (partitions by designation) as minimum_salary_by_des, e.* from (select min(salary) over(partition by department order by salary) as minimum_salary , a.* from employee a ) e; 2.select max(hire_date) over (partition by department order by hire_date) as oldest_employee , a.* from employee a ...after this how to proceed ? 3.select count(employee_id) over(partition by manager_id) as total_number_of_employee , a.* from employee a I know i am wrong on 2 and 3 one Please correct me what is the exact answer. I dont have toad or plsql developer with my right now.
select * from emp where (empno,deptno,sal) in ( select empno,deptno, min(sal) over (partition by job order by sal) T from emp a) select a.*,sal*1.1 as bonus from emp a where (empno,deptno,hiredate) in (select empno,deptno,min(hiredate) over (partition by deptno order by hiredate) H from emp) select a.*,count(empno) over (partition by mgr) from emp a order by mgr asc
Hi.. have any idea to know how can i find Query to generate Employee absent report for a given range of dates but.. not possible to query.. can you help me.
I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad". th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
im working in RMG sector in Bangladesh as a IT person.. every month many people absent continuously like 5 or more days.. so i calculate in those employe who absent continuously basent five or more days in a month ... i can't do this. I need your help to find out this.
yes it is and it's pretty easy, select * from ( select rank() over (order by salary) as rank1,e.* from employees ) where rank1=n please let me know if it helps. I have a small request I am working on this new channel "An Indian Abroad " I will really appreciate if you watch and subscribe to it. th-cam.com/video/IR6mVE181a4/w-d-xo.html
@@TechCoach Small mistake in above query.. Corrected in below.. Select * FROM (Select RANK() OVER(Order By Salary ASC) as Latest_Rank, e.* From employees e) Where Latest_Rank=&N;
@@a_mhite2 Don't use rank function if you use rank 24000 1 17000 2 17000 2 14500 4 If u want 3rd rank salary we don't get it So use dense_rank function Then we can query for whatever rank we want 24000 1 17000 2 17000 2 14500 3
Hi Sudhee, Unfortunately I don't have it handy right now, Majority of the tables used here are standard HR schema tables which comes by default when you install oracle express edition. I would recommend installing oracle express edition and trying the queries shown in video on those tables. I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it. th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
@Manga , Quite a few people havd answered them in comments. If you don't find them helpful please let me know I will be more than happy to provide them
@@TechCoach select e.*, round(avg(salary)) over(partition by department_id order by salary ) avg_sal from employees e ; shows from keyword not found where expected.
Pls anyone tell me how to solve this --> Q. The percentage of people who were handraisers for each campaign. For reference this is the table - Table: user_handraisers +--------------------------+--------+---------+ | name | type | comment | +--------------------------+--------+---------+ | campaign_date | string | | | userid | string | | | campaign_id | string | | +--------------------------+--------+---------+
Thanks Shanmuga for the kind words I have a small request I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well. th-cam.com/video/GnVn3mPBRz4/w-d-xo.html
Thanks Pankaj for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it. th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
![fellow fellow - พรุ่งนี้ไม่มีใครรู้ feat. INK WARUNTORN [OFFICIAL MV]](http://i.ytimg.com/vi/QP6JyjYx_W0/mqdefault.jpg)
I think these are one of the best videos available on TH-cam. Appreciate your efforts in making these videos.. And many many thanks
Thanks a lot for the kind words. I have a small request, I am working on this new channel and will really appreciate if you watch and subscribe to it.
th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
A legend himself 🙏🏻 Big thanks!
I am glad I could help :)
This amazing video you made 6 years back from now and even today it is very relevant and helpfull. You have such an amazing explanation talent, why you dont made video on SQL queries regularly!!! .
Thanks for the kind words buddy . I will try to make videos more often
Excellent !!!. Thank you very much for deep explanation.
Solution of exercises 1:
select min(salary) over (partition by job_id) as min_sal
,a.* from emp a;
2:
select e.* ,(salary+salary*.10) as Bonus
from (
select lag(hire_date,1,null) over (partition by department_id order by hire_date ) as old_emp, a.* from emp a) e where e.old_emp is null;
3:select count(*) over (partition by manager_id ) as cntofReportee,a.* from emp_bk a
4:select a.*, avg(salary) over (partition by department_id) from emp a where
(months_between(sysdate ,hire_date) /12)> =12;
I am confused about order by ,when to use and when not .Please clear my doubt.
I will review them real quick and will let you know :), for order by try practicing an example using lag and lead function it shall clear all your doubts :)
Hello
In your video ...at the point where you are explaining cumulative sum, you said order by doesn't matter but in fact it matters a lot if you don't specify order by clause it will not provide the cumulative sum of salaries instead it will give sum of salaries department wise.
Other than that it was a very helpful video thanks a lot man...keep up good work...
Thanks for this comment. I too thought the same
I made a mistake If I said that. My apologies.
Order does matter
The narrative is very well-paced, a very good presentation. Good to see such videos and hope we shall have more from you.
Thanks Devinder for the kind words :)
Thank you for the video. Really helpful to understand OLAP basics.
On a light note, when you said at 18:35 "to come up with an easier answer to find the money spent by company as salary to employees", well, instead of cumulative, we can just use SELECT SUM(SALARY) FROM EMPLOYEE; and if we want department wise, we can use the same with GROUP BY clause. I believe there is a fine line where we should use all these OLAP functions or aggregate functions.
Thanks for the kind words, I will review what I have mentioned at 18:35 :)
I would love your support for my new channel, Please watch subscribe and share with your friends.
th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
Vivek please continue your effort. Nice explanation.
Thanks a lot Madhavi :)
You are doing great . You are actually help me to understand it in easier way . 😃 Thank you so much brother.
Thanks buddy,
Try these 3 videos as well on the same topic, they will help you a lot :)
th-cam.com/video/7sJ3FooAuBE/w-d-xo.html
Correction -- @12:05 you said Previous value -- so in LEAD() it should be Next row not previous value. if its a LAG() I can understand. Hope this is useful to the viewers.
I second this; it should be next row.
Best Explanation of Analytical functions. Thank you!
Thanks a lot Rashi for the kind words :)
select*from (
select lag(hire_date) over(partition by department_id order by hire_date) as old_emp,a.*, (salary+(0.1*salary)) as new_salary from employees a order by employee_id) where old_emp is null order by employee_id
Wow didn’t know analytical function is so powerful until now
Thanks Patrick I am glad I could help
Excellent. You made my life easy. Thanks
Thanks for the kind words, I have a small request I am working on a new youtube channel " An Indian Abroad" .
I would really appreciate if you watch and subscribe to it.
th-cam.com/video/HBQHekM1U2c/w-d-xo.html
thanks for making best video, I haven't seen before, great job and appreciate your efforts
I am glad shaik abdul arif I could be of any help. I have a small request, I am working on this new channel please do watch share and subscribe to it.
Your support is my motivation.
th-cam.com/video/Oz4TbQwrrOY/w-d-xo.html
Excellent Vivek.. The best explanation over analytical functions..
Thank a lot...
Thanks Mahesh for the kind words.
I have a small request, I am working on this new channel please do watch and subscribe to it.
Your support is my motivation. th-cam.com/video/JobONiVWA9Q/w-d-xo.html
Very Good Explanation looking forward more videos like that. Given link is not working please provide link to download the data set
--Q1
select * from
(
select min(salary) over (partition by job_id) as min_sal
,a.* from employees a
) A
where salary = min_sal;
--Q2:
select e.* ,(salary*1.10) as Bonus
from (
select dense_rank() over (partition by department_id order by hire_date nulls last ) as old_emp_rank
, a.* from employees a
order by a.employee_id,department_id desc) e
where e.old_emp_rank = 1 ;
--Q3:
select count(*) over (partition by manager_id ) as cntofReportee,a.* from employees a;
--4:
select a.*, avg(salary) over (partition by department_id) from employees a where
(months_between(sysdate ,hire_date) /12)> =12;
Best explanation 🔥
Thanks Abhinav :)
Great Sir. Highly appreciated. The way you presented and talked highly impressive. #Respect.
Thanks Raj for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it.
th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
Vivek...too good...very good explanation...it's worth watching your videos
Thanks amrita for the kind words,I have a small request
I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well.
th-cam.com/video/GnVn3mPBRz4/w-d-xo.html
Amazing explanation
Thanks buddy, I have 4 more videos with practice exercise on this that may interest you
Can u help me know the playlist name
@@piyush2010n th-cam.com/video/7sJ3FooAuBE/w-d-xo.html
You can start with this one
1 :
select * from (
select e.*,min(e.salary) over(partition by e.job_id) as min_salary from employees e order by e.job_id ) where salary = min_salary;
2:
select a.*,((a.salary * 10)/100) as bonus from (
select e.*,lag(e.hire_date) over(partition by e.department_id order by e.hire_date) as oldest_emp from employees e) a
where a.oldest_emp is null;
hi shivkanya you need to add order by hiredate desc to pick dept wise oldest_emp otherwise it would pick dept wise recent emp
That was an interesting piece of information. Many thanks!
awesome explanation on the analytical functions, Vivek !! Kudos !!
Thanks a lot for the kind words :)
Excellent Sir i am working on production environment i had faced so many times this difficulties bu after watching this i definetely improved my knowledge.
Thanks you so much please make more videos about debugging and give some shortcuts while using plsql developers
Thanks durwesh for the kind words, I will certainly work on more videos.Meanwhile
I have a small request, I am working on this new channel please do watch and subscribe to it.
Your support is my motivation.
th-cam.com/video/Oz4TbQwrrOY/w-d-xo.html
Find most recent employee
Select * from emp where hiredate in ( select max(hiredate) from emp group by deptno); - this is done in Sub Query. No need to introduce Self join with join condition.
Excellent 👌
Yes correct , but what if we have 2 employees in same department hired on same date ...for example in this scenario department 80 hired 2 employees on same day so it should show 2 employees with analytical function as recent joinee but it shows only one .
You are right Shivkanya, I mean a real project query will be a little more sophisticated covering such scenarios. But good catch :)
@@TechCoach so, what is the correct ans of Shivkanya's doubt ?
Hi Shivkanya ,
in this scenario it is good to use the dense_rank function , below query can be used :-
select * from (select e.* , dense_rank() over(partition by deptno order by hiredate desc) as recent_joinee from EMP e) where recent_joinee=1;
even if 10 people joined on the same date , there rank will be same
@@TechCoach hi please answer shivakanyas doubt ..
Answer 1:-
select * from(
select a.*, min(a.salary) over(partition by job_id ) min_sal from employees a order by employee_id
) where min_sal in salary
Answer 2:-
select b.*,((b.salary*10)/100) as bonus from(
select lead(hire_date) over(partition by department_id order by hire_date desc) oldest_joinee, a.* from employees a order by employee_id
)b where oldest_joinee is null
Hi Vivek,
I was looking the vedio for the analytic function vedio no 19 and it was really good and help full.
I jst want to ask you that you have not uploaded the next analytic vedio that might be helpful. I was searching that but still I am not getting it in your channel..
If possible please upload it for which i will get notification.
Really i am very much interested to get more and more knowledge on analytic function and much more about oracle...
If you will publish the vedio class wise or pattern wise for every concept then i would be a great help for the people like me those who are very much interested to get knowledge..
Please requesting you to upload the remaining vedio for analytic function for which it would be helpful.
sure Ranaj, I will try to make some more, if you have specific question let me know .
I am working on this new youtube channel"An Indian Abroad", please support me by watching subscribing and sharing.
th-cam.com/users/AnIndianAbroadd
I was totally viewing the vedio for partitions today...
So please upload the remaining vedio for analytic function...
sure, I will be really happy if you subscribe to my new channel as well.
th-cam.com/users/AnIndianAbroadd
It is very helpful !!! can't thankyou enough!
Glad to hear that!
Thank you man..
Thanks Prakash , i am glad I could help
Please consider subscribing to my other channel as well.
th-cam.com/video/6G1xGi-D5ow/w-d-xo.html
Brother, doing great job. Thanks a lot. Can you please also start making videos on plsql with real project examples as you always give??
Sure Shubham I will work on them
Thank you for the excellent explanation,
I would like to ask you if the following query solve the fourth point in the exercise ?
select b.* , avg(salary) over (partition by job_id order by hire_date range 4380 preceding) as Average_Salary
from EMPLOYEES b
;
Thanks for the kind words.I will review the query soon.
I have a small request I am working on this new youtube channel "An Indian Abroad" it will really help me if you watch and subscribe to it.
th-cam.com/video/HBQHekM1U2c/w-d-xo.html
You can use this query to find the recent joinee:
SELECT department_id
, max (hire_date) recent_hiredate
, min (employee_id) keep (dense_rank first order by hire_date desc) recent_employee_id
FROM employees GROUP BY department_id ORDER BY department_id;
for a more detailed resultset, use this
SELECT department_id
, MAX(hire_date) recent_hiredate
, MAX(employee_id) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_emp_id
, MAX(last_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_last_name
, MAX(first_name) KEEP (DENSE_RANK FIRST ORDER BY hire_date DESC) recent_first_name
FROM employees GROUP BY department_id ORDER BY department_id;
bro best video .Subscribed it best content on youtube regarding SQL
Thanks manish for the kind words, I have a small request.
I am working on this new TH-cam channel.I would really appreciate if you watch and subscribe to it.
Your support is my motivation.
th-cam.com/video/0sLU8YaYr4w/w-d-xo.html
Vivek , great explanation :) and good/simple examples to make us understand. Helps amateurs like me too to catch it quickly :)
+Santhosh Gayakvad Thanks a lot buddy for the kind words :)
I would love your support for my new channel, Please watch subscribe and share with your friends.
th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
So the main advantage is to avoid joins in your query, right. Now, I have a DataWarehouse 3rd normal form with 2000 tables, will AF be an advantage for me?
3rd normal in DW , your data would be highly normalized.
If you fine yourself using self join often you can consider analytical functions.
But with 3NF you mayn't get too much help with analytical functions.
Video content is good. Using ORDER BY in the output would ease the readability and understanding.
Thanks a lot for the kind words:)
Hey Vivek, you are doing a great job. Keep it up.
Also, have you posted the solutions for the practice questions anywhere?
Thanks for the kind words, I have not posted the answers but I will try and post soon.
I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it.
th-cam.com/video/HBQHekM1U2c/w-d-xo.html
you are explaining awsome sir...
Bhavana Priyanka zanavarapu Thanks a lot for the kind words :)
I would love your support for my new channel, Please watch subscribe and share with your friends.
th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
thanks man ... it would really helps .. can u please post more videos with real time scenarios on DWH
Hi bro I am fresher 2021 passed out student and I finished Oracle developer certification.And how to became a SQL developer what are the ways to finding jobs?please reply bro...
Hi Gunal,
The job market is hot so you can find a job if you bring some "Value"
Try learning SQL, Excel and one ETL or reporting tool, Polish your communication skills and apply at linkedln for every single job.
All the very best
Can you please let us know that how to install the same set of data and table in personal laptop?
This video is so knowledgeable and it helped a lot in order to Analytical functions!! Would like to appreciate your efforts!!!
Sure Vikas , I will be uploading a video later today.
@@TechCoach Waiting for it
Can you post the answers too for the sample problems :)
Do you have any forum to contact in case of any doubts.
superb explanation. Thank you.
Thanks a lot for the kind words :),
I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad".
th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
clear as crystal...thank you :)
Thanks Abhishek for the kind words, It means a lot to me. I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it.
th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
hi
your videos have good stuff, thanks for providing it in your channel.
i have moderate knowledge in sql,plsql'
but i don,t know how real time applications are build.
will you please make a video in it.
I would recommend watching my videos on DWH,ODS and star snow flake schema to get a basic idea, I will try to make some more
I would love your support for my new channel, Please watch subscribe and share with your friends.
th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
Commendable. Thanks a lot for this video :)
Thanks a lot Sakshi, this video on Indexing may interest you as well :).
th-cam.com/video/0X9bbtwTnuE/w-d-xo.html
Great tutorial
Thanks Dave for the kind words :)
thanks a lot..
You are welcome buddy :)
ques: select oldest employee for each designation and give them 10% BONUS?
SELECT ID,NAME, ROLE_ID, SALARY , CREATED_DATE, OLDEST,
CASE WHEN OLDEST IS NULL THEN (SALARY*(10/100)) WHEN OLDEST IS NOT NULL THEN 0 END AS "BONUS" FROM
(
select a.id,a.name, a.role_id,a.salary, a.created_date, LAG(created_date) over (partition by role_id order by created_date) as "OLDEST"
from employees a)
order by id;
Please let me know if the above is right
Very nice
Very nice sir 👍
Thanks a lot Ankita for the kind words :)
Thanks... Nicely explained
Thanks a lot for the kind words.
I would really appreciate if you help me in my new Initiative "An Indian Abroad"
by subscribing viewing and sharing.
th-cam.com/users/AnIndianAbroadd
Very Good Series..
Thanks Amaresh for the kind words :), I hope you share it with your team and friends as well :)
I would love your support for my new channel, Please watch subscribe and share with your friends.
th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
Hello. At 17.40 scenario, please try without giving order by clause. i think output will be different. Thanks
you need to specify order by clause in almost every analytical function other wise it will give you an error.
Please help me in building my new channel by subscribing sharing and watching.
th-cam.com/users/AnIndianAbroadd
@@TechCoach But I don't think below will give error,
SELECT SUM(SALARY) OVER( ) CUM_SAL, E.* FROM EMPLOYEES E
@@khobareamol7215 yes sir, you are right that's why I wrote "Almost".
Hi, can you plz make a video on listagg.
Please check this video it should help .
th-cam.com/video/-DDBt48G2ug/w-d-xo.html
I have a small request I am working on this new youtube channel
"An Indian Abroad"
It will really help me if you watch and subscribe to it
th-cam.com/users/AnIndianAbroadd
Do provide feedback.
1.select min(salary) over(partition by job_id) as min_sal , a.* from employees a
order by a.job_id;
2.select employee_id,department_id, hire_date,salary , salary * 1.1 from
(select a.*,rank() over(partition by department_id order by hire_date ) as old_emp from employees a
order by a.department_id,a.hire_date)
where old_emp=1;
3. select count(employee_id) over(partition by manager_id) as count_emp , a.* from employees a
order by a.manager_id;
4.select avg(salary) over(partition by job_id) ,a.* from employees a where add_months(trunc(sysdate) , -12*12) > a.hire_date
2.select * from (select lag(hiredate) over (partition by deptno order by hiredate) old_emp,sal*.10 bonous_sal,e.* from emp e) where old_emp is null
try this
could you please guide for 2nd sample question. i'll try this but it gives min hire_date with all employees.
@Tech Coach: instead of using Lead , we could have used max of hire_Date partition by dept. to find recently hired person dept wise.
WHAT SAY?
Yup you are right Varun, In fact in this video I have shown 3 ways of writing this query along with a lot of different queries
th-cam.com/video/oiN8rfrWMwU/w-d-xo.html
Hi.., the explanation is clear.
But can you please tell on sum(salary) using order by employee gives sum of last two employees like window function.
Similarly i am confused on window as avg is done three rows where we require only for two rows
Please explain
Does order by clause has these effects?
I would love your support for my new channel, Please watch subscribe and share with your friends.
th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
what if I want lead(hiredate) -1 in output . how to do this?
Rest questions are simple but did not understand question no. 4.....in avg sal column, row who joined after 12 years should be null?
Hi, at 5:15 I didn't gt why would we use a self join without self join we got the department wise avg sal
I meant that analytical function helps your do things which would other require a self join.
SELECT COUNT(*) over (partition by MANAGER_ID),EMPLOYEE_ID, FIRST_NAME, LAST_NAME
FROM Employees order by EMPLOYEE_ID ;
is this the correct answer for question #3 ?
it will count of manager
where is the solution ? Please share it.. as I want to cross check my answers
Let me pin the answers so we can verify.
Ok
One question - For cumulative salary you didn't mention the significance of "Order By" in over() clause, which is most imp. This would explain how sum function provides cumulative sum.
Thanks sameer, I will review what I have mentioned.
I have a small request I am working on this new youtube channel " An Indian Abroad " and will really appreciate if you watch and subscribe to it.
th-cam.com/video/11DK-oyRql0/w-d-xo.html
thank u for ur useful video...in the sample scenario u asked to , Select the details of employees with minimum salary on their designation...i got the query as follows ---- select a.*,min(salary) over (partition by job_id) from employees a order by job_id;but it still retrieves multiple JOB_IDs with all salary ....BUt it need to the minimum salary according to each designation...how to correct it??pls calrify
I will take a look at the query Fathima and will get back to you soon, you haven't specified the order by clause along with partition by but that shouldn't cause any issues.
Meanwhile I have a small request, I am working on this new channel and would really appreciate if you watch and subscribe to it.
th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html
Hi ..nice video about analyitcal functions..thank you...instead of "lead(hire_date)" can'nt we use "max(hire_date)" after partitioning by as below :
select MAX(hire_date) OVER(PARTITION BY department_id) ,a.* from employee a
HI Sateesh in this particular case max can be used, but max and lead are very different functions in general.
Let's say I am trying to analyze how my aircarft flies throughout the day.
Source|Dest|dept|arrival|aircraft_type
Delhi|Mumbai|7:00|9:00|787
Chennai|Hyderabad|14:00|16:00|787
Mumbai|Chennai|10:00|12:00|787
Hyderabad|Delhi|17:00|19:00|787
I can use lead function here to identify my aircraft positions which can't be done using max.
I hope this clarifies the difference.
I need a small help I am working on this new youtube channel and would really appreciate if you watch and subscribe to it.
th-cam.com/video/11DK-oyRql0/w-d-xo.html
Got u !!! Appreciate your quick response
Thanks Satheesh I am happy I could help.
@@TechCoach - But your logic of using LEAD will fail if there are multiple people with the same hire date in the same department. I used RANK() which I think works better here because the same RANK is assigned to multiple rows if they have the same value for the concerned column.
Hi great video on analytical functions, just got one issue.
In the video, at 22:18 you said that it is going to compute the average salary of Neena by taking average of three salaries, whereas the function is row 2 preceding, should not it be calculating the average salaries only using only 2 prior salaries, the salary of Neena and only one before her?
Please give clarification.
Hi Aditya, why don't you try running the statement in oracle once? I think that will clarify all your doubts.
If you still have any questions please do let me know. I will be happy to help :)
@@TechCoach Sure! Thanks for your reply. Also, if you have time please make an extended video on this topic, explaining everything, every function.
Thanks
@@AdityaVermaopenheartedme Sure I will work on it :)
@@TechCoach could you please recommend some good resource?
@@AdityaVermaopenheartedme you can check asktom on google, there is a course on udemy that explains analytical functions in detail as well. I will try posting some practice scenarios in the next couple of days.
also i tried 2nd question from ur assignment (oldest employee department wise) using min(hire_date) ... output comes with multiple different date for each department.
Bro the recent employee,we can get by max(hiredate)
If we need the count of distinct entries of a table without using the column name, can we do it using Analytic functions without any join?
Select count() over (),e.* from employees.
I am guessing this is what you are looking for ishika
@@TechCoach It will give me the total count of the rows of the table but I need distinct rows, can you tell me how can I implement that?
I need a simplified version of this command :
select count(*) from (select distinct(*) from employees)
i tried question 2 with lag ... output not correct.
hire_date in employees table is not in proper asec order.
many many thanks
Thanks Siva for the kind words :)
sum(salary) : it should give total sum of salary department wise, for cumulative sum i think we add 'rows unbounded preceding' in analytical function, can you please clear my doubt.
Great Question Ashutosh.
First Explanation:
once you have used partition by department that means the sum function will be applied on individual departments, now here order by is of vital importance actually because of it the 1st row doesn't have any preceding row so we will get just its salary sum, the 2nd row will have 1 preceding row so it will have the sum of 1st and itself and so on. Try running the query on your own it will give you the correct values.
Rows unbounded preceding : yes you are right there are multiple ways to achieve the same result you can use Rows unbounded preceding clause instead of partition by departments to obtain the same result, In Rows unbounded preceding the order by clause is used to describe the window instead of ordering.
sum(salary) over (order by dept_no rows unbounded preceding) cum_sum
will give you cum_sum of salary department_wise, Because here it will continue to do cum_sum as long as it find the same dept_no, As soon the dept_no changes it will again cum_sum for that dept.So here order by is acting as a windowing function instead of ordering.
I hope this clarifies your doubt, I have a small request I am working on this new youtube channel "An Indian Abroad "
and it will really help me if you watch and subscribe to it.
th-cam.com/video/HBQHekM1U2c/w-d-xo.html
Please share it with your friends as well, your support is my motivation.
Answer for homework:
1.select minimum_salary over (partitions by designation) as minimum_salary_by_des, e.* from (select min(salary) over(partition by department order by salary) as minimum_salary , a.* from employee a ) e;
2.select max(hire_date) over (partition by department order by hire_date) as oldest_employee , a.* from employee a ...after this how to proceed ?
3.select count(employee_id) over(partition by manager_id) as total_number_of_employee , a.* from employee a
I know i am wrong on 2 and 3 one Please correct me what is the exact answer. I dont have toad or plsql developer with my right now.
select * from emp where (empno,deptno,sal) in (
select empno,deptno, min(sal) over (partition by job order by sal) T from emp a)
select a.*,sal*1.1 as bonus from emp a where (empno,deptno,hiredate) in
(select empno,deptno,min(hiredate) over (partition by deptno order by hiredate) H from emp)
select a.*,count(empno) over (partition by mgr) from emp a order by mgr asc
this is great
Thanks Enock for the kind words :)
Hi.. have any idea to know how can i find Query to generate Employee absent report for a given range of dates
but.. not possible to query.. can you help me.
Can you please elaborate a lil more?
I Would really appreciate if you help me build my new channel by watching subscribing and sharing "An Indian Abroad".
th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
im working in RMG sector in Bangladesh as a IT person.. every month many people absent continuously like 5 or more days.. so i calculate in those employe who absent continuously basent five or more days in a month ... i can't do this. I need your help to find out this.
Can be done using analytical function or a stored procedure both. I can share it with you after 15 days as I am on vacation right now.
can u share me your mail address. i send you query with database..
Is it possible to find nth highest/lowest salary by using analytical function?
yes it is and it's pretty easy,
select * from (
select rank() over (order by salary) as rank1,e.* from employees
) where rank1=n
please let me know if it helps.
I have a small request I am working on this new channel "An Indian Abroad "
I will really appreciate if you watch and subscribe to it.
th-cam.com/video/IR6mVE181a4/w-d-xo.html
NO
@@TechCoach Small mistake in above query.. Corrected in below..
Select * FROM
(Select RANK() OVER(Order By Salary ASC) as Latest_Rank, e.* From employees e) Where Latest_Rank=&N;
@@a_mhite2
Don't use rank function if you use rank
24000 1
17000 2
17000 2
14500 4
If u want 3rd rank salary we don't get it
So use dense_rank function
Then we can query for whatever rank we want
24000 1
17000 2
17000 2
14500 3
@@a_mhite2
Use desc in order by that too in dense_rank() function
superb
Thanks Pankaj for the kind words :)
Can I have the ddl & dml for the tables explained above pls ...
Hi Sudhee, Unfortunately I don't have it handy right now, Majority of the tables used here are standard HR schema tables which comes by default when you install oracle express edition.
I would recommend installing oracle express edition and trying the queries shown in video on those tables.
I have a small request I am working on this new youtube channel "An Indian Abroad" ,it will really help me if you watch and subscribe to it.
th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
Where can I find the data and the answer to the questions?
Hi Hani, most of the answers are posted in the comments section.
can you provide answers for those 4 assignment questions.
@Manga , Quite a few people havd answered them in comments. If you don't find them helpful please let me know I will be more than happy to provide them
@@TechCoach i tried question 2 with lag ... output not correct.
Grt❤️
Thanks
I am glad I could help Akshay :)
@@TechCoach Your welcome sir 🙏
Can you please post the answers as well
Deepankar Kotnala Sure I will post them soon :)
Tech Coach okay..thanks :)
We want to match our queries with the solutions provided by you :)
I would love your support for my new channel, Please watch subscribe and share with your friends.
th-cam.com/channels/oLdaMxhfDgdgIAcRGlUvkg.html
I tried round (avg(salary)). it didnot work. why?
Can you post the query please?
@@TechCoach select e.*, round(avg(salary)) over(partition by department_id order by salary ) avg_sal from employees e ;
shows from keyword not found where expected.
query should be like
select e.*,
round(avg(e.salary) over(partition by e.department_id order by e.hire_date)) as avg_sal from employees e;
have to use round function to complete analytical function not only for the avg function
Pls anyone tell me how to solve this --> Q. The percentage of people who were handraisers for each campaign. For reference this is the table - Table: user_handraisers
+--------------------------+--------+---------+
| name | type | comment |
+--------------------------+--------+---------+
| campaign_date | string | |
| userid | string | |
| campaign_id | string | |
+--------------------------+--------+---------+
Crazy 😘
Thanks Shanmuga for the kind words I have a small request
I am working on this new youtube channel, I Would love it if you watch and subscribe to it as well.
th-cam.com/video/GnVn3mPBRz4/w-d-xo.html
video blurr
Can you please try watching the video at 1080p?
@@TechCoach my display on laptop is set with highest 1366 X 768
watched other video of yours, didn't see any issues with that..
Oh I am sorry :(
Not a good explain
I am sorry pramod, I will try to do better next time.
Sir apka number do
Thanks a lot for detailed explanation....
Thanks Pankaj for the kind words. I am working on this new channel and would really appreciate if you watch and subscribe to it.
th-cam.com/video/IP2Vn6jK8Hg/w-d-xo.html