SQL Commands: A Brief Guide

SQL stands for Structured Query Language. And SQL is used for communication with the database for accessing, creating, updating the data stored in the database.

SQL allows the user to do the following:

• Fetching data from the RDMS (Relational Database Management System) is enabled by SQL.
• SQL also allows users to provide the data description.
• Data can be defined and manipulated in the database with the help of SQL.
• Databases and tables stored in the database can also be created and dropped, and updated using SQL.
• The creation of stored procedures, views, and functions is also made possible by SQL.
• SQL also allows the users to set and remove permissions for the user on views, tables, databases, and procedures stored in the database.

SQL is a standard database language used by most relational database management systems such as MS Access, MySQL, Oracle, SQL Server, and Postgres for performing CRUD operations. CRUD operations consist of create, read, update and delete operations performed on the database.  

Tables in SQL

Data is stored in the form of relations, i.e., tables in the relational database. And in the table, data is organized in the form of rows and columns. And the named column of the relation is called the attribute of the relation. And the data is stored in the relation in the form of tuples or rows. And the unknown and inapplicable values are represented by the NULL in the relation.

Commands Used in SQL Are Categorized Into Four Different Categories

1. DDL —  Data Definition Language
2. DML —  Data Manipulation Language
3. DCL — Data Control Language
4. TCL — Transaction Control Language

DDL —  Data Definition Language

Data Definition Language is abbreviated as DDL, and the DDL commands are used for database structure specification. Commands for designing, creating, manipulating, and deleting the different objects of the database, like views, tables, indexes, procedures, etc., are provided by the DDL. 

DDL command output is put into the data dictionary. Create, Drop, Alter, and Truncate commands are examples of DDL commands.

CREATE

Create table command is used for table creation in the database. Tables in the database are organized in the form of rows and columns. There is a name for every table stored in the database, and any number of columns is there in the table(but at minimum, there is one column in the table). 

The data type is associated with every column stored in the database, which represents the type of value that can be stored in that column. Execution of the create table fails if the table with the same name already exists in the database. All the tables stored in the database must have a different name.

Syntax:

Syntax:
CREATE TABLE NAME_OF_THE_TABLE(
COLUMN1 COLUMN1DATATYPE,
 COLUMN2 COLUMN2DATATYPE,
COLUMN3 COLUMN3DATATYPE,
……..
);

Example:

Here we are creating the table with the name student with the fields student_id having data type integer, student_name with the data type varchar2, and dob with the data type date.

CREATE TABLE STUDENT(
STUDENT_ID INTEGER,
STUDENT_NAME VARCHAR2(2000),
DOB DATE
);

DROP

When there is a need to delete the entire table from the database, then use the DROP command for this purpose.

Syntax:

DROP TABLE TABLE_NAME;

Example:

Here we are deleting the table with the name Student.

DROP TABLE STUDENT;

ALTER

ALTER command is used to change the structure of the table. Through the ALTER command, we can change the data type of a column and the name of the column and also remove or add the constraints in the table columns.

Example:

• Adding the column to an existing table.

ALTER TABLE STUDENT ADD ADDRESS VARCHAR2(20)

• Modifying the existing column of a table.

ALTER TABLE STUDENT MODIFY ADDRESS VARCHAR2(50)

• Renaming the column of the table.

ALTER TABLE STUDENT RENAME ADDRESS TO STUDENT_ADDRESS

• Deleting the column of the table.

ALTER TABLE STUDENT DROP(STUDENT_ADDRESS)

• Adding a constraint to the column of the table.

ALTER TABLE STUDENT ADD CONSTRAINT stud_id_constraint PRIMARY KEY (Student_id)

• Dropping an existing constraint of the table.

ALTER TABLE STUDENT DROP CONSTRAINT stud_id_constraint

TRUNCATE

The TRUNCATE command is a DDL command used to delete all data from an existing table, but it does not allow the use of a where clause. As compared to the DELETE command, TRUNCATE is found to be faster in terms of deleting all data from the table present in the database.

Syntax:

TRUNCATE TABLE TABLE_NAME

Example:

Here the truncate command is used for deleting all the existing data of the table student.

TRUNCATE TABLE STUDENT

DML —  Data Manipulation Language

Data Manipulation Language is abbreviated as DML, and it allows the user for fetching, modify, and insert data in a relational database. Commands for data accessing, manipulation, and data deletion are provided by the DML. 

DML allows the user to access the data by specifying what data is required instead of specifying how to get that data. Insert, Select, Delete, and Update are examples of DML commands.

INSERT

Insert command is used for the addition of rows (Tuples) into an existing table. Alternate syntaxes are supported by this command. And these are given below:

Example:

• If the name of the column is not provided in the insert statement, then the values of the columns are provided in the order specified at the time of the table creation.

INSERT INTO STUDENT VALUES(1,’ABC’,’1-JUN-2000’);

• If the column names are provided in the insert statement, then the data entered in the values clause has the data type the same as the data type of columns in the order of their positions.

INSERT INTO STUDENT (STUDENT_ID,DOB,STUDENT_NAME) VALUES(1,’ABC’,’1-JUN-2000’);

SELECT

The select command is considered the most commonly used command in SQL. SQL command is used for accessing or fetching the data from the database. Data can be fetched from the database by specifying some conditions, or we can also fetch the entire table from the database. 

The output of the SQL statement is returned in the form of a table. And the result returned by the Select statement is also called the result set

We can also specify some selected columns in the select statement if we do not want to display all columns of the table.

Example:

• Fetching entire data from the table.

SELECT * FROM STUDENT;

• Fetching selected columns from the table.

SELECT STUDENT_ID,STUDENT_NAME FROM STUDENT;

DELETE

Through the DELETE command, we can delete records present in tables of the database. It is the responsibility of the database to make sure that constraints will not be violated in the execution of the delete command. If there is any violation of the constraints, then it will fail the statement execution.

Example:

The command to delete the data of the student having id 6 is given below:

DELETE FROM STUDENT WHERE STUDENT_ID = 6;

UPDATE

The update command is used for modifying the already stored rows in the table in the database. It is ensured by the database that no constraint is violated at the time of update command execution. If it is so, then the query execution will fail.

Example:

Below is the command given to update the name of the student having id 2.

UPDATE STUDENT SET STUDENT_NAME=’XYZ’ WHERE ID=2;

DCL —  Data Control Language

Data Control Language is abbreviated as the DCL, and these commands allow the users to provide and withdraw access to the different database objects stored in the relational database. It allows the user to provide and withdraw access to views, procedures, tables, etc. 

Generally, the DBA (Database Administrator) is provided with the right to grant and revoke the privileges of the database objects. When the query is submitted by the user, then that query is checked by the database against the privileges granted, and if the query is not authorized, then the query will be rejected by the database. Grant and revoke commands are examples of DCL commands.

GRANT

SQL grant statement is used for granting privileges to the user for selecting, updating, inserting, deleting, etc., on the tables or views stored in the database.

Example:

Suppose we want to grant an UPDATE privilege for the table name TAB1 to the user ABC. Then the following command will be used:

GRANT UPDATE ON TAB1 TO ABC

REVOKE

Revoke SQL statement is used for revoking the granted privileges to the user.

Example:

REVOKE privilege_name
ON object_name
FROM {user_name |PUBLIC |role_name}

TCL —  Transaction Control Language

Transaction Control Language is abbreviated as the TCL, and commands for transaction initiation and end are provided by the TCL. A transaction is a group of SQL statements that are to be executed in an atomic manner. All the changes made in the transaction are applied to the database with the help of commit and rollback to undo all the changes made in the transaction. Commit and Rollback are examples of TCL commands.

COMMIT

Commit SQL statement is used for updating all the changes done in the transaction to the database.

Syntax:

COMMIT;

Example:

DELETE FROM STUDENT WHERE STUDENT_ID=2;

COMMIT;

ROLLBACK

Rollback to revert the changes done by the transaction. 

Syntax:

ROLLBACK;

Example:

DELETE FROM STUDENT WHERE STUDENT_ID=2;

ROLLBACK;

Conclusion

This article on SQL commands has briefly explained all the SQL commands, namely, DDL, DML, TCL, and DCL, with example codes. Hope this tiny guide will help you in quickly prepare for your job interview or next term exams! Thanks for reading.