Real Time Scenarios of ORACLE SQL developement - 2

How to extract the Successor and Predecessor of a student based on marks.

Tables:

drop table student;
create table student(id int,name varchar(10),dept int,marks int);
insert into student values(1,'Vishnu',111,95);

insert into student values(2,'Ashwin',111,26);
insert into student values(3,'Vinay',111,56);
insert into student values(4,'Ajay',222,48);
insert into student values(5,'Teja',222,92);
insert into student values(6,'Venkat',333,28);
insert into student values(7,'Maneesh',333,12);
select * from student;

To extract the successor and predecessor of each student based on the marks ORACLE supports LEAD and LAG functions.

LEAD Function:

Allows to access the following row from the current row with out join.

Syntax:

Lead(column name) over (partition by column name order by column name)

LAG Function:

Allows to access the prior row from the current row with out join.

Syntax:

Lag(column name) over (partition by column name order by column name)

Note: Returns NULL, if no value after or before the current value.

Query:

select id,name,dept,marks,lag(name) OVER (partition by dept ORDER BY marks desc) AS predecessor, lead(name) OVER (partition by dept ORDER BY marks desc) AS successor from student;

Read more

Real Time Scenarios of ORACLE SQL developement - 1

Display Credit and Debit Amount in an account for a given date in ORACLE.

Table : Transaction

Queries:

drop table Transaction;

create table Transaction (Account_Number varchar2(10), 

Transaction_Type varchar2(10), 

Amount integer, Transaction_date date);

insert into Transaction values ('111','Credit', 1000, '11-Nov-27');

insert into Transaction values ('111','Credit', 1200, '11-Nov-27');

insert into Transaction values ('222','Debit', 2000, '11-Nov-29');

insert into Transaction values ('333','Credit', 1000, '11-Nov-28');

insert into  Transaction values('222','Credit', 1000, '11-Nov-29');

insert into Transaction values ('111','Debit', 1200, '11-Nov-27');

Sum of credits and debits of an account for the given dates.

select 

Transaction_date, 

Transaction_Type,

Account_Number, 

sum(Amount) amt 

from Transaction

group  by Transaction_date, Transaction_Type,Account_Number;

Sum of credits and debits of an account for the given dates in two separate  columns like below (credit_amt,debit_amt).

Note: Sub queries are marked in RED

select
Transaction_date,
Account_Number,
max(case when Transaction_Type = 'Credit' then amt end) credit_amt,
max(case when Transaction_Type = 'Debit' then amt end) debit_amt
from
(select Transaction_date, Transaction_Type,Account_Number, sum(Amount) amt from Transaction group by Transaction_date, Transaction_Type,Account_Number)
group by Transaction_date,Account_Number;

Substituting '0' in place of NULL values in the above table.

The NVL() function replaces null with an alternative in the results of a query.

Syntax: NVL(e1, e2)

In the below example Null will be replaced by 0

select 

Transaction_date,

Account_Number, 

NVL(max(case when Transaction_Type = 'Credit' then amt end),0) credit_amt, 

NVL

(max(case when Transaction_Type = 'Debit' then amt end),0) debit_amt 

from 

(select Transaction_date, Transaction_Type,Account_Number, sum(Amount) amt from Transaction group by Transaction_date, Transaction_Type,Account_Number) 

group by Transaction_date,Account_Number;

Total Amount in the account for the given date.

select 

Transaction_date,

Account_Number,

(credit_amt-debit_amt) as total_amt 

from 

(select Transaction_date,Account_Number, nvl(max(case when Transaction_Type = 'Credit' then amt end),0) credit_amt, NVL(max(case when Transaction_Type = 'Debit' 

then amt end),0) debit_amt 

from 

(select Transaction_date, Transaction_Type,Account_Number, sum(Amount) amt from Transaction

  group  by Transaction_date, Transaction_Type,Account_Number) 

group by Transaction_date,Account_Number);

Read more

How to connect and fetch result in DB2 through python?

Till now I have automated db2 tasks using shell script, now I'm using python as automation tool.

To use db2 in python, we must install ibm_db2 plugin in python.

apt install python-pip
pip install ibm_db

Then we need to import ibm_db function in our python script.

Now I'll show you a simple script to connect and fetch result using Python script.

Read more

Installation of DB2 V11.5 software using db2_install

Before preparing to install DB2,we should check the required library files on OS.

Note: I installed DB2 on Ubuntu so used apt-get to install libraries.

Installation of library files:

To verify whether the required packages are installed on OS use below command:

 

dpkg --get-selections | grep -i <package-name> 

dpkg --add-architecture i386 #Command will enable i386 Architecture

apt-get update

We must install below library files:

Read more

Steps to ask yourself while troubleshooting in DB2.

First important step is to describe the problem completely. You will not know where to start and what investigation you need to do without a problem description.

Basic questions you should ask yourself:

==> What are the symptoms?

==> Where is the problem happening?

==> When does the problem happen?

==> Under which condition problem happen?

==> Is the problem reproducible?

Read more