SQL Commands (DQL, DDL, DCL, TCL, and DML) Made Easy | By KodeSrc

What is SQL and It's Use?

• SQL is a language developed and designed to perform operations on Databases, Structured Query Language is the full form of SQL. 
• SQL provides us ways to Access and Manipulate the data present in the databases. 
• It is the backbone of RDBMS (Relational Database Management System), without it performing operations on RDBMS would be difficult.

What is SQL Command and Different types of SQL Commands?

• SQL Commands are nothing but instructions that are given to database to perform particular tasks.
• This commands are grouped based on the functionality and behaviour into different types, DQL, DDL, DCL, TCL, and DML

Different types of SQL Commands (DQL, DDL, DCL, TCL, and DML):

DQL(Data Query Language): This group contains SQL command like SELECT. This command is used to Query/Retrieve data from the database either all data or some fields from the tables based on certain conditions.

We will see this SELECT Query in dept in later part of this post.

DDL(Data Definition Language): This group contains SQL commands that are used to define structure of the database / tables, it includes commands like CREATE,DROP,TRUNCATE,ALTER.

DCL (Data Control Language) : DCL commands are used to control the access to the database and tables. It includes commands like GRANT (Gives permission to the user), REVOKE (Removes permission of the user).

TCL (Transaction Control Language ): TCL includes commands like COMMIT,SAVEPOINT,ROLLBACK. This can be used with DML commands only.

DML (Data Manipulation Language):This group contains SQL commands that are responsible for manipulating data in the database. It includes INSERT, UPDATE,DELETE.

Let's see each groups in details now.

DQL(Data Query Language): 

• SELECT Command: This is a search command, that is used to search/query/filter records present in tables  as a whole or based on certain conditions.

Lets say we have a table User which has following columns.

Now we have to fetch all records of users, so we can do that using SELECT query.

        SELECT * FROM User;

  

Output:

DDL(Data Definition Language)

CREATE Command: This command is used to Create Tables in database or Create Database on the server.

CREATE DATABASE:

        CREATE DATABASE testDB1;

  

Before executing the above query we have these databases in our system.

After running the above query the database would be created in our system.

CREATE TABLE:

Query Struture:

CREATE TABLE tableName(
    fieldName datatype constraints(Optional),
    )

  

Example:

CREATE TABLE User(
  user_id varchar(255) NOT NULL,
  user_name varchar(255) NOT NULL,
  password varchar(255) NOT NULL,
  PRIMARY KEY(user_id)
  )

  

Do you want to create a table having foreign keys in it?

It's simple say we have a Orders table and order_id is the primary key in Orders table and we want the order_id to be foreign key in our User table we can do that using below query.

Point to remember: NULL values are allowed in Foreign Key.

Orders table:

CREATE TABLE Orders(
	order_id varchar(255),
	order_price double,
	PRIMARY KEY(order_id)
)

User table:

CREATE TABLE User(
  	user_id varchar(255) NOT NULL,
  	order_id varchar(255),
  	user_name varchar(255) NOT NULL,
  	password varchar(255) NOT NULL,
  	PRIMARY KEY(user_id),
  	FOREIGN KEY(order_id) REFERENCES Orders(order_id)
  )

  

NOTE: When you insert a record in User table without order_id(NULL value) it will work, but if you try to insert a record with some random order_id which doesn't exists in Orders table then the insert query will fail.

If you run above query then the two tables will be created with following structure.

DROP Command: Drop command is used to drop a database or table from the system.

We have following Databases in our system.

To DROP a database from the system we can use below structure.

        DROP DATABASE db_name;

  

Now if we want to DROP the testDB2, we can do that using below query.

        DROP DATABASE testDB2;

  

Databases remaining after dropping the testDB2 from our system.

DROP TABLES from Database:

To DROP table from a database we can use below query structure.

        DROP TABLE tableName;

  

We have these two tables in testDB1.

Now if we want DROP the table User , we can use the below query.

        DROP TABLE User;

  

List of tables remaining after dropping User table.

ALTER Command : Alter command is used to change the structure of the tables like adding new columns to table, deleting existing column from table.

Structure to ADD new column to any table:

ALTER TABLE tableName
  ADD columnName datatype constraints(Optional);


  

Say if we have to add total_items present for a particular order, so we can add a new column total_items of type integer in our Orders table using below query.

ALTER TABLE Orders
  ADD total_items integer;


  

Output:

DROP Existing Column from table: Similarly to DROP any column we can use below syntax.

ALTER TABLE Orders
    DROP COLUMN order_price;

  

DML(Data Manipulation Language)

INSERT: Insert query is used to insert records in the table. We can insert records using below query in our orders table.

INSERT INTO testDB1.Orders (order_id, order_price, total_items)
		VALUES('6', 4000, 7);

  

Records in table:

After Inserting:

UPDATE: Update query is used to update any existing records in table.

UPDATE testDB1.Orders 
		SET order_price =567.32 
WHERE order_id =4

  

Result after running update query:

DELETE: Delete Query is used to delete a record from the table

DELETE FROM testDB1.Orders 
	WHERE order_id=1

  

Result after running delete query:

Interested in understanding few more topics?

Check it Out !

• Mastering Stream APIs in Java: A Comprehensive Guide | KodeSrc
  
• A Step-by-Step Tutorial on AES 256 Encryption and Decryption in Java using Password by KodeSrc
  
• Stream API filter() method in Java | KodeSrc
  
• Demystifying Classes and Objects in Java : Everything You Need to Know | By KodeSrc
  
• Birthday Cake Candle Hackerrank Solution | By KodeSrc