DATABASE – MySQL
Database and RDBMS Basics ⭐⭐
• Database: Organised collection of structured information stored electronically in a computer
system.
• DBMS: Software used to create, manage and control databases.
• RDBMS: Data stored in tables with relations using Primary Key and Foreign Key.
• MySQL: Popular open-source RDBMS.
MySQL and Its Importance ⭐⭐⭐
• Open-source software, free to use.
• Powerful and supports large databases.
• Works on multiple operating systems.
• Supports many languages like PHP, Java, C, C++.
• Fast processing and high performance.
• Customizable as per user needs.
Types of SQL Commands ⭐⭐⭐
Data Definition Language DDL ⭐⭐
Used to define or change structure of database objects. Auto-committed. - CREATE - ALTER - DROP -
TRUNCATE
Data Manipulation Language DML ⭐⭐⭐
Used to modify data in tables. Not auto-committed. - INSERT - UPDATE - DELETE
Data Control Language DCL ⭐⭐
Used to grant or revoke permissions. - GRANT - REVOKE
Transaction Control Language TCL ⭐
Used with DML commands. - COMMIT - ROLLBACK - SAVEPOINT
Data Query Language DQL ⭐⭐
Used to fetch data. - SELECT - SHOW - HELP
1
Creating, Selecting and Removing Database ⭐⭐
• Create Database CREATE DATABASE database_name;
• Select Database USE database_name;
• Remove Database DROP DATABASE database_name;
MySQL Data Types ⭐⭐⭐
• CHAR: Fixed length string
• VARCHAR: Variable length string
• TEXT: Large text
• INT: Integer values
• FLOAT / DOUBLE: Decimal values
• DATE: YYYY-MM-DD
• DATETIME: YYYY-MM-DD HH:MM:SS ⭐⭐⭐
• TIME: HH:MM:SS
• YEAR: Year values
Data type depends on - Storage size - Type of value stored
MySQL Tables ⭐⭐
• Table consists of rows and columns.
• Rows represent records.
• Columns represent attributes.
• Degree: Number of columns.
• Cardinality: Number of rows.
Rules for Naming a Table ⭐⭐⭐
• Maximum 30 characters.
• Must start with an alphabet.
• Can contain alphabets, numbers and underscore.
• No reserved words allowed.
Creating a Table ⭐⭐⭐
• CREATE TABLE command is used.
• Each column has name, data type and size.
• PRIMARY KEY uniquely identifies records.
• NOT NULL does not allow empty values.
2
Constraints ⭐⭐⭐
Constraints ensure validity of data. - PRIMARY KEY ⭐⭐⭐ - NOT NULL ⭐⭐⭐ - UNIQUE - FOREIGN KEY
Displaying Table Structure ⭐⭐⭐
• DESCRIBE table_name;
• DESC table_name;
Inserting Data in Table ⭐⭐
• INSERT INTO table_name VALUES(...);
• Multiple rows can be inserted in a single query.
ALTER TABLE Command ⭐⭐⭐
Used to modify structure of a table. - Add columns - Modify columns - Rename column - Drop column - Add
or delete constraints
SELECT Statement ⭐⭐⭐
Used to retrieve data from table.
• Select all columns SELECT * FROM table_name;
• Select specific columns SELECT column1, column2 FROM table_name;
• Using WHERE clause ⭐⭐⭐ Used to retrieve selected rows based on condition.
DISTINCT and ALL ⭐⭐
• DISTINCT removes duplicate values.
• ALL displays all values.
Pattern Matching using LIKE ⭐⭐⭐
• % matches any number of characters.
• _ matches single character.
3
BETWEEN Clause ⭐⭐⭐
• Used to search values within a range.
• Includes both lower and upper limits.
Operators in MySQL ⭐⭐⭐
Arithmetic Operators ⭐⭐
Used in calculations. - + - * / %
Relational Operators ⭐⭐
• = != < > <= >=
Logical Operators ⭐⭐⭐
• AND
• OR
• NOT
Sorting Data ⭐⭐
• ORDER BY clause used.
• ASC for ascending
• DESC for descending
Deleting Data ⭐⭐⭐
• DELETE FROM table_name;
• DELETE FROM table_name WHERE condition;
Updating Data ⭐⭐⭐
• UPDATE table_name SET column=value;
• UPDATE table_name SET column=value WHERE condition;
Aggregate Functions ⭐⭐⭐
Used to perform calculations on a group of values. - SUM() - AVG() - MAX() - MIN() - COUNT() - COUNT(*)
4
Very Important One Mark Facts ⭐⭐⭐
• MySQL statement is terminated by semicolon ; ⭐⭐⭐
• Command used to delete database physically: DROP DATABASE ⭐⭐⭐
• Commands used to modify database: DML ⭐⭐⭐
• Data type used to store date and time: DATETIME ⭐⭐⭐
• Clause used to search data within a range: BETWEEN ⭐⭐⭐
• Command to display complete information about table fields: DESCRIBE / DESC ⭐⭐⭐
• Command used to fetch data from database: SELECT ⭐⭐⭐
• Rules that ensure validity of data while entering into table: Constraints ⭐⭐⭐
• Commands used to grant and take back authority from users: DCL ⭐⭐⭐
• Operator used during viewing records and data manipulation: Arithmetic Operator ⭐⭐
• Organised collection of structured information: Database ⭐⭐⭐
• Statements used to give or withdraw access privileges: GRANT / REVOKE ⭐⭐⭐
PYQ Focused Two Mark Answers ⭐⭐⭐
DML Commands ⭐⭐⭐
• INSERT
• UPDATE
• DELETE
WHERE Clause ⭐⭐⭐
• Used to retrieve selected rows that satisfy a given condition.
Renaming a Column ⭐⭐⭐
ALTER TABLE table_name CHANGE old_column new_column datatype size;
Operators in MySQL ⭐⭐⭐
• Arithmetic operators
• Relational operators
• Logical operators
Constraints ⭐⭐⭐
Constraints are rules applied on columns to ensure validity of data. Examples: - PRIMARY KEY - NOT NULL
LIKE Operator ⭐⭐⭐
Used for pattern matching using wildcard characters % and _
5
BETWEEN Clause ⭐⭐⭐
Used to retrieve values within a specified range including limits.
Determining Data Type ⭐⭐⭐
• Storage size required
• Type of value represented
Adding NULL or NOT NULL Later ⭐⭐⭐
Yes, using ALTER TABLE MODIFY command.
PYQ Focused Three Mark Answers ⭐⭐⭐
Basic SQL Commands ⭐⭐⭐
• DDL
• DML
• DCL
• TCL
• DQL
Rules for Naming a Table ⭐⭐⭐
• Maximum 30 characters
• Must start with alphabet
• Can contain alphabets, numbers and underscore
• Reserved words not allowed
Popularity of MySQL ⭐⭐⭐
• Free and open-source
• Supports large databases
• Fast and reliable
• Works on multiple platforms
PYQ Focused Four Mark Answer ⭐⭐⭐
SQL Commands with Example ⭐⭐⭐
• DDL: CREATE TABLE student (...);
• DML: INSERT INTO student VALUES (...);
• DCL: GRANT SELECT ON student TO user;
• TCL: COMMIT;
• DQL: SELECT * FROM student;
6
Practical Questions from MySQL ⭐⭐⭐
Practical PYQ Pattern Analysis ⭐⭐⭐
Based on 2023, 2024 and 2025 practical papers, MySQL practical questions strictly follow a fixed pattern.
1. Table Creation Practical ⭐⭐⭐
Almost every year asked.
Common requirements: - Create table with 4–6 fields - Decide data types - One field must be PRIMARY KEY -
Use NOT NULL constraint
Example Pattern ⭐⭐⭐ - Create table PATIENT / SALES / STUDENT - Fields include code, name, age or
amount, date field
2. Insert Records Practical ⭐⭐⭐
Always asked after table creation.
Pattern: - Insert 4 to 6 records - Values given in table format - DATE written as YYYY-MM-DD
Important Points ⭐⭐⭐ - CHAR values inside single quotes - DATE values inside single quotes - Numeric
values without quotes
3. SELECT Query Practical ⭐⭐⭐
Asked every year with variations.
Commonly asked SELECT operations: - Display all records - Display selected columns - Display records using
WHERE clause
4. WHERE Clause Practical ⭐⭐⭐
Most repeated practical area.
Conditions asked: - Age less than a value - Sales amount greater than or equal to value - Category equal to
specific letter
7
5. DISTINCT and COUNT Practical ⭐⭐⭐
Very important.
Patterns: - Display number of different departments - Display distinct categories
Functions used: - DISTINCT - COUNT - COUNT DISTINCT
6. ORDER BY Practical ⭐⭐⭐
Frequently asked.
Patterns: - Ascending order - Descending order
7. BETWEEN Practical ⭐⭐⭐
Repeated practical question.
Pattern:_toggle - Display records having value between range
Includes both limits.
8. LIKE Practical ⭐⭐⭐
Repeated practical.
Patterns: - Name starts with letter - Name ends with letter - Name contains characters
9. Aggregate Functions Practical ⭐⭐⭐
Asked almost every year.
Functions: - COUNT - MAX - MIN - AVG - SUM
10. Examiner Favourite MySQL Commands ⭐⭐⭐
• CREATE TABLE
• INSERT INTO
• SELECT
• WHERE
• DISTINCT
8
• COUNT
• ORDER BY
• LIKE
• BETWEEN
One-Line Practical Viva Points ⭐⭐⭐
• Primary key uniquely identifies a record
• NOT NULL does not allow empty values
• DISTINCT removes duplicate values
• COUNT returns number of records
• DATE format is YYYY-MM-DD
• WHERE filters records