Daniel Calbimonte

SQL commands in SQL Server

November 22, 2021 by

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

3 main types of commands

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

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:

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:

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:

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.

DML SQL Commands

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:

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:

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:

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:

For more information about DML, please refer to our related articles below:

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.

DCL SQL 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:

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:

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.

For more information about DCL, please refer to our related articles:

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.

Daniel Calbimonte
Latest posts by Daniel Calbimonte (see all)
DDL, DML, SQL commands, T-SQL

About Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 13 years of experience working with different databases. 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

258 Views