Introduction
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.
Some history
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:
|
1 2 3 4 5 6 7 |
CREATE TABLE dbo.sales ( ID int, Description varchar (8000), CustomerID int, Price decimal(8,2) ) |
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.
Alter commands
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:
|
1 |
ALTER TABLE dbo.sales ADD taxid int NULL; |
Drop commands
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:
|
1 |
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.
Select command
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:
|
1 2 |
Select ID,Description From dbo.sales |
Insert command
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:
|
1 |
Insert into dbo.sales values(1,’HP Product’,3,1233) |
Update command
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:
|
1 2 3 |
UPDATE dbo.sales SET Description=’HP Product v2’ Where Description =’HP Product’ |
Delete command
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:
|
1 2 |
DELETE FROM dbo.sales Where ID=1 |
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.
GRANT Sentences
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:
|
1 |
GRANT SELECT ON dbo.sales TO [mydomain\sales]; |
REVOKE Statemets
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:
|
1 |
REVOKE EXECUTE ON dbo.listCustomers to mydomain\jrambo |
DENY statements
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.
|
1 |
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)
Conclusion
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.
He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs. He writes SQL Server training materials for certification exams.
He also helps with translating SQLShack articles to Spanish
View all posts by Daniel Calbimonte
- PostgreSQL tutorial to create a user - November 12, 2023
- PostgreSQL Tutorial for beginners - April 6, 2023
- PSQL stored procedures overview and examples - February 14, 2023