Query related solutions that work for most of Database Management Servers
How to create a conceptual model of a system? One of the most important task of an analyst is to turn the requirements provided by the client or user into a physical model. There are many methodologies to represent business rules that will be implemented in an information system. On this post we will use … Continue reading “Systems Modeling (part 1)”
Let´s imagine a situation that you have a small market in which you have the inventory stored in a database table. In this table we have the date, the product code and the quantity available, but we don´t have the information for all days. The information in the tables refers only to the days the … Continue reading “SQL – Filling Gaps Between Dates”
Sometimes you have a lot of things to do, but you don´t have the means for that, so you will have to create your own means. We will apply this “philosophy” to present the data you have in your database, but you don´t have access to any reporting tool. In this case, we will create … Continue reading “C# – Create Your Own Report”
In this post we will work on a database connection using python. It´s very simple, let´s see how it works! Obviously you need the python installed on your system. For this exercise I am using verson 3.9.2. On the database side, we are using a table from Microsoft SQL Server with sales information. Although there … Continue reading “Python – Querying data from SQL Server”
Let´s say that you have a list of clients which you would like to insert into your database. If you don´t have access to an ETL Tool (such as Oracle Data Integrator, IBM DataStage, Microsoft Integration Services…) maybe it could look like a problem, but it’s not. You can use the BULK INSERT command to … Continue reading “SQL Server – Loading Data from csv file with BULK INSERT”
Different from the other SGBDs solutions, you can´t have a netezza instalation as easily as you can access the microsoft or Oracle site to download the latest database version. If you want to have a Netezza instalation you will have to spare a few hundreds of thousands dollars to acquire a server. While you don´t … Continue reading “IBM PureData – Create a Netezza environment on your computer”
You can easily implement a loading process for you NETEZZA database, even if you don´t have access to an ETL tool. You can do it just following a few steps. Let´s say that you have a text file that needs to be loaded on the database, but you don´t have access to an ETL tool … Continue reading “NETEZZA – DATA LOAD PROCESS USING TEXT FILES”
This is just an exercise of how to work with dinamyc SQL inside procedures. This one is a very simple example of how create a procedure that will create objects according to the information that you will provide, in this case it will be a table. The procedure will receive two parameters: 1) The table … Continue reading “SQL – CREATING A PROCEDURE TO CREATE TABLES DYNAMICALLY”
Let´s say that you are the CEO of a big chain of supermarkets, you know what are your best departments and your most profitable stores, but you would like to know what are your top 5 departments by store. Sales by Stores and Departments TOP 5 Departments It´s very simple to know what are the … Continue reading “SQL – Creating Rankings On Existent Categories.”
This case is when you have data in one table that needs to be corrected with data from another table. It´s very simple, you can solve this problem with a small query, with a minimal cost for the database. Considering the case that you have two tables, one with the informantion of the department ( … Continue reading “SQL – Updating a table using another table”
