MySQL Revision Tour Notes | Class 12 Informatic Practices

1. Introduction

A database is an organised collection of data stored and accessed electronically. While files store raw data with no structure, a database management system (DBMS) adds facilities to store, retrieve, update, and protect data in a systematic way. MySQL is one of the most popular open-source relational database management systems (RDBMS) in the world, and it forms the SQL half of the Class 12 Informatic Practices syllabus. This chapter is a revision tour that consolidates everything learnt in Class 11 about MySQL, serving as the base for the querying and function chapters that follow.

An RDBMS stores data in tables, where a table is a collection of rows and columns. Each row, called a tuple or record, describes one entity, and each column, called an attribute or field, holds one property of that entity. Tables can be related to each other through shared columns, which is the "relational" part of the name. MySQL communicates with the user through SQL (Structured Query Language), a standard language whose statements are divided into categories such as DDL, DML, and DCL.

This revision chapter covers the complete command set needed to build and manage tables. We will start with the distinction between DBMS and RDBMS, then look at the MySQL data types, and finally work through the full set of SQL commands: CREATE, INSERT, SELECT, UPDATE, DELETE, ALTER, and DROP, together with the constraints that enforce data integrity.

2. DBMS versus RDBMS

A DBMS stores data as files and has no relationship between the files. An RDBMS stores data in tables and allows the tables to be related through common fields. Because relationships reduce redundancy and enforce consistency, RDBMS systems such as MySQL, Oracle, and PostgreSQL dominate industry use.

Key characteristics of an RDBMS:

Data is stored in two-dimensional tables.
Tables can be linked through common columns known as keys.
Data integrity is enforced using constraints.
SQL is used to query and manipulate the data.

3. MySQL Data Types

Every column of a table must have a declared data type. The most commonly used types in the syllabus are:

CHAR(n): Fixed-length string of exactly n characters, e.g. CHAR(10).
VARCHAR(n): Variable-length string of up to n characters, e.g. VARCHAR(30).
INT: Whole number ranging from about -2.1 billion to +2.1 billion.
DECIMAL(p, s): Exact decimal number with p total digits and s digits after the decimal point, e.g. DECIMAL(6, 2).
DATE: Stores a date in the format YYYY-MM-DD.
FLOAT: A floating-point number for approximate values.
TEXT: A long string for large amounts of text.

CREATE TABLE student (
    roll INT,
    name VARCHAR(30),
    class CHAR(3),
    fees DECIMAL(6, 2),
    dob DATE
);

4. Categories of SQL Commands

4.1 DDL (Data Definition Language)

Commands that define or modify the structure of database objects. Examples: CREATE, ALTER, DROP.

4.2 DML (Data Manipulation Language)

Commands that work with the data inside tables. Examples: INSERT, UPDATE, DELETE, SELECT.

4.3 DCL (Data Control Language)

Commands that control access rights, such as GRANT and REVOKE.

5. Creating and Using a Database

CREATE DATABASE school;
USE school;
SHOW DATABASES;
DROP DATABASE school;

CREATE DATABASE creates a new database.
USE selects the database for subsequent commands.
SHOW DATABASES lists all databases on the server.
DROP DATABASE removes a database completely.

6. Creating and Describing a Table

CREATE TABLE student (
    roll INT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    marks DECIMAL(5, 2),
    city VARCHAR(20) DEFAULT 'Delhi'
);

After creation, the structure can be inspected:

DESCRIBE student;
DESC student;
SHOW TABLES;

DESCRIBE (or its short form DESC) lists each column, its data type, nullability, key status, and default value. SHOW TABLES lists all tables in the current database.

7. Constraints

Constraints enforce rules on the data and protect its integrity.

NOT NULL: The column cannot contain a NULL value.
UNIQUE: All values in the column must be different.
PRIMARY KEY: Uniquely identifies each row; a combination of NOT NULL and UNIQUE. Only one primary key is allowed per table.
FOREIGN KEY: A column in one table that refers to the primary key of another table, creating a relationship.
DEFAULT: Supplies a value when none is given.
CHECK: Validates that column values satisfy a condition.

CREATE TABLE fee (
    roll INT,
    amount DECIMAL(6, 2),
    FOREIGN KEY (roll) REFERENCES student(roll)
);

8. Inserting Records

INSERT INTO student (roll, name, marks) VALUES (101, 'Ravi', 88.5);
INSERT INTO student VALUES (102, 'Simran', 92.0, 'Mumbai');
INSERT INTO student (roll, name, marks, city) VALUES (103, 'Amit', 78.0, 'Kolkata');

The first form names the columns and then gives the values.
The second form omits the column list, so values must be supplied for every column in table order.
The DEFAULT value is used automatically if the column is omitted.

9. Updating Records

UPDATE student SET marks = 95 WHERE roll = 101;
UPDATE student SET marks = marks + 5 WHERE city = 'Mumbai';

UPDATE changes existing rows. The SET clause assigns new values and the WHERE clause decides which rows are affected. If WHERE is omitted, all rows are updated.

10. Deleting Records

DELETE FROM student WHERE roll = 103;
DELETE FROM student;

DELETE removes rows matching the WHERE condition. Without a WHERE clause it removes all rows but keeps the table structure.

11. Altering the Table Structure

ALTER TABLE modifies an existing table:

ALTER TABLE student ADD COLUMN phone VARCHAR(10);
ALTER TABLE student DROP COLUMN phone;
ALTER TABLE student MODIFY name VARCHAR(40);
ALTER TABLE student RENAME COLUMN marks TO score;
ALTER TABLE student RENAME TO student_new;

ADD COLUMN inserts a new column.
DROP COLUMN removes a column.
MODIFY changes the data type or constraints of a column.
RENAME COLUMN changes the name of a column.
RENAME TO renames the whole table.

12. Dropping a Table

DROP TABLE student;

DROP TABLE deletes the table together with all of its data and structure. Unlike DELETE, it cannot be undone and the table must be recreated.

13. The SELECT Statement

The SELECT command retrieves data from one or more tables:

SELECT * FROM student;
SELECT roll, name FROM student;
SELECT DISTINCT city FROM student;

* selects all columns.
Listing column names selects only those columns.
DISTINCT removes duplicate values from the result.
WHERE filters rows by a condition.
ORDER BY sorts the result.
LIMIT restricts the number of rows shown.

SELECT name, marks FROM student WHERE marks > 80 ORDER BY marks DESC;

Quick Revision Tables

Table 1: SQL Commands by Category

Command	Category	Purpose
CREATE	DDL	Create database or table
ALTER	DDL	Change table structure
DROP	DDL	Remove table or database
INSERT	DML	Add rows to a table
UPDATE	DML	Modify existing rows
DELETE	DML	Remove rows
SELECT	DML	Retrieve data
GRANT	DCL	Give access rights

Table 2: Common MySQL Data Types

Data Type	Use
INT	Whole numbers
CHAR(n)	Fixed-length string
VARCHAR(n)	Variable-length string
DECIMAL(p, s)	Exact decimal numbers
DATE	Date in YYYY-MM-DD format
FLOAT	Approximate decimal numbers

Mind Map

graph TD A["MySQL Revision Tour"] --> B["Database Concepts"] A --> C["Data Types"] A --> D["SQL Categories"] A --> E["Constraints"] A --> F["Commands"] B --> B1["DBMS vs RDBMS"] B --> B2["Table, tuple, attribute"] C --> C1["INT, CHAR, VARCHAR"] C --> C2["DECIMAL, DATE, FLOAT"] D --> D1["DDL: CREATE, ALTER, DROP"] D --> D2["DML: INSERT, UPDATE, DELETE, SELECT"] D --> D3["DCL: GRANT, REVOKE"] E --> E1["NOT NULL, UNIQUE"] E --> E2["PRIMARY KEY, FOREIGN KEY"] E --> E3["DEFAULT, CHECK"] F --> F1["CREATE, DESCRIBE"] F --> F2["INSERT, UPDATE, DELETE"] F --> F3["SELECT, ALTER, DROP"]

Important Diagrams (SVG)

Diagram 1: Structure of a Relational Table

Diagram 2: Foreign Key Relationship between Tables

Common Mistakes

Forgetting to use USE database; after creating the database, so commands give an error because no database is selected.
Using VARCHAR without a length like VARCHAR(name), which is invalid syntax; it must be VARCHAR(30).
Attempting to insert a NULL value into a PRIMARY KEY column, which fails because primary keys are NOT NULL.
Omitting the WHERE clause in an UPDATE or DELETE, which unintentionally affects all rows of the table.
Using INSERT with a column list that does not match the number of values, producing a column count mismatch error.
Confusing DELETE FROM with DROP TABLE; DELETE keeps the structure while DROP removes everything.
Writing ALTER TABLE ADD COLUMN without the word COLUMN in older MySQL versions, giving a syntax error.
Inserting a date in DD-MM-YYYY format, which MySQL rejects because it expects YYYY-MM-DD.
Using a reserved word such as date or order as a column name without backticks.

Exam Tips

Memorise the exact syntax of CREATE TABLE with its column definitions and constraints, as it is frequently asked.
Remember that DESC table and DESCRIBE table are identical commands.
Learn the difference between DELETE (removes rows) and DROP (removes the table structure as well).
Practise questions that ask which command is DDL and which is DML; classification is a favourite 1-mark question.
Know the length requirements of CHAR and VARCHAR and give correct examples in answers.
Be able to write UPDATE statements with a WHERE clause, and to predict the result when WHERE is absent.
Remember that a foreign key must match an existing primary key value for the insert to succeed.

Conclusion

MySQL Revision Tour consolidates the complete command vocabulary of the relational database system. Data is stored in related tables whose columns carry declared data types and constraints that enforce integrity. DDL commands like CREATE, ALTER, and DROP manage the structure of databases and tables, while DML commands such as INSERT, UPDATE, DELETE, and SELECT manage the data inside them. The primary key identifies rows uniquely and the foreign key builds relationships between tables. A clear grasp of this command set is the necessary foundation for the two following chapters, which add advanced querying techniques such as filtering, grouping, ordering, joins, and the rich library of SQL functions.

MySQL Revision Tour — Study Notes