SQL Commands:DQL,DDL,DML,DCL,TCL

SQL Commands 

SQL Commands:DQL,DDL,DML,DCL,TCL

SQL Commands are mainly divided into five types of categories there are DQL,DDL,DML,DCL,TCL lets we see in details.

Introduction

Welcome to all who want learn and to become professional in SQL.This tutorial is special made to our our students and who interested to learn SQL with us.

This SQL tutorial provide to all of you basic SQL commands.here we see all the SQL commands in details with there syntax.

SQL Stands for (Structured Query Language) and it is help to users and used to store the records,creating tables,update tables,delete records,drop the recordes,insert the records,by using SQL we change the table and column name. 

Also SQL gives permission to Grant and Revoke permission to user so for that we have to understand the concept and syntax/commands of SQL SO let's see.

"SQL is structured query language and it is used to storing,manipulating and retrieving data which is stored in relational database" 

NOTE:-SQL is not case sensitive language i.e select is same as SELECT you write select or SELECT both are valid.

Use semicolon after SQL Statements

It important to understand the semicolon important in SQL.Basically semicolon is used to separate each SQL statements and it is standard and good approach.semicolon is used to denote the end of a statements and helps to database engine to understand the each individual queries or commands  will executing.

But some database not require the semicolon but by practise its being good approach to not raise any single issue.

Therefore best way to give semicolon at the end of statement and its also a good approach.

Following are the types SQL commands

SQL Commands are mainly divided into five types of categories there are DQL,DDL,DML,DCL,TCL lets we see in details.

SQL Commands

1.DQL                                                                                               

1) DQL is first type of SQL command where full form of DQL is (Data Query Language).

2) Select command is come under DQL.

3) DDL is subset of SQL data query language is used to retrieve or fetch the data from database.

A.SELECT Command/Select Query 

In DQL command select command is come in SQL Select command is used to retrieve or fetch the data from database whatever data is fetch from database is return in the form of result set.

Syntax 

                                                                     

SELECT *FROM table_name;

Example

SELECT Customer name,city FROM customer;

note:-Here * means column names from the table where you wants fetch the records

2.DDL                                                                                                   

1) DDL stands for Data Definition Language.

2) DDL change the structure of table by using following commands like it can be create,alter,drop,truncate and rename the table.

3) In DDL what we change is permanently save in database that means it is auto-committed.

4) Following are five commands comes under DDL                                                           

•  CREATE
•  ALTER
•  DROP
•  TRUNCATE
•  RENAME

A)  CREATE 

Use

1.CREATE command is used to create a table 

2.By using create command we can create database also

For CREATE TABLE

Syntax

                                                                                                                                               

CREATE TABLE Table_Name 

(column 1 datatype, column 2 datatype, column 3  datatype........);                                                                                                                                                                                                                                           

Example

CREATE TABLE employe 

(employe id int, firstname varchar(20), address varchar(20));      

                                                                                                     

For CREATE DATABASE

Syntax

CREATE DATABASE database_name;

Example

CREATE DATABASE facebook;

B) ALTER

Use

1.ALTER Command comes under ddl 

2.ALTER command is used to  add new column in the table

3.By using alter command we also drop the existing column from table

4.By using alter command also use to modify the data from existing table.

For Add new column in table

Syntax

ALTER TABLE table_name

ADD column_name DATATYPE;

Example

ALTER TABLE customer

ADD column_name varchar(20);

For Drop the existing column from table

Syntax

ALTER TABLE table_name

DROP column_name;

Example

ALTER TABLE customer

DROP Email ;

For modify the Data From Existing Table.

Syntax

ALTER TABLE table_name

MODIFY column_name DATATYPE;

Example

ALTER TABLE customer

MODIFY column_name ;

c) DROP

USE

1.DROP command is used to drop the database.

2.DROP the existing table.

3.DROP command  Rollback is not possible means what we can delete we can't  get it back.

4.DROP Command is used to delete all records with table(structure).

For DROP database

Syntax

DROP database database_name;

Example

DROP database facebook;

For DROP Table

Syntax

DROP TABLE table_name;

Example

DROP table customer;

D) TRUNCATE

USE

1.TRUNCATE command is used to delete the data from table structure of table remains same.

Syntax

TRUNCATE TABLE table_name;

Example

TRUNCATE table customer;

E) RENAME

USE

1.RENAME command is used to rename the existing column and existing table.

RENAME the existing column

Syntax

exec sp_rename  'table_name.old column_name',  'new column_name',  'column'  ;

Example

exec sp_rename  customer.customer name',  'first name',  'column'  ;

RENAME the existing Table

Syntax

exec sp_rename  'old table_name',  'new table_name' ;

Example

exec sp_rename  'customer',  'employee' ;

3.DML                                                                                                  

1) DML stands for data manipulation language.

2) DML Commands are

• INSERT
• UPDATE 
• DELETE  

3) DML is used to modify database/table

4) By using INSERT,UPDATE,DELETE Commands from dml we do all changes in database.

A) INSERT

USE   

1. INSERT command used to insert the new records/new value/new data in table.

2.INSERT having two syntax 

1.Syntax ( if we want to add column name with value)

INSERT INTO  table_name;

(column 1,column 2,column 3,......)

VALUES

(value 1, value 2, value 3);

Example

INSERT INTO  Customer;

(first name,middle name, address)

VALUES

(trisha,sanjay, abc);

1.Syntax ( if we want to add only values)

INSERT INTO  table_name;

VALUES

(value 1, value 2, value 3........);

Example

INSERT INTO  Customer;

VALUES

(trisha,sanjay, abc);

B) UPDATE

USE

1.UPDATE command is used to update the existing data from table.

Syntax

UPDATE  table_name;

SET (column=value 1,column 2=value 2....)

WHERE condition;

Example

UPDATE  customer;

SET first name=sanket,address=abc

WHERE customer id=1;

A) DELETE

USE

1.DELETE command is used to delete the existing data from table.

2.Rollback is possible in delete.

Syntax

DELETE FROM table_name;

WHERE condition;

Example

DELETE FROM customer;

WHERE customer name='manisha';

DELETE All records from table

USE

By using DELETE command it is possible to delete all records without deleting the table

Syntax

DELETE FROM table_name;

Example

DELETE FROM customer;

4.DCL                                                                                                   

1.DCL stand for Data Control Language

2.DCL have authority to gives permission to user and also take back given permission from user.

3.Following are the DCL commands

• GRANT
• REVOKE

A) GRANT

USE

By using GRANT command it is possible to give access permission to user.

Syntax

GRANT select,update on my_table to some_user,another_user;

B) REVOKE

USE

By using REVOKE command it is possible to give access permission back from user.

Syntax

REVOKE select,update on my_table to from user 1, user 2;

5.TCL                                                                                                   

1.TCL stands for Transaction Control Language.

2.TCL commands used with DML commands like insert,update,delete.

3.TCL is auto committed

4.TCL commands

• Commit
• Rollback

     

For that we need Begin transaction  is used to start the transaction

Begin transaction

<write statement>

A) COMMIT

USE

By using COMMIT command transactions is permanently save.

Syntax

Begin transaction

<write statement>

Example

Begin transaction

<write statement>

commit;

B) ROLLBACK 

USE

By using COMMIT command is used to cancel the transactions.

Syntax

Begin transaction

rollback;

People also ask this Questions

1.What are the 5 basic SQL commands?

2.What is basic SQL commands?

3.What is DDL,DML,TCL in SQL.

4.What are the basic SQL languages?

5.SQL full form? and what is SQL?

6.Why SQL is used?

7.SQL commands/syntax?

8.What are the advantages of SQL?

9.What are the disadvantages of SQL?

Our popular Java articles

1.What is JVM in Java? difference between JDK,JRE,JVM

2.What is java? Different java applications, Advantages & Disadvantages

Viewers

This  software testing tutorial is specially  for beginners and professionals and students and who want to learn testing from basic.

Comments

Please drop your comment and send me your valuable response.