#Postgresql vs sql series

RDF Store: In the secondary model, RDF stores is seen in Oracle.Indexing on all nodes is not really provided. The processing of data becomes easier with simple calculations of particular graph properties. Herein, the database is represented in graph structures as nodes and edges which represent the relationship between nodes. Latter is also known as a Graph Database.

Graph DBMS: Another notable attribute which is only seen in Oracle and Microsoft SQL Server is the Graph DBMS.

But mostly the simplicity is the primary aspect which makes it more attractive in some special instances. This is a simple database system which is not considered appropriate for complex applications.

Key Store: Another common feature is the key-value store which basically stores keys and values in pairs.

Latter basically means that there is no uniform structure of records and can have a nested form. They are also called database systems which are document-oriented and are known for schema-free data organization.

Document Store: Document stores are common to all.

Secondary Database Model: It is a vital element of comparison. All basically use the Relational Database Management System which supports the relational data model. The most widely used database model is the relational database model which is based on a table-based format. It also determines the manner in which data can be stored, organized and further used. It thus decides the logical makeup of any database.

#Postgresql vs sql series

Primary Database Model: A database model is a series of concepts which are used to describe data, its relationships, constraints, and even the semantics. Microsoft SQL Server is relational DBMS by Microsoft but both MySQL and PostgreSQL are widely used open source RDBMS. Here is a comparison of the all the four in a pointwise manner: The inability to work around all aspects of comparison becomes a limitation and leads to indecisiveness about which database to use. Below is the result of the query by referring the alias name in GROUP BY clause.Oracle, SQL Server, MySQL 5 and PostgreSQL are the most commonly used databases and people normally get caught in the comparison between them. If you use MySQL and PostgreSQL, you can write an SQL query with an alias in SELECT statement and refer the alias name in the GROUP BY clause. The above SQL query is shorter and much more concise, especially if we have a CASE statement that is very long. We can convert the CASE in SELECT statement into an alias, and refer this alias in the GROUP BY clause as follows: SELECT CASE WHEN qty > 5 THEN 'more than 5' ELSE 'less than 5' END AS inventory_status, COUNT(*) FROM inventory GROUP BY inventory_status The SQL query above works, but it’s not concise enough and the readability obviously can be improved. To do this, we will write an SQL query something like this: SELECT CASE WHEN qty > 5 THEN 'more than 5' ELSE 'less than 5' END, COUNT(*) FROM inventory GROUP BY CASE WHEN qty > 5 THEN 'more than 5' ELSE 'less than 5' END

Now let’s say we want to group and aggregate the table above purely based on the quantity status, i.e whether qty is above or below 5. Let’s dive into some scenario where at least one of PostgreSQL, MySQL, and Apache Hive behave differently given the same SQL syntax.

MySQL, PostgreSQL, and Hive all have similar SQL syntax that sometimes we assume that they will behave similarly according to a given SQL query. If you’re working with Hadoop ecosystem, chances are you’re using Apache Hive for managing and querying structured data residing in Hadoop Distributed File System or HDFS. This is where we need the distributed cluster to store and process a massive amount of data and Hadoop facilitated it for us. However, as the amount and size of data are rapidly growing, we can’t store all of the data solely with RDBMS. If we are dealing with data in a traditional RDBMS, more often than not we are going to use DB engines like MySQL or PostgreSQL. SQL helps us to manage data in a traditional relational database (RDBMS), distributed cluster, or even in a cloud environment. In an era of Big Data where the amount, size, and velocity of data are rapidly growing, knowing SQL is still an essential thing for Data Analyst and Data Scientist to know.