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=>\ connect databasename postgres=> \c databasename For 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 mydatabase mydatabase=>

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 mounted Open the database SQL> 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 duplicate DELETE FROM demo WHERE id IN (SELECT id FROM (SELECT id,ROW_NUMBER() OVER (PARTITION BY name ORDER BY name) AS row_nu