Category Archives: Database Systems

Virtualization in Cloud Computing

What is Virtualization? A virtual machine is an isolated runtime environment which consists from a guest operating system and applications. Virtualization enables a single computing system to look like many computers where multiple virtual machines can run on a single …

Read more »

Perl DBI Connect Example

Here is a sample code snippet to connect and access an informix database using Perl DBI #!/usr/bin/perl # DBI moduleuse DBI ; # Database handler$dbh = DBI->connect("DBI:Informix:$db_name"); # Statement handler$sth = $dbh->prepare("$sql"); # Execute statement$sth->execute(); # For each row in the tablewhile (@row = $sth->fetchrow_array()){ …

Read more »

Select Latest Data from Table

Problem Given the following database table: DATA (id, data_date, data_value). The id field is not unique. Write an SQL statement to select the latest value for each id Solution Group rows by id then select rows with maximum date Code …

Read more »

How to Find Duplicates in a Table using SQL

Problem Write an SQL statement to find rows with duplicate column values in a database table Solution You need to group by the column containing the duplicate values then select only the groups with count greater than 1 Code Here …

Read more »

Delete Table vs Truncate Table

Question What is the difference between delete table and truncate table operation Answer Delete table operation deletes all rows in the table. The deletion of rows is logged in the transaction log. On the other hand truncate table operation similarly …

Read more »

Sql Query Optimization

Question Suggest few areas to inspect to improve the performance of a slow query Answer Here are few areas to check: Tables have no indexes. Database indexes improve query performance dramatically on the expense of slow writes and extra space …

Read more »

Creating Indexes on Multiple Columns

Question What happens if you create an index on each column of a database table Answer Query optimizer can choose from all existing indexes and come up with an efficient execution plan which in turn improves performance. On the other …

Read more »

Page 1 of 3123