Category: Database Systems

Introduction I wanted to connect to an MS SQL server database using Python from my Mac laptop. I struggled a bit but eventually I had a working solution. I thought it can be useful so I am sharing it… Steps Make sure you have brew installed on mac. If not, you can get it from

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 physical machine. The size and power of individual virtual machines is variable and configurable. Each

Here is a sample code snippet to connect and access an informix database using Perl DBI

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 Here is the code

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 is the code in SQL

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 deletes all rows in the table but it does not log the deletion of rows

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 Tables are scanned in full. Scanning huge tables degrades performance Table statistics are not updated.

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 hand data modification operations such as insert, update and delete will become slow because indexes

Question What is the difference between clustered and non clustered database index Answer A database index is a special data structure (tree, hash, etc) that improves the performance of data retrieval. There is nothing free so the performance gain comes with slower writes and increased storage space. Database index architecture can be classified into clustered

Question What is the difference between WHERE and HAVING clauses. Answer The WHERE clause restricts which rows are returned while the HAVING clause operates on groups of rows. HAVING is always used with GROUP BY and must have a function it restricts. The rule says if a condition refers to an aggregate function put that