The “Struc­tured Query Language” or SQL for short is one of the best-known pro­gram­ming languages. SQL serves as a universal interface for working with re­la­tion­al data or re­la­tion­al database man­age­ment systems (RDBMS). The language contains a con­fus­ing­ly large number of SQL commands. Let’s take a closer look at the most important ones and their un­der­ly­ing patterns.

$1 Domain Names – Register yours today!
  • Simple reg­is­tra­tion
  • Premium TLDs at great prices
  • 24/7 personal con­sul­tant included
  • Free privacy pro­tec­tion for eligible domains
Tip

Find out how to program with SQL in our SQL intro article with examples.

What are SQL commands?

SQL commands instruct database man­age­ment systems (DBMS) to perform certain actions. These include defining tables and their structure, entering, modifying, and deleting data, and executing queries.

The scope of SQL commands is defined in various ISO or ANSI standards. There are also many im­ple­men­ta­tion-specific dialects. This means that the im­ple­men­ta­tions of major providers like Post­greSQL, MySQL, Oracle DBMS, and Microsoft SQL Server each have own language variants. Some of them have their own commands, though most differ at least in terms of how they process strings and other data.

SQL includes several sub-languages, each of which covers different areas and contains its own commands. Let’s look at the main types of SQL commands.

What kinds of SQL commands are there?

The most important SQL commands can be roughly divided into five sub-languages. And each sub-language is re­spon­si­ble for different commands:

SQL sub-language Command Example
Data De­f­i­n­i­tion Language (DDL) Database schema de­f­i­n­i­tion commands: create, modify, and delete database tables; define primary keys, foreign keys, and con­straints. CREATE TABLE, DROP TABLE
Data Ma­nip­u­la­tion Language (DML) Commands to ma­nip­u­late data: edit, enter, and delete data sets. INSERT, UPDATE
Data Query Language (DQL) Commands to query and prepare data. SELECT
Data Control Language (DCL) Commands for rights man­age­ment. GRANT, REVOKE
Trans­ac­tion Control Language (TCL) Commands for trans­ac­tion control. COMMIT, ROLLBACK

What’s the un­der­ly­ing syntax of SQL commands?

Unlike other common pro­gram­ming languages, SQL is a de­clar­a­tive language. It describes the result to be achieved without spec­i­fy­ing which steps are necessary to achieve it. This char­ac­ter­is­tic is reflected in its typically longer commands. In turn, fewer lines of code are often required than with con­ven­tion­al im­per­a­tive languages.

A good example is the SQL command DROP TABLE IF EXISTS. That’s right, this is a single command that’s used to delete a table if it already exists:

DROP TABLE IF EXISTS SomeTable;

A Python example code with similar func­tion­al­i­ty includes several function calls and a branch that spans two lines:

if db.has_table(some_table):
    db.drop_table(some_table)

As shown, a single SQL command can consist of several keywords. This leads to visual sim­i­lar­i­ty between the commands. Here’s an example: The two SQL commands CREATE TABLE and CREATE OR REPLACE VIEW at first glance look to be man­i­fes­ta­tions of the CREATE command. But despite the sim­i­lar­i­ty, they’re in­de­pen­dent commands.

As known from other languages, some SQL commands accept pa­ra­me­ters. These are often the names of databases, tables, or columns. For example, we use the columns “Name” and “Age” from the “People” table:

SELECT Name, Age FROM People;
Note

SQL commands such as SELECT and CREATE TABLE are usually com­plete­ly cap­i­tal­ized even though SQL doesn’t dis­tin­guish between upper and lower case. It’s merely a widely used con­ven­tion.

Strictly speaking, SQL commands are state­ments. But there are also other syntax con­structs that act like commands. Here’s an overview of the most important SQL syntax elements:

Term De­scrip­tion Example
Statement Instructs the DBMS to perform an action; ends with a semicolon. CREATE TABLE People;
Clause Modifies an in­struc­tion; can only occur within in­struc­tions. WHERE, HAVING
Ex­pres­sion Returns a value when eval­u­at­ing. 6 * 7
Iden­ti­fi­er Name of a database object, variable, or procedure; can be qualified or un­qual­i­fied. dbname.tablename / tablename
Predicate Ex­pres­sion that evaluates TRUE, FALSE, or UNKNOWN. Age < 42
Query Special statement; returns found set of records. SELECT Name FROM People WHERE Age < 42;
Function Processes one or more values; usually creates a new value. UPPER('text') -- Returns 'TEXT'
Comment Used to comment on SQL code; ignored by the RDBMS. -- Comment up to the end of the line / /* If necessary, multiline comment */

Your overview of the most important SQL commands

Databases structure data in a hierarchy of storage layers, from the database server down to the value stored in a field. Since all aspects of a re­la­tion­al database man­age­ment system (RDBMS) can be con­trolled by SQL, SQL commands exist for each of the layers. Here’s an overview of the hierarchy of RDBMS objects:

RDBMS object Includes
Server Databases
Database Tables
Table Data sets
Data set Fields
Field Typed value

In addition to the primary RDBMS objects shown, other objects such as views and stored pro­ce­dures are used. These also have their own SQL commands. Next, we’ll take a closer look at the commands of the five main SQL sub-languages:

  1. Data De­f­i­n­i­tion Language (DDL)
  2. Data Ma­nip­u­la­tion Language (DML)
  3. Data Query Language (DQL)
  4. Data Control Language (DCL)
  5. Trans­ac­tion Control Language (TCL)

SQL commands for data de­f­i­n­i­tion

These SQL commands are used to define data struc­tures. These operate on aggregate objects like databases, tables and indexes. An aggregate object is used to store multiple records; a record contains multiple fields, with each field as­so­ci­at­ed with a column. The column defines the data type of the field, e.g., number, string, Boolean, etc. Con­straints like “must be unique”, “must not be null” etc. can also be defined for a column.

SQL commands to define databases

At the highest level of a re­la­tion­al database man­age­ment system (RDBMS) are databases. These can be created and deleted via SQL command:

SQL command De­scrip­tion Example
CREATE DATABASE Create new database. CREATE DATABASE Store;
DROP DATABASE Delete a database. DROP DATABASE Store;
USE Select database for the following commands. USE Store;

SQL commands to define tables

The con­struc­tion of a database begins with the de­f­i­n­i­tion of the database schema. The schema is the basis for ef­fi­cien­cy and requires a careful design that maps the in­di­vid­ual tables and their re­la­tion­ships to one another. Usually, the database schema is based on entity relation (ER) diagrams or special UML diagrams.

If the database schema is available as an abstract de­scrip­tion, the database structure is built using suitable SQL commands. Tables with columns and their types are defined and any links between tables are im­ple­ment­ed using “foreign keys”. The structure can be modified af­ter­wards by executing further SQL commands. Here’s an overview of the most important commands:

SQL command De­scrip­tion Example
CREATE TABLE Create new table in database; besides the name of the table, the names of columns with their types are defined. CREATE TABLE Customers ( Cus­tomerID INT UNSIGNED NOT NULL AUTO_INCREMENT, Cus­tomer­Name VARCHAR(255) NOT NULL, Country VARCHAR(60) NOT NULL, PRIMARY KEY (Cus­tomerID) );
ALTER TABLE Modify existing table: add/remove columns; change type or name of a column. ALTER TABLE Customers ADD Email VARCHAR(50);
TRUNCATE TABLE Delete all entries in a table; retain table structure in the process. TRUNCATE TABLE Customers;
DROP TABLE Delete table com­plete­ly; triggers an error during execution if the table doesn’t exist. DROP TABLE Customers;
DROP TABLE IF EXISTS Delete the table if it exists. DROP TABLE IF EXISTS Customers;
ALTER COLUMN Change data type of an existing column. ALTER TABLE Customers ALTER COLUMN Email VARCHAR(255);
DROP COLUMN Delete column of a table com­plete­ly. ALTER TABLE customers DROP COLUMN Email;
CREATE INDEX Create named index for column(s) in existing table. CREATE INDEX IdxEmail ON Customers (Email);
DROP INDEX Remove existing index. ALTER TABLE Customers DROP INDEX IdxEmail;

An important part of a database man­age­ment system (DBMS) is to ensure the con­sis­ten­cy of the data. For example, it’s possible to specify that the fields of in­di­vid­ual columns mustn’t be empty or that the values they contain must be within permitted limits. These are known as con­straints. Foreign keys can also be used to ensure that links between tables are created correctly.

To set con­straints for in­di­vid­ual columns, several clauses within data de­f­i­n­i­tion state­ments are used. Here are a couple without examples:

SQL command De­scrip­tion
NOT NULL Define that the value of the field must not be NULL.
UNIQUE Define that the value of the field may not occur twice within the column.
DEFAULT Set a default for the field; if no value is specified for the field when creating the record, the default will be used.
CHECK Set a condition that the value of the field must meet.
PRIMARY KEY Specify that the field contains the primary key; implies UNIQUE and NOT NULL.
FOREIGN KEY Specify that the value of the field must be a primary key of another table.

SQL commands for data ma­nip­u­la­tion

If the tables of a database are already defined, then it’s time for op­er­a­tions on in­di­vid­ual data records. Using the right SQL commands, data sets can be newly inserted, changed and deleted. This is also referred to as the CRUD operation (Create, Read, Update, Delete) which can also be found in NoSQL databases.

SQL command De­scrip­tion Example
INSERT INTO Enter data sets into a table. INSERT INTO Customers (Cus­tomer­Name) VALUES('Tester');
UPDATE Update fields in one or more data sets. UPDATE Customers SET Email = 'test@example.com' WHERE Cus­tomer­Name = 'Tester';
DELETE FROM Delete data sets from a table. DELETE FROM Customers WHERE Cus­tomer­Name = 'Tester';

Keep in mind that the clauses or functions “WHERE”, “SET”, and “VALUES” in the examples can also be found in other contexts. But despite the same name, slightly different rules may apply. Be careful when using the SQL commands UPDATE and DELETE FROM without a WHERE clause. All data records contained in the table will be changed or deleted.

SQL commands to query data

The SQL command SELECT is probably the language’s best-known command. It’s used to query data from the database. Normally, the data set isn’t changed in the process. That’s why the SELECT command is often ac­ces­si­ble to analysts. Let’s look at the basic com­po­nents of the SQL SELECT command:

SQL command De­scrip­tion Example
SELECT Query data in a database. SELECT Cus­tomer­Name FROM Customers;
WHERE Restrict query to records that match a given predicate. SELECT Email FROM Customers WHERE Cus­tomer­Name = 'Tester';
AS Define alias for table or row within a query. SELECT Cus­tomerID AS ID, Cus­tomer­Name AS Customer FROM Customers;
HAVING Limit query with aggregate function to ap­plic­a­ble records. SELECT COUNT(Cus­tomerID), Country FROM Customers HAVING COUNT(Cus­tomerID) >= 1;

Even though SELECT provides only one SQL command for querying data, it offers a wide range of possible ap­pli­ca­tions. For example, there are a number of clauses that are used to filter, sort, and summarize data. Let’s take a look at them.

SQL commands to refine queries

The SELECT SQL command returns a so-called result set. Con­cep­tu­al­ly, a result set can be thought of as a table with columns and values. In practice, it’s often necessary to filter or sort the results or to limit the number of records returned. For all these use cases there are cor­re­spond­ing clauses that can be used within a SELECT SQL command:

SQL command De­scrip­tion Example
DISTINCT Remove du­pli­cates from results. SELECT DISTINCT Country FROM Customers;
LIMIT Restrict the result set to the top results. SELECT * FROM Customers LIMIT 5;
GROUP BY Group result set according to a common char­ac­ter­is­tic. SELECT Cus­tomer­Name, Country FROM Customers GROUP BY Country;
ORDER BY Sort result set according to a char­ac­ter­is­tic. SELECT Cus­tomer­Name, Email FROM Customers SORT BY Cus­tomer­Name;
ASC Sort in ascending order. SELECT DISTINCT Country FROM Customers SORT BY Country ASC;
DESC Sort in de­scend­ing order. SELECT DISTINCT Country FROM Customers SORT BY Country DESC;

SQL commands for linking queries

In addition to refining the result set, it’s possible to link queries across multiple tables. Remember that a SELECT SQL command returns a result set. That’s why SQL contains commands that let you merge two result sets according to the rules of re­la­tion­al set theory.

To explain the SQL commands for joining queries in detail, more complex examples with several defined tables are necessary. That’s why we’ve omitted the example code here. Let’s look at the most important set op­er­a­tions:

SQL command De­scrip­tion
UNION Merge two result sets; the result sets must have columns of the same type in the same order. Their rows are merged.
INNER JOIN Filter two result sets according to a common criterion.
LEFT JOIN Match the result set of the left query with matching results of the right query; unmatched fields are set to NULL.
RIGHT JOIN Match the result set of the right query with matching results of the left query; unmatched fields are set to NULL.
FULL OUTER JOIN Com­bi­na­tion of a LEFT JOIN and RIGHT JOIN.

SQL commands to save and recreate queries

As we’ve seen, SQL queries can be quite complex. In practice, it’s useful to execute queries re­peat­ed­ly. For example, you can save the SQL commands as code and import them as needed. But this isn’t very efficient. Instead, there are special SQL commands that can be used to store complex queries as a unit directly in the database man­age­ment system (DBMS).

Let’s first look at views. A database view is roughly equiv­a­lent to a stored query. Note that a query returns a tabular result set as a result. Instead of dis­card­ing it, we store it as a view, which is also called a “virtual table”. As a rule, a view can only be read. There are a handful of SQL commands for working with views:

SQL command De­scrip­tion Example
CREATE VIEW Create a new view. CREATE VIEW Amer­i­can­Cus­tomers AS SELECT Cus­tomer­Name, Email FROM Customers WHERE Country = "US";
ALTER VIEW Edit an existing view. ALTER VIEW Amer­i­can­Cus­tomers AS SELECT * FROM Customers WHERE Country = "US";
CREATE OR REPLACE VIEW Create or replace an existing view. CREATE OR REPLACE VIEW Amer­i­can­Cus­tomers AS SELECT * FROM Customers WHERE Country = "US";
SHOW CREATE VIEW Show the SQL command used to create a view. SHOW CREATE VIEW Amer­i­can­Cus­tomers;
DROP VIEW Delete an existing view. DROP VIEW Amer­i­can­Cus­tomers;

In addition to database views, there are stored pro­ce­dures. Stored pro­ce­dures are used to re­peat­ed­ly execute queries and are more complex than views. They can take pa­ra­me­ters and use them to assemble queries dy­nam­i­cal­ly. It’s also possible to use a stored procedure for write access to the un­der­ly­ing data. Here’s an overview of the relevant SQL commands, omitting examples for reasons of space:

SQL command De­scrip­tion
CREATE PROCEDURE Create a new procedure.
ALTER PROCEDURE Edit an existing procedure.
CREATE OR REPLACE PROCEDURE Create or replace an existing procedure.
DROP PROCEDURE Delete an existing procedure.
CALL Execute a stored procedure.

The use of stored pro­ce­dures moves code from the client to the server. The sep­a­ra­tion of concerns leads to better security and per­for­mance. A possible dis­ad­van­tage is that the “business logic” contained within a stored procedure is outside the version control system. Since stored pro­ce­dures are heavily dependent on the sur­round­ing DBMS, at worst, a change of vendor will result in loss of func­tion­al­i­ty.

Note

Don’t confuse stored pro­ce­dures with the well-known prepared state­ments. Both improve security, but prepared state­ments are defined on the client ap­pli­ca­tion side.

SQL commands for access control

A single database server can contain multiple databases. To manage them in­de­pen­dent­ly, access control mech­a­nisms are used. For example, you can define the rights of in­di­vid­ual users to access databases and the tables they contain. You can also define user groups and assign users to them. Two main SQL commands come into play:

SQL command De­scrip­tion Example
GRANT Rights granted GRANT ALL ON SomeDB.* TO 'john'@'lo­cal­host';
REVOKE Rights revoked REVOKE INSERT ON *.* FROM 'john'@'lo­cal­host';

SQL commands for trans­ac­tion control

One of the ad­van­tages of using re­la­tion­al database man­age­ment systems (RDBMS) is the guarantee of “ACID” prop­er­ties which ensures that the data is always in a tidy state. Tech­ni­cal­ly, so-called trans­ac­tions are used, which are in­di­vis­i­ble as “atomic op­er­a­tions”. A trans­ac­tion is either completed in full and without errors, or an error has occurred. Then the in­di­vid­ual steps are undone. Let’s look at the SQL commands for trans­ac­tion control:

SQL command De­scrip­tion Example
START TRANS­AC­TION Mark the beginning of a trans­ac­tion. START TRANS­AC­TION;
COMMIT Complete a trans­ac­tion that’s already been started. START TRANS­AC­TION; TRUNCATE TABLE Customers; COMMIT;
ROLLBACK Cancel a started trans­ac­tion and return the dataset to the initial state. START TRANS­AC­TION; TRUNCATE TABLE Customers; ROLLBACK;
SAVEPOINT Create a named save point within a trans­ac­tion. START TRANS­AC­TION; SAVEPOINT Be­fore­Ad­dDa­ta;
ROLLBACK TO Jump back to a named save point. ROLLBACK TO Be­fore­Ad­dDa­ta;

An overview of the afore­men­tioned ACID prop­er­ties:

ACID property De­scrip­tion
Atomicity Trans­ac­tions are “in­di­vis­i­ble”. They are either executed com­plete­ly or not at all. If an atomic trans­ac­tion is aborted, the system is in the state before the trans­ac­tion started.
Con­sis­ten­cy After a trans­ac­tion has been executed, the data set is again con­sis­tent.
Isolation Trans­ac­tions executed at the same time must not affect each other.
Dura­bil­i­ty The effects of a trans­ac­tion must remain per­ma­nent­ly in the data set. And they must not be lost if, for example, the RDBMS crashes.
Tip

Pin the Digital Guide to your Windows taskbar. That way, you’ll always have our articles at hand for reference. Here’s how to:

Go to Main Menu