What is SQL? In­tro­duc­tion to Struc­tured Query Language

SQL can be used to create re­la­tion­al databases and perform an array of op­er­a­tions on existing databases, including data queries. The language is part of the standard rep­er­toire of web de­velopers, data analysts and re­search­ers. But compared to other pro­gram­ming languages, SQL is special. We’ll explain the features of the language.

What exactly is SQL?

SQL is the ‘Struc­tured Query Language’. It can be used to make queries to databases that contain struc­tured or re­la­tion­al data. The language is based on ‘re­la­tion­al algebra’. This is a math­em­at­ic­al theory for struc­tur­ing data and cal­cu­lat­ing the results of queries. Many of the special features of SQL as a pro­gram­ming language are derived from this math­em­at­ic­al basis. Developed in the mid-1970s, SQL is now con­sidered the standard pro­gram­ming language for database ap­plic­a­tions.

An important detail about SQL is that it is a pure query or pro­gram­ming language, not a com­pre­hens­ive database man­age­ment system (DBMS). Some popular DBMSs that implement SQL are MySQL, Oracle SQL and SQLite. However, these DBMSs mostly use dialects of SQL, which may have ad­di­tion­al and/or different commands.

SQL as a domain-specific and de­clar­at­ive language

Compared to most es­tab­lished pro­gram­ming languages, SQL is special because it is a domain-specific language (DSL). In contrast to the General Purpose Languages (GPLs), which are suitable for use in many different ap­plic­a­tions, SQL can only be used for one thing, databases.

SQL is also a de­clar­at­ive pro­gram­ming language. This means that the pro­gram­mer enters a desired result as a command and the system ensures that this result is achieved. This is in contrast to im­per­at­ive pro­gram­ming, in which the in­di­vidu­al steps to achieve the goals are ex­pli­citly defined in the code.

What is SQL used for?

SQL serves as an interface for in­ter­act­ing with re­la­tion­al database man­age­ment systems (RDBMS). A re­la­tion­al database can be thought of as a table in which each row entry has a pre­de­ter­mined set of at­trib­utes populated with values. The SQL code can either be entered by humans via a text-based interface or in­teg­rated into API accesses.

Ad­vant­ages and dis­ad­vant­ages of SQL

Ad­vant­ages of SQL

The biggest advantage of SQL lies in the high profile and wide­spread use of the tech­no­logy. Since its origin in the 1970s, SQL has been the industry standard for database ap­plic­a­tions. This makes it re­l­at­ively easy to find ex­per­i­enced SQL pro­gram­mers, as well as in­ter­faces with other common tech­no­lo­gies and languages.

Moreover, SQL has become the industry standard for a reason. The language is based on a robust math­em­at­ic­al found­a­tion that allows for optimal data storage. However, re­la­tion­al databases require a solid un­der­stand­ing of the tech­no­logy and theory, as well as skill and planning in modelling. But a well-designed database schema makes it possible to gain new insights from the data through ap­pro­pri­ate queries.

Dis­ad­vant­ages of SQL

One dis­ad­vant­age of SQL and re­la­tion­al databases in general is the high com­plex­ity of the tech­no­logy. SQL comprises hundreds of commands and clauses, which represent a major challenge for newcomers. Many of these are im­ple­ment­a­tion-specific, making things even more chal­len­ging again.

In addition, the structure of a re­la­tion­al database requires a number of as­sump­tions about the data to be stored. These serve to ensure the quality of the stored data, but also entail a number of lim­it­a­tions that can cause permanent problems if the schema is poorly designed. Changes to the schema during operation can pose a serious challenge. In addition to this lack of flex­ib­il­ity, it is usually very chal­len­ging to dis­trib­ute an SQL database geo­graph­ic­ally. Op­tim­ising the per­form­ance of a database through de­cent­ral­isa­tion is, therefore, anything but simple.

A final dis­ad­vant­age of SQL is its in­com­pat­ib­il­ity with the widely used object-oriented pro­gram­ming, which is becoming in­creas­ingly relevant. In object-oriented pro­gram­ming, data and ‘behaviour’ (methods) are en­cap­su­lated in objects. Data and methods are inherited through class hier­arch­ies. The re­la­tion­al approach is fun­da­ment­ally different, as data can be dis­trib­uted across several tables. In addition, it is im­possible to model the behaviour of an object. For this reason, objects cannot be trans­ferred 1:1 to re­la­tion­al database struc­tures.

Al­tern­at­ives to SQL

As SQL was invented at the beginning of the digital re­volu­tion, the language has not lost its relevance. However, some al­tern­at­ive schemas have since emerged that may be more suitable for some ap­plic­a­tions.

Object-re­la­tion­al database man­age­ment systems

Object-re­la­tion­al database man­age­ment systems (ORDBMS) such as Post­gr­eSQL use SQL as a query language, but also support core concepts of object-oriented pro­gram­ming. Object hier­arch­ies, in­her­it­ance and object behaviour can be used without the use of object-re­la­tion­al mapping (ORM). User-defined and composite data types in par­tic­u­lar reduce the com­plex­ity of schemas and queries.

NoSQL

SQL-based DBMSs are primarily intended for storing struc­tured data, but not all data follows a fixed schema. This is where NoSQL databases come into play. The term NoSQL refers to a family of non-re­la­tion­al DBMS. Instead of modelling data as fields in a table, various other ap­proaches are used.

A popular approach is the document-based storage of data. This works by storing data in in­di­vidu­al documents rather than storing it in a table. One advantage of the document-based approach is that the data can be self-writing. This means that the schema of the data is de­term­ined by the in­di­vidu­al document, not by the database, meaning that data entries can follow different schemas.

NoSQL solutions are usually less complex and offer ad­vant­ages in terms of scaling and per­form­ance op­tim­isa­tion. Fur­ther­more, it is usually easier to change the schema during operation or to store data flexibly. On the other hand, there may be fewer guar­an­tees with regard to the quality of the data.