October 8, 2018

COALESCE-SQL

Coalesce- return the null values from the expression. It works similar to a case statement where if expression 1 is false then goes to expression 2 or returns a default set value.

In same way Coalesce will take the null values and replace with the given default value or act according to expression defined for null.

e.g:-

SELECT product_id, list_price, min_price,
   COALESCE(0.9*list_price, min_price, 5) "Sale"
   FROM product_information
   WHERE supplier_id = 102050
   ORDER BY product_id, list_price, min_price, "Sale";

PRODUCT_ID LIST_PRICE  MIN_PRICE       Sale
---------- ---------- ---------- ----------
      1769         48                  43.2
      1770                    73         73
      2378        305        247      274.5
      2382        850        731        765
      3355                                5

August 4, 2018

NULLS LAST/FIRST

When we use RANK function and if we have the ranking criteria column with NULL values, they do have chances to be listed in first position. By using Nulls last we can avoid such occurrences.

Ex:

Student ranking first based on their achieved grades on all test they appeared.

select p.name Student, m.name, m.tests, rank() over ( order by tests desc ) as S_rank from ptudents p, test m where p.name = m.student_name(+) and p.name in ('M','V','P','M','N') order by s_rank


StudentNAMEtestsS_RANK
V- - 1
M- - 1
NT11353.43
PC1603.64
NP12105
NN11706


select p.name Student, m.name, m.tests, rank() over ( order by tests desc NULLS LAST ) as S_rank
from ptudents p, test m where p.name = m.student_name(+) and p.name in ('M','V','P','M','N') order by s_rank


StudentNAMEtestsS_RANK

NT11353.41
PC1603.62
NP12103
NN11704
Ans null will be listed in last

More on Ranking Functions


More on Ranking:

We have seen RANK(),DENSE_RANK(),ROW_NUMBER in our previous post, lets see about CUME_DIST,PERCENT_RANK() and NTILE

PERCENT_RANK:- Given with the details of sales made in a year for n number of items and list as per sales percentage.
(rank of row-1)/(total number of rows -1)=Percent_rank
select name, gross_sales,
       100*percent_rank() over ( order by gross_sales ) as pctrank
from movies
order by 2,name

Name   Gross Sales         PCTRank
Abc        35                           1
Efg          33                           2.344
Igh          30                           3.344

CUME_DIST: For cumulative ranking often used for graphical purpose.
select name, gross_sales,
100*percent_rank() over ( order by gross_sales ) as pctrank
       100*cume_dist() over ( order by gross_sales ) as cumedist
from movies
order by 2,name
Name   Gross Sales         PCTRank        Cumedist
Abc        35                           1                              1
Efg          33                           2.344                  2
Igh          30                           2.346                  2
Yhi          29                           2.12                        3

NTILE(n):-Segregate the data as per the set of records given to n-tile value

select name, gross_sales,
       ntile(3) over ( order by gross_sales desc ) as quartile
from movies
order by 2,nam Name   Gross Sales         PCTRank        Cumedist
Abc        35                           1                              1                              1
Efg          33                           2.344                  2                              1

Igh          30                           2.346                  2                              2
Yhi          29                           2.12                        3                              2

Abc1      29                           1                              1                              3
Efg2       28                           2.344                  2                                3









July 31, 2018

Find leave taken in alternate intervals within a month

We saw the Rank Function in my previous post for sorting records. In the same way using the function we can get the count of leaves taken in alternate intervals with a month for an employee.

For example:
Leaves taken on 1,2,3,4 of a month and came to office on 5th and again went on leave from 6th. the summary of absence using the query can be obtained.

select min(date_taken) date_from,
       max(date_taken) date_to,
       count(*) num_days
from (
  select date_taken,
         date_taken-row_number() over(order by date_taken) as daysto
         from Absence
     )
group by daysto
order by 1
DATE_FROMDATE_TONUM_SAMPLES
01-DEC-1504-DEC-154
07-DEC-1510-DEC-154
14-DEC-1516-DEC-153
19-DEC-1520-DEC-152


July 29, 2018

RANK Function

RANK Function:

Each entry can be ranked based on any of the given criteria. For e.g:- grading system in a school.

Syntax:
Function
(arg1,arg2)
OVER(
Partition Clause-Sort as per a particular criteria(eg.job,grade)
Sorting Clause-Normal sort of full record
Windowing Clause- sort based on any one record value.
)


Function -Rank, Dense Rank, Row number

Rank:

1 2 3 3 - Two person are in tie while ranking both will be given same ranking by the next rank will be  added value of one more position i.e

1 2 3 3 5- the 3rd position has a tie and occupies 2 position with same ranking, so the next person will be pushed to 5th in order ranking.

E.g:

Select empno,empname,job,sal,
rank() OVER (order by sal) as rank_sal from emp order by sal.

Dense Ranking:

1 2 3 3 4 -  the 3rd position has a tie and occupies 2 position with same ranking, but the next person will be ranked to next sequence 4th.


Select empno,empname,job,sal,
dense_rank() OVER (order by sal) as rank_sal from emp order by sal.



Row number

1 2 3 4 5 6  -Tie records will be ordered as per the column we add to order and the tie will replaced to sequential

Based on what field we need to position the tie records we can mention in sorting clause.

E.g:

Select empno,empname,job,sal,
row_number() OVER (order by sal,empno) as rank_sal from emp order by sal.

here empno column next to order by clause indicates that the tie should be ranked with precedance of employee number.


July 26, 2018

Basics of Tables


Concepts in 18 C:

Let see about tables.

In a relational database tables play a vital part to organize data and regularize it as per size and requirement.

Let’s see about permanent tables, temporary tables, external tables, heap organized tables, index-organized tables.

You can define table into two categories: Relational and object tables:

Relational tables are simple and common tables. For example, recording list of students, employees etc. No need to look for dependencies while creating.

Object type tables are table with reference to the application it is related to. The table will be designed for a purpose and also validating or summarizing the dependence’s.

Relational Tables:

S.no
Emp Name
Emp ID
Dept ID
1
a
12
0001
2
b
23
0002

Object Type:

Test
Maths
Science
English
Total
1
100
100
100
300
2
100
100
100
300


create type stud as object (
          maths number(3),
          phy   number(3),
          chem  number(3),
          member function tot return number);

functions can be called inside the object type column.


Ok, Let’s see about table structures:

 heap-organized table :Stores data where ever the storage is available. As normal as our own table at office. Place the things as we go.
index-organized table: Stores data with its primary key as key reference for sequential ordering of storing of data. Or by using pseudocolumn to be referenced to sequence data. Index is not a separate column which points to the data. Instead works as organized data which is sequential and easy to identify in the order we arrange it.
Temporary table:  for a particular session and only be accessed by its owner. While the session gets completed the data will be erased.
 External table: is a read-only table whose metadata is stored in the database but whose data is stored outside the database. For example, the text file needs to be loaded in ETL from a other database and which creates a virtual column in our database while loading.

November 13, 2017

RTF to Excel Sheet (Separate) O/P

Different Sheet Xls O/P


Form Bi Publisher Report RTF Template if you need to generate output in different excel sheets need to perform the exercise as below.


1. Ensure the grouping of block items.

Different Grouped Data Blocks
2. After Each Grouped Data Block click 'CTRL+ENTER' and place the block you need to be on next sheet.

3.To name the sheets - use the XML tag before the data block:





The output will be generated as below in different sheets.



Happy Learning !!!


COALESCE-SQL

Coalesce- return the null values from the expression. It works similar to a case statement where if expression 1 is false then goes to expr...