Class 12 CBSE MySQL – Complete Notes
1. What is a Database?
A database is an organized collection of related data.
Example:
A school database stores information about students, teachers, classes, etc.
Create a Database
CREATE DATABASE School;
View Databases
SHOW DATABASES;
Select a Database
USE School;
Delete a Database
DROP DATABASE School;
2. Create a Table
A table stores data in rows and columns.
Syntax
CREATE TABLE table_name
(
column_name datatype constraints
);
Example
CREATE TABLE Student
(
RollNo INT PRIMARY KEY,
Name VARCHAR(30),
Class VARCHAR(10),
Marks INT,
City VARCHAR(20)
);
View Tables
SHOW TABLES;
View Table Structure
DESCRIBE Student;
or
DESC Student;
3. Data Types
Data Type Description
INT Integer values
FLOAT Decimal values
CHAR(n) Fixed-length string
VARCHAR( Variable-length
n) string
DATE Date values
TIME Time values
Example:
Age INT
Name VARCHAR(30)
4. Constraints
Constraints restrict the type of data entered.
PRIMARY KEY
Unique and cannot be NULL.
RollNo INT PRIMARY KEY
NOT NULL
Name VARCHAR(30) NOT NULL
UNIQUE
Email VARCHAR(50) UNIQUE
DEFAULT
City VARCHAR(20) DEFAULT 'Delhi'
5. Insert Records
Insert One Record
INSERT INTO Student
VALUES
(101,'Rahul','XII',88,'Delhi');
Insert Multiple Records
INSERT INTO Student
VALUES
(102,'Priya','XII',92,'Mumbai'),
(103,'Aman','XII',80,'Kolkata'),
(104,'Neha','XII',90,'Chennai');
6. Display Records (SELECT)
Display Everything
SELECT * FROM Student;
Display Particular Columns
SELECT Name,Marks
FROM Student;
Display Distinct Values
SELECT DISTINCT City
FROM Student;
7. WHERE Clause
Used to display selected records.
Equal
SELECT *
FROM Student
WHERE Marks=90;
Greater Than
SELECT *
FROM Student
WHERE Marks>80;
Less Than
SELECT *
FROM Student
WHERE Marks<80;
Not Equal
SELECT *
FROM Student
WHERE City!='Delhi';
8. Logical Operators
AND
SELECT *
FROM Student
WHERE Marks>80
AND City='Delhi';
OR
SELECT *
FROM Student
WHERE City='Delhi'
OR City='Mumbai';
NOT
SELECT *
FROM Student
WHERE NOT City='Delhi';
9. BETWEEN
SELECT *
FROM Student
WHERE Marks BETWEEN 80 AND 90;
10. IN Operator
SELECT *
FROM Student
WHERE City IN ('Delhi','Mumbai');
11. LIKE Operator
Starts with A
SELECT *
FROM Student
WHERE Name LIKE 'A%';
Ends with a
SELECT *
FROM Student
WHERE Name LIKE '%a';
Contains "an"
SELECT *
FROM Student
WHERE Name LIKE '%an%';
Second letter is a
SELECT *
FROM Student
WHERE Name LIKE '_a%';
12. ORDER BY
Ascending Order
SELECT *
FROM Student
ORDER BY Marks;
Descending Order
SELECT *
FROM Student
ORDER BY Marks DESC;
13. Aggregate Functions
COUNT
SELECT COUNT(*)
FROM Student;
SUM
SELECT SUM(Marks)
FROM Student;
AVG
SELECT AVG(Marks)
FROM Student;
MAX
SELECT MAX(Marks)
FROM Student;
MIN
SELECT MIN(Marks)
FROM Student;
14. GROUP BY
SELECT City,
COUNT(*)
FROM Student
GROUP BY City;
15. HAVING
SELECT City,
AVG(Marks)
FROM Student
GROUP BY City
HAVING AVG(Marks)>80;
16. UPDATE
UPDATE Student
SET Marks=95
WHERE RollNo=103;
Update Multiple Columns
UPDATE Student
SET Marks=95,
City='Delhi'
WHERE RollNo=103;
17. DELETE
Delete One Record
DELETE FROM Student
WHERE RollNo=103;
Delete All Records
DELETE FROM Student;
18. ALTER TABLE
Add Column
ALTER TABLE Student
ADD Phone VARCHAR(15);
Modify Column
ALTER TABLE Student
MODIFY Name VARCHAR(50);
Drop Column
ALTER TABLE Student
DROP COLUMN Phone;
Rename Column (MySQL 8.0+)
ALTER TABLE Student
RENAME COLUMN Name TO StudentName;
19. Rename Table
RENAME TABLE Student TO Students;
20. Remove Table
DROP TABLE Students;
21. SQL Clauses (CBSE)
Clause Purpose
WHERE Filters records
ORDER
Sorts records
BY
Clause Purpose
GROUP
Groups records
BY
HAVING Filters groups
Removes duplicate
DISTINCT
values
22. SQL Operators
Operat
Example
or
= Marks=90
> Marks>80
< Marks<80
>= Marks>=80
<= Marks<=80
!= or
Marks!=90
<>
Two conditions must be
AND
true
At least one condition is
OR
true
NOT Opposite condition
IN Matches a list
BETWEE
Range of values
N
LIKE Pattern matching
23. Complete Flow (Practical Exam)
CREATE DATABASE School;
USE School;
CREATE TABLE Student
(
RollNo INT PRIMARY KEY,
Name VARCHAR(30),
Class VARCHAR(10),
Marks INT,
City VARCHAR(20)
);
INSERT INTO Student
VALUES
(101,'Rahul','XII',88,'Delhi'),
(102,'Priya','XII',92,'Mumbai'),
(103,'Aman','XII',80,'Kolkata');
SELECT * FROM Student;
UPDATE Student
SET Marks=95
WHERE RollNo=103;
DELETE FROM Student
WHERE RollNo=102;
DESC Student;
SHOW TABLES;
DROP TABLE Student;
DROP DATABASE School;
These are the core MySQL topics and commands prescribed in the CBSE Class 12 Informatics
Practices (IP) and Computer Science (CS) syllabus, and they cover the questions most
commonly asked in practicals and board examinations.
Make the pdf file