DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Please enter at least three characters to search
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

Last call! Secure your stack and shape the future! Help dev teams across the globe navigate their software supply chain security challenges.

Modernize your data layer. Learn how to design cloud-native database architectures to meet the evolving demands of AI and GenAI workloads.

Releasing software shouldn't be stressful or risky. Learn how to leverage progressive delivery techniques to ensure safer deployments.

Avoid machine learning mistakes and boost model performance! Discover key ML patterns, anti-patterns, data strategies, and more.

Related

  • An Introduction to DDL, DML, and DCL Commands in MySQL: A Comprehensive Guide With Examples
  • SQL Data Manipulation Language (DML) Operations: Insert, Update, Delete
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years
  • Kafka JDBC Source Connector for Large Data

Trending

  • AI’s Role in Everyday Development
  • AWS to Azure Migration: A Cloudy Journey of Challenges and Triumphs
  • Agile and Quality Engineering: A Holistic Perspective
  • How Trustworthy Is Big Data?
  1. DZone
  2. Data Engineering
  3. Databases
  4. SQL Commands: A Brief Guide

SQL Commands: A Brief Guide

This article on SQL commands has briefly explained all the SQL commands, namely, DDL, DML, TCL, and DCL, with example codes.

By 
Sarang S Babu user avatar
Sarang S Babu
·
Dec. 08, 22 · Tutorial
Likes (3)
Comment
Save
Tweet
Share
5.8K Views

Join the DZone community and get the full member experience.

Join For Free

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:

SQL
 
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.

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

SQL
 
DROP TABLE TABLE_NAME;


Example:

Here we are deleting the table with the name Student.

SQL
 
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.

SQL
 
ALTER TABLE STUDENT ADD ADDRESS VARCHAR2(20)


  • Modifying the existing column of a table.

SQL
 
ALTER TABLE STUDENT MODIFY ADDRESS VARCHAR2(50)


  • Renaming the column of the table.

SQL
 
ALTER TABLE STUDENT RENAME ADDRESS TO STUDENT_ADDRESS


  • Deleting the column of the table.

SQL
 
ALTER TABLE STUDENT DROP(STUDENT_ADDRESS)


  • Adding a constraint to the column of the table.

SQL
 
ALTER TABLE STUDENT ADD CONSTRAINT stud_id_constraint PRIMARY KEY (Student_id)


  • Dropping an existing constraint of the table.
SQL
 
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:

SQL
 
TRUNCATE TABLE TABLE_NAME


Example:

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

SQL
 
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.

SQL
 
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.

SQL
 
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.

SQL
 
SELECT * FROM STUDENT;


  • Fetching selected columns from the table.

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

SQL
 
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.

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

SQL
 
GRANT UPDATE ON TAB1 TO ABC


REVOKE

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

Example:

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

SQL
 
COMMIT;


Example:

SQL
 
DELETE FROM STUDENT WHERE STUDENT_ID=2;

COMMIT;


ROLLBACK

Rollback to revert the changes done by the transaction. 

Syntax:

SQL
 
ROLLBACK;


Example:

SQL
 
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.

Control Language Data definition language Data manipulation language Database MySQL Relational database Command (computing) Data (computing) sql Data Types

Opinions expressed by DZone contributors are their own.

Related

  • An Introduction to DDL, DML, and DCL Commands in MySQL: A Comprehensive Guide With Examples
  • SQL Data Manipulation Language (DML) Operations: Insert, Update, Delete
  • Architecture and Code Design, Pt. 1: Relational Persistence Insights to Use Today and On the Upcoming Years
  • Kafka JDBC Source Connector for Large Data

Partner Resources

×

Comments
Oops! Something Went Wrong

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • support@dzone.com

Let's be friends:

Likes
There are no likes...yet! 👀
Be the first to like this post!
It looks like you're not logged in.
Sign in to see who liked this post!