ORACLE Tips

• For typical day-to-day administrative tasks, it is recommended that you log in with the SYSTEM account. If you want to back up, recover, or upgrade the database, you must log in with the SYS account.
• PL/SQL treats any zero-length string like a NULL value. 
• When using NULL values, remember the following rules:
  ■ Comparisons involving NULL values always yield NULL.
  ■ Applying the logical operator NOT to a NULL value yields NULL.
  ■ In conditional control statements, if the condition yields NULL, its associated
  sequence of statements is not executed.
  ■ If the expression in a simple CASE statement or CASE expression yields NULL, it
  cannot be matched by using WHEN NULL. Instead, use a searched CASE syntax with
  WHEN expression IS NULL.
• The concatenation operator ignores null operands

Know SQL Rank through SQL query.

Problem Statement:

                     We have a table 'Student' as shown below. And we wanted to find the rank of the students in the class.

Student_Name             Total_Marks

John                                           980

Peter                                          990

Vijay                                          990

Raj                                              960

Patrick                                       980

Arnold                                       950

William                                      955

gilbert                                        955

Solution:

The first thing that would strike is, If we sort the data  in descending order, we would get the result. But from a practical point of view Ranking is not just about ordering but also resolve the conflict of those students whose rank is the same because both of them got the same marks. And what about the next student's rank whose total marks comes next to these conflicting students. To answer this we write a smart query for finding Rank. So lets see that,

Select S1.Student_Name, S1.Total_Marks,Count(S2.Total_Marks) as Rank 

From Student S1, Student S2

where  S1.Total_marks <= S2.Total_Marks

or (S1.Total_Marks = S2.Total_Marks and S1.Student_Name = S2.Student_Name)

Group by S1.Student_Name, S1.Total_Marks

Order by S1.Total_Marks DESC , S1.Name

Result:
 

Student_Name             Total_Marks       Rank

Peter                                             990                        1

Vijay                                             990                        1

John                                              980                        3

Patrick                                          980                        3

Raj                                                 960                        5

gilbert                                           955                        6

William                                         955                        6

Arnold                                          950                        8

Sounds Good right.

- 

KVSK

Welcome to TechStairs

Right !

This is my First Technical Blog to help myself in re-iterating the "Tech Buzz" I Learn(t) in my Career. I wanted to have one stop reference spot on the internet to refer back what I feel important and when ever I wanted to. Strictly do not expect anything funny here. I am very Serious and expect who ever reads this blog to have a Serious face and mind. So fasten your seat belts, take a long breath, close your eyes and start reading my blog. :)

NOTE: This Blog is not created for any Commercial purpose. It is strictly started for a non-profit educational purpose.

- VIJAY