Looking for software development internships? Hackr.io is hiring!

Various Types of SQL Commands Explained

Posted in SQL
Various Types of SQL Commands Explained

Sequential Query Language or SQL is the most widely used programming language for managing data in a relational database management system. It is also used for stream processing in a relational data stream management system.

Oracle Database, MySQL, Microsoft SQL Server, PostgreSQL, and MariaDB are some of the most popular relational database management systems.

SQL commands are used for communicating with the database. Everything ranging from creating a table and adding data to modifying a table and setting user permissions is accomplished using SQL commands.

SQL Commands

So, learning SQL commands is the first step towards learning SQL. There are a total of 5 types of SQL commands, as described below:

DDL (Data Definition Language)

Data Definition Language or DDL commands are used for changing the structure of a table. In other words, DDL commands are capable of creating, deleting, and modifying data.
All DDL commands are auto-committed which means that changes made by them are automatically saved in the database. Following are the various DDL commands:

ALTER

Used for altering the structure of a database. Typically, the ALTER command is used either to add a new attribute or modify the characteristics of some existing attribute.

For adding new columns to the table:

General Syntax

ALTER TABLE table_name ADD (column_name1 data_type (size), column_name2 data_type (size),….., column_nameN data_type (size));

Example

ALTER TABLE Student ADD (Address varchar2(20));
ALTER TABLE Student ADD (Age number(2), Marks number(3));

For modifying an existing column in the table:

General Syntax:

ALTER TABLE table_name MODIFY (column_name new_data_type(new_size));

Example:

ALTER TABLE Student MODIFY (Name varchar2(20));

The ALTER command can also be used for dropping a column from the table:

General Syntax:

ALTER TABLE table_name DROP COLUMN column_name;

Example:

ALTER TABLE Student DROP COLUMN Age;
</pre.
Note: - It is not possible to do the following using the ALTER command:
    • Change the name of a column
    • Change the name of a table
    • Decrease the size of a column

CREATE

Used for creating a new table in the database. General Syntax:

CREATE TABLE table_name (column_name1 data_type(size), column_name2 data_type(size),…., column_nameN data_type(size));

Example:

CREATE TABLE Employee(Name varchar2(20), D.O.B. date, Salary number(6);

DROP

Used for deleting an entire table from the database and all the data stored in it.

General Syntax:

DROP TABLE table_name;

Example:

DROP TABLE Student;

RENAME

Used for renaming a table.

General Syntax:

RENAME old_table_name TO new_table_name

Example:

RENAME Student TO Student_Details

TRUNCATE

Used for deleting all rows from a table and free the space containing the table.

General Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE Student;

DML (Data Manipulation Language)

The DML or Data Manipulation Language commands help in modifying a relational database. These commands are not auto-committed, which simply means that all changes made to the database using DML commands aren’t automatically saved.

It is possible to rollback DML commands. Various DML commands are:

DELETE

Used for removing one or more rows from a table.

General Syntax:

DELETE FROM table_name; (deletes all rows from a table)
DELETE FROM table_name WHERE some_condition; (delete only the row(s) where the condition is true)

Example:

DELETE FROM Student;
DELETE FROM Student WHERE Name = “Akhil”;

INSERT

Used for inserting data into the row of a table.

General Syntax:

INSERT INTO table_name (column_name1, column_name2,….,column_nameN) VALUES (value1, value2,….,valueN);
OR
INSERT INTO table_name VALUES (value1, value2,….,valueN);

Example:

INSERT INTO Student (Name, Age) VALUES (“Vijay”, “25”);

Insert command can also be used for inserting data into a table from another table.

General Syntax:

INSERT INTO table_name1 SELECT column_name1, column_name2,….,column_nameN FROM table_name2;

Example:

INSERT INTO Student SELECT Id, Stream FROM Student_Subject_Details

UPDATE

Used to modify or update the value of a column in a table. It can update all rows or some selective rows in the table.

General Syntax:

UPDATE table_name SET column_name1 = value1, column_name2 = value2,….,column_nameN = valueN (for updating all rows)
UPDATE table_name SET column_name1 = value1, column_name2 = value2,….,column_nameN = valueN [WHERE CONDITION] (for updating particular rows)

Example:

UPDATE Student SET Name = “Akhil” WHERE Id = 22;

DCL (Data Control Language)

In order to protect information in a table from unauthorized access, DCL commands are used. A DCL command can either enable or disable a user from accessing information from a database. List of user access privileges:

  • ALTER
  • DELETE
  • INDEX
  • INSERT
  • SELECT
  • UPDATE

GRANT

Used for granting user access privileges to a database.

General Syntax:

GRANT object_privileges ON table_name TO user_name1, user_name2,….,user_nameN;
GRANT object_privileges ON table_name TO user_name1, user_name2,….,user_nameN WITH GRANT OPTION; (allows the grantee to grant user access privileges to others)

Example:

GRANT SELECT, UPDATE ON Student TO Akhil Bhadwal

This will allow the user to run only SELECT and UPDATE operations on the Student table.

GRANT ALL ON Student TO Akhil Bhadwal WITH GRANT OPTION

Allows the user to run all commands on the table as well as grant access privileges to other users.

REVOKE

Used for taking back permission given to a user.

General Syntax:

REVOKE object_privileges ON table_name FROM user1, user2,… userN;

Example:

REVOKE UPDATE ON Student FROM Akhil;

Note: - A user who is not the owner of a table but has been given the privilege to grant permissions to other users can also revoke permissions.

TCL (Transaction Control Language)

Transaction Control Language commands can only be used with DML commands. As these operations are auto-committed in the database, they can’t be used while creating or dropping tables. Various TCL commands are:

COMMIT

Used for saving all transactions made to a database. Ends the current transaction and makes all changes permanent that were made during the transaction. Releases all transaction locks acquired on tables.

General Syntax:

COMMIT;

Example:

DELETE FROM Student WHERE Age = 25;
COMMIT;

ROLLBACK

Used to undo transactions that aren’t yet saved in the database. Ends the transaction and undoes all changes made during the transaction. Releases all transaction locks acquired on tables.

General Syntax:

ROLLBACK;

Example:

DELETE FROM Student WHERE Age = 25;
ROLLBACK;

SAVEPOINT

Used for rolling back to a certain state known as the savepoint. Savepoints need to be created first so that they can be used for rollbacking transactions partially.

General Syntax:

SAVEPOINT savepoint_name;

Note: - An active savepoint is one that has been specified since the last COMMIT or ROLLBACK command.

DQL (Data Query Language)

DQL commands are used for fetching data from a relational database. There is only one command, which is the SELECT command.

Equivalent to the projection operation in relational algebra, SELECT command selects the attribute based on the condition described by the WHERE clause.

General Syntax:

SELECT expressions
FROM table_name
WHERE condition1, condition2,…., conditionN;

Example:

Suppose we have a relational table called Student that has all the information regarding a student, such as name, roll no., stream, age, address, etc. and we need to fetch data regarding all student names who are less than 18 years of age, then we can use the SELECT command as follows:

SELECT student_name
FROM student
WHERE age < 18;

The result will be a list of all the student names who are less than 18 years of age.

The SELECT command can also be used for eliminating duplicates from a table.

General Syntax:

SELECT DISTINCT column_name1, column_name2,…., column_nameN FROM table_name;

Example:

SELECT DISTINCT Name, Age FROM Student;

This command will scan through entire rows and will eliminate rows that are identical.

Rows retrieved using the SELECT command can be sorted in either ascending or descending order.

General Syntax:

SELECT column_name1, column_name2,…., column_nameN FROM table_name ORDER BY column_name; (gives results in ascending order)
SELECT column_name1, column_name2,…., column_nameN FROM table_name ORDER BY column_name DESC; (gives results in descending order)

Example:

SELECT Name, Age FROM Student ORDER BY Name;
SELECT Name, Age FROM Student ORDER BY Name DESC;

Conclusion

Acquiring knowledge of commonly used commands is the first step to be able to understand and work with SQL databases. Mastering SQL commands demands a good amount of practice and we hope this article will help you get started on your journey!

Preparing for an SQL interview? Check out these most important SQL interview questions to seize the opportunity.

People are also Reading:

Vijay Singh

Vijay Singh

My name is Vijay Singh Khatri, and I enjoy meeting new people and finding ways to help them have an uplifting experience. I have had a variety of customer service opportunities, through which I was able to have fewer returned products and increased repeat customers, when compared with co-workers. Currently working with hackr.io View all posts by the Author

Leave a comment

Your email will not be published
Cancel
Graig
Graig

Thanks for your marvelous posting! I genuinely enjoyed reading it, you
might be a great author.I will remember to bookmark your blog and may come back from now on. I want to encourage yourself to continue your
great work, have a nice day!