If you are here, it means that you want to learn the SQL commands. This article applies to SQL Server especially, but most of the theory is similar to Oracle, MySQL, MariaDB, PostgreSQL, and other databases. The SQL commands are instructions that we send to the database to get information, manipulate the information or create objects, modify them, and handle the access to the information.
From ancient times (1970) IBM developed a SQL language with some Commands to query information from databases. The SQL language was created and evolved. Until today, we are using that language to query the relational database with some extensions for each database.
In this article, we will talk about the most important SQL sentences. Most of them are universal and can apply to other databases different than SQL Server.
DDL DML DCL SQL sentences
There are 3 main types of commands. DDL (Data Definition Language) commands, DML (Data Manipulation Language) commands, and DCL (Data Control Language) commands. Let’s talk about them.
DDL SQL Statements
The Data Definition Language allows to create, alter, drop and database objects. Here you have some examples:
Create SQL commands
The create allows to create objects. The following example creates a Table named sales:
CREATE TABLE dbo.sales
Description varchar (8000),
The previous example shows how to create a table and we also specified the columns for the table. You can also create a view, a stored procedure, database users, databases, triggers, keys, and several other database objects.
The alter command allows to modify an existing database object. The following example shows how to add a column named taxid to the table created previously:
ALTER TABLE dbo.sales ADD taxid int NULL;
The drop command, allows to drop an object like a table, view, stored procedure, function, etc. The following example shows how to drop the dbo.sales table created:
Drop table dbo.sales
For more information about DDL, please refer to our related articles:
DML SQL commands
The Data Manipulation Language allows to manipulate the information. You can Select (see the information), insert data, Update data, Delete data.
The select command is used to return data from a table or view or another database object. You can specify the columns to show and filter the data. The following example will show the ID, Description data of the dbo.sales table:
The insert command is used to insert data into a database object like a table or view. The following example will show how to insert data into the dbo.sales table:
Insert into dbo.sales values(1,’HP Product’,3,1233)
The update command is used to update the information of database objects like tables. The following example will show how to use the update SQL sentences. The example will change the Description from HP Product to HP Product v2:
SET Description=’HP Product v2’
Where Description =’HP Product’
The delete command allows to delete information from a database object like a table or a view. In the next example, we will show how to delete information from a table using the delete SQL command. The example shows how to delete from the dbo.sales table the sales where the ID is equal to 1:
DELETE FROM dbo.sales
For more information about DML, please refer to our related articles below:
- Data Manipulation Language (DML)
- SQL examples for beginners: SQL SELECT statement usage
- SQL UPDATE syntax explained
- The internals of SQL Truncate and SQL Delete statements
DCL SQL commands
The Data Control Language contains SQL commands used to handle the security. For example, the employees’ salaries are confidential information and may not be visible to all the employees. Just to some administrative levels and some HR members. The main commands are the GRANT, REVOKE and DENY commands.
The Grant command allows to grant permissions to an object. The following example will grant select permissions to the windows sales group to the dbo.sales table:
GRANT SELECT ON dbo.sales TO [mydomain\sales];
The revoke statement allows to revoke permissions to database objects. The following example shows how to revoke execute permissions of a stored procedure named listCustomers to the Windows user JRambo:
REVOKE EXECUTE ON dbo.listCustomers to mydomain\jrambo
The DENY sentence allows to deny permissions to certain objects. The main difference between a Revoke and a Deny permission is that the revoke undoes permission while a deny blocks the access to that permission. The following example will deny the update permission to the dbo.users view to a SQL group named managers.
DENY UPDATE ON dbo.users to managers
For more information about DCL, please refer to our related articles:
- Grant, With Grant, Revoke and Deny statements in SQL Server and Azure SQL Database
- GRANT (Transact-SQL)
- REVOKE (Transact-SQL)
- DENY (Transact-SQL)
In this article, we learned the different commands used. We explained 3 main categories. DDL to define the objects, DML to manage the information and finally DCL to handle the security.