SQL

SQL, short for Structured Query Language, is a special language used to work with data in databases. Databases store information in tables, like rows and columns in a spreadsheet.

SQL was created in the 1970s. It has two big advantages. First, you can work with many pieces of information at once with one command. Second, you don’t need to tell the computer exactly where to find the data; SQL figures that out for you.

SQL includes different types of commands for different tasks. You can use it to search for data, change data, create new tables, and control who can see or change the data. Even though SQL is mostly a declarative language—meaning you tell it what you want without saying how to do it—it also has some parts that are more step-by-step. SQL became an official standard in 1986 and has been updated many times. Most real-world uses of SQL need a few changes to work across different database systems.

History

SQL was first made at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. They got the idea from Edgar F. Codd. The first version was called SEQUEL (Structured English Query Language) for IBM's System R database.

They changed the name to SQL because another company was already using "SEQUEL." Later, IBM made products like System/38, SQL/DS, and IBM Db2 using SQL. Companies such as Oracle Corporation also started using SQL. By 1986, groups like ANSI and ISO made SQL an official standard. New versions of this standard have come out many times since then.

Interoperability and standardization

SQL works a bit differently depending on the software you use. There is a standard way SQL should work, but many programs don’t follow it exactly. This means that code you write for one SQL system might not work on another without changes.

There are a few reasons for these differences. The SQL standard is very big, so most software doesn’t support every part of it. The standard also doesn’t explain everything clearly, leaving some choices up to each software maker. Also, many companies have many users who rely on the way their old software works, so they are careful about changing it, even if it means not following the standard exactly.

Syntax

SQL has different parts that help it work. These parts include clauses, which are pieces of commands; expressions, which can give single values or groups of data; and queries, which are used to find specific information. SQL also uses statements that can change data or control how the program runs, and these often end with a semicolon (;). Extra spaces in SQL code don't change what the code does, so people can format it to look nice and easy to read.

Procedural extensions

SQL is made to work with data in special databases called relational databases. It is different from languages like C or BASIC because it tells the computer what to find or change, not how to do it step by step.

There are extra features and special versions of SQL that let it work with other languages. For example, SQL can work with Java inside databases. Some databases, like Microsoft SQL Server, let users add pieces of code written in .NET languages. PostgreSQL allows functions to be written in many languages such as Perl, Python, Tcl, JavaScript, and C. These additions give SQL more power and flexibility.

Alternatives

There are other ways to work with data besides using SQL. Some of these can work with relationships between data points, like SQL does. Here are a few examples:

• .QL
• 4D Query Language (4D QL)
• Datalog
• HTSQL
• IBM Business System 12 (IBM BS12)
• ISBL
• jOOQ
• Java Persistence Query Language (JPQL)
• JavaScript used by MongoDB
• LINQ
• Object Query Language
• QBE (Query By Example)
• QUEL
• XQuery

For other ways to store and find data that don’t focus on relationships, see navigational database and NoSQL.

Distributed SQL processing

Distributed Relational Database Architecture (DRDA) was made by a team at IBM from 1988 to 1994. It helps databases that are connected over a network work together to answer SQL questions.

With DRDA, a user or program can send SQL commands to a nearby database and get tables of information from databases that are far away. SQL commands can also be saved in faraway databases as packages, which can then be called by their name. This makes it easier for programs to handle many complex and quick questions, especially when the data is stored in different places.

The rules, messages, and parts of DRDA are set by the Distributed Data Management Architecture. DRDA’s way of handling distributed SQL is different from newer distributed SQL databases.

Criticisms

SQL has some areas where it doesn't work exactly as it was first designed. For example, in theory, tables should show each item only once, but in SQL, the same item can appear more than once. Also, the order of items can sometimes matter.

Early versions of SQL were missing some important features, like ways to make sure each row is unique. These features were added later. Some people think SQL doesn't handle certain types of data, like JSON, as well as it could.

SQL also has special rules for missing values, shown as "Null". This is different from having a value of zero or an empty space.

SQL allows tables to have rows that are exactly the same, which can make it harder to use in other programming languages. This is often fixed by creating rules to make each row unique.

There can be challenges when using SQL inside other programming languages that work step by step.

SQL data types

SQL has different kinds of data types to help organize information. These include types that are already set up, types you can build yourself, and types made by users.

Predefined data types are the ones that come ready to use. These include character types like CHAR and VARCHAR for text, binary types like BINARY and VARBINARY for binary data, and numeric types such as NUMERIC and FLOAT for numbers. There are also datetime types like DATE and TIME, an interval type, a boolean type for true or false values, XML, and JSON.