MySQL Handbook - CBSE Class 12 Reference Report

Introduction to MySQL

What is MySQL Workbench?

MySQL Workbench is a visual tool for database architects, developers, and DBAs. It provides data modeling,
SQL development, and comprehensive administration tools for server configuration, user administration,
backup, and much more.

What is a Database Management System (DBMS)?

A Database Management System (DBMS) is software that interacts with end users, applications, and the
database itself to capture and analyze data. It allows for the creation, retrieval, updating, and management of
data in databases. If you know one DBMS, you can easily transition to another, as they share similar concepts
and functionalities.

Getting Started with MySQL

What is a Database?
A database is a container that stores related data in an organized way. In MySQL, a database holds one or more
tables.

Think of it like:

Folder analogy:
A database is like a folder

Each table is a file inside that folder

The rows in the table are like the content inside each file

Excel analogy:
A database is like an Excel workbook

Each table is a separate sheet inside that workbook

Each row in the table is like a row in Excel

Step 1: Create a Database

sql

CREATE DATABASE school;

After creating the database, use this SQL command to select it:
 sql

USE school;

Step 2: Create a Table

Now we'll create a simple students table:

sql

CREATE TABLE students (

roll_no INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
class VARCHAR(10) NOT NULL,
marks INT,
city VARCHAR(50)
);

Example: Insert Sample Data

sql

INSERT INTO students VALUES

(1, 'Rahul Kumar', '12-A', 85, 'Delhi'),
(2, 'Priya Sharma', '12-B', 92, 'Mumbai'),
(3, 'Amit Singh', '12-A', 78, 'Delhi'),
(4, 'Sneha Gupta', '12-C', 88, 'Bangalore'),
(5, 'Rohan Verma', '12-B', 95, 'Mumbai');

View the table:

sql

SELECT * FROM students;

Output:

roll_no name class marks city

1 Rahul Kumar 12-A 85 Delhi

2 Priya Sharma 12-B 92 Mumbai

3 Amit Singh 12-A 78 Delhi

4 Sneha Gupta 12-C 88 Bangalore

5 Rohan Verma 12-B 95 Mumbai

Step 3: Show Table Structure

sql

DESC students;

Step 4: Drop the Database

You can delete the entire database (and all its tables) using:

sql

DROP DATABASE school;

Be careful — this will delete everything in that database.

Data Types Explained

INT: Integer type, used for whole numbers

VARCHAR(100): Variable-length string, up to 100 characters

ENUM: A string object with a value chosen from a list of permitted values. eg. gender ENUM('Male',
'Female', 'Other')

DATE: Stores date values. eg date_of_birth DATE

TIMESTAMP: Stores date and time, automatically set to the current timestamp when a row is created

BOOLEAN: Stores TRUE or FALSE values, often used for flags like is_active

DECIMAL(10, 2): Stores exact numeric data values, useful for financial data. The first number is the total
number of digits, and the second is the number of digits after the decimal point

Constraints Explained
AUTO_INCREMENT: Automatically generates a unique number for each row

PRIMARY KEY: Uniquely identifies each row in the table

NOT NULL: Ensures a column cannot have a NULL value

UNIQUE: Ensures all values in a column are different

DEFAULT: Sets a default value for a column if no value is provided. eg. is_active BOOLEAN DEFAULT
TRUE

Working with Tables in MySQL

Selecting Data from a Table

Select All Columns

sql

SELECT * FROM students;

This fetches every column and every row from the students table.

Select Specific Columns

sql

SELECT name, marks FROM students;

Output:

name marks

Rahul Kumar 85

Priya Sharma 92

Amit Singh 78

Sneha Gupta 88

Rohan Verma 95

Altering a Table
You can use ALTER TABLE to modify an existing table.

Add a Column

sql

ALTER TABLE students ADD COLUMN email VARCHAR(100);

Drop a Column

sql

ALTER TABLE students DROP COLUMN email;

Modify a Column Type

sql

ALTER TABLE students MODIFY COLUMN city VARCHAR(100);

Inserting Data into MySQL Tables
To add data into a table, we use the INSERT INTO statement.

Insert by Specifying Column Names (Best Practice)

sql

INSERT INTO students (roll_no, name, class, marks, city) VALUES

(6, 'Kavita Reddy', '12-A', 91, 'Hyderabad');

Insert Multiple Rows at Once

sql

INSERT INTO students (roll_no, name, class, marks, city) VALUES

(7, 'Arjun Mehta', '12-B', 82, 'Pune'),
(8, 'Neha Patel', '12-C', 89, 'Ahmedabad'),
(9, 'Vikram Joshi', '12-A', 76, 'Delhi');

This is more efficient than inserting rows one by one.

Querying Data in MySQL using SELECT

The SELECT statement is used to query data from a table.

Basic Syntax

sql

SELECT column1, column2 FROM table_name;

Filtering Rows with WHERE

Equal To

sql

SELECT * FROM students WHERE city = 'Delhi';

Output:
 roll_no name class marks city

1 Rahul Kumar 12-A 85 Delhi

3 Amit Singh 12-A 78 Delhi

9 Vikram Joshi 12-A 76 Delhi

Not Equal To

sql

SELECT * FROM students WHERE class != '12-A';

Greater Than / Less Than

sql

SELECT * FROM students WHERE marks > 85;

Output:

roll_no name class marks city

2 Priya Sharma 12-B 92 Mumbai

4 Sneha Gupta 12-C 88 Bangalore

5 Rohan Verma 12-B 95 Mumbai

6 Kavita Reddy 12-A 91 Hyderabad

8 Neha Patel 12-C 89 Ahmedabad

Working with NULL

Create example with NULL values:

sql
 CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
salary INT,
department VARCHAR(50)
);

INSERT INTO employees VALUES

(101, 'Rajesh Kumar', 50000, 'Sales'),
(102, 'Anjali Mehta', NULL, 'Marketing'),
(103, 'Suresh Verma', 60000, 'IT'),
(104, 'Pooja Singh', NULL, 'HR');

IS NULL

sql

SELECT * FROM employees WHERE salary IS NULL;

Output:

emp_id name salary department

102 Anjali Mehta NULL Marketing

104 Pooja Singh NULL HR

IS NOT NULL

sql

SELECT * FROM employees WHERE salary IS NOT NULL;

Output:

emp_id name salary department

101 Rajesh Kumar 50000 Sales

103 Suresh Verma 60000 IT

BETWEEN

sql

SELECT * FROM students WHERE marks BETWEEN 80 AND 90;

Output:
 roll_no name class marks city

1 Rahul Kumar 12-A 85 Delhi

4 Sneha Gupta 12-C 88 Bangalore

7 Arjun Mehta 12-B 82 Pune

8 Neha Patel 12-C 89 Ahmedabad

IN

sql

SELECT * FROM students WHERE city IN ('Delhi', 'Mumbai');

Output:

roll_no name class marks city

1 Rahul Kumar 12-A 85 Delhi

2 Priya Sharma 12-B 92 Mumbai

3 Amit Singh 12-A 78 Delhi

5 Rohan Verma 12-B 95 Mumbai

9 Vikram Joshi 12-A 76 Delhi

LIKE (Pattern Matching)

sql

SELECT * FROM students WHERE name LIKE 'R%'; -- Starts with R

Output:

roll_no name class marks city

1 Rahul Kumar 12-A 85 Delhi

5 Rohan Verma 12-B 95 Mumbai

sql

SELECT * FROM students WHERE name LIKE '%a'; -- Ends with a

SELECT * FROM students WHERE name LIKE '%ar%'; -- Contains 'ar'

ORDER BY

sql
 SELECT * FROM students ORDER BY marks DESC;

Output:

roll_no name class marks city

5 Rohan Verma 12-B 95 Mumbai

2 Priya Sharma 12-B 92 Mumbai

6 Kavita Reddy 12-A 91 Hyderabad

8 Neha Patel 12-C 89 Ahmedabad

4 Sneha Gupta 12-C 88 Bangalore

1 Rahul Kumar 12-A 85 Delhi

7 Arjun Mehta 12-B 82 Pune

3 Amit Singh 12-A 78 Delhi

9 Vikram Joshi 12-A 76 Delhi

DISTINCT Keyword

sql

SELECT DISTINCT city FROM students;

Output:

city

Delhi

Mumbai

Bangalore

Hyderabad

Pune

Ahmedabad

UPDATE - Modifying Existing Data

The UPDATE statement is used to change values in one or more rows.

Basic Syntax

sql
 UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example: Update One Column

sql

UPDATE students
SET marks = 90
WHERE roll_no = 3;

Verify the update:

sql

SELECT * FROM students WHERE roll_no = 3;

Output:

roll_no name class marks city

3 Amit Singh 12-A 90 Delhi

Example: Update Multiple Columns

sql

UPDATE students
SET marks = 94, city = 'Chennai'
WHERE roll_no = 4;

Without WHERE Clause (Warning)

sql

UPDATE students
SET class = '12-A';

This updates every row in the table. Be very careful when omitting the WHERE clause.

DELETE - Removing Data from a Table

The DELETE statement removes rows from a table.
Basic Syntax

sql

DELETE FROM table_name

WHERE condition;

Example: Delete One Row

sql

DELETE FROM students

WHERE roll_no = 9;

Example: Delete Multiple Rows

sql

DELETE FROM students

WHERE marks < 80;

Best Practices
Always use WHERE unless you're intentionally deleting everything

Consider running a SELECT with the same WHERE clause first to confirm what will be affected:

sql

SELECT * FROM students WHERE roll_no = 9;

Always back up important data before performing destructive operations

Understanding Keys in MySQL

Primary Key
A PRIMARY KEY is a constraint in SQL that uniquely identifies each row in a table.

A PRIMARY KEY:

Must be unique

Cannot be NULL

Is used to identify rows in a table

Each table can have only one primary key

Example:

sql

CREATE TABLE books (

book_id INT PRIMARY KEY,
title VARCHAR(100),
author VARCHAR(50),
price DECIMAL(10,2)
);

INSERT INTO books VALUES

(1, 'Python Programming', 'John Smith', 450.00),
(2, 'Data Science Basics', 'Sarah Johnson', 550.00),
(3, 'Web Development', 'Mike Brown', 500.00);

Candidate Key, Primary Key, and Alternate Key

Candidate Key: Any column or combination of columns that can uniquely identify a record. A table can have
multiple candidate keys.

Primary Key: The main key chosen from candidate keys to uniquely identify records in a table.

Alternate Key: Any candidate key that is not chosen as the primary key.

Example:

sql

CREATE TABLE users (

user_id INT PRIMARY KEY, -- Primary Key
email VARCHAR(100) UNIQUE, -- Alternate Key (also a Candidate Key)
phone VARCHAR(15) UNIQUE, -- Alternate Key (also a Candidate Key)
name VARCHAR(100)
);

INSERT INTO users VALUES

(1, 'rahul@[Link]', '9876543210', 'Rahul Kumar'),
(2, 'priya@[Link]', '9876543211', 'Priya Sharma'),
(3, 'amit@[Link]', '9876543212', 'Amit Singh');

In this example:

user_id is the Primary Key

email and phone are Candidate Keys (both can uniquely identify users)

email and phone are also Alternate Keys (since user_id was chosen as Primary Key)
Foreign Keys in MySQL
A foreign key is a column that creates a link between two tables. It ensures that the value in one table must
match a value in another table.

Example: Students and Library Books

Create Students Table:

sql

CREATE TABLE students (

student_id INT PRIMARY KEY,
name VARCHAR(50),
class VARCHAR(10)
);

INSERT INTO students VALUES

(1, 'Rahul Kumar', '12-A'),
(2, 'Priya Sharma', '12-B'),
(3, 'Amit Singh', '12-A');

Create Library Table with Foreign Key:

sql

CREATE TABLE library_books (

book_id INT PRIMARY KEY,
book_name VARCHAR(100),
student_id INT,
issue_date DATE,
FOREIGN KEY (student_id) REFERENCES students(student_id)
);

INSERT INTO library_books VALUES

(101, 'Python Programming', 1, '2024-01-15'),
(102, 'Data Science', 2, '2024-01-16'),
(103, 'Web Development', 1, '2024-01-17');

View the data:

sql

SELECT * FROM library_books;

Output:

book_id book_name student_id issue_date

101 Python Programming 1 2024-01-15

102 Data Science 2 2024-01-16

103 Web Development 1 2024-01-17

Explanation:

student_id in library_books is a foreign key

It references the student_id column in the students table

This ensures that every book must be linked to a valid student

Adding a Foreign Key Later (Using ALTER)

sql

ALTER TABLE library_books

ADD CONSTRAINT fk_student FOREIGN KEY (student_id) REFERENCES students(student_id);

SQL Aggregate Functions

SQL functions help you analyze, transform, or summarize data in your tables.

Example Table Setup

sql

CREATE TABLE sales (

sale_id INT PRIMARY KEY,
product VARCHAR(50),
quantity INT,
price DECIMAL(10,2),
sale_date DATE
);

INSERT INTO sales VALUES

(1, 'Laptop', 2, 45000.00, '2024-01-10'),
(2, 'Mouse', 5, 500.00, '2024-01-11'),
(3, 'Keyboard', 3, 1500.00, '2024-01-12'),
(4, 'Laptop', 1, 45000.00, '2024-01-13'),
(5, 'Mouse', 10, 500.00, '2024-01-14');
COUNT()

Count total number of sales:

sql

SELECT COUNT(*) AS total_sales FROM sales;

Output:

total_sales

Count sales of specific product:

sql

SELECT COUNT(*) AS laptop_sales FROM sales WHERE product = 'Laptop';

Output:

laptop_sales

SUM()
Calculate total quantity sold:

sql

SELECT SUM(quantity) AS total_quantity FROM sales;

Output:

total_quantity

21

Calculate total revenue:

sql

SELECT SUM(quantity * price) AS total_revenue FROM sales;

Output:
 total_revenue

99500.00

AVG()
Find average price:

sql

SELECT AVG(price) AS average_price FROM sales;

Output:

average_price

18500.00

MIN() and MAX()

Get minimum and maximum prices:

sql

SELECT MIN(price) AS min_price, MAX(price) AS max_price FROM sales;

Output:

min_price max_price

500.00 45000.00

Summary Table
Function Purpose

COUNT() Count rows

SUM() Total of a column

AVG() Average of values

MIN() / MAX() Lowest / highest value

GROUP BY and HAVING in MySQL

The GROUP BY clause is used to group rows that have the same values in specified columns. It is typically
used with aggregate functions.

The HAVING clause is used to filter groups after aggregation.

Example: Product-wise Sales Analysis
Using the sales table created above:

GROUP BY Example: Total Quantity by Product

sql

SELECT product, SUM(quantity) AS total_quantity

FROM sales
GROUP BY product;

Output:

product total_quantity

Laptop 3

Mouse 15

Keyboard 3

GROUP BY with COUNT

sql

SELECT product, COUNT(*) AS number_of_sales

FROM sales
GROUP BY product;

Output:

product number_of_sales

Laptop 2

Mouse 2

Keyboard 1

HAVING Clause: Filtering Groups

Show only products with total quantity greater than 5:

sql

SELECT product, SUM(quantity) AS total_quantity

FROM sales
GROUP BY product
HAVING SUM(quantity) > 5;
Output:

product total_quantity

Mouse 15

Example: Class-wise Student Analysis

Using the students table:

Average marks by class:

sql

SELECT class, AVG(marks) AS average_marks

FROM students
GROUP BY class;

Output:

class average_marks

12-A 83.00

12-B 89.67

12-C 88.50

Classes with average marks above 85:

sql

SELECT class, AVG(marks) AS average_marks

FROM students
GROUP BY class
HAVING AVG(marks) > 85;

Output:

class average_marks

12-B 89.67

12-C 88.50

Why not WHERE?

WHERE is used before grouping (filters individual rows)

HAVING is used after groups are formed (filters groups based on aggregate values)
Summary

Clause Purpose Can use aggregates?

WHERE Filters rows before grouping No

GROUP BY Groups rows based on column values N/A

HAVING Filters groups after aggregation Yes

SQL JOINs in MySQL

In SQL, JOINs are used to combine rows from two or more tables based on related columns.

Example Tables Setup

Students Table:

sql

CREATE TABLE students (

student_id INT PRIMARY KEY,
name VARCHAR(50),
class VARCHAR(10)
);

INSERT INTO students VALUES

(1, 'Rahul Kumar', '12-A'),
(2, 'Priya Sharma', '12-B'),
(3, 'Amit Singh', '12-A');

Marks Table:

sql

CREATE TABLE marks (

mark_id INT PRIMARY KEY,
student_id INT,
subject VARCHAR(50),
marks INT
);

INSERT INTO marks VALUES

(1, 1, 'Mathematics', 85),
(2, 2, 'Mathematics', 92),
(3, 4, 'Mathematics', 78); -- student_id 4 doesn't exist in students table
1. INNER JOIN (Equi-Join)

Returns only the matching rows from both tables.

sql

SELECT [Link], [Link], [Link]

FROM students
INNER JOIN marks ON students.student_id = marks.student_id;

Output:

name subject marks

Rahul Kumar Mathematics 85

Priya Sharma Mathematics 92

Note: Amit Singh is excluded because he has no matching marks. The mark for student_id 4 is excluded
because that student doesn't exist.

2. LEFT JOIN
Returns all rows from the left table (students), and matching rows from the right table (marks). If no match is
found, NULLs are returned.

sql

SELECT [Link], [Link], [Link]

FROM students
LEFT JOIN marks ON students.student_id = marks.student_id;

Output:

name subject marks

Rahul Kumar Mathematics 85

Priya Sharma Mathematics 92

Amit Singh NULL NULL

Note: Amit Singh is shown even though he has no marks recorded.

3. RIGHT JOIN
Returns all rows from the right table (marks), and matching rows from the left table (students). If no match is
found, NULLs are returned.

sql
 SELECT [Link], [Link], [Link]
FROM students
RIGHT JOIN marks ON students.student_id = marks.student_id;

Output:

name subject marks

Rahul Kumar Mathematics 85

Priya Sharma Mathematics 92

NULL Mathematics 78

Note: The mark for student_id 4 is shown even though that student doesn't exist in the students table.

4. Cartesian Product (Cross Join)

Returns all possible combinations of rows from both tables.

sql

SELECT [Link], [Link]

FROM students, marks;

Or explicitly:

sql

SELECT [Link], [Link]

FROM students
CROSS JOIN marks;

Output:

name subject

Rahul Kumar Mathematics

Rahul Kumar Mathematics

Rahul Kumar Mathematics

Priya Sharma Mathematics

Priya Sharma Mathematics

Priya Sharma Mathematics

Amit Singh Mathematics

Amit Singh Mathematics

Amit Singh Mathematics

Total rows = 3 students × 3 marks = 9 rows

Practical Example: Complete Student Report

Create more comprehensive tables:

sql

CREATE TABLE student_info (

student_id INT PRIMARY KEY,
name VARCHAR(50),
class VARCHAR(10),
city VARCHAR(50)
);

CREATE TABLE student_marks (

student_id INT,
subject VARCHAR(50),
marks INT,
FOREIGN KEY (student_id) REFERENCES student_info(student_id)
);

INSERT INTO student_info VALUES

(101, 'Rahul Kumar', '12-A', 'Delhi'),
(102, 'Priya Sharma', '12-B', 'Mumbai'),
(103, 'Amit Singh', '12-A', 'Delhi');

INSERT INTO student_marks VALUES

(101, 'Physics', 85),
(101, 'Chemistry', 88),
(102, 'Physics', 92),
(102, 'Chemistry', 90);

Get complete report with student details and marks:

sql

SELECT
student_info.name,
student_info.class,
student_info.city,
student_marks.subject,
student_marks.marks
FROM student_info
LEFT JOIN student_marks ON student_info.student_id = student_marks.student_id
ORDER BY student_info.name, student_marks.subject;
Output:

name class city subject marks

Amit Singh 12-A Delhi NULL NULL

Priya Sharma 12-B Mumbai Chemistry 90

Priya Sharma 12-B Mumbai Physics 92

Rahul Kumar 12-A Delhi Chemistry 88

Rahul Kumar 12-A Delhi Physics 85

Summary Table
JOIN Type Description

INNER JOIN / Equi-Join Only matching rows from both tables

LEFT JOIN All rows from left table + matching from right

RIGHT JOIN All rows from right table + matching from left

Cartesian Product All possible combinations of rows

Interface of Python with SQL Database

What is Python-MySQL Integration?

Python-MySQL integration means connecting your Python program with a MySQL database so that you can:

Store data from Python into database

Retrieve data from database to Python

Update or delete data using Python code

Why Do We Need It?

To create applications that store permanent data

To make user-friendly programs with database backend

To automate database operations

Basic Steps to Connect Python with MySQL

Step 1: Install the Connector

bash

pip install mysql-connector-python

Step 2: Import the Module

python

import [Link]

Step 3: Create Connection

python

mydb = [Link](
host="localhost",
user="root",
password="your_password",
database="school"
)

Step 4: Create Cursor

python

mycursor = [Link]()

Step 5: Execute SQL Commands

python

[Link]("SELECT * FROM students")

Step 6: Close Connection

python

[Link]()
[Link]()

Understanding Key Concepts

1. What is a Cursor?

A cursor is like a pointer that helps you execute SQL commands and fetch results.

python
 mycursor = [Link]() # Create cursor
[Link]("SELECT * FROM students") # Use cursor to run query

2. What is commit()?

commit() saves your changes to the database. Use it after INSERT, UPDATE, or DELETE.

python

[Link]("INSERT INTO students VALUES (1, 'Rahul', 85, 'Delhi')")

[Link]() # This saves the data permanently

3. What is fetchone()?

fetchone() gets only ONE record from the result.

python

[Link]("SELECT * FROM students")

result = [Link]()
print(result) # Shows first student only

4. What is fetchall()?

fetchall() gets ALL records from the result.

python

[Link]("SELECT * FROM students")

result = [Link]()
for row in result:
print(row) # Shows all students

5. What is rowcount?

rowcount tells you how many rows were affected.

python

[Link]("SELECT * FROM students WHERE marks > 80")

print("Number of students:", [Link])

Simple Examples

Example 1: Connect to Database

 python

import [Link]

# Connect to database
mydb = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)

print("Connected to database successfully!")

# Close connection
[Link]()

Example 2: Insert One Record

python

import [Link]

# Connect
mydb = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = [Link]()

# Insert data
sql = "INSERT INTO students VALUES (%s, %s, %s, %s)"
val = (1, "Rahul", 85, "Delhi")

[Link](sql, val)
[Link]()

print("Record inserted!")

# Close
[Link]()
[Link]()
Important: Always use %s for values, not direct values. This is safer!

Example 3: Insert Multiple Records

python

import [Link]

# Connect
mydb = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = [Link]()

# Insert multiple records

sql = "INSERT INTO students VALUES (%s, %s, %s, %s)"
val = [
(2, "Priya", 92, "Mumbai"),
(3, "Amit", 78, "Delhi"),
(4, "Sneha", 88, "Bangalore")
]

[Link](sql, val)
[Link]()

print([Link], "records inserted!")

# Close
[Link]()
[Link]()

Example 4: Display All Records (fetchall)

python
 import [Link]

# Connect
mydb = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = [Link]()

# Fetch all records

[Link]("SELECT * FROM students")
result = [Link]()

print("All Students:")
for row in result:
print(row)

# Close
[Link]()
[Link]()

Output:

All Students:
(1, 'Rahul', 85, 'Delhi')
(2, 'Priya', 92, 'Mumbai')
(3, 'Amit', 78, 'Delhi')
(4, 'Sneha', 88, 'Bangalore')

Example 5: Display One Record (fetchone)

python
 import [Link]

# Connect
mydb = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = [Link]()

# Fetch first record only

[Link]("SELECT * FROM students")
result = [Link]()

print("First Student:")
print(result)

# Close
[Link]()
[Link]()

Output:

First Student:
(1, 'Rahul', 85, 'Delhi')

Example 6: Display with WHERE Clause

python
 import [Link]

# Connect
mydb = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = [Link]()

# Fetch students with marks > 80

[Link]("SELECT * FROM students WHERE marks > 80")
result = [Link]()

print("Students with marks > 80:")

for row in result:
print(row)

print("\nTotal:", [Link], "students")

# Close
[Link]()
[Link]()

Output:

Students with marks > 80:

(1, 'Rahul', 85, 'Delhi')
(2, 'Priya', 92, 'Mumbai')
(4, 'Sneha', 88, 'Bangalore')

Total: 3 students

Example 7: Update Records

python
 import [Link]

# Connect
mydb = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = [Link]()

# Update marks
sql = "UPDATE students SET marks = %s WHERE roll_no = %s"
val = (95, 1)

[Link](sql, val)
[Link]()

print([Link], "record updated!")

# Close
[Link]()
[Link]()

Output:

1 record updated!

Example 8: Delete Records

python
 import [Link]

# Connect
mydb = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = [Link]()

# Delete record
sql = "DELETE FROM students WHERE roll_no = %s"
val = (4,)

[Link](sql, val)
[Link]()

print([Link], "record deleted!")

# Close
[Link]()
[Link]()

Output:

1 record deleted!

Example 9: Search Student by Roll Number

python
 import [Link]

# Connect
mydb = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = [Link]()

# Input from user

roll = int(input("Enter roll number: "))

# Search
sql = "SELECT * FROM students WHERE roll_no = %s"
[Link](sql, (roll,))
result = [Link]()

if result:
print("Roll No:", result[0])
print("Name:", result[1])
print("Marks:", result[2])
print("City:", result[3])
else:
print("Student not found!")

# Close
[Link]()
[Link]()

Example 10: Simple Menu Program

python
import [Link]

# Connect to database
mydb = [Link](
host="localhost",
user="root",
password="1234",
database="school"
)
mycursor = [Link]()

while True:
print("\n--- Student Database ---")
print("1. Add Student")
print("2. View All Students")
print("3. Exit")

choice = input("Enter choice: ")

if choice == '1':
# Add student
roll = int(input("Roll No: "))
name = input("Name: ")
marks = int(input("Marks: "))
city = input("City: ")

sql = "INSERT INTO students VALUES (%s, %s, %s, %s)"

val = (roll, name, marks, city)
[Link](sql, val)
[Link]()
print("Student added!")

elif choice == '2':

# View all
[Link]("SELECT * FROM students")
result = [Link]()

print("\nRoll\tName\tMarks\tCity")
print("-" * 40)
for row in result:
print(row[0], "\t", row[1], "\t", row[2], "\t", row[3])

elif choice == '3':

print("Goodbye!")
break
else:
 print("Invalid choice!")

# Close
[Link]()
[Link]()

Important Points to Remember

1. Always use commit() after changes

python

[Link]("INSERT INTO students VALUES (1, 'Rahul', 85, 'Delhi')")

[Link]() # Don't forget this!

2. Use %s for values (Safe method)

python

# Correct way
sql = "SELECT * FROM students WHERE roll_no = %s"
[Link](sql, (1,))

# Wrong way (Don't do this!)

sql = "SELECT * FROM students WHERE roll_no = 1"

3. Close connections when done

python

[Link]()
[Link]()

4. Difference between fetchone() and fetchall()

fetchone() = Get only 1 record

fetchall() = Get all records

5. Use rowcount to count records

python

[Link]("SELECT * FROM students")

print("Total students:", [Link])
Common Mistakes to Avoid
❌ Mistake 1: Forgetting commit()

python

[Link]("INSERT INTO students VALUES (1, 'Rahul', 85, 'Delhi')")

# Forgot [Link]() - Data will not be saved!

✅ Correct:

python

[Link]("INSERT INTO students VALUES (1, 'Rahul', 85, 'Delhi')")

[Link]() # Now data is saved

❌ Mistake 2: Not using %s

python

roll = 1
sql = "SELECT * FROM students WHERE roll_no = " + roll # Wrong!

✅ Correct:

python

roll = 1
sql = "SELECT * FROM students WHERE roll_no = %s"
[Link](sql, (roll,)) # Correct way

❌ Mistake 3: Forgetting tuple for single value

python

[Link](sql, 1) # Wrong!

✅ Correct:

python

[Link](sql, (1,)) # Note the comma - makes it a tuple

Important SQL Commands Summary for CBSE Class 12

Data Definition Language (DDL)

CREATE DATABASE database_name - Create a new database

USE database_name - Select database to work with

SHOW DATABASES - List all databases

DROP DATABASE database_name - Delete a database

SHOW TABLES - List all tables in current database

CREATE TABLE table_name (columns...) - Create a new table

DESC table_name - Show table structure

ALTER TABLE table_name ADD COLUMN column_name datatype - Add column

ALTER TABLE table_name MODIFY COLUMN column_name datatype - Modify column

ALTER TABLE table_name DROP COLUMN column_name - Remove column

DROP TABLE table_name - Delete a table

Data Manipulation Language (DML)

INSERT INTO table_name VALUES (...) - Add new records

UPDATE table_name SET column = value WHERE condition - Modify records

DELETE FROM table_name WHERE condition - Remove records

Data Query Language (DQL)

SELECT columns FROM table_name - Retrieve data from tables

SELECT * FROM table_name - Select all columns

SELECT DISTINCT column FROM table_name - Select unique values

Key Operators and Clauses

WHERE - Filter records based on conditions

IN (value1, value2, ...) - Match any value in a list

BETWEEN value1 AND value2 - Range of values

LIKE 'pattern' - Pattern matching

% - Any sequence of characters

_ - Single character
 ORDER BY column ASC|DESC - Sort results

IS NULL - Check for null values

IS NOT NULL - Check for non-null values

Aggregate Functions
COUNT(*) or COUNT(column) - Count rows

SUM(column) - Sum of values

AVG(column) - Average of values

MAX(column) - Maximum value

MIN(column) - Minimum value

Grouping and Filtering

GROUP BY column - Group rows with same values

HAVING condition - Filter grouped data (used with GROUP BY)

Joins
INNER JOIN or EQUI-JOIN - Matching records from both tables

LEFT JOIN - All from left table + matches from right

RIGHT JOIN - All from right table + matches from left

Cartesian Product - All combinations (using comma or CROSS JOIN)

Keys
Primary Key - Uniquely identifies each row (unique, not null)

Candidate Key - Any column(s) that can uniquely identify a row

Alternate Key - Candidate keys not chosen as primary key

Foreign Key - Links two tables together

Python-MySQL Integration
[Link]() - Establish connection

cursor() - Create cursor object

execute(sql, values) - Run SQL query

executemany(sql, list_of_values) - Run query multiple times

fetchone() - Fetch single record (returns tuple)

 fetchall() - Fetch all records (returns list of tuples)

rowcount - Number of affected/returned rows

commit() - Save changes to database

close() - Close cursor or connection

Practice Questions for CBSE Class 12

Section A: Basic SQL Commands

1. Write SQL command to create a database named "LIBRARY".

2. Write SQL command to create a table BOOKS with columns: Book_ID (Primary Key), Title, Author, Price,
Publisher.

3. Write SQL command to add a column "Edition" to the BOOKS table.

4. Write SQL command to modify the Price column to DECIMAL(10,2) in BOOKS table.

5. Write SQL command to display structure of BOOKS table.

Section B: DML Commands

6. Insert 5 records into the BOOKS table.

7. Update the price of all books by author "R.K. Narayan" to 350.

8. Delete all books with price less than 200.

9. Display all books published by "Penguin".

10. Display book titles starting with letter 'T'.

Section C: Aggregate Functions and GROUP BY

11. Find the total number of books in the library.

12. Calculate the average price of all books.

13. Find the maximum and minimum price of books.

14. Display author-wise count of books.

15. Show authors who have written more than 2 books.

Section D: Joins
16. Create two tables: STUDENTS (Student_ID, Name, Class) and MARKS (Student_ID, Subject, Marks).
Write a query to display student names with their marks.

17. Display all students even if they don't have marks recorded (use LEFT JOIN).
18. Display all marks records even if student doesn't exist (use RIGHT JOIN).

Section E: Python-MySQL Integration

19. Write a Python program to connect to a MySQL database and create a table EMPLOYEES with columns:
Emp_ID, Name, Salary, Department.

20. Write a Python function to insert employee records into the EMPLOYEES table.

21. Write a Python program to display all employees with salary greater than 50000 using fetchall().

22. Write a Python program to search for an employee by Emp_ID using fetchone().

23. Write a Python program to update the salary of an employee.

24. Write a Python program to delete an employee record and display the number of records deleted using
rowcount.

25. Write a complete menu-driven Python program for employee management system with options to add,
display, search, update, and delete employee records.

Quick Reference Card

Common SQL Query Patterns

Select with condition:

sql

SELECT * FROM table_name WHERE condition;

Select with multiple conditions:

sql

SELECT * FROM table_name WHERE condition1 AND condition2;

SELECT * FROM table_name WHERE condition1 OR condition2;

Pattern matching:

sql

SELECT * FROM table_name WHERE column LIKE 'A%'; -- Starts with A

SELECT * FROM table_name WHERE column LIKE '%ing'; -- Ends with ing
SELECT * FROM table_name WHERE column LIKE '%test%'; -- Contains test

Sorting:
 sql

SELECT * FROM table_name ORDER BY column ASC; -- Ascending

SELECT * FROM table_name ORDER BY column DESC; -- Descending

Grouping with aggregate:

sql

SELECT column, COUNT(*) FROM table_name GROUP BY column;

SELECT column, AVG(value) FROM table_name GROUP BY column HAVING AVG(value) > 50;

Join pattern:

sql

SELECT t1.col1, t2.col2

FROM table1 t1
INNER JOIN table2 t2 ON [Link] = [Link];

Python-MySQL Pattern

python
 import [Link]

# Connect
mydb = [Link](
host="localhost",
user="username",
password="password",
database="dbname"
)
mycursor = [Link]()

# Execute query
[Link]("SELECT * FROM table_name")
result = [Link]()

# Display results
for row in result:
print(row)

# For INSERT, UPDATE, DELETE - use commit()

[Link]()

# Close
[Link]()
[Link]()

Conclusion
This handbook covers all the essential SQL topics required for CBSE Class 12 Computer Science:

Database and table creation

Data manipulation (INSERT, UPDATE, DELETE)

Data querying with various conditions

Aggregate functions and grouping

Different types of joins

Understanding of keys (Primary, Candidate, Alternate, Foreign)

Python-MySQL database connectivity

Complete working examples with sample data

Practice these concepts with real examples, write Python programs to interact with databases, and you'll be
well-prepared for your CBSE Class 12 exams!

Remember: Always test your SQL queries and Python programs multiple times to understand how they work.
Good luck with your studies!