Scribd
Upload
16 views3 pages

GATE DBMS Revision

The document provides a quick revision of SQL query concepts, detailing types of queries including DDL, DML, DCL, and TCL, along with their commands and examples. It outlines the SQL query execution order, subqueries, joins, aggregate functions, normalization forms, ACID properties of transactions, and indexing techniques. Key concepts are summarized in a table format for easy reference.

Uploaded by

vajid371ak
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
16 views3 pages

GATE DBMS Revision

The document provides a quick revision of SQL query concepts, detailing types of queries including DDL, DML, DCL, and TCL, along with their commands and examples. It outlines the SQL query execution order, subqueries, joins, aggregate functions, normalization forms, ACID properties of transactions, and indexing techniques. Key concepts are summarized in a table format for easy reference.

Uploaded by

vajid371ak
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

GATE DBMS – QUERY CONCEPTS (Quick Revision Notes)

Types of Queries in SQL


**DDL (Data Definition Language)**

Commands: CREATE, ALTER, DROP, TRUNCATE

Example:

CREATE TABLE Students (


ID INT PRIMARY KEY,
Name VARCHAR(50)
);

**DML (Data Manipulation Language)**

Commands: INSERT, UPDATE, DELETE, SELECT

Example:

INSERT INTO Students (ID, Name) VALUES (1, 'Vajid');

**DCL (Data Control Language)**

Commands: GRANT, REVOKE

Example:

GRANT SELECT ON Students TO User1;

**TCL (Transaction Control Language)**

Commands: COMMIT, ROLLBACK, SAVEPOINT

Example:

BEGIN TRANSACTION;
DELETE FROM Students WHERE ID = 1;
ROLLBACK;

SQL Query Execution Order


Correct Order of Execution:

1. FROM (Tables are selected first)

2. WHERE (Filtering is done)


3. GROUP BY (Grouping occurs)

4. HAVING (Filter groups)

5. SELECT (Columns are selected)

6. ORDER BY (Sorting is done)

7. LIMIT (Restrict output)

Subqueries & Joins


**Subquery**: Query inside another query

Example: Find students whose marks are greater than the average.

SELECT Name FROM Students


WHERE Marks > (SELECT AVG(Marks) FROM Students);

**Types of Joins in SQL**

- INNER JOIN: Returns matching records from both tables

- LEFT JOIN: Returns all records from left table & matching from right

- RIGHT JOIN: Returns all records from right table & matching from left

- FULL JOIN: Returns all records from both tables

Aggregate Functions
COUNT(), SUM(), AVG(), MAX(), MIN()

Example:

SELECT COUNT(*) FROM Students;

SELECT SUM(Marks) FROM Students;

SELECT AVG(Marks) FROM Students;

SELECT MAX(Marks) FROM Students;

SELECT MIN(Marks) FROM Students;

Normalization
1NF: Ensure atomicity, no duplicate columns, unique rows

2NF: Remove partial dependencies

3NF: Remove transitive dependencies


BCNF: Every determinant must be a key

Transactions & ACID Properties


Atomicity: All operations are done or none

Consistency: Data remains valid

Isolation: Transactions don’t interfere

Durability: Data is permanently saved

Indexing & Query Optimization


Types of Indexes: Primary, Secondary, Clustered, B+ Tree

Optimization Tips:

- Use EXPLAIN to analyze query execution.

- Avoid SELECT *, specify needed columns.

- Use indexes on frequently searched columns.

- Use JOIN instead of subqueries when possible.

Final Wrap-Up: GATE DBMS QUERY REVISION AT A GLANCE


| Topic | Key Concept |

|-----------------|-------------|

| SQL Types | DDL, DML, DCL, TCL |

| Execution Order | FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY |

| Joins | INNER, LEFT, RIGHT, FULL |

| Aggregate Functions | COUNT, SUM, AVG, MAX, MIN |

| Normalization | 1NF, 2NF, 3NF, BCNF |

| Transactions | ACID Properties |

| Indexing | Primary, Secondary, Clustered, B+ Tree |

You might also like