DBMS SQL – GATE & RPSC Short Revision Notes

DBMS SQL – GATE & Rajasthan Competitive Exams Short Revision Notes

DBMS, SQL, GATE CSE, RPSC, Informatics Assistant, JVVNL GATE · UGC NET · RPSC · ISRO · BARC · NIELIT

DBMS SQL GATE CSE RPSC Informatics Assistant JVVNL

1. SQL Basics

SQL = Structured Query Language
Standard language for Relational Database Management Systems (RDBMS).
Used to Create, Read, Update, Delete (CRUD) data.
SQL is a Declarative (Non-Procedural) language.

2. SQL Command Categories

Category	Commands	Purpose
DDL	CREATE, ALTER, DROP, TRUNCATE, RENAME	Defines database structure
DML	INSERT, UPDATE, DELETE	Manipulates data
DQL	SELECT	Retrieves data
DCL	GRANT, REVOKE	Controls permissions
TCL	COMMIT, ROLLBACK, SAVEPOINT	Controls transactions

Exam Trick

DDL → Structure
DML → Data
DQL → Query
DCL → Control
TCL → Transaction

3. SQL Data Types

Numeric

INT
BIGINT
FLOAT
DOUBLE
DECIMAL

Character

CHAR
VARCHAR

Date & Time

DATE
TIME
DATETIME
TIMESTAMP

4. DDL Commands

CREATE

Creates objects.

CREATE TABLE Student(
            RollNo INT PRIMARY KEY,
            Name VARCHAR(50)
            );

ALTER

Changes table structure.

ALTER TABLE Student
            ADD Email VARCHAR(50);

DROP

Deletes table permanently.

TRUNCATE

Deletes all rows but keeps table structure.

RENAME

Changes table name.

5. DML Commands

INSERT

Adds records.

INSERT INTO Student
            VALUES(101,'Rahul');

UPDATE

Modifies records.

UPDATE Student
            SET Marks=90
            WHERE RollNo=101;

DELETE

Deletes selected records.

DELETE FROM Student
            WHERE RollNo=101;

6. SELECT Statement

Retrieve all records

SELECT * FROM Student;

Specific columns

SELECT Name,Marks
            FROM Student;

7. WHERE Clause

Filters rows.

SELECT *
            FROM Student
            WHERE Marks>80;

Cannot use aggregate functions.

8. ORDER BY

Sorts output.

Ascending

ORDER BY Marks ASC;

Descending

ORDER BY Marks DESC;

9. DISTINCT

Removes duplicate values.

SELECT DISTINCT City
            FROM Student;

10. LIKE Operator

Symbol	Meaning
%	Multiple characters
_	Single character

Example

WHERE Name LIKE 'R%'

11. BETWEEN

WHERE Marks BETWEEN 60 AND 80;

Inclusive operator.

12. IN Operator

WHERE City IN ('Jaipur','Delhi');

13. NULL Operators

IS NULL

            IS NOT NULL

Never use

= NULL

14. Aggregate Functions

Function	Purpose
COUNT()	Count rows
SUM()	Total
AVG()	Average
MIN()	Minimum
MAX()	Maximum

Remember

COUNT(*) counts all rows.
COUNT(column) ignores NULL values.
AVG() ignores NULL values.

15. GROUP BY

Groups rows.

SELECT City,COUNT(*)
            FROM Student
            GROUP BY City;

16. HAVING

Filters grouped records.

HAVING COUNT(*)>5;

WHERE vs HAVING

WHERE	HAVING
Before GROUP BY	After GROUP BY
Row Filter	Group Filter
No Aggregate	Aggregate Allowed

17. SQL Constraints

PRIMARY KEY
FOREIGN KEY
UNIQUE
NOT NULL
CHECK
DEFAULT

18. Keys

Primary Key

Unique
NOT NULL

Foreign Key

Creates relationship
Maintains Referential Integrity

Unique

Unique values
NULL allowed (DBMS dependent)

19. SQL Joins

INNER JOIN – Matching rows only.
LEFT JOIN – All rows from left table.
RIGHT JOIN – All rows from right table.
FULL OUTER JOIN – All rows from both tables.
CROSS JOIN – Cartesian Product
Rows = m × n
SELF JOIN – Table joins with itself.

20. SQL Views

Virtual table.

CREATE VIEW TopStudents AS
            SELECT *
            FROM Student
            WHERE Marks>90;

21. Index

Purpose

Faster searching
Faster SELECT

Disadvantages

Extra storage
Slower INSERT
Slower UPDATE
Slower DELETE

22. Transactions

Commands

COMMIT
ROLLBACK
SAVEPOINT

23. ACID Properties

Property	Meaning
A	Atomicity
C	Consistency
I	Isolation
D	Durability

24. SQL Execution Order (Highly Important)

FROM
            ↓
            WHERE
            ↓
            GROUP BY
            ↓
            HAVING
            ↓
            SELECT
            ↓
            DISTINCT
            ↓
            ORDER BY
            ↓
            LIMIT

25. Frequently Asked GATE Facts

SQL is Declarative.
SELECT belongs to DQL.
CREATE belongs to DDL.
INSERT belongs to DML.
COMMIT belongs to TCL.
GRANT belongs to DCL.
PRIMARY KEY = UNIQUE + NOT NULL.
FOREIGN KEY maintains Referential Integrity.
HAVING works after GROUP BY.
WHERE cannot use aggregate functions.
COUNT(*) counts NULL rows.
COUNT(column) ignores NULL.
AVG ignores NULL.
CROSS JOIN = Cartesian Product.

Memory Tricks

CRUD

Create
Read
Update
Delete

SQL Commands

DDL → Define
DML → Modify
DQL → Query
DCL → Control
TCL → Transaction

ACID

A → Atomicity
C → Consistency
I → Isolation
D → Durability

Last-Minute Revision

            ✅ SQL = Structured Query Language
✅ SQL is Declarative.
✅ DDL is Auto Commit.
✅ DML can be Rolled Back.
✅ DELETE removes selected rows.
✅ TRUNCATE removes all rows only.
✅ DROP removes the complete table.
✅ PRIMARY KEY = UNIQUE + NOT NULL.
✅ FOREIGN KEY creates relationships.
✅ WHERE filters rows.
✅ HAVING filters groups.
✅ GROUP BY creates groups.
✅ DISTINCT removes duplicates.
✅ COUNT(*) counts every row.
✅ COUNT(column) ignores NULL.
✅ AVG() ignores NULL.
✅ INNER JOIN returns matching rows.
✅ LEFT JOIN returns all left rows.
✅ RIGHT JOIN returns all right rows.
✅ CROSS JOIN returns Cartesian Product.
✅ COMMIT saves transaction permanently.
✅ ROLLBACK undoes uncommitted changes.
✅ ACID = Atomicity, Consistency, Isolation, Durability.

        

One-Line Exam Formula

DDL → Structure → Auto Commit
DML → Data → Rollback Possible
WHERE → Rows
HAVING → Groups
COUNT(*) ≠ COUNT(column)
Primary Key ⊂ Candidate Key ⊂ Super Key
Foreign Key → Relationship
SQL Execution: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

These are the highest-yield facts repeatedly tested in GATE CSE, UGC NET, RPSC Computer Teacher, Rajasthan Informatics Assistant, JVVNL, ISRO, BARC, and NIELIT examinations.

Practice MCQs (Exam-Oriented)

Score Yourself

32–35 Correct: Excellent (GATE/RPSC Ready)
28–31 Correct: Very Good
22–27 Correct: Good Revision Needed
Below 22: Revise SQL Basics Again

Exam Tip: Questions on DDL vs DML, WHERE vs HAVING, JOINs, Aggregate Functions, Constraints, ACID Properties, and SQL Execution Order are among the most frequently asked in GATE, RPSC Computer Teacher, Rajasthan Informatics Assistant, JVVNL, UGC NET, and other computer science competitive examinations.