Article Categories
- All Categories
-
Data Structure
-
Networking
-
RDBMS
-
Operating System
-
Java
-
MS Excel
-
iOS
-
HTML
-
CSS
-
Android
-
Python
-
C Programming
-
C++
-
C#
-
MongoDB
-
MySQL
-
Javascript
-
PHP
-
Economics & Finance
Articles by Kiran P
Page 11 of 11
How to produce group subtotals for all combinations of the dimensions specified in Oracle?
Problem Statement:You want to find out subtotals for all combinations of the dimensions specified in Oracle.Solution:The CUBE function will generate subtotals for all combinations of the dimensions specified. If “n” is the number of columns listed in the CUBE, there will be 2n subtotal combinations.We will begin by create the necessary data for this requirement.Example-- Drop table DROP TABLE atp_titles; -- Create table CREATE TABLE atp_titles ( player VARCHAR2(100) NOT NULL, title_type VARCHAR2(100) NOT NULL, titles NUMBER NOT NULL);Example-- insert ...
Read MoreHow to produce group subtotals and a grand total in Oracle?
Problem Statement:You want to find out totals, subtotals and a grand total in Oracle.Solution:Oracle ROLLUP function performs grouping at multiple levels, using a right to left method of rolling up through intermediate levels to any grand total. To demonstrate the ROLLUP function we will create a table to hold tennis player along with the ATP tour titles and Grandslam titles acheived by the player.We will begin by create the necessary data for this requirement.Example-- Drop table DROP TABLE atp_titles; -- Create table CREATE TABLE atp_titles ( player VARCHAR2(100) NOT NULL, title_type ...
Read MoreHow to find and replace text in Oracle?
Problem Statement: You want to find and replace a string in Oracle.Solution:Function: TRANSLATESyntax: TRANSLATE(expr, from_string, to_string)TRANSLATE funtion in oracle allows you to make many single character, one on one substitutions in a single operation.However, the to_string and from_string values must not be empty, if an empty string is passed to TRANSLATE Function, Oracle Database interprets the empty string as null and returns null./* replace all occurances of b with j in below string */ SELECT 'back and bill ' AS original_string , TRANSLATE('back and bill', 'b', 'j') AS replaced_string FROM dual;Outputback and bill jack and ...
Read MoreHow to identify rows that are not parents of any other rows in a Hierarchy table in Oracle?
Problem Statement: How to identify leaf rows in a Hierarchy table i.e. rows that are not parents of any other rows.Solution: Oracle provides CONNECT_BY_ISLEAF clause to identify rows that are not parents of any other rows. To begin with let us see how does a connect_by_isleaf works.SQL:/* Function - Example to show if the row is parent of any other rows or not Tables Used - students Data - Documented below */ SELECT student_id, level, CASE WHEN connect_by_isleaf = 0 THEN 'Yes' ...
Read MoreHow to remove a branch with in a hierarchy of data in Oracle?
Problem Statement: You need to traverse the hierarchy data from top to bottom but doesn’t want a particular branch in the output.Solution: We will look at couple of examples for this problem statement.Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.Additionally, we can use the START WITH clause to indicate where to start the tree navigation. We must use the PRIOR ...
Read MoreHow to traverse Hierarchical data in Oracle?
Problem Statement: You need to traverse the hierarchy data from top to bottom marking the level of each row in the hierarchy.Solution:Oracle provides CONNECT BY clause to specify a hierarchical query i.e. how to connect the parent nodes and child nodes and the PRIOR operator to define the join condition/s between the parent nodes, and the LEVEL pseudo-column to indicate how far from the root/parent row the current row is.Additionally, we can use the START WITH clause to indicate where to start the tree navigation. We must use the PRIOR operator to specify the column/s in the parent row that have ...
Read MoreHow to select subsets of data In SQL Query Style in Pandas?
IntroductionIn this post, I will show you how to perform Data Analysis with SQL style filtering with Pandas. Most of the corporate company’s data are stored in databases that require SQL to retrieve and manipulate it. For instance, there are companies like Oracle, IBM, Microsoft having their own databases with their own SQL implementations.Data scientists have to deal with SQL at some stage of their career as the data is not always stored in CSV files. I personally prefer to use Oracle, as the majority of my company’s data is stored in Oracle.Scenario – 1 Suppose we are given a ...
Read More