What You Need to Know About Structured Query Language

The Structured Query Language (SQL) is the set of instructions used to interact with a relational database. In fact, SQL is the only language that most databases understand. Whenever you interact with such a database, the software translates your commands (whether they are mouse clicks or form entries) into a SQL statement that the database knows how to interpret. SQL has three main components: the Data Manipulation Language (DML), the Data Definition Language (DDL), and the Data Control Language (DCL).

Common Uses of SQL on the Web

As a user of any database-driven software program, you’re probably using SQL, even if you don’t know it. For example, a database-driven dynamic web page (like most websites) takes user input from forms and clicks and uses it to compose a SQL query that retrieves information from the database required to generate the next web page.

Consider the example of a simple online catalog with a search function. The search page might consist of a form containing just a text box in which you enter a search term and then click a search button. When you click the button, the web server retrieves any records from the product database containing the search term and uses the results to create a web page specific to your request.
For example, if you searched for products containing the term "Irish," the server might use the following SQL statement to retrieve related products:

SELECT *
FROM products
WHERE name LIKE '%irish%'

Translated, this command retrieves any records from the database table named "products" that contain the characters "irish" anywhere within the product name.

Data Manipulation Language

The Data Manipulation Language (DML) contains the subset of SQL commands used most frequently — those that simply manipulate the contents of a database in some form. The four most common DML commands retrieve information from a database (the SELECT) command, add new information to a database (the INSERT command), modify information currently stored in a database (the UPDATE command), and remove information from a database (the DELETE command).

Data Definition Language

The Data Definition Language (DDL) contains commands that are less frequently used. DDL commands modify the actual structure of a database, rather than the database’s contents. Examples of commonly used DDL commands include those used to generate a new database table (CREATE TABLE), modify the structure of a database table (ALTER TABLE), and delete a database table (DROP TABLE).

Data Control Language

The Data Control Language (DCL) is used to manage user access to databases. It consists of two commands: the GRANT command, used to add database permissions for a user, and the REVOKE command, used to remove existing permissions. These two commands form the core of the relational database security model.

Structure of an SQL Command

Fortunately for those of us who aren’t computer programmers, SQL commands are designed to have a syntax similar to the English language. They normally begin with a command statement describing the action to take, followed by a clause that describes the target of the command (such as the specific table within a database affected by the command) and finally, a series of clauses that provide additional instructions.

Often, simply reading an SQL statement out loud will give you a very good idea of what the command is intended to do. Take a moment to read this example of a SQL statement:

DELETE
FROM students
WHERE graduation_year = 2014

Can you guess what this statement will do? It accesses the student's table of the database and deletes all records for students who graduated in 2014.