Learn SQL commands, DBMS/RDBMS, keys, joins, functions, normalization, transactions, ACID properties, and important MySQL concepts in a simple and quick-revision format.

1. MySQL

MySQL is an open-source Relational Database Management System (RDBMS) used to store, organize, manage, and retrieve data. It uses SQL (Structured Query Language) and stores data in tables containing rows and columns.

Key points

Open source
RDBMS
Uses SQL
Cross-platform
Supports multiple users
Supports transactions
Supports indexes, views, procedures and triggers
Owned by Oracle Corporation

2. Important Basic Terms

Term	Exam Definition
Database	Organized collection of data
DBMS	Software used to manage databases
RDBMS	DBMS based on relationships between tables
SQL	Language used to communicate with databases
Table	Collection of related data
Row/Record	One complete data item
Column/Field	Attribute of data
Query	Request to retrieve or manipulate data
Primary Key	Uniquely identifies each record
Foreign Key	Connects one table with another

3. MySQL Architecture

MySQL follows a client-server architecture.

Client

Sends SQL commands to the server.

Examples:

MySQL Workbench
MySQL Command Line Client
PHP/Python/Java applications

Server

Receives request
Processes SQL query
Accesses database
Returns result

Exam trick:
Client → Request → Server → Process → Result

4. Database Commands

Create database

CREATE DATABASE school;

Show databases

SHOW DATABASES;

Select database

USE school;

Delete database

DROP DATABASE school;

DROP DATABASE removes the database and its contents.

5. Table

A table contains rows and columns.

Example:

CREATE TABLE student (
    id INT,
    name VARCHAR(50),
    age INT,
    address VARCHAR(100)
);

Remember

Column = Field/Attribute
Row = Record/Tuple

6. Important MySQL Data Types

Numeric

INT – integer
DECIMAL – exact decimal
FLOAT – approximate floating-point
DOUBLE – larger/more precise floating-point values

String

CHAR – fixed length
VARCHAR – variable length
TEXT – large text

Date/Time

DATE
TIME
DATETIME
YEAR

Very important

CHAR = Fixed
VARCHAR = Variable

7. Constraints

Constraints are rules applied to columns.

Main constraints:

PRIMARY KEY
FOREIGN KEY
NOT NULL
UNIQUE
DEFAULT
CHECK
AUTO_INCREMENT

Primary Key

Uniquely identifies a record
Cannot contain NULL
May contain one or multiple columns

Foreign Key

Creates a relationship between tables.

NOT NULL

Does not allow NULL.

UNIQUE

Prevents duplicate values.

DEFAULT

Provides a default value.

CHECK

Restricts values according to a condition.

AUTO_INCREMENT

Automatically generates sequential numbers.

8. SQL Command Categories

DDL — Data Definition Language

Used to define database structure.

Main commands:

CREATE
ALTER
DROP
TRUNCATE

DML — Data Manipulation Language

Used to manipulate data.

INSERT
UPDATE
DELETE

DQL — Data Query Language

Main command:

SELECT

Memory Trick

DDL = Structure
DML = Data
DQL = Query

9. Most Important SQL Commands

INSERT

Adds records.

INSERT INTO student (id, name, age)
VALUES (1, 'Ram', 18);

UPDATE

Modifies existing data.

UPDATE student
SET age = 20
WHERE id = 1;

DELETE

Deletes records.

DELETE FROM student
WHERE id = 1;

SELECT

Retrieves records.

SELECT * FROM student;

Exam warning: UPDATE or DELETE without WHERE can affect all rows.

10. WHERE Clause

Used to filter records.

SELECT * FROM student
WHERE age > 18;

Comparison operators

Operator	Meaning
`=`	Equal
`<>`, `!=`	Not equal
`>`	Greater than
`<`	Less than
`>=`	Greater/equal
`<=`	Less/equal

11. Logical Operators

AND

Both conditions must be true.

OR

At least one condition must be true.

NOT

Negates a condition.

Memory:
AND = Both
OR = Any one
NOT = Opposite

12. BETWEEN

Selects values within a range.

SELECT * FROM student
WHERE age BETWEEN 18 AND 20;

13. IN

Checks values against a list.

SELECT * FROM student
WHERE address IN ('Janakpur', 'Jaleshwar', 'Kathmandu');

14. LIKE

Used for pattern matching.

name LIKE 'A%'

Starts with A.

name LIKE '%a'

Ends with a.

name LIKE '%am%'

Contains am.

name LIKE '_am'

_ represents a single character.

Memory

% = Multiple characters
_ = One character

15. ORDER BY

Used to sort records.

Ascending:

ORDER BY age ASC;

Descending:

ORDER BY age DESC;

ASC = Ascending
DESC = Descending

16. DISTINCT

Removes duplicate results.

SELECT DISTINCT address
FROM student;

17. LIMIT

Restricts the number of returned rows.

SELECT * FROM student
LIMIT 5;

18. Aggregate Functions ⭐

Very important for exams.

Function	Purpose
`COUNT()`	Counts records
`SUM()`	Calculates total
`AVG()`	Calculates average
`MAX()`	Finds maximum
`MIN()`	Finds minimum

Example:

SELECT COUNT(*) FROM student;

Memory Trick

COUNT → How many?
SUM → Total?
AVG → Average?
MAX → Highest?
MIN → Lowest?

19. GROUP BY

Groups records having the same value.

SELECT address, COUNT(*)
FROM student
GROUP BY address;

Used, for example, to count students by address.

20. HAVING

Filters groups created by GROUP BY.

SELECT address, COUNT(*)
FROM student
GROUP BY address
HAVING COUNT(*) > 5;

WHERE vs HAVING ⭐

WHERE	HAVING
Filters rows	Filters groups
Used before grouping	Used with grouped results

Memory:
WHERE → Rows
HAVING → Groups

21. NULL

NULL means missing, unknown, or unavailable value.

Correct:

WHERE email IS NULL;

Correct:

WHERE email IS NOT NULL;

Not normally:

WHERE email = NULL;

22. Joins ⭐⭐⭐

Joins retrieve related data from multiple tables.

Main types in your notes:

INNER JOIN
LEFT JOIN
RIGHT JOIN
CROSS JOIN

INNER JOIN

Returns matching records from both tables.

LEFT JOIN

Returns all records from the left table and matching records from the right table.

RIGHT JOIN

Returns all records from the right table and matching records from the left table.

CROSS JOIN

Produces a Cartesian product.

Memory:

INNER = Matching
LEFT = All left
RIGHT = All right
CROSS = Everything with everything

23. Subquery

A query inside another query is called a subquery.

Example:

SELECT name
FROM student
WHERE age > (
    SELECT AVG(age)
    FROM student
);

24. View

A view is a virtual table based on a query.

Create:

CREATE VIEW student_view AS
SELECT id, name, age
FROM student;

Use:

SELECT * FROM student_view;

Delete:

DROP VIEW student_view;

Exam answer:
View = Virtual Table

25. Index

An index improves the speed of data retrieval.

CREATE INDEX idx_name
ON student(name);

Advantage

Faster searching
Can improve query performance

Disadvantage

Requires additional storage
Can make data modification more expensive

Memory:
INDEX = Faster Search

26. Transaction

A transaction is a group of database operations treated as one unit.

Important commands:

COMMIT
ROLLBACK
SAVEPOINT

COMMIT

Permanently saves changes.

ROLLBACK

Undoes uncommitted changes.

SAVEPOINT

Creates a point to which a transaction can roll back.

27. ACID Properties ⭐⭐⭐

Letter	Meaning
A	Atomicity
C	Consistency
I	Isolation
D	Durability

Atomicity

Transaction happens completely or not at all.

Consistency

Database remains valid.

Isolation

Transactions do not improperly interfere with each other.

Durability

Committed changes remain saved.

Memory:
ACID = Atomicity + Consistency + Isolation + Durability

28. InnoDB

InnoDB is an important MySQL storage engine.

Features mentioned in the notes:

Supports transactions
Supports foreign keys
Supports row-level locking
Provides crash recovery

It is the default storage engine in modern MySQL.

29. Important Keys ⭐

Primary Key

Uniquely identifies a record.

Foreign Key

Creates a relationship between tables.

Candidate Key

Can uniquely identify records.

Alternate Key

Candidate key not selected as primary key.

Composite Key

Key made of two or more columns.

Example:

PRIMARY KEY (student_id, subject_id);

30. Database Relationships

One-to-One

One record → one related record.

One-to-Many

One record → many related records.

Example:

One Department → Many Students

Many-to-Many

Many records → many records.

Usually implemented using a junction/bridge table.

Example:

Students ↔ Subjects

31. Normalization ⭐⭐⭐

Normalization organizes data to:

Reduce redundancy
Improve data integrity
Prevent update anomalies

1NF

Atomic values
No repeating groups

2NF

Must satisfy 1NF
No partial dependency on part of a composite key

3NF

Must satisfy 2NF
No inappropriate transitive dependency

BCNF

A stronger form of 3NF.

Memory:
1NF → Atomic
2NF → No Partial Dependency
3NF → No Transitive Dependency

32. Stored Procedure

A stored procedure is a group of SQL statements stored in the database and executed when called.

CALL GetStudents();

33. MySQL Functions

String Functions

UPPER()
LOWER()
LENGTH()
CONCAT()
SUBSTRING()
TRIM()

Numeric Functions

ROUND()
CEIL()
FLOOR()
ABS()
MOD()

Date Functions

NOW()
CURDATE()
CURTIME()
YEAR()
MONTH()
DAY()

34. Trigger

A trigger is automatically executed when a specified database event occurs.

Events:

INSERT
UPDATE
DELETE

Memory:
Trigger = Automatic Action

35. DELETE vs TRUNCATE vs DROP ⭐⭐⭐

Feature	DELETE	TRUNCATE	DROP
Removes rows	Yes	Yes	Yes
Removes structure	No	No	Yes
`WHERE`	Yes	No	No
Selected rows	Yes	No	No
Table remains	Yes	Yes	No

Best memory trick

DELETE → Selected Data
TRUNCATE → All Rows
DROP → Whole Object

36. CHAR vs VARCHAR ⭐

CHAR	VARCHAR
Fixed length	Variable length
Suitable for fixed-size values	Suitable for varying-size values

Remember:
CHAR = Fixed
VARCHAR = Variable

37. DBMS vs RDBMS

DBMS

Software used to manage databases.

RDBMS

Database system based on relationships between tables and supporting relational concepts such as keys.

Examples listed in your notes include MySQL, PostgreSQL and Oracle Database.

38. SQL vs MySQL ⭐

SQL = Database language
MySQL = Database management system that uses SQL.

Easy trick

SQL = Language
MySQL = Software/RDBMS

39. Most Important Commands — One Table

Command	Use
`CREATE DATABASE`	Create database
`SHOW DATABASES`	Show databases
`USE`	Select database
`CREATE TABLE`	Create table
`SHOW TABLES`	Show tables
`DESC`	Show table structure
`ALTER TABLE`	Modify table
`INSERT`	Add data
`SELECT`	Retrieve data
`UPDATE`	Modify data
`DELETE`	Delete data
`TRUNCATE`	Remove all rows
`DROP`	Delete object
`ORDER BY`	Sort data
`GROUP BY`	Group data
`HAVING`	Filter groups
`JOIN`	Combine related tables

40. 🔥 Top 20 Questions for Exam Revision

What is MySQL?
What is DBMS and RDBMS?
What is SQL?
Differentiate SQL and MySQL.
What is a primary key?
What is a foreign key?
What is a composite key?
What are DDL, DML and DQL?
Differentiate DELETE, TRUNCATE and DROP.
What is the use of WHERE?
What is GROUP BY?
Differentiate WHERE and HAVING.
What are aggregate functions?
What is a JOIN? Explain its types.
What is a subquery?
What is a view?
What is an index?
Explain ACID properties.
What is normalization? Explain 1NF, 2NF and 3NF.
What are stored procedures and triggers?

These topics correspond directly to the major exam-question areas listed in your uploaded notes.

🚀 Last-Minute Memory Sheet

MySQL → RDBMS
SQL → Language
Table → Rows + Columns
Primary Key → Unique Identity
Foreign Key → Relationship
DDL → CREATE, ALTER, DROP, TRUNCATE
DML → INSERT, UPDATE, DELETE
DQL → SELECT
WHERE → Filters Rows
GROUP BY → Makes Groups
HAVING → Filters Groups
ORDER BY → Sorts
LIKE → Pattern Matching
% → Multiple Characters
_ → One Character
JOIN → Combines Tables
VIEW → Virtual Table
INDEX → Faster Searching
TRANSACTION → Unit of Work
ACID → Atomicity, Consistency, Isolation, Durability
1NF → Atomic
2NF → No Partial Dependency
3NF → No Transitive Dependency
TRIGGER → Automatic Action
CHAR → Fixed
VARCHAR → Variable
DELETE → Selected Rows
TRUNCATE → All Rows
DROP → Whole Object.

MySQL Short Notes: Complete Exam-Focused Guide for SQL & Database Preparation

Learn SQL commands, DBMS/RDBMS, keys, joins, functions, normalization, transactions, ACID properties, and important MySQL concepts in a simple and quick-revision format.

1. MySQL

Key points

2. Important Basic Terms

3. MySQL Architecture

Client

Server

4. Database Commands

Create database

Show databases

Select database

Delete database

5. Table

Remember

6. Important MySQL Data Types

Numeric

String

Date/Time

Very important

7. Constraints

Primary Key

Foreign Key

NOT NULL

UNIQUE

DEFAULT

CHECK

AUTO_INCREMENT

8. SQL Command Categories

DDL — Data Definition Language

DML — Data Manipulation Language

DQL — Data Query Language

Memory Trick

9. Most Important SQL Commands

INSERT

UPDATE

DELETE

SELECT

10. WHERE Clause

Comparison operators

11. Logical Operators

AND

OR

NOT

12. BETWEEN

13. IN

14. LIKE

Memory

15. ORDER BY

16. DISTINCT

17. LIMIT

18. Aggregate Functions ⭐

Memory Trick

19. GROUP BY

20. HAVING

WHERE vs HAVING ⭐

21. NULL

22. Joins ⭐⭐⭐

INNER JOIN

LEFT JOIN

RIGHT JOIN

CROSS JOIN

23. Subquery

24. View

25. Index

Advantage

Disadvantage

26. Transaction

COMMIT

ROLLBACK

SAVEPOINT

27. ACID Properties ⭐⭐⭐

Atomicity

Consistency

Isolation

Durability

28. InnoDB

29. Important Keys ⭐

Primary Key

Foreign Key