SQL supports most aggregate functions except one fundamental aggregation to calculate product of values in a column. If you are wondering what could be a scenario where you may have to calculate product of values in a column, here is a business scenario that demands it. Business Scenario: For the sake of brevity, let's say... Continue Reading →
Why is SQL one of the most powerful tools in the arsenal of an Analyst?
Out of information analysis, comes wisdom. It’s this information analysis that is one of the important reasons that created the need for storage of data. The world of databases was sprouted to address this need of data storage and many languages were born to interact and deal with the data stored in these databases. But... Continue Reading →
Share this:
Infographic of Table Merge or Join Operations
Most data analyses start with organizing the data, at least starting with organizing in tabular format. This implies tables are the most fundamental formats to deal when analysing data. Here I crafted an infographic showing the most commonly performed merge or join operations among tables. Hope you will find this useful. Feel free to share... Continue Reading →
Share this:
Oracle SQL script to generate Date Dimension data for use in Microsoft Excel Data Models
Date dimension is that one dimension or business perspective without which an Excel Data Model cannot ever exist. Here is an Oracle SQL script to generate the data for a Date Dimension. All that you have to do is change the start date and end date values in the in the last line of the... Continue Reading →
Share this:
A White Paper – Solving Relational Division problem in SQL – The Analyst Way
Hi readers..!! Here I go posting a white paper on one of the classical and intellectually challenging problems that an analyst might come across. The paper is titled - "Understanding and Solving Relational Division problems in SQL - The Analyst way". Hope you enjoy reading this paper. Please leave your comments and suggestions, if any, here.... Continue Reading →
Share this:
A Use Case of CROSS JOINS in SQL
Use case of SQL CROSS JOIN: Although CROSS JOIN is available in SQL, you rarely find any instance where you can use it. Here is one use case for CROSS JOIN in SQL: Example with fictitious data: Assuming a tournament of Soccer to happen among four teams, what are the all possible matches between each... Continue Reading →
Share this:
Complement Negation: SQL Design Pattern to solve a frequently occurring interesting business data analysis problem
Here is an interesting business data analysis problem that I have come across multiple times in the recent times. Since I have seen this problem surfacing multiple times and the solution boils down to the same technique, it becomes an SQL Design Pattern and I named this baby as "Complement Negation". Have shared the justification... Continue Reading →
Share this:
Truncate time part from Datetime column or literal in Oracle SQL
I have shown how to truncate the time part from a datetime column value in SQL Server T-SQL in one of my previous posts here - http://pivotronics.com/2012/12/19/comparing-datetime-column-with-a-date-literal-in-t-sql/ Here is the same (how to truncate the time part from a datetime column value) in Oracle SQL: TRUNC(datetime_column) or TRUNC(datetime_expression) very straight forward...!! isn't it.. ? 🙂
Share this:
Inclusiveness of SQL – BETWEEN operator
Both parameters passed to the BETWEEN operator in SQL are inclusive to it. e.g. SELECT * FROM Employees WHERE Age BETWEEN 20 and 30; The above SQL returns all the records from the table - Employees where the age is >= 20 and <= 30. Here the employee records whose age is either 20 or 30... Continue Reading →
