What is SQL?

Experienced IT professionals will more than likely have encountered SQL (Structured Query Language) and understand what it is. Commonly pronounced as ‘sequel’, SQL is one of the most widely-used programming languages for managing databases.

SQL is particularly useful for handling structured data in relational databases, where the information in one table is connected to information in another.

There are a number of benefits to using SQL as a programming language. Given that multiple records can be accessed with a single command, this makes it a much faster tool to use compared to legacy read/write tools such as ISAM or VSAM.

SQL is based upon tables and rows, with each query requesting information from a data set housed in each of these tables - or rows within tables.

What is SQL?

The concept of SQL was first developed in the 1970s by Edgar F. Codd, a pioneer in relational database management. He wanted to create a system that would make it easier to manage shared databases.

IBM computer scientists Donald D. Chamberlin and Raymond F. Boyce picked up on the theory behind Codd's research and began developing the concept, using it to query information within System R, the company's semi-relational database.

First known as SEQUEL (Structured English Query Language), IBM continued to evolve the technology and, in 1979, Oracle (previously called Relational Software) released the first commercialised version of the system.

What elements comprise SQL?

SQL language consists of a number of separate elements, all of which comprise a 'statement'. Statements, or queries, start with a term like SELECT or CREATE, and finish with a semicolon, indicating the end of the query.

Here are the elements that you typically find in SQL language:

• Clauses - the individual components of a statement; like 'UPDATE' or 'WHERE' - these set the nature of the query
• Predicates - these specify conditions that can change the scope of the query: for instance, stipulating either 'BETWEEN' or 'ALL' will give you different datasets; the former, a range between x and y, or the latter, the entire data that fits your query.
• Expressions - expressions can produce scalar values (a storage location paired with an identifier) or tables, containing columns and rows.
• Queries - these retrieve data relevant to the criteria you define.
• Statements - statements are the way queries are sent from your SQL software to the database server. They start with a term like SELECT or CREATE, (your clause) and finish with a semicolon, indicating the end of the query.

Common SQL queries

Codecademy put together a useful list of common SQL queries that demonstrate how SQL is used to query and manipulate data. We've used some examples below:

• ALTER TABLE - this lets you add new columns to a database, increasing the kinds of data it can record.
• CREATE TABLE - adding a new table lets your database store a whole new type of data.
• ORDER BY - this is a useful command to ensure the data you query is presented in a useful manner - for instance, alphabetically.
• UPDATE - updating a database lets you alter rows, say for instance if the data has changed, or you've found it to be incorrect

SQL servers

Most IT professionals are more likely to interact with SQL via the database server software it powers than the language itself. SQL powers database software such as Oracle Database, MySQL, PostgreSQL and Microsoft's venerable family of SQL Server products.

SQL database servers have been around for decades, and many businesses rely on them to power their applications. Due to their age, SQL databases also have a much greater bedrock of community support resources available.

Some organisations choose to use NoSQL databases rather than SQL, which is seen by some as being more scalable than SQL servers due to the ability to add more nodes. NoSQL is non-relational, and does not require a predefined schema for its databases. MongoDB and Couchbase are both examples of NoSQL servers.

SQL data manipulation

SQL's ability to change and edit data makes it an incredibly useful programming language. Rather than simply storing data, you can issue commands to change it when necessary. Data isn't often very useful when it's out of data, so being able to update your database to keep your data accurate is essential.

SQL Security

One of the most pervasive types of cyber attack facing businesses is the SQL injection attack.

SQL injection attacks allow cyber attackers to view, modify, delete, or otherwise access the data held in these databases if they are relied upon by vulnerable websites or web applications.

Typically targeting websites or web apps which both rely on SQL-based databases and use web forms for various user functions, cyber criminals can exploit vulnerabilities in these websites or apps to query the back-end database for themselves.

They can also knock services offline using the same method, if data theft isn’t their game.

Web forms are used for a variety of purposes but one of the most common uses is for login pages where users enter their usernames and passwords. A website or web app then uses the information taken from these fields, cross-references it with the data in its SQL database and, if the two credentials match, then the user can access their account.

SQL injection attacks see hackers submit specially crafted lines of code in these input fields in an attempt to submit their own query to the database and access it in the way they want. Many web forms are set up to prevent SQL injections from being possible - they’re coded so they only accept valid username and/or password characters.

However, some web forms are not set up as securely and can allow attackers to inject their custom code which often leads to the theft of data.