{Learn-Intern-Certify}©

Understanding Analytic Functions in Oracle SQL by Rocky Jagtiani Oracle Certified Associate I train on Oracle Certification from last 7 years Analytic functions in Oracle SQL are very useful and are utilized by many people, but sometimes without completely understanding how they work or what data they operate on. This article does not focus on a specific function, but on analytic functions in general. I decided to write it because I have seen people being confused by the results they get when they make even a very small change in the analytic clause of a function call, which is the part that determines on which data the function will operate and how it will calculate the results, and also because several friends have asked me, during my Oracle Training sessions in the past. So, if you are new to analytic functions , or have only used them sporadically without completely understanding how they work, you might find this article useful. Contents [ 45 mins read & exe...

Top-N queries in Oracle SQL by Rocky Jagtiani Oracle Certified Associate I train on Oracle Certification from last 7 years I decided to write about Top-N queries today because even though there are lots of articles and documentation about the correct way to do it in Oracle out there, I have seen this question asked on different forums, and to my surprise, many times people suggesting incorrect ways to do it without anyone clarifying their mistake. Now, every time I see the question again, I will have a trustworthy link to share with my students, data analyst and data-science-learners !! Contents [ 25 mins to read & Execute ] What are Top-N queries? Using the rownum pseudocolumn Using analytic functions Using the row limiting clause For understanding Top-N Queries , make sure of : 1> Installing Oracle-11g ( steps of installation and Oracle-11g download copy is here , please see      Oracle SQL - Pl/SQL > downloads section ). By the way any version ...

Learn Oracle SQL : The Hierarchical Query Clause by Rocky Jagtiani Oracle Certified Associate I train on Oracle Certification from last 7 years For understanding the Hierarchical Query Clause , make sure of : 1> Installing Oracle-11g ( steps of installation and Oracle-11g download copy is here  , please see      Oracle SQL - Pl/SQL >  downloads section ). By the way any version from 9i and upwards would do. 2> Reading each and every step. 3> & finally Executing as you read on.  Lets create some data sets to play with : create table hierarchy( empno int not null unique, ename varchar2(20) not null, Job varchar2(20) not null,  mgr int, deptno int not null ); // Add some records to the above table // Assume deptno : 100 -> HR and 101 -> Training insert into hierarchy values(1,'Simran', 'director', null, 100);  insert into hierarchy values(2,'Rocky', 'Training-Head', 1, 101);  insert in...