SQL Databases

SQL or Relational databases are used to store and manage the data objects that are related to one another, i.e. the process of handling data in a relational database is done based on a relational model.

This relational model is an approach to manage data in a structured way (using tables). A system used to manage these relational databases is known as Relational Database Management System (RDBMS).

SQL Database Table Structure

SQL database server stores data in table form. Tables are database objects used to collect data in Row and Column format. Rows represent the entities whereas columns define the attributes of each entity in a table.

Columns: Columns are vertical elements in a table. Each column in a table holds specific attribute information, and column properties such as column name and data types (Integer, Char, String, etc).

Rows: Rows are horizontal elements in a table and users can add data or retrieve by executing SQL queries.

Types of SQL Databases

There are many popular RDBMS available to work with. Some of the most popular RDBMS are listed below −

  • MySQL
  • MS SQL Server
  • ORACLE
  • MS ACCESS
  • PostgreSQL
  • SQLite

This SQL databases tutorial gives a brief overview of these RDBMS specified above. This would help you to compare their basic features.

MySQL

MySQL is an open source SQL database, which is developed by a Swedish company, MySQL AB. MySQL is pronounced as "my ess-que-ell," in contrast with SQL, pronounced "sequel."

MySQL is supporting many different platforms including Microsoft Windows, the major Linux distributions, UNIX, and Mac OS X.

MySQL has free and paid versions, depending on its usage (non-commercial/commercial) and features. MySQL comes with a very fast, multi-threaded, multi-user and robust SQL database server.

History

  • 1994 - Development of MySQL by Michael Widenius & David Axmark beginning in 1994.
  • 1995 - First internal release on 23rd May 1995.
  • 1998 - Windows Version was released on the 8th January 1998 for Windows 95 and NT.
  • 2001 - Version 3.23 released beta from June 2000, and production release January 2001.
  • 2003 - Version 4.0 was released on August 2002 as beta, and as production release on March 2003 (unions).
  • 2004 - Version 4.1 was released on June 2004 as beta, and as production release on October 2004.
  • 2005 - Version 5.0 was released on March 2005 as beta, and as a production release on October 2005.
  • 2008 - Sun Microsystems acquired MySQL AB on the 26th February 2008, and Oracle Version 5.1 had its production release on 27th November 2008.
  • 2010 - Oracle acquired Sun Microsystems on 27th January 2010 and general availability of version 5.5 was on 3rd December 2010.
  • 2013 - General availability of Version 5.6 was enabled on 5th February 2013
  • 2015 - General availability of Version 5.7 was enabled on 21st October 2015
  • 2018 - General availability of Version 8.0 was enabled on 19th April 2018, and is the latest version of MySQL.

Features

  • High Performance.
  • High Availability.
  • Scalability and Flexibility Run anything.
  • Robust Transactional Support.
  • Web and Data Warehouse Strengths.
  • Strong Data Protection.
  • Comprehensive Application Development.
  • Management Ease.
  • Open Source Freedom and 24 x 7 Support.
  • Lowest Total Cost of Ownership.

MS SQL Server

MS SQL Server is a Relational Database Management System developed by Microsoft Inc. Its primary query languages are −

  • T-SQL
  • ANSI SQL

History

  • 1987 - Sybase releases SQL Server for UNIX.
  • 1988 - Microsoft, Sybase, and Aston-Tate port SQL Server to OS/2.
  • 1989 - Microsoft, Sybase, and Aston-Tate release SQL Server 1.0 for OS/2.
  • 1990 - SQL Server 1.1 is released with support for Windows 3.0 clients.
  • 2000 - Microsoft releases SQL Server 2000.
  • 2001 - Microsoft releases XML for SQL Server Web Release 1 (download).
  • 2002 - Microsoft releases SQLXML 2.0 (renamed from XML for SQL Server).
  • 2002 - Microsoft releases SQLXML 3.0.
  • 2006 - Microsoft releases SQL Server 2005 on January 14, 2006.
  • 2008 - Microsoft releases SQL Server 2008 on November 6, 2008. And R2 version is released on July 20, 2010.
  • 2012 - Microsoft releases SQL Server 2012 on May 20, 2012.
  • 2014 - Microsoft releases SQL Server 2014 on June 5, 2014.
  • 2016 - Microsoft releases SQL Server 2016 on June 1, 2016.
  • 2017 - Microsoft releases SQL Server 2017 on September 29, 2017.
  • 2019 - Microsoft releases SQL Server 2019 on November 4, 2019.
  • 2022 - Microsoft releases SQL Server 2022 on November 16, 2022.

Features

  • High Performance
  • High Availability
  • Database mirroring
  • Database snapshots
  • CLR integration
  • Service Broker
  • DDL triggers
  • Ranking functions
  • Row version-based isolation levels
  • XML integration
  • TRY...CATCH
  • Database Mail

ORACLE

Oracle is a very large multi-user based database management system. Oracle is a relational database management system developed by 'Oracle Corporation'.

Oracle works to efficiently manage its resources, a database of information among the multiple clients requesting and sending data in the network.

It is an excellent database server choice for client/server computing. Oracle supports all major operating systems for both clients and servers, including MSDOS, NetWare, UnixWare, OS/2 and most UNIX flavors.

History

Oracle began in 1977 and celebrating its 46 wonderful years in the industry (from 1977 to 2023).

  • 1977 - Larry Ellison, Bob Miner and Ed Oates founded Software Development Laboratories to undertake development work.
  • 1979 - Version 2.0 of Oracle was released and it became first commercial relational database and first SQL database. The company changed its name to Relational Software Inc. (RSI).
  • 1981 - RSI started developing tools for Oracle.
  • 1982 - RSI was renamed to Oracle Corporation.
  • 1983 - Oracle released version 3.0, rewritten in C language and ran on multiple platforms.
  • 1984 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency, etc.
  • 1985 - Oracle version 4.0 was released. It contained features like concurrency control - multi-version read consistency, etc.
  • 2007 - Oracle released Oracle11g. The new version focused on better partitioning, easy migration, etc.
  • 2013 - Oracle Database 12c R1 version was released in July, 2013; and R2 version was released on cloud in August 2016, and released on-prem in March 2017.
  • 2018 - Oracle Database 18c version was initially released in July 2018.
  • 2019 - Oracle Database 19c version was released in February 2019.
  • 2020 - Oracle Database 21c version was released in December 2020.
  • 2023 - Oracle Database 23c version was released in April 2023.

Features

  • Concurrency
  • Read Consistency
  • Locking Mechanisms
  • Quiesce Database
  • Portability
  • Self-managing database
  • SQL*Plus
  • ASM
  • Scheduler
  • Resource Manager
  • Data Warehousing
  • Materialized views
  • Bitmap indexes
  • Table compression
  • Parallel Execution
  • Analytic SQL
  • Data mining
  • Partitioning

MS ACCESS

Microsoft Access is one of the most popular Microsoft products. It is an entry-level database management software. It is not only inexpensive but also a powerful database for small-scale projects.

MS Access uses the Jet database engine, which utilizes a specific SQL language dialect (sometimes referred to as Jet SQL). It comes with the professional edition of MS Office package. MS Access has easy to-use intuitive graphical interface.

  • 1992 - Access version 1.0 was released.
  • 1993 - Access 1.1 released to improve compatibility with inclusion the Access Basic programming language.
  • The most significant transition was from Access 97 to Access 2000.
  • 2007 - Access 2007, a new database format was introduced ACCDB which supports complex data types such as multi valued and attachment fields.
  • 2010 - Microsoft Access 2010 introduced a new version of the ACCDB format supported hosting Access Web services on a SharePoint 2010 server.
  • 2013 - Microsoft Access 2013 offers traditional Access desktop applications plus a significantly updated SharePoint 2013 web service
  • 2021 - Microsoft Access is no longer included in one-time purchase version of Microsoft Office 2021, but remains within the Microsoft 365 counterpart.

Features

  • Users can create tables, queries, forms and reports and connect them together with macros.
  • Option of importing and exporting the data to many formats including Excel, Outlook, ASCII, dBase, Paradox, FoxPro, SQL Server, Oracle, ODBC, etc.
  • There is also the Jet Database format (MDB or ACCDB in Access 2007), which can contain the application and data in one file. This makes it very convenient to distribute the entire application to another user, who can run it in disconnected environments.
  • Microsoft Access offers parameterized queries. These queries and Access tables can be referenced from other programs like VB6 and .NET through DAO or ADO.
  • The desktop editions of Microsoft SQL Server can be used with Access as an alternative to the Jet Database Engine.
  • Microsoft Access is a file server-based database. Unlike the client-server relational database management systems (RDBMS), Microsoft Access does not implement database triggers, stored procedures or transaction logging.


PostgreSQL

PostgreSQL is object relational database management system (ORDBMS) that supports both relational (SQL) and non-relational (JSON) query. It is a highly stable database that is developed based on the POSTGRES, version 4.2 released by the Computer Science Department in University of Berkeley, California. This database is a popular database or data warehouse for many applications.

History

  • 1970s - Ingres was being developed in Computer Science department of University of Berkeley as a research project which ended in 1985.
  • 1985 - A post-Ingres project, namely POSTGRES, has been initiated to address the issues in the older database systems. This project aimed to fully support datatypes and define them to establish relationships between the objects of the database. POSTGRES also was able to retrieve data using rules.
  • 1989 - POSTGRES Version 1 was released to the small number of users.
  • 1990 - POSTGRES Version 2 was released again with rewritten rules.
  • 1994 - With the increase in users, POSTGRES Version 4.2 was released, ending the project with it.
  • 1994 - Andrew Yu and Jolly Chen, students of University of Berkeley, replaced the POSTQUEL query language that run POSTGRES, with the SQL language, creating Postgres95.
  • 1995 - Postgres95's first version was released with a more liberal license.
  • 1996 - The project was renamed to PostgreSQL to support SQL.
  • 1997 - The first version of PostgreSQL version 6.0 was released.
  • Since then, next versions of the project is released as free and open-source software PostgreSQL License.
  • 2000 - PostgreSQL version 7.0 was released on July 8th, 2000. This version had the most changes in features until then. Major changes made were foreign keys were implemented, optimizer had been improved, psql had been updated, and JOIN syntax is now supported.
  • 2005 - The version 8.0 was released on January 19th, 2005. Changes in this version include, the server can now be run on Microsoft natively, savepoints are introduced, Point-in-time recovery, tablespaces, improved buffer managements, checkpoints, changing column types is introduced, etc.
  • 2010 - PostgreSQL version 9.0 was released on 20th September, 2010.
  • 2017 - Version 10.0 was released on 5th October, 2017. Major improvements include: logical replication, declarative table partitioning, improved query parallelism, significant improvements in general performance, etc.
  • 2018 - PostgreSQL version 11.0 was released on 18th October, 2018.
  • 2019 - PostgreSQL version 12.0 was released on 3rd October, 2019.
  • 2020 - PostgreSQL version 13.0 was released on 24th September, 2020.
  • 2021 - PostgreSQL version 14.0 was released on 30th September, 2021.
  • 2022 - PostgreSQL version 15.0 was released on 13th October, 2022.

Features

  • PostgreSQL supports advanced data types.
  • It possesses high levels of resilience, integrity and correctness of the data.
  • It contains Multi-Version Concurrency Control (MVCC)
  • Point in time recovery
  • Granular Access Controls
  • Asynchronous Replication
  • Nested transactions
  • Online backups
  • Refined query optimizer
  • Write ahead logging
  • It supports international character sets, multi-byte character encodings, Unicode.
  • It is locale-aware for sorting, case-sensitivity, and formatting.
  • Highly scalable.

SQLite

SQLite is an embedded relational database system. It is referred to as a lightweight database as it is not more than 500kB in size, which is way less than other relational databases. This database is an in-memory open source library; which is why it can access the files stored in it directly and the source code for it is available in the public domain.

History

  • 2000 - SQLite 1.0 was released with GNU Database Manager. Dr. Richard Hipp designed it in the same year to create a database that requires no administration.
  • 2001 - SQLite 2.0 was released which contained a custom B-Tree implementation instead of GNU Database Manager, adding transaction capability to the database.
  • 2003 - SQLite 3.0 was released with major improvements like internalization, manifest typing etc.
  • 2011 - Hipp announced his plans to introduce a non-relational interface to SQLite.
  • 2018 - SQLite adopted the Code of Conduct from the Rule of Saint Benedict which was later renamed as Code of Ethics due to some controversies.
  • Despite such controversies, newer versions of SQLite 3.x are being released continuously with better features and improvements.
  • 2023 - The latest version of SQLite is 3.42.0 which was released on 16th July, 2023.

Features

  • SQLite is an open source library witten in ANSI C.
  • It works on cross-platforms, making it easier to port to other systems.
  • The source code for SQLite is available in public domain.
  • Transactions in SQLite are atomic, consistent, isolated and durable even when the system crashes for any reason.
  • This database does not need any configuration or administration.
  • SQLite is server less, unlike other relational databases. It is linked with application that accesses it. The application directly interacts with the SQLite to read and write on files stored in the disk.
  • It has a simple and easy to use API.
  • In some cases, SQLite is faster than direct filesystem I/O operations.
  • SQLite is self contained, i.e. it is not dependent on any external libraries or operating systems.
  • Uses a stand-alone command line interface (CLI) client to administer SQLite.


Benefits of Using SQL Database

Relational databases are the most popular and preferred databases used for data storage. There are many benefits to using SQL database, including:

Enhanced Flexibility

Relational databases utilize Data Definition Language (DDL) to modify data stored in tables in real time. The most important thing users can easily add new tables and columns, rename, and implement various changes without disrupting ongoing database operations.

Data Consistency

Data Consistency is another important benefit of using SQL databases because it maintains data consistency across applications and server instances.

Minimum Data Redundancy

Relational Database Management Systems (RDBMS) use the normalization process to reduce data redundancy. This approach eliminates anomalies in Data stores in the Database.

Optimized Performance

Relational databases offer a range of value-added features that boast minimal memory usage, reduced storage costs, and high processor speed.

Higher Compatibility

Relational databases offer higher compatibility for integration with modern technologies.

Scalability

Higher scalability is another feature that makes Relational databases most preferred.