How to use the most important SQL commands
The “Structured Query Language” or SQL for short is one of the best-known programming languages. SQL serves as a universal interface for working with relational data or relational database management systems (RDBMS). The language contains a confusingly large number of SQL commands. Let’s take a closer look at the most important ones and their underlying patterns.
- What are SQL commands?
- Your overview of the most important SQL commands
- SQL commands to query data
- SQL commands for access control
- SQL commands for transaction control
$1 Domain Names
Register great TLDs for less than $1 for the first year.
Why wait? Grab your favorite domain name today!
Find out how to program with SQL in our SQL intro article with examples.
What are SQL commands?
SQL commands instruct database management 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 implementation-specific dialects. This means that the implementations of major providers like PostgreSQL, 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 responsible for different commands:
|Data Definition Language (DDL)
|Database schema definition commands: create, modify, and delete database tables; define primary keys, foreign keys, and constraints.
|CREATE TABLE, DROP TABLE
|Data Manipulation Language (DML)
|Commands to manipulate data: edit, enter, and delete data sets.
|Data Query Language (DQL)
|Commands to query and prepare data.
|Data Control Language (DCL)
|Commands for rights management.
|Transaction Control Language (TCL)
|Commands for transaction control.
What’s the underlying syntax of SQL commands?
Unlike other common programming languages, SQL is a declarative language. It describes the result to be achieved without specifying which steps are necessary to achieve it. This characteristic is reflected in its typically longer commands. In turn, fewer lines of code are often required than with conventional imperative 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 functionality includes several function calls and a branch that spans two lines:
As shown, a single SQL command can consist of several keywords. This leads to visual similarity between the commands. Here’s an example: The two SQL commands CREATE TABLE and CREATE OR REPLACE VIEW at first glance look to be manifestations of the CREATE command. But despite the similarity, they’re independent commands.
As known from other languages, some SQL commands accept parameters. 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;
SQL commands such as SELECT and CREATE TABLE are usually completely capitalized even though SQL doesn’t distinguish between upper and lower case. It’s merely a widely used convention.
Strictly speaking, SQL commands are statements. But there are also other syntax constructs that act like commands. Here’s an overview of the most important SQL syntax elements:
|Instructs the DBMS to perform an action; ends with a semicolon.
|CREATE TABLE People;
|Modifies an instruction; can only occur within instructions.
|Returns a value when evaluating.
|6 * 7
|Name of a database object, variable, or procedure; can be qualified or unqualified.
|dbname.tablename / tablename
|Expression that evaluates TRUE, FALSE, or UNKNOWN.
|Age < 42
|Special statement; returns found set of records.
|SELECT Name FROM People WHERE Age < 42;
|Processes one or more values; usually creates a new value.
|UPPER('text') -- Returns 'TEXT'
|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 relational database management system (RDBMS) can be controlled by SQL, SQL commands exist for each of the layers. Here’s an overview of the hierarchy of RDBMS objects:
In addition to the primary RDBMS objects shown, other objects such as views and stored procedures 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:
SQL commands for data definition
These SQL commands are used to define data structures. 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 associated with a column. The column defines the data type of the field, e.g., number, string, Boolean, etc. Constraints 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 relational database management system (RDBMS) are databases. These can be created and deleted via SQL command:
|Create new database.
|CREATE DATABASE Store;
|Delete a database.
|DROP DATABASE Store;
|Select database for the following commands.
SQL commands to define tables
The construction of a database begins with the definition of the database schema. The schema is the basis for efficiency and requires a careful design that maps the individual tables and their relationships 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 description, the database structure is built using suitable SQL commands. Tables with columns and their types are defined and any links between tables are implemented using “foreign keys”. The structure can be modified afterwards by executing further SQL commands. Here’s an overview of the most important commands:
|Create new table in database; besides the name of the table, the names of columns with their types are defined.
|CREATE TABLE Customers ( CustomerID INT UNSIGNED NOT NULL AUTO_INCREMENT, CustomerName VARCHAR(255) NOT NULL, Country VARCHAR(60) NOT NULL, PRIMARY KEY (CustomerID) );
|Modify existing table: add/remove columns; change type or name of a column.
|ALTER TABLE Customers ADD Email VARCHAR(50);
|Delete all entries in a table; retain table structure in the process.
|TRUNCATE TABLE Customers;
|Delete table completely; 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;
|Change data type of an existing column.
|ALTER TABLE Customers ALTER COLUMN Email VARCHAR(255);
|Delete column of a table completely.
|ALTER TABLE customers DROP COLUMN Email;
|Create named index for column(s) in existing table.
|CREATE INDEX IdxEmail ON Customers (Email);
|Remove existing index.
|ALTER TABLE Customers DROP INDEX IdxEmail;
An important part of a database management system (DBMS) is to ensure the consistency of the data. For example, it’s possible to specify that the fields of individual columns mustn’t be empty or that the values they contain must be within permitted limits. These are known as constraints. Foreign keys can also be used to ensure that links between tables are created correctly.
To set constraints for individual columns, several clauses within data definition statements are used. Here are a couple without examples:
|Define that the value of the field must not be NULL.
|Define that the value of the field may not occur twice within the column.
|Set a default for the field; if no value is specified for the field when creating the record, the default will be used.
|Set a condition that the value of the field must meet.
|Specify that the field contains the primary key; implies UNIQUE and NOT NULL.
|Specify that the value of the field must be a primary key of another table.
SQL commands for data manipulation
If the tables of a database are already defined, then it’s time for operations on individual 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.
|Enter data sets into a table.
|INSERT INTO Customers (CustomerName) VALUES('Tester');
|Update fields in one or more data sets.
|UPDATE Customers SET Email = 'email@example.com' WHERE CustomerName = 'Tester';
|Delete data sets from a table.
|DELETE FROM Customers WHERE CustomerName = '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 accessible to analysts. Let’s look at the basic components of the SQL SELECT command:
|Query data in a database.
|SELECT CustomerName FROM Customers;
|Restrict query to records that match a given predicate.
|SELECT Email FROM Customers WHERE CustomerName = 'Tester';
|Define alias for table or row within a query.
|SELECT CustomerID AS ID, CustomerName AS Customer FROM Customers;
|Limit query with aggregate function to applicable records.
|SELECT COUNT(CustomerID), Country FROM Customers HAVING COUNT(CustomerID) >= 1;
Even though SELECT provides only one SQL command for querying data, it offers a wide range of possible applications. 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. Conceptually, 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 corresponding clauses that can be used within a SELECT SQL command:
|Remove duplicates from results.
|SELECT DISTINCT Country FROM Customers;
|Restrict the result set to the top results.
|SELECT * FROM Customers LIMIT 5;
|Group result set according to a common characteristic.
|SELECT CustomerName, Country FROM Customers GROUP BY Country;
|Sort result set according to a characteristic.
|SELECT CustomerName, Email FROM Customers SORT BY CustomerName;
|Sort in ascending order.
|SELECT DISTINCT Country FROM Customers SORT BY Country ASC;
|Sort in descending 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 relational 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 operations:
|Merge two result sets; the result sets must have columns of the same type in the same order. Their rows are merged.
|Filter two result sets according to a common criterion.
|Match the result set of the left query with matching results of the right query; unmatched fields are set to NULL.
|Match the result set of the right query with matching results of the left query; unmatched fields are set to NULL.
|FULL OUTER JOIN
|Combination 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 repeatedly. 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 management system (DBMS).
Let’s first look at views. A database view is roughly equivalent to a stored query. Note that a query returns a tabular result set as a result. Instead of discarding 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:
|Create a new view.
|CREATE VIEW AmericanCustomers AS SELECT CustomerName, Email FROM Customers WHERE Country = "US";
|Edit an existing view.
|ALTER VIEW AmericanCustomers AS SELECT * FROM Customers WHERE Country = "US";
|CREATE OR REPLACE VIEW
|Create or replace an existing view.
|CREATE OR REPLACE VIEW AmericanCustomers AS SELECT * FROM Customers WHERE Country = "US";
|SHOW CREATE VIEW
|Show the SQL command used to create a view.
|SHOW CREATE VIEW AmericanCustomers;
|Delete an existing view.
|DROP VIEW AmericanCustomers;
In addition to database views, there are stored procedures. Stored procedures are used to repeatedly execute queries and are more complex than views. They can take parameters and use them to assemble queries dynamically. It’s also possible to use a stored procedure for write access to the underlying data. Here’s an overview of the relevant SQL commands, omitting examples for reasons of space:
|Create a new procedure.
|Edit an existing procedure.
|CREATE OR REPLACE PROCEDURE
|Create or replace an existing procedure.
|Delete an existing procedure.
|Execute a stored procedure.
The use of stored procedures moves code from the client to the server. The separation of concerns leads to better security and performance. A possible disadvantage is that the “business logic” contained within a stored procedure is outside the version control system. Since stored procedures are heavily dependent on the surrounding DBMS, at worst, a change of vendor will result in loss of functionality.
Don’t confuse stored procedures with the well-known prepared statements. Both improve security, but prepared statements are defined on the client application side.
SQL commands for access control
A single database server can contain multiple databases. To manage them independently, access control mechanisms are used. For example, you can define the rights of individual 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:
|GRANT ALL ON SomeDB.* TO 'john'@'localhost';
|REVOKE INSERT ON *.* FROM 'john'@'localhost';
SQL commands for transaction control
One of the advantages of using relational database management systems (RDBMS) is the guarantee of “ACID” properties which ensures that the data is always in a tidy state. Technically, so-called transactions are used, which are indivisible as “atomic operations”. A transaction is either completed in full and without errors, or an error has occurred. Then the individual steps are undone. Let’s look at the SQL commands for transaction control:
|Mark the beginning of a transaction.
|Complete a transaction that’s already been started.
|START TRANSACTION; TRUNCATE TABLE Customers; COMMIT;
|Cancel a started transaction and return the dataset to the initial state.
|START TRANSACTION; TRUNCATE TABLE Customers; ROLLBACK;
|Create a named save point within a transaction.
|START TRANSACTION; SAVEPOINT BeforeAddData;
|Jump back to a named save point.
|ROLLBACK TO BeforeAddData;
An overview of the aforementioned ACID properties:
|Transactions are “indivisible”. They are either executed completely or not at all. If an atomic transaction is aborted, the system is in the state before the transaction started.
|After a transaction has been executed, the data set is again consistent.
|Transactions executed at the same time must not affect each other.
|The effects of a transaction must remain permanently in the data set. And they must not be lost if, for example, the RDBMS crashes.