Posts

Showing posts from June, 2020

Switch database on PGSQL

To switch to another database when connect to PostgreSQL Server use following command on psql.
postgres=>\connectdatabasenamepostgres=> \c databasenameFor eg: Lets say you are connected to default postgres database and want to connect to database name mydatabase, try below syntax
postgres=> \c mydatabase or postgres=> \connect mydatabasemydatabase=>

Commonly use oracle queries and commands

To View all databases 
SQL> SELECT NAME FROM v$database
Shutdown database SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. Start the instance SQL> startup;ORACLE instance started. Total System Global Area 4966053832 bytes Fixed Size 8906696 bytes Variable Size 922746880 bytes Database Buffers 4026531840 bytes Redo Buffers 7868416 bytes Database mountedOpen the databaseSQL> alter database open;SQL> select instance_name, status from v$instance;INSTANCE_NAME STATUS
---------------- ------------
orcl MOUNTED
SQL> alter database open;
Database altered.
SQL> select instance_name, status from v$instance;
INSTANCE_NAME    STATUS
---------------- ------------
orcl            OPEN

MariaDB delete duplicate using ROW_NUMBER()

Please note : Window Functions are available in MariaDB 10.2 onwards
In our previous article, we talked about deleting duplicate data in MySQL using join. In this article we are going to demo on how to delete duplicate using row_number(). Since we already have our demo table created with dummy data, going ahead directly with the query
Step 1: Find duplicate using row_number()
SELECT id FROM (SELECT id, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) AS row_num FROM demo) d WHERE row_num > 1; +------+ | id | +------+ | 7 | | 3 | +------+ 2 rows in set (0.000 sec)
Step 2: Select * from demo  select * from demo; +------+--------+ | id   | name   | +------+--------+ |    1 | abc    | |    2 | abd    | |    3 | vijay  | |    4 | vijay  | |    5 | ajay   | |    6 | sanjay | |    7 | vijay  | +------+--------+ 7 rows in set (0.000 sec)
Step 3: Delete duplicateDELETE FROM demo WHERE id IN (SELECT id FROM (SELECT id,ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS row_num FROM demo) d  WHE…