What is SQL?
In 1970, IBM developed a SQL language or structured query language which was used in programming and designing for managing data in an RDBMS or relational database management system. SQL can effectively handle structured data. It incorporates relationships among the entities and variables that are used in making the program. These database structures are stored as a database table.
In this blog, we shall be discussing various types of SQL commands. These are universal commands and can be used in other databases apart from the SQL server. You all must be aware of the names of other database servers. For keeping this blog simple, we shall be sticking to simple database queries of relational databases. After all, SQL is a Domain-specific Language.
What are the different types of SQL commands? Explain with an example of each.
There are three main types of basic SQL commands –
- DDL commands or Data Definition Language commands
- DML commands or Data Manipulation Language commands
- DCL commands or Data Control Language commands
These 3 categories of SQL commands are used for the distribution of privileges. Let’s discuss these SQL command types in detail.
What are DDL, DML, and DCL in SQL?
These are various DQL statements that will use a “Schema Relation” to access an SQL database or an Oracle Database. These commands help in database creation and database administration. This can also be considered as a database query language that uses various database schema descriptions for handling the database objects. To simplify the work of database administrators, it is often used GUI tool for Oracle. DDL, DML, and DCL help in building database objects, accessing a centralized database, and giving selected users database access. You can even scan all the data in the database by commands of this type.
These commands can also help you to save database backups and access databases across various database engines. You can make several database modifications across the whole database object structure. Overall, it improves the database performance of the Database Structure Schema. It can also help in changing the entire database views.
1. DDL SQL commands
The DDL or Data Definition Language is a set of commands that allows a user to drop, alter and create database objects. We will show you how to use it with the help of an example.
CREATE DDL commands
The create command allows the user to create objects in a database schema. The following example creates a table named “Sales”:
| CREATE TABLE employees.dbo ( ID int, Description varchar (8000), CustomerID int, Price decimal(8,2) ) |
|---|
The program shows how to create a table. The columns of the table have been also specified. With the help of this program, you can create a view, store a procedure, make database users (or relational database users), make triggers, and create keys in several database objects.
ALTER DDL commands
The alter command gives permission to users for modifying the existing database schema objects. An example that shows how to add a column named “employeeid” to the table created before:
ALTER TABLE employees.dbo ADD employeeid int NULL;
DROP DDL commands
The drop database command (or simply the DROP command), allows the user to drop an object like a database table, view the table, store the procedure or store the function for permissions on tables. The following example shows how to drop the employees.dbo table is created:
Drop table employees.dbo
These commands cover the various forms of DDL SQL commands of the standard database language.
2. DML SQL commands
The DML or Data Manipulation Language is a set of SQL commands, which gives user access privileges among users for manipulation of the data that is stored inside the tables. You can Insert data, Select data, Update data, or Delete data.
SELECT commands
Select command (also called SELECT Expressions) in SQL is used for choosing data from a given table. It is also used for viewing another object in the database. For extracting the right information, you can point out the columns, which need to be displayed and filtered. The example that has been given below shows the ID and Description data of the employees.dbo table:
Select ID,Description
From employees.dbo
INSERT commands
The insert command is used for the insertion of the data into a database schema object. It could be a table or a view. Have a look at the example given below and it will show you how to insert data into the employees.dbo table:
Insert into employees.dbo.values(1,’Employee Name’,3,1233)
UPDATE commands
The UPDATE command in SQL is used to update the information of database objects like tables. The following example will show you the method to use the update SQL command. In this case, the description will be changed:
UPDATE employees.dbo
SET Description = ’Sales Executive’
Where Description = ’Sales Manager’
The table where the information is updated could be a single table or a temporary table or a specified table like the Employee table.
DELETE commands
The delete command in SQL allows you to delete specified information or data from a database object. It could be a table (for example – table student.dbo) or it could also be used to delete a view.
For example – The following program given below will show you how to delete information from a table by using the delete SQL command. Here, the entry with an ID equal to 1 will be deleted:
DELETE FROM employees.dbo.
Where ID=1
These commands cover the topic of DDL. Now we will head over to DCL SQL commands.
3. DCL SQL commands
The DCL SQL commands or Data Control Language contains SQL commands used for handling the security. Let us understand these commands with the help of an example. Let us handle the information or data regarding employees’ salaries. The structure of database objects in this database file is organized in such a manner that it can only be viewed by HR or the top management. The important DCL SQL commands are the GRANT, REVOKE, and DENY commands.
GRANT commands
The Grant command allows the user to grant permissions to an object. The following example shows how the Grant command is used to give chosen permissions to the employee’s group through the employees.dbo table:
GRANT SELECT ON employees.dbo TO [mydomain\salaries];
REVOKE commands
As the name suggests, the Revoke command allows the handlers to revoke or take away the permissions for the specified database objects. In the following example, you will see how to use the Revoke command JohnRambo from a file called listEmployees:
REVOKE EXECUTE ON listEmployees to mydomain\JohnRambo
DENY commands
The DENY command is used to deny permissions for users to access specific objects. Now you will be able to understand the example given below where the users are blocked from using files that can be accessed by managers.
DENY UPDATE ON users.dbo to managers
These commands cover the entire topic of DCL SQL commands of the standard database language.
Conclusion
Thus, in general, query languages are used to store relational databases of all kinds. These query languages are used in the back-end of various stacks of web development and software development. SQL is also used as a complex database stack for applications of all kinds, such as bank database systems, or accounts database objects, which handles logical transactions in a commercial database. SQL is the best language for database management.
