SQL Query Fundamentals

Question 1

List the 4 Primary Types of SQL Queries?

Answer 1

SQL commands are mainly categorized into four categories as:

DDL – Data Definition Language
DQl – Data Query Language
DML – Data Manipulation Language
DCL – Data Control Language

Some consider TCL (Transaction Control Language ) a 5th type.

Question 2

Data Definition Language (DDL) Commands

Answer 2

Data Definition Language (DDL):
DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema.

CREATE
ALTER
DROP
RENAME
TRUNCATE
COMMENT

Question 3

Data Query Language (DQL) Commands

Answer 3

Data Query Language (DQL):
DQL statements are used for performing queries on the data within schema objects. The purpose of the DQL Command is to get some schema relation based on the query passed to it.

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

Question 4

Data Manipulation Language (DML) Commands

Answer 4

Data Manipulation Language (DML):
The SQL commands that deals 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.

INSERT
UPDATE
DELETE
MERGE
CALL
EXPLAIN PLAN
LOCK TABLE

Question 5

Data Control Language (DCL) Commands

Answer 5

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.

GRANT
REVOKE

Question 6

Transaction Control Language (TCL) Commands

Answer 6

Transaction Control Language (TCL):
Sometimes considered a 5th type of SQL commands.

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.

Incomplete steps result in the failure of the transaction. A database transaction, by definition, must be atomic, consistent, isolated and durable. These are popularly known as ACID properties.

COMMIT: Commits a Transaction.
ROLLBACK: Rollbacks a transaction in case of any error occurs.
SAVEPOINT:Sets a savepoint within a transaction.
SET TRANSACTION: Specify characteristics for the transaction.