What are the types of SQL commands with Example ?

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.

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.

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

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.

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 :

• DATA MANIPULATION LANGUAGE (DML)

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: 

• TRANSACTION CONTROL LANGUAGE (TCL)

• DATA CONTROL LANGUAGE (DCL)

• DATA QUERY LANGUAGE (DQL)