SQL

Structured Query Language, or SQL, is a special kind of language used to work with data, especially in databases where information is organized in tables. It helps people find, change, add, or remove data without needing to know exactly where or how the data is stored.

SQL was created in the 1970s and offered two big advantages over older ways of handling data. First, it lets you work with many pieces of information at once with just one command. Second, you don’t have to tell the computer exactly how to find the data; SQL figures that out for you.

SQL includes different types of statements for various tasks, like searching for data, changing data, creating new tables, and controlling who can see or change the data. Even though it’s mainly a declarative language—meaning you tell it what you want without saying how to do it—it also has some procedural parts. SQL became an official standard in 1986 and has been updated many times since, though most real-world uses of SQL need some adjustments to work across different database systems.

History

SQL was first created at IBM by Donald D. Chamberlin and Raymond F. Boyce in the early 1970s. They learned about a new way to organize data from Edgar F. Codd. Their early version was called SEQUEL (Structured English Query Language) and was made for IBM's database system called System R.

They changed the name to SQL because "SEQUEL" was already used by another company. Later, IBM made several products using SQL, such as System/38, SQL/DS, and IBM Db2. Other companies, like Oracle Corporation, also began using SQL. By 1986, groups like ANSI and ISO made SQL an official standard, and new versions of the standard have been released many times since then.

Interoperability and standardization

SQL works a little differently depending on which software you use. Even though there is a standard way SQL should work, 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 stored in special types of 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. But there are ways to add more features to SQL, letting it control processes and work with other programming styles.

Besides the basic SQL rules, 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 different ways to work with data besides using SQL. Some of these ways are designed to work with relationships between data points, just 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 created by a team at IBM between 1988 and 1994. It helps databases 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 back tables of information from faraway databases. SQL commands can also be prepared and 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 perfectly match its original design ideas. For example, in its basic theory, tables should be collections of unique items, but in SQL, tables can show the same item more than once and the order of items can matter in some cases.

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 also feel that SQL doesn't handle certain types of data, like JSON, as well as it could.

Another point of discussion is how SQL deals with missing values, shown as "Null". This is different from having a value of zero or an empty space, and it follows special rules in SQL.

SQL also allows tables to have duplicate rows, which can make it harder to work with in other programming languages. This is often managed by setting up special rules to make each row unique.

There can also be challenges when using SQL inside other programming languages that work in a more step-by-step way.

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.