Posts

Showing posts from 2018
Image
How to use rownum correctly in Oracle SQL ?by Rocky Jagtiani Oracle Certified Associate I train on Oracle Certification from last 7 years
This is one such question , which freshers face a lot at on+off-campus interviews. The concept of rownum is quite simple. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

You can use ROWNUM to limit the number of rows returned by a query, as in this example:

SELECT employee_id, first_name FROM Top_N_employees WHERE ROWNUM < 10;




You would notice only 9 rows (as the condition is < 10).
Contents : [ 20 mins read & execute ] 1> How to use rownum ? 2> Where not to use rownum ? 3> Face the most common interview question.
For beginners, who wish get the schema and records of my test table : Top_N_employees , do see here
For running all the queries, make sure…
Image
Understanding Analytic Functions in Oracle SQLby 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 & execute]
So, what are anal…
Image
Top-N queries in Oracle SQLby 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 pseudocolumnUsing analytic functionsUsing 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 from 9i and upwards would do. In case i…
Image
Learn Oracle SQL : The Hierarchical Query Clauseby 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> & finallyExecuting 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 into hierarchy values(3,'Neetu', 'IT Recruiter', 1, 10…