Data Definition Language (DDL) Commands

What is DDL?
DDL, which stands for Data Definition Language, is a subset of SQL (Structured Query
Language) commands used to define and modify the database structure. These commands are
used to create, alter, and delete database objects like tables, indexes, and schemas.

The primary DDL commands in SQL include:

1. CREATE: This command is used to create a new database object. For example,
creating a new table, a view, or a database.
 Syntax for creating a table: CREATE TABLE table_name (column1 datatype,
column2 datatype, ...);
2. ALTER: This command is used to modify an existing database object, such as
adding, deleting, or modifying columns in an existing table.
 Syntax for adding a column in a table: ALTER TABLE table_name ADD
column_name datatype;
 Syntax for modifying a column in a table: ALTER TABLE table_name MODIFY
COLUMN column_name datatype;
3. DROP: This command is used to delete an existing database object like a table, a
view, or other objects.
 Syntax for dropping a table: DROP TABLE table_name;
4. TRUNCATE: This command is used to delete all data from a table, but the structure
of the table remains. It’s a fast way to clear large data from a table.
 Syntax: TRUNCATE TABLE table_name;
5. COMMENT: Used to add comments to the data dictionary.
 Syntax: COMMENT ON TABLE table_name IS 'This is a comment.';
6. RENAME: Used to rename an existing database object.
 Syntax: RENAME TABLE old_table_name TO new_table_name;
DDL commands play a crucial role in defining the database schema.

Data Manipulation Language (DML) Commands in SQL

What is DML Commands in SQL?
Data Manipulation Language (DML) is a subset of SQL commands used for adding
(inserting), deleting, and modifying (updating) data in a database. DML commands are
crucial for managing the data within the tables of a database.

The primary DML commands in SQL include:

1. INSERT: This command is used to add new rows (records) to a table.
 Syntax: INSERT INTO table_name (column1, column2, column3, ...) VALUES
(value1, value2, value3, ...);
2. UPDATE: This command is used to modify the existing records in a table.
 Syntax: UPDATE table_name SET column1 = value1, column2 = value2, ...
WHERE condition;
 The WHERE clause specifies which records should be updated. Without it, all
records in the table will be updated.
3. DELETE: This command is used to remove one or more rows from a table.
  Syntax: DELETE FROM table_name WHERE condition;
 Like with UPDATE, the WHERE clause specifies which rows should be deleted.
Omitting the WHERE clause will result in all rows being deleted.
4. SELECT: Although often categorized separately, the SELECT command is
sometimes considered part of DML as it is used to retrieve data from the database.
 Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;
 The SELECT statement is used to query and extract data from a table, which can
then be used for various purposes.
Data Control Language (DCL) Commands in SQL
What is DCL commands in SQL?
Data Control Language (DCL) is a subset of SQL commands used to control access to data in
a database. DCL is crucial for ensuring security and proper data management, especially in
multi-user database environments.

The primary DCL commands in SQL include:

1. GRANT: This command is used to give users access privileges to the database. These
privileges can include the ability to select, insert, update, delete, and so on, over
database objects like tables and views.
 Syntax: GRANT privilege_name ON object_name TO user_name;
 For example, GRANT SELECT ON employees TO user123; gives user123 the
permission to read data from the employees table.
2. REVOKE: This command is used to remove previously granted access privileges
from a user.
 Syntax: REVOKE privilege_name ON object_name FROM user_name;
 For example, REVOKE SELECT ON employees FROM user123; would
remove user123‘s permission to read data from the employees table.
 Database administrators typically use DCL commands. When using these commands,
it’s important to carefully manage who has access to what data, especially in
environments where data sensitivity and user roles vary significantly.
 In some systems, DCL functionality also encompasses commands
like DENY (specific to certain database systems like Microsoft SQL Server), which
explicitly denies specific permissions to a user, even if those permissions are granted
through another role or user group.
 Remember, the application and syntax of DCL commands can vary slightly between
different SQL database systems, so it’s always good to refer to specific
documentation for the database you are using.
Transaction Control Language (TCL) Commands in SQL
What are TCL commands in SQL?
Transaction Control Language (TCL) is a subset of SQL commands used to manage
transactions in a database. Transactions are important for maintaining the integrity and
consistency of data. They allow multiple database operations to be executed as a single unit
of work, which either entirely succeeds or fails.

The primary TCL commands in SQL include:

 1. BEGIN TRANSACTION (or sometimes just BEGIN): This command is used to
start a new transaction. It marks the point at which the data referenced in a transaction
is logically and physically consistent.
 Syntax: BEGIN TRANSACTION;
 Note: In many SQL databases, a transaction starts implicitly with any SQL
statement that accesses or modifies data, so explicit use of BEGIN
TRANSACTION is not always necessary.
2. COMMIT: This command is used to permanently save all changes made in the
current transaction.
 Syntax: COMMIT;
 When you issue a COMMIT command, the database system will ensure that all
changes made during the current transaction are saved to the database.
3. ROLLBACK: This command is used to undo changes that have been made in the
current transaction.
 Syntax: ROLLBACK;
 If you issue a ROLLBACK command, all changes made in the current transaction
are discarded, and the state of the data reverts to what it was at the beginning of
the transaction.
4. SAVEPOINT: This command creates points within a transaction to which you can
later roll back. It allows for partial rollbacks and more complex transaction control.
 Syntax: SAVEPOINT savepoint_name;
 You can roll back to a savepoint using ROLLBACK TO savepoint_name;
5. SET TRANSACTION: This command is used to specify characteristics for the
transaction, such as isolation level.
 Syntax: SET TRANSACTION [characteristic];
 This is more advanced usage and may include settings like isolation level which
controls how transaction integrity is maintained and how/when changes made by
one transaction are visible to other transactions.
TCL commands are crucial for preserving a database’s ACID (Atomicity, Consistency,
Isolation, Durability) properties, ensuring that all transactions are processed reliably. These
commands play a key role in any database operation where data consistency and integrity are
important.

Data Query Language (DQL) Commands in SQL

What are DQL commands in SQL?
Data Query Language (DQL) is a subset of SQL commands used primarily to query and
retrieve data from existing database tables. In SQL, DQL is mostly centered around
the SELECT statement, which is used to fetch data according to specified criteria. Here’s an
overview of the SELECT statement and its common clauses:

1. SELECT: The main command used in DQL, SELECT retrieves data from one or
more tables.
 Basic Syntax: SELECT column1, column2, ... FROM table_name;
 To select all columns from a table, you use SELECT * FROM table_name;
2. WHERE Clause: Used with SELECT to filter records based on specific conditions.
 Syntax: SELECT column1, column2, ... FROM table_name WHERE condition;
 Example: SELECT * FROM employees WHERE department = 'Sales';
 3. JOIN Clauses: Used to combine rows from two or more tables based on a related
column between them.
 Types include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN.
 Syntax: SELECT columns FROM table1 [JOIN TYPE] JOIN table2 ON
table1.column_name = table2.column_name;
4. GROUP BY Clause: Used with aggregate functions (like COUNT, MAX, MIN,
SUM, AVG) to group the result set by one or more columns.
 Syntax: SELECT column1, aggregate_function(column2) FROM table_name
GROUP BY column1;
5. ORDER BY Clause: Used to sort the result set in ascending or descending order.
 Syntax: SELECT column1, column2 FROM table_name ORDER BY column1
[ASC|DESC], column2 [ASC|DESC];

SQL | Subquery
In SQL a Subquery can be simply defined as a query within another query. In other words
we can say that a Subquery is a query that is embedded in WHERE clause of another SQL
query. Important rules for Subqueries:
 You can place the Subquery in a number of SQL
clauses: WHERE clause, HAVING clause, FROM clause. Subqueries can be used with
SELECT, UPDATE, INSERT, DELETE statements along with expression operator. It
could be equality operator or comparison operator such as =, >, =, <= and Like operator.
 A subquery is a query within another query. The outer query is called as main query and
inner query is called as subquery.
 The subquery generally executes first when the subquery doesn’t have any co-
relation with the main query, when there is a co-relation the parser takes the decision on
the fly on which query to execute on precedence and uses the output of the subquery
accordingly.
 Subquery must be enclosed in parentheses.
 Subqueries are on the right side of the comparison operator.
 Use single-row operators with single row Subqueries. Use multiple-row operators with
multiple-row Subqueries.
Syntax: There is not any general syntax for Subqueries. However, Subqueries are seen to be
used most frequently with SELECT statement as shown below:
SELECT column_name
FROM table_name
WHERE column_name expression operator
(SELECT column_name FROM table_name WHERE ...);
Sample Table:
DATABASE
 NAME ROLL_NO LOCATION PHONE_NUMBER
Ram 101 Chennai 9988775566
Raj 102 Coimbatore 8877665544
Sasi 103 Madurai 7766553344
Ravi 104 Salem 8989898989
Sumath
i 105 Kanchipuram 8989856868
STUDENT
NAME ROLL_NO SECTION
Ravi 104 A
Sumathi 105 B
Raj 102 A
Sample Queries:
 To display NAME, LOCATION, PHONE_NUMBER of the students from DATABASE
table whose section is A
SELECT NAME, LOCATION, PHONE_NUMBER
FROM DATABASE
WHERE ROLL_NO IN (SELECT ROLL_NO
FROM STUDENT
WHERE SECTION='A');
 Explanation : First subquery executes “ SELECT ROLL_NO from STUDENT where
SECTION=’A’ ” returns ROLL_NO from STUDENT table whose SECTION is ‘A’.Then
outer-query executes it and return the NAME, LOCATION, PHONE_NUMBER from the
DATABASE table of the student whose ROLL_NO is returned from inner subquery.
Output:
NAME ROLL_NO LOCATION PHONE_NUMBER

Ravi 104 Salem 8989898989

Raj 102 Coimbatore 8877665544

 Insert Query Example:
Table1: Student1
NAME ROLL_NO LOCATION PHONE_NUMBER
Ram 101 chennai 9988773344
Raju 102 coimbatore 9090909090
Ravi 103 salem 8989898989
Table2: Student2
 NAME ROLL_NO LOCATION PHONE_NUMBER
Raj 111 chennai 8787878787
Sai 112 mumbai 6565656565
Sri 113 coimbatore 7878787878
 To insert Student2 into Student1 table:
INSERT INTO Student1
SELECT * FROM Student2;
 Output:
NAME ROLL_NO LOCATION PHONE_NUMBER
Ram 101 chennai 9988773344
Raju 102 coimbatore 9090909090
Ravi 103 salem 8989898989
Raj 111 chennai 8787878787
Sai 112 mumbai 6565656565
Sri 113 coimbatore 7878787878
 To delete students from Student2 table whose rollno is same as that in Student1 table and
having location as chennai
DELETE FROM Student2
WHERE ROLL_NO IN (SELECT ROLL_NO
FROM Student1
WHERE LOCATION = 'chennai');
 Output:
1 row delete successfully.
 Display Student2 table:
NAME ROLL_NO LOCATION PHONE_NUMBER
Sai 112 mumbai 6565656565
Sri 113 coimbatore 7878787878
 To update name of the students to geeks in Student2 table whose location is same as
Raju,Ravi in Student1 table
UPDATE Student2
SET NAME='geeks'
WHERE LOCATION IN (SELECT LOCATION
FROM Student1
WHERE NAME IN ('Raju', 'Ravi'));
 Output:
1 row updated successfully.
 Display Student2 table:
NAME ROLL_NO LOCATION PHONE_NUMBER
Sai 112 mumbai 6565656565
geeks 113 coimbatore 7878787878