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

0

 


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

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

  1. Receives request
  2. Processes SQL query
  3. Accesses database
  4. Returns 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:

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. 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

  1. What is MySQL?
  2. What is DBMS and RDBMS?
  3. What is SQL?
  4. Differentiate SQL and MySQL.
  5. What is a primary key?
  6. What is a foreign key?
  7. What is a composite key?
  8. What are DDL, DML and DQL?
  9. Differentiate DELETE, TRUNCATE and DROP.
  10. What is the use of WHERE?
  11. What is GROUP BY?
  12. Differentiate WHERE and HAVING.
  13. What are aggregate functions?
  14. What is a JOIN? Explain its types.
  15. What is a subquery?
  16. What is a view?
  17. What is an index?
  18. Explain ACID properties.
  19. What is normalization? Explain 1NF, 2NF and 3NF.
  20. What are stored procedures and triggers?

 

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.

 

LAB ACTIVITY:

1. Create a table structure and modify as given:

a. Create a table named Book with the following columns:

Field (Column Name)

Data Type

BookID

Integer, Primary Key

Title

Varchar(50)

Author

Varchar(50)

Price

Decimal(6,2)

 

CREATE TABLE Book (

    BookID INT PRIMARY KEY,

    Title VARCHAR(50),

    Author VARCHAR(50),

    Price DECIMAL(6,2)

);

 

b. Add a new column Publisher (Varchar(50)) and PublishedYear (INT) to the Book table.

 

ALTER TABLE Book

ADD Publisher VARCHAR(50),

ADD PublishedYear INT;

 

c. Add a new column Publisher (Varchar(50)) and PublishedYear (INT) to the Book table. The expected table structure is as below:

 

ALTER TABLE Book

ADD Publisher VARCHAR(50),

ADD PublishedYear INT;

 

d. Change the data type of the Price column in the Book table from Decimal(6,2) to INT. The expected table structure is as below:

 

ALTER TABLE Book

MODIFY Price INT;

 

e. Remove the Publisher column from the Book table.

 

ALTER TABLE Book

DROP COLUMN Publisher;

 

f. Rename the Book table to LibraryBook, then rename the column Title to BookTitle.

 

ALTER TABLE Book

RENAME TO LibraryBook;

 

ALTER TABLE LibraryBook

RENAME COLUMN Title TO BookTitle;

 

2. Create a table structure and modify as given:

a. Create a table named Student with the following columns:

Field (Column Name)

Data Type

StudentID

INT, Primary Key

FullName

VARCHAR(50)

Age

INT

Gender

VARCHAR(30)

 

CREATE TABLE Student (

    StudentID INT PRIMARY KEY,

    FullName VARCHAR(50),

    Age INT,

    Gender VARCHAR(30)

);

 

b. Add two new columns Contact Number VARCHAR(15) and Email VARCHAR(50)

to the Student table.

 

ALTER TABLE Student

ADD ContactNumber VARCHAR(15),

ADD Email VARCHAR(50);

 

c. Change the column name FullName to Student Name and also change the data type of Age from INT to VARCHAR(3).

 

ALTER TABLE Student

RENAME COLUMN FullName TO StudentName;

 

ALTER TABLE Student

MODIFY Age VARCHAR(3);

 

d. Remove the Contact Number column from the Student table.

 

ALTER TABLE Student

DROP COLUMN ContactNumber;

 

e. Rename the table Student to CollegeStudent.

 

ALTER TABLE Student

RENAME TO CollegeStudent;

 

3. Create a table named Product with these columns:

Field Name

Data Type

Description

ProductID

INT PRIMARY KEY

Unique product ID

ProductName

CHAR(30)

Name of product

Price

FLOAT (8,2)

Price of product (2 decimal places)

 

CREATE TABLE Product (

    ProductID INT PRIMARY KEY,

    ProductName CHAR(30),

    Price FLOAT(8,2)

);

 

4. Create a table named My_Books with these columns:

Firld Name

Data Type

Description

BookID

INT PRIMARY KEY

Unique book ID

Title

VARCHAR(100)

Book title

Description

TEXT

Full book description

PublishedOn

DATE

Publication date

 

CREATE TABLE My_Books (

    BookID INT PRIMARY KEY,

    Title VARCHAR(100),

    Description TEXT,

    PublishedOn DATE

);

 

5. Create a table named Marks with these columns:

Field Name

Data Type

Description

Student ID

 INT PRIMARY KEY

 Unique student number

MathsMarks

 DOUBLE (6,2)

 Marks in Maths

ScienceMarks

 DOUBLE (6,2)

Marks in Science

 

CREATE TABLE Marks (

    StudentID INT PRIMARY KEY,

    MathsMarks DOUBLE(6,2),

    ScienceMarks DOUBLE(6,2)

);

 

6. Create a table named Feedback with these columns:

Field Name

Data Type

Description

FeedbackID

INT PRIMARY KEY

Unique feedback number

CustomerName

CHAR(50)

Customer's name

Comment

TEXT

 Feedback text

IsSatisfied

TINYINT (1)

 1 for yes, 0 for no

 

CREATE TABLE Feedback (

    FeedbackID INT PRIMARY KEY,

    CustomerName CHAR(50),

    Comment TEXT,

    IsSatisfied TINYINT(1)

);

 

7. Create a table named FileStore with these columns:

Field Name

Data Type

Description

FileID

INT PRIMARY KEY

Unique file number

CustomerName

CHAR(50)

Customer's name

FileName

VARCHAR(100)

Name of the file

FileData

BLOB

Binary file data

 

CREATE TABLE FileStore (

    FileID INT PRIMARY KEY,

    CustomerName CHAR(50),

    FileName VARCHAR(100),

    FileData BLOB

);


Post a Comment (0)
Previous Post Next Post