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.

A Comprehensive Guide to PostgreSQL Commands: Unlocking the Power of Postgres

Introduction: PostgreSQL, also known as Postgres, is a feature-rich and highly acclaimed open-source relational database management system (RDBMS). In this comprehensive guide, we will delve into the world of PostgreSQL commands, equipping you with the knowledge needed to effectively manage and interact with your PostgreSQL databases.

1. Creating and Managing Databases:

• Mastering Database Management: PostgreSQL's createdb, dropdb, and psql commands

2. Creating and Managing Tables:

• Building Strong Foundations: PostgreSQL's CREATE TABLE, ALTER TABLE, and DROP TABLE commands

3. Inserting, Updating, and Deleting Data:

• Manipulating Data: PostgreSQL's INSERT INTO, UPDATE, and DELETE FROM commands

4. Querying Data:

• Unlocking the Insights: PostgreSQL's SELECT, WHERE, ORDER BY, GROUP BY, and JOIN commands

5. Indexing:

• Supercharging Performance: PostgreSQL's CREATE INDEX and DROP INDEX commands

6. Transactions:

• Ensuring Data Integrity: PostgreSQL's BEGIN, COMMIT, and ROLLBACK commands

7. User and Permission Management:

• Controlling Access: PostgreSQL's CREATE USER, ALTER USER, GRANT, and REVOKE commands

8. Backup and Restore:

• Safeguarding Your Data: PostgreSQL's pg_dump and pg_restore commands

Conclusion: PostgreSQL, the versatile and powerful RDBMS, offers an extensive suite of commands to manage and interact with your databases efficiently. This comprehensive guide has provided a detailed overview of essential PostgreSQL commands, including database creation, table management, data manipulation, querying, indexing, transaction handling, user and permission management, and backup and restore operations.

By familiarizing yourself with these PostgreSQL commands and leveraging their capabilities, you can unlock the full potential of Postgres and streamline your database management processes. Remember to consult the official PostgreSQL documentation and other valuable resources to further expand your knowledge and proficiency in using PostgreSQL effectively.

Empower yourself with the knowledge of PostgreSQL commands, and embark on a journey to harness the power and versatility of Postgres for your data management needs.

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_num FROM demo) d  WHERE row_num > 1);
Query OK, 2 rows affected (0.005 sec)

Step 4: Check if duplicate values are deleted using step 2 query

SELECT  id  FROM (SELECT id, ROW_NUMBER() OVER(PARTITION BY name ORDER BY name) AS row_num FROM demo) d WHERE row_num > 1;
Empty set (0.000 sec)

Done, duplicate data deleted.

Powershell to find Text in PDF and Word

In continuation to my previous article on PowerShell to find hyperlink texts in ppt, here I am going to show how to find hyperlinks in Word or PDF files. Sample code below, tweak it for your purpose and drop me a comment if you need help.

$FilePath= "C:\Users\xxx\" 

$OurDocuments = Get-ChildItem -Path "$FilePath" -Filter "*.pdf" -Recurse #change to .doc* for word

$Word = New-Object -ComObject word.application

$Word.Visible = $false

$i = 0

$OurDocuments | ForEach-Object {

try {

    $Document = $Word.Documents.Open($_.FullName,$false,$true) 

    #"Processing file: {0}" -f $Document.FullName

    

    try{

    $Document.Hyperlinks | ForEach-Object {

        if ($_.Address -like "https://domain.com*" -or $_.Text -like "https://domain.com*") 

        {

                "Found issues {0} `r`n" -f $Document.Fullname 

                "Found issues {0} `r`n" -f $_.Address

                "Found issues {0} `r`n" -f $_.Hyperlinks

                break

         } 

      

    }

    }catch{Write-Host "Error has occured while accessing" $Document.FullName}

    }

    catch{Write-Error $Document.FullName}

   #"Completed processing {0} `r`n" -f $Document.Fullname

    

  Write-Progress -Activity "Searching Hyperlinks" -Status "Progress:" -PercentComplete ($i/$OurDocuments.count*100)

  $i++

}

$Word.Quit()

[gc]::collect()

[gc]::WaitForPendingFinalizers()

PowerShell to find HyperLink Text in PowerPoint

Recently I faced a scenario where I needed to find if a particular url is used in hyperlink in 100s of ppt. Opening ppt and looking for the ppt is cumbersome,  so wrote a small PowerShell to find the url. Here is the PowerShell Script code

Click here to view article on powershell to find hyerlink text in word or pdf

$FilePath= "C:\Users\xxx\Path"

Add-Type -AssemblyName Office

$ppt = New-Object -ComObject powerpoint.Application

$DocumentsLib = Get-ChildItem -Path "$FilePath" -Filter "*.ppt*" -Recurse

$Doc = New-Object -ComObject powerpoint.Application

$i=0

$DocumentsLib | ForEach-Object {

Write-Host $_.FullName

$Doc = $ppt.Presentations.Open($_.FullName,$Null,$Null,[Microsoft.Office.Core.MsoTriState]::msoFalse)

$Slides = $Doc.Slides

Foreach ($Slide in $Slides) {

    $Slide.Hyperlinks | ForEach-Object {

       

        if ($_.Address -like "*texttofind*" -or $_.Text -like "texttofind*") 

        {

           write-host $_.Address

           write-host "Found"

            break

        }

               

 }

}

#$Doc.Save()

#$Doc.Close($false)

#$ppt = $null

 Write-Progress -Activity "Searching Hyperlinks" -Status "Progress:" -PercentComplete ($i/$DocumentsLib.count*100)

  $i++

}

[gc]::collect()

[gc]::WaitForPendingFinalizers()