SQL

Databases are central to websites as we know them today. It’s easy to argue that today’s world wide web only exists due to the possibilities created by modern databases. Yet managing a database is very complicated and it needs a structured approach. SQL, or structured query language, is the approach most commonly used to manage a database and it is a programming language that makes it easy to query and edit the information that is contained in a wide range of database management systems (DBMS).

The history of SQL

IBM has been the driver of many of the technologies we commonly use today, and that’s the case for SQL too. You need to think back to the 1970s to find the start of SQL as we know it today, because that’s when IBM created a new database software package called System R.

Note that the task of managing System R was given to SQL, even though at the start SEQUEL was the name IBM chose. People still sometimes pronounce SQL as SEQUEL, but everyone refers to the language as SQL in writing.

Another company called Relational Software, a precursor to Oracle, saw the real potential of SQL in 1979 and decided to release its own database based on it, called Oracle version 2.

SQL has now been around for many decades and it still offers incredibly flexibility to the companies that make use of it, as it is an excellent way to deal with databases that are distributed – in other words, databases which are run on several different networks at the same time.

ANSI and ISO has also certified SQL which has contributed to SQL becoming the standard query language for databases. In essence, SQL is the building blocks for countless database applications that are currently running on the internet.

SQL is so flexible that it can meet academic needs where research is paramount, while helping both private individuals and corporations run important database applications using SQL as the query language. As database technology has progressed SQL applications have become more and more accessible to ordinary people. One reason for this is the contribution of open-source versions of SQL. This includes SQLite, PostgreSQL and of course the incredibly popular MySQL.

How the SQL standard developed

Like all technology standards, the SQL standard has changed dramatically over the years, including the addition of key new features. Think the support for XML, for example, alongside regular expression matching and trigger plus reclusive queries and sequences that are standardised.

Interestingly, because SQL has become so large in size with the wide variety of features it supports, many SQL databases have simply decided not to implement the entire SQL standard. Both PostgreSQL and MySQL only support a limit set of SQL functions.

Also note that in many cases the way databases behave with respect to file storage and indexes are not so closely defined so there is a degree of discretion for vendors in terms of how they would like to implement SQL, and how it will behave. It is for this reason that as much as SQL is standardised, the implementations of SQL are not really compatible with each other – despite using the same base.

The key elements of the SQL language

So how does SQL work? The SQL language is based on several elements and to make SQL easy to use for developers every DBMS executes SQL language commands through a specific command line interface or CLI which processes SQL commands. The five key parts of the SQL language are:

• Clauses which are the individual components of each SQL statement or query
• Expressions produce scalar values or entire tables that consist out of rows and columns of data
• Predicates that specify conditions limiting the effects of SQL statements or queries, or that change the flow of a SQL program
• Queries that retrieve data from a database, according to the criteria specified
• Statements which controls program flow, SQL transactions, SQL sessions or database connections.

In a database SQL statements are used to send queries from the client program across to the database which contains the data. The SQL server will process the SQL statements and return the data that is requested to the client program. SQL statements is an easy way for users to execute very fast data operations using very simple inputs to generate queries that are essentially very complicated.

Basic structure of a SQL query

What does the most common SQL operation look like? SQL queries are the most common SQL operations in use. With a SQL query a user can search a database for the information that they require.

You execute a SQL query by using the “SELECT” statement and by using specific clauses to make your SQL query more specific:

• FROM is the clause that selects the table from which you’ll be drawing data
• WHERE will define the rows in which the search will be performed, note that all the rows for which WHERE is not true will not be included in the data delivered
• ORDER BY specifies the way the results from a query is sorted, without the ORDER BY clause the results you get will be sent in a completely random order

A typical SQL query

SELECT * FROM mytable WHERE active='1' ORDER BY name, surname;

Using SQL to control, define and manipulate data

While SQL as a language is primarily intended to store data, it is worth noting that the data stored in a SQL database will change over time – data is not static. Very simple SQL commands can be used to modify the data in a database, and the syntax that SQL uses is very easy to read, and very easy to use.

SQL for data manipulation

An essential function of SQL tables is the ability to manipulate data because database users can modify a table which is already in place. Users can remove values that already exist or change these values.

The INSERT command can add new rows to a table that already exists, and these new rows can either be NULL in value at the start, or already contain data.

SQL INSERT example

INSERT INTO mytable (gender, name, surname, phone) VALUES ('male','Adam','Smith','+1 345 837 7897');

Note that the UPDATE statement allows you to easily modify content that already exists in a SQL table.

SQL UPDATE example

UPDATE mytable SET  phone = '+1 345 837 7898' WHERE name = 'Adam' AND surname = 'Smith';

The delete command lets you remove rows in table which are no longer needed.

SQL DELETE example

DELETE FROM mytable WHERE name = 'Adam' AND surname = 'Smith';

Data control with SQL

Managing access to a database is always important, and SQL makes it easy to control who has access to your database.

The GRANT statement can authorise specific users to have access to your database. Here is an example:

GRANT ALL PRIVILEGES ON database TO db_user;

On the flipside, the REVOKE statement will remove all the privileges granted to a user. Here is an example:

REVOKE ALL PRIVILEGES ON database TO db_user;

SQL data definition

Data definition allows the user to define new tables and elements.

SQL CREATE example

CREATE – CREATE statement allows you to create a new table inside existing database.

CREATE TABLE mytable (gender VARCHAR(25), phone VARCHAR(32), name VARCHAR(64), surname VARCHAR(64), address VARCHAR(64));

SQL DROP example

DROP – DROP statement makes it possible to delete tables, which you no longer need

DROP TABLE mytable;

SQL TRUNCATE example

TRUNCATE – TRUNCATE statement allows you to delete all the content in the table, but keep the actual table intact and ready for further use

TRUNCATE TABLE mytable;

SQL ALTER example

The ALTER statement permits the user to modify an existing object.

ALTER TABLE mytable RENAME TO mysupertable;