What is SQL?
This guide should help you understand the programming language that defined the database query
Anyone who's spent any time working in IT will likely have come across SQL (Structured Query Language), even if only in passing. SQL - commonly pronounced 'sequel' - is one of the most widely-used programming languages for managing databases. It's particularly useful for handling structured data in relational databases - where the information in one table is connected to the information in another.
There are lots of benefits to using SQL as a programming language, particularly given that multiple records can be accessed with a single command, making 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.
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
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.