SQL | DDL, DQL, DML, DCL and TCL Commands

Structured Query Language, or SQL, is a programming?language for manipulating databases. It is the language used in database systems such as PostgreSQL, MySQL, Microsoft SQL Server, and Oracle Database.SQL?uses certain commands like CREATE, DROP, INSERT, etc. to carry out the required tasks.?

SQL commands are like instructions to a table. It is used to interact with the database with some operations. It is also used to perform specific tasks, functions, and queries of data. SQL can perform various tasks like creating a table, adding data to tables, dropping the table, modifying the table, set permission for users.

These?SQL?commands are mainly categorized into five categories:?

1. DDL – Data Definition Language
2. DQL – Data Query Language
3. DML – Data Manipulation Language
4. DCL – Data Control Language
5. TCL – Transaction Control Language

1. DDL (Data Definition Language)

DDL?or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. DDL is a set of SQL commands used to create, modify, and delete database structures but not data. These commands are normally not used by a general user, who should be accessing the database via an application.

List of DDL commands:?

• CREATE: This command is used to create the database or its objects (like table, index, function, views, store procedure, and triggers).
• DROP: This command is used to delete objects from the database.
• ALTER:?This is used to alter the structure of the database.
• TRUNCATE:?This is used to remove all records from a table, including all spaces allocated for the records are removed.
• RENAME:?This is used to rename an object existing in the database.

CREATE TABLE STATEMENT

The CREATE TABLE statement is used to create a

new table in a database.

CREATE TABLE table_name (**column

datatype, **column2 datatype,

**column3 datatype,....);        

1. The column parameters specify the names of the columns of the table.
2. The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).

CREATE TABLE IF NOT EXISTS Music.Artists 
? ? ArtistId INT NOT NULL AUTO_INCREMENT,
? ? ArtistName VARCHAR(255) NOT NULL,
? ? PRIMARY KEY (ArtistId)
);        

TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete the data inside

a table, but not the table itself.

Removes all rows from a table or specified partitions of a table, without logging the individual row deletions. TRUNCATE TABLE is similar to the DELETE statement with no WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

DROP

DROP is?used to delete a whole?database?or just a table.

To Drop a table

DROP TABLE table_name;

DROP?TABLE?artists;        

table_name: Name of the table to be deleted.

To Drop a database

DROP DATABASE database_name;        

database_name: Name of the database to be deleted.

DROP vs TRUNCATE

The major difference between TRUNCATE and DROP is that truncate is used to delete the data inside the table not the whole table.

• Truncate is normally ultra-fast and it’s ideal for deleting data from a temporary table.
• Truncate preserves the structure of the table for future use, unlike drop table where the table is deleted with its full structure.
• Table or Database deletion using a DROP statement?cannot?be rolled?back, so it must be used wisely.

MYSQL ALTER STATEMENT

MySQL ALTER statement is used when you want to change the name of your table or any table field. It is also used to add or delete an existing column in a table.

The ALTER statement is always used with "ADD", "DROP" and "MODIFY" commands according to the situation.

ALTER TABLE employee ADD city CHAR(20)
DESCRIBE employee;
ALTER TABLE employes MODIFY name CHAR (30) NOT NULL;        

ADD A COLUMN IN THE TABLE

Syntax
ALTER TABLE table_name
ADD new_column_name column_definition
[FIRST | AFTER column_name];:        

PARAMETERS

table_name : It specifies the name of the table that you want to modify.

new_column_name: It specifies the name of the new column that you want to add to the table.

column_definition: It specifies the data type and definition of the column (NULL or NOT NULL, etc).

FIRST | AFTER column_name: It is optional. It tells MySQL where in the table to create the column. If this parameter is not specified, the new column will be added to the end of the table.

MODIFY COLUMN IN THE TABLE

The MODIFY command is used to change the column definition of the table.

Syntax
ALTER TABLE Table_name
MODIFY Column_name Column_definition
[FIRST | AFTER Column_name];

ALTER TABLE brands
MODIFY name VARCHAR(255);        

RENAME COLUMN IN TABLE

Syntax
ALTER TABLE Table_name
CHANGE COLUMN Old_name New_name Column_definition [ FIRST | AFTER Column_name]

# rename a colum
alter table `student data`
change column emergency_contact contact varchar(10);

# rename a table
alter table students
rename to student_123;        

2. Data Manipulation Language (DML)

The SQL commands that deal with the manipulation of data present in the database belong to DML or Data Manipulation Language and this includes most of the SQL statements.?It is the component of the SQL statement that controls access to data and to the database. Basically, DCL statements are grouped with DML statements.

List of DML commands:?

• INSERT: It is used to insert data into a table.
• UPDATE:?It is used to update existing data within a table.
• DELETE: It is used to delete records from a database table.
• LOCK:?Table control concurrency.
• CALL:?Call a PL/SQL or JAVA subprogram.
• EXPLAIN PLAN:?It describes the access path to data.

MYSQL INSERT INTO STATEMENT

The INSERT INTO statement is used to insert new records in a table.

INSERT INTO Syntax:

It Is Possible To Write The INSERT INTO Statement

In Two Ways:

Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1
column2, column3, ...)
VALUES (value1, value2, value3, ...);

INSERT?INTO?dbo.Patien
????????????(Surname, FirstName, DOB, Sex, [Weight], Height, Vaccinated)
VALUES??????(NULL,?NULL,'2019-11-19',?'F', 14.0, 23.1,?'No')t        

If we are adding values for all the columns of the table, we do not need

to specify the column names in the SQL query.

Here, the INSERT INTO

syntax would be as follows:

INSERT INTO table_name
VALUES (value1, value2, value3, ...);        

Inserting Multiple Rows

To insert more than one row of data with just one statement, use parentheses and commas to specify the distinct new rows.

INSERT?INTO?dbo.Patien
????????????(Surname, FirstName, DOB, Sex, [Weight], Height, Vaccinated)
VALUES??????('Hitson',?'George','2019-11-19',?'M', 13.9, 22.5,?'No'),
????????????('Hitson',?'Jenny','2019-11-19',?'F', 13.7, 22.3,?'No');        

MYSQL UPDATE STATEMENT

The UPDATE statement is used to modify the existing records in a table. We can update single columns as well as multiple columns using the UPDATE statement as per our requirement.

In a very simple way, we can say that SQL commands(UPDATE and DELETE) are used to change the data that is already in the database. The SQL DELETE command uses a WHERE clause.

Syntax
UPDATE table_name SET column1 = valuel, column2 = value2, ... WHERE condition;

UPDATE Fruit
SET UnitId = 2
WHERE FruitName = 'Apple' AND UnitId = 1;        

UPDATE table_name SET column1 = value1, column2 = value2,…?

WHERE condition;

table_name: name of the table

column1: name of first , second, third column….

value1: new value for first, second, third column….

condition: condition to select the rows for which the?

values of columns needs to be updated.

Update Multiple Rows

If you need to update multiple rows of data, it's easy with the?UPDATE?statement. Here we use?the WHERE?clause.

Imagine you want to recategorize all the inpatient wards from "Ward" to "Room" under the group column.

UPDATE?dbo.Departmen
???SET?GroupName =?'Room'
WHERE?GroupName =?'Ward'        

MYSQL DELETE STATEMENT

The DELETE statement is used to delete existing records in a table.We can delete a single record or multiple records depending on the condition we specify in the WHERE clause.

Syntax
DELETE FROM table_name WHERE condition;        

Deleting Single Record

DELETE FROM GFG_EMPLOyees WHERE NAME = 'Rithvik';          

?Deleting Multiple Records

DELETE FROM employees
WHERE department = 'Development';         

Delete All of the Records

There are two queries to do this as shown below,

Query:

DELETE FROM employees;

Or

DELETE * FROM employees;        

3. Data Control Language (DCL)

DCL includes commands such as GRANT and REVOKE which mainly deal with the rights, permissions, and other controls of the database system.?

List of?DCL commands:?

GRANT:?This command?gives users access privileges to the database.

Syntax:

GRANT?SELECT,?UPDATE?ON?MY_TABLE?TO?SOME_USER,?ANOTHER_USER;??        

REVOKE:?This command withdraws the user’s access privileges given by using the GRANT command.

Syntax:

REVOKE?SELECT,?UPDATE?ON?MY_TABLE?FROM?USER1,?USER2;??        

4. Data Query Language (DQL)

DQL?statements are used for performing queries on the data within schema. The purpose of the DQL Command is to get some schema relation based on the query passed to it.?We can define DQL as follows it is a component of SQL statement that allows getting data from the database and imposing order upon it. It includes the SELECT statement. This command allows getting the data out of the database to perform operations with it.

List of DQL:?

• SELECT:?It is used to retrieve data from the database.

To fetch any column in the table.

Syntax:

SELECT column1,column2 FROM table_name

column1 , column2: names of the fields of the table
table_name: from where we want to apply query        

This query will return all the rows in the table with fields column1 and column2.

To fetch the entire table or all the fields in the table:

Syntax:

SELECT * FROM table_name;

?— asterisks represent all attributes of the table?        

5. TCL (Transaction Control Language)

Transactions group a set of tasks into a single execution unit. Each transaction begins with a specific task and ends when all the tasks in the group successfully complete. If any of the tasks fail, the transaction fails. Therefore, a transaction has only two results: success or failure.Hence, the following TCL commands are used to control the execution of a transaction:?

BEGIN:?Opens a Transaction.

COMMIT:?Commits a Transaction.

Syntax:

COMMIT;??        

ROLLBACK:?Rollbacks a transaction in case of any error occurs.

ROLLBACK;??        

SAVEPOINT:?Sets a save point within a transaction.

Syntax:

SAVEPOINT?SAVEPOINT_NAME;??        

#sql #sqllearning #sqlskills #sqlprogramming #rdbms #mysql