What are the types of SQL commands with Example ?

Share

SQL is a query language that is very popular nowdays for accessing, modifying and retrieving data from database. It stands for structured query language. Each and every organisation has been investing a decent amount of money to maintain their data in structured way that is on database.

In short, we use SQL language to access data from database (A database may contain so many tables and those tables are used for storing row data and figures in the form of columns and rows in meaningful way).

Lots of Relational database management system uses SQL such as My SQL Server,Oracle and  MS SQL server and so on.

RDBMS is the basis for SQL, and for all the modern database systems like MS SQL Server, IBM DB2, Oracle, MySQL and Microsoft Access.

A Relational Database Management System (RDBMS) is a database management system is based on relational database and firstly it was introduced by E.F Codd.

Today all the RDBMS (MySQL, Oracle, Sybase, MS Access) uses SQL as the Standard Database Language and SQL is used to perform all types of Data Operations in RDBMS.

It is not case sensitive but its better to write all keywords in capital (like SELECT,DATABASE ) to make the queries more readable and clean. Like other languages, to execute multiple queries at a time, each query should end up with semicolon(;).

Some SQL Advantages :-

Easy to learn:-

It is easy to understand and learn as its all keywords and syntax are alike to English  words.

Not case Sensitive:-
Unlike other programming languages such as JAVA and C, SQL is not case sensitive which  means queries can be write and execute properly ,despite of considering uppercase or lowercase.

Communicate with database:-

It is used to create, update,delete and manipulate tables, data and database as well.

Types of SQL commands with Example ?

  1. DATA DEFINITION LANGUAGE (DDL)
  2. DATA MANIPULATION LANGUAGE (DML)
  3. TRANSACTION CONTROL LANGUAGE (TCL)
  4. DATA QUERY LANGUAGE (DQL)
  5. DATA CONTROL LANGUAGE (DCL)

 

  • DATA DEFINITION LANGUAGE (DDL)

It is a standard that defines the different structures in Database.
These statements Create, Modify and removes the database objects such as table, indexes and users.
for Ex.-CREATE, SHOW, ALTER and DROP.

  • ALTER Command with Usage :

Alter statement is used to change the structure of a table. It can be associated with adding a new column, deleting and modifying an existing column .

Syntax:

ALTER TABLE table_name
ADD new_columnName / MODIFY  COLUMN columnName dataType / DROP COLUMN columnName;

Add a column to existing column :

Suppose there is a table name customers and we require to add a column customer_name.

ALTER TABLE customers
ADD customer_name varchar(255);

In same way, the command can be used for modifying and deleting an existing column

  • DROP Command with Usage:

Drop statement is used to get rid of a specific table and its associated data / information from a database. Before Dropping any table, always keep in mind that information would also  be gone along with the table.
It is good to have backup of a table or a database, so that it can be recovered whenever a mistake is happened.

Syntax:

DROP TABLE tableName;

For Example, Suppose there is a table named ‘user’ within a database named ‘test’ and I have to delete it completely from  the given database.

 

The Statement would be:

1. First, get into the respected database that is ‘test’.
2. After, Drop that table.

USE test;                          // To get into test database
DROP TABLE user;  // Deleting the table

  • CREATE Command with Usage :

The CREATE Table statement is used to create table in database to store the data. the columns define the integrity constraints like Primary key, Secondary key, Unique key, Foreign key while creating tables.

Syntax  

CREATE TABLE
table_name (
 column1 datatype(Size) Constraints,
 column2 datatype(Size) Constraints,
 column3 datatype(Size) Constraints,
 ........
columnN datatype(Size) Constraints
)

CREATE TABLE keyword just simply tells the RDBMS that you wanna create a table for your data.
A table must has a name that must write next to the CREATE TABLE keyword.
Come to the parentheses part in this you mention your columns or fields along with its datatype(A datatype means the type of data you wanna insert in a particular table like for numbers you would prefer int) and the size of the data. Lastly, after giving the size you can specify constraints which are primary key, unique key and not null.

For Example, Suppose you need to create a table named Customer with columns named ID, customer_name, phone number. ID must be a primary key and other fields must not be null.

CREATE TABLE customer(ID INT(10) PRIMARY KEY, customer_name VARCHAR(20) NOT NULL,phone_number VARCHAR(20) NOT NULL);

Output:

 

Explanation:

In above example, create table statement used to form the table called ‘customer’ and within parentheses we put in fields such as ID, customer_name and phone_number with datatype.In the table, ID declared as primary key.

 

  • Show Command with Usage :

Show statement can be used for getting the number of databases reside within a DBMS along with the database names. This is the syntax:

For Example: SHOW DATABASES;

Output:


It will return the name of the databases which are currently in  the SQL server. In above example, 6 databases were returned.

It is also used to know how many tables are there within a particular database. For this purpose the syntax is: SHOW TABLES;

For Example:- 

USE test;
SHOW TABLES;

Output:

Similarly,  In above example , first we had selected a database by using ‘USE’ query and after that we had used the show tables query.

 If you curious about the number of columns of the table along with their names then show statement would come into play. The syntax is

Syntax:-

SHOW COLUMNS FROM table_name;


For Example:

USE test;
SHOW COLUMNS FROM j;

Output:

It will return all the columns of the table.

4.  To know about each and every related information of a particular table such as name of the table, version, number of rows and columns and  their format and so many other things about table. The syntax is: SHOW TABLE STATUS FROM table_name;
For Example:
SHOW TABLE STATUS FROM j;

Output:

 

 

  • DATA MANIPULATION LANGUAGE (DML)

It is used to retrieve, store, modify, delete, insert and data update data in database.
for Ex.- UPDATE, INSERT.

  • INSERT COMMAND with Usage:

The SQL’s INSERT INTO statement is used to add new records to a table in database.

Syntax
There are two possible ways to write the INSERT INTO statement which are shown in below:
INSERT INTO table_name (column1, column2, column3..........)
VALUES (value1, value2, value3,.........);
Here, (columns; 1,2,3,..) are the names of the column in the table.
If we are adding the more values in the table, we may not need to specify the column’s name in the SQL query. so however we have to make sure that the order of the values in the same order as the columns in the table. so, the INSERT INTO syntax is as follows:
INSERT INTO table_name
VALUES ( value1, value2, value3,........)

 

You can create a record in the CUSTOMERS table by using the second syntax as shown below :

INSERT  INTO CUSTOMER
VALUES (5, king sachin, 1122)


All the above statements will produce the following records in the CUSTOMER table as shown below :

 

POPULATE ONE TABLE USING ANOTHER TABLE :

we can populate the data into a table through the select statement over another table

Syntax

INSERT INTO first_table_name (column1, column2,.....)
SELECT column1, column2......
FROM second_table_name
(WHERE condition);

  • UPDATE Command with usage :

Update command is used to change or update existing records in a table. In this, WHERE clause can also be used to update some particular rows.

Syntax: 

UPDATE table_Name

SET column_name_1 = some value, .. .. .. column_name_N = some value WHERE condition

For Example :

The is a table named customers and we need to update customer_lastname where NULL value exist

  • TRANSACTION CONTROL LANGUAGE (TCL)

TCL Commands are used to manage transactions in database. It allows statements to be grouped together into logical transactions and also managed the changes made by DML statements.
For Ex.- COMMIT, ROLLBACK.
COMMIT : It saves transactions to the database
ROLLBACK : It reverts back all the transactions to the last committed state

  • DATA CONTROL LANGUAGE (DCL)

DCL is a syntax which is similar to a computer programming language used to control and access the data stored in a database.
For Ex.- GRANT, REVOKE
GRANT : It is used provide access to a user for a database
REVOKE : It does opposite, it takes back the permissions from existing users of a database

  • DATA QUERY LANGUAGE (DQL)

It is a Database Language to search for information. It is also referred as Filtering the data. For Example SELECT .

  • Select Command with Usage :

In SQL, SELECT statement is not just for selecting one or more fields but also allowing us to pick up specific and desired information from one and more fields with the help of Where clause, RegExpr, distinct and many more for avoiding irrelevant data.

Here are some basic uses of Select statement.

1. It is used for getting all the table’s information from one or more tables.

Syntax:

SELECT * FROM table_name;


For Example: 
Suppose there is a table called customers and we want to know how many columns are there in that table along with its data. For this we use:

SELECT * FROM Customers;

Output:

 

Explanation:
So, ‘SELECT’ is for selecting columns from the table. Come to the ‘*’ (asterisk) sign ,it simply means ‘all the columns’ (we  will discuss it later) while ‘From’ represents ‘from which table you want to access data’ that is write down to its next.
2. It is also used for getting information of one or more specific columns from a table.
Syntax: SELECT column_name,coulmn_name2 FROM table_name

For Example:
So, consider you want to extract Name and City from Customer table.For that purpose:

SELECT Name,City FROM Customers   /*If you want to select more than one column then you need to separate each Field With Comma’,’*/

 

 

Explanation:
Same as above except in the we can specify one or more column name (to specify more than one columns , a separator is used that is ‘,’).
3. It’s interesting to know that this clause can compute two mathematical expressions.
Syntax: SELECT expressions;

 

Explanation:
Rather than just selecting columns from the table it is also used to compute some mathematical calculations such as addition, subtraction,division and multiplication.

This post was last modified on July 4, 2022

Sandeep Verma

Leave a Comment
Published by
Sandeep Verma
Tags: DATA CONTROL LANGUAGE (DCL) DATA DEFINITION LANGUAGE (DDL) DATA MANIPULATION LANGUAGE (DML) DATA QUERY LANGUAGE (DQL) database features Introduction sql TRANSACTION CONTROL LANGUAGE (TCL) types of commands