Running Total In SQL Server

 Running total refers to the sum of values in all cells of a column that precedes the next cell in that particular column.

Using Window Function

Assuming, the table has 2 columns, col1 is primary key with data type integer and col2 is integer data type values. To calculate running total on col2 write a query as

select col1, col2,sum(col2) over(order by col1) as runnintotal from tablename

using window function is more performance efficient.

Check if PostgreSQL is running on UBUNTU - Start/Stop

There are commands within ubuntu to check if your postgresql is running or not. Based on its status, you can either start or stop. 

Check if postgresql is running, run following cmd

root@DESKTOP:~# sudo /etc/init.d/postgresql start

10/main (port 5432): down

The output of the above cmd shows the postgresql version and port and status. So the status down means that the server is not running, lets start the server, use following command

root@DESKTOP:~# sudo /etc/init.d/postgresql start
 * Starting PostgreSQL 10 database server

So now you can connect to your postgresql. Inorder to stop the server use following command

root@DESKTOX-QMQVNCE:~# sudo /etc/init.d/postgresql stop
 * Stopping PostgreSQL 10 database server

Reset PostgreSQL master password ubuntu bash

I recently lost my admin password of ubuntu postgresql, this is how I did a reset to access my postgresql again

Step 1 : Run following command change the authentication method in the configuration file and then reload

    

sudo sed -ibak 's/^\([^#]*\)md5/\1trust/g' pg_hba.conf
/etc/init.d/postgresql reload

Step 2 : Once the authentication method is reset then you can login to postgres using following and set the new password value

psql -U postgres
postgres=# alter user postgres with password 'NEW_PASSWORD';
postgresl=# \q

Step 3 : Once the new password is set, change the authentication method back to md5

sudo sed -i 's/^\([^#]*\)trust/\1md5/g' pg_hba.conf
sudo -u postgres pg_ctl reload or /etc/init.d/postgresql reload

You are all set to login using your new password

MySQL delete duplicate

One of the biggest problem with analysis or business intelligence is clean data. In many real world scenarios you will find that data is corrupt, missing or duplicate. In this article we are going to demonstrate multiple ways to delete duplicate data from a table. 

Step 1: Lets create a dummy table. 

create table demo (id int, name char(10))

Step 2: Insert duplicate values

insert into demo(id,name) values(1,'abc');
insert into demo(id,name) values(2,'abd');
insert into demo(id,name) values(3,'vijay');
insert into demo(id,name) values(4,'vijay');
insert into demo(id,name) values(5,'ajay');
insert into demo(id,name) values(6,'sanjay');

Step 3: Select * from demo;

select * from demo;
+------+--------+
| id   | name   |
+------+--------+
|    1 | abc    |
|    2 | abd    |
|    3 | vijay  |
|    4 | vijay  |
|    5 | ajay   |
|    6 | sanjay |
+------+--------+
6 rows in set (0.00 sec)

So the table now contains duplicate values, id 3,4 with name vijay is duplicate. Now lets look at the options to delete this duplicate value

Step 4: Find duplicate value using select

select id, count(name) from demo group by name having count(name)>1;
+------+-------------+
| id   | count(name) |
+------+-------------+
|    3 |           2 |
+------+-------------+
1 row in set (0.00 sec)

So the output shows that id 3 with name vijay has two entries. The simple way to find this was using group by and count on column where you think duplicate are there and should not be. Lets delete this

Step 5: Delete duplicate using inner join
delete d1 from demo d1 inner join demo d2 where d1.id>d2.id AND d1.name=d2.name;
Query OK, 1 row affected (0.00 sec)
Step 6: Check if the query ran successfully, repeat step 4.
select id, count(name) from demo group by name having count(name)>1;
Empty set (0.00 sec)

So no output that means duplicate data has been deleted success. Reconfirm with select on table

select id,name from demo;
+------+--------+
| id   | name   |
+------+--------+
|    1 | abc    |
|    2 | abd    |
|    3 | vijay  |
|    5 | ajay   |
|    6 | sanjay |
+------+--------+
5 rows in set (0.00 sec)

In next article, I am going to show other queries to delete duplicate.

Oracle to PostgreSQL using Ora2PG

In this article, we are going to talk about using ora2pg to migrate the database from Oracle to PostgreSQL. We are going to consider PostgreSQL in Azure VM, PostgreSQL and Azure Database for PostgreSQL as target from Oracle. Source Oracle version is 11g XE.

The website https://ora2pg.darold.net/documentation.html talks about manual installation of ora2pg which is complex, there is an easy way to install in on Linux & Windows all you have to do is download the respective scripts for Linux or Windows and run it as root or administrator.

https://github.com/microsoft/DataMigrationTeam/tree/master/IP%20and%20Scripts/PostgreSQL%20Migration%20and%20Assessment%20Tools

Once the installation is done, the very first step would be to assess your Oracle and create a report

 

Drop Database in Oracle

Steps to drop a container database in oracle

Step 1 : Connect to the database as sysdba

bash#$ sqlplus / as sysdba

Step 2 : Shutdown the database

SQL> shutdown immediate;

Step 3 : Start mount the database with exclusive restrict to avoid the database being opened

SQL> startup mount exclusive restrict;

Step 4 : Drop database

SQL> drop database;

PSQL Commands

To connect to postgresql using psql, update port, dbname, user and your_password in cmd below

psql "host=localhost port=5432 dbname={your_database} user=user password={your_password} sslmode=require"

To import data dump from .sql file using psql, use following cmd

psql > \i 'path-to-dump.sql'

Having single quote around the path is important else you may see C:: permission denied error. If using sudo 

sudo -u postgres psql db_name < 'file_path'  

Create user 

postgres=# create user user_name password 'password';  

Create database 

postgres=# create database mydb owner user_name ;

Install PostgreSQL Client on Ubuntu

bash:=# apt install postgresql-client- ;

To Quit

bash:=# \q;

Help

bash:=# \h;

To describe attributes of a table.

bash:=# \d tablename ;

To get list of databases.

bash:=# \l ;

To connect to a database.

bash:=# \c databasename ;

To list all details of a database.

bash:=# \z  or select * from pg_tables where schemaname = 'information_schema';