📚 Database Management – Complete Explanation for CBSE 12th Board Exam
✅ 1. Database Concepts
• Database: A database is an organized collection of data that can be easily accessed,
managed, and updated.
• DBMS (Database Management System): A software system that allows users to create,
store, and retrieve data ef ciently.
• Need for Database:
◦ To avoid data redundancy (duplication of data).
◦ To maintain data consistency.
◦ To ensure data integrity (correctness and reliability).
◦ To allow concurrent access by multiple users.
◦ To enforce security constraints.
🔥 2. Relational Data Model
The Relational Model organizes data into tables (also called relations), which consist of rows and
columns.
Basic Terminology:
• Relation: A table in the database.
• Tuple: A row in the table, representing a single record.
• Attribute: A column in the table, representing a data eld.
• Domain: The set of permissible values for an attribute.
• Degree: The number of attributes (columns) in a table.
• Cardinality: The number of tuples (rows) in a table.
Keys:
• Candidate Key: A column or a combination of columns that can uniquely identify each
record. A table can have multiple candidate keys.
• Primary Key: The candidate key chosen to uniquely identify records. It cannot contain
NULL values.
• Alternate Key: The candidate keys that are not chosen as the primary key.
• Foreign Key: An attribute in one table that refers to the primary key of another table,
establishing a relationship between the two tables.
💻 3. Structured Query Language (SQL)
SQL is a standard language used to interact with relational databases.
SQL Commands:
SQL commands are divided into two categories:
1. DDL (Data De nition Language): De nes the structure of the database.
2. DML (Data Manipulation Language): Performs operations on the data.
1 of 17
fi
fi
fi
fi
⚙ DDL Commands:
1. CREATE DATABASE: Creates a new database.
sql
CREATE DATABASE school;
2. USE DATABASE: Switches to the speci ed database.
sql
USE school;
3. SHOW DATABASES: Displays all databases.
sql
SHOW DATABASES;
4. DROP DATABASE: Deletes a database.
sql
DROP DATABASE school;
5. SHOW TABLES: Displays all tables in the current database.
sql
SHOW TABLES;
6. CREATE TABLE: Creates a new table.
sql
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50),
age INT,
marks FLOAT
);
7. DESCRIBE TABLE: Displays the structure of the table.
sql
DESCRIBE students;
8. ALTER TABLE: Modi es the table structure.
• Add a new column:
sql
ALTER TABLE students ADD email VARCHAR(50);
• Remove a column:
sql
ALTER TABLE students DROP COLUMN email;
• Add a primary key:
sql
ALTER TABLE students ADD PRIMARY KEY (student_id);
• Remove a primary key:
sql
ALTER TABLE students DROP PRIMARY KEY;
9. DROP TABLE: Deletes the table and all its data.
Sql
DROP TABLE students;
2 of 17
fi
fi
🔥 DML Commands:
1. INSERT: Adds new records to the table.
sql
INSERT INTO students (student_id, name, age, marks)
VALUES (101, 'Alice', 18, 85.5);
2. DELETE: Removes records from the table.
sql
DELETE FROM students WHERE student_id = 101;
3. SELECT: Retrieves data from the table.
sql
SELECT * FROM students;
🔥 Operators in SQL:
• Mathematical: +, -, *, /, %
• Relational: =, !=, <, >, <=, >=
• Logical: AND, OR, NOT
🎯 Aliasing:
• Used to give a temporary name to a column or table.
sql
SELECT name AS StudentName FROM students;
🔥 Distinct Clause:
• Removes duplicate records.
sql
SELECT DISTINCT name FROM students;
🔥 Where Clause:
• Filters records based on a condition.
Sql
SELECT * FROM students WHERE age > 18;
🔥 IN and BETWEEN:
• IN: Matches values from a list.
sql
SELECT * FROM students WHERE age IN (18, 20, 22);
• BETWEEN: Selects values within a range.
sql
SELECT * FROM students WHERE marks BETWEEN 50 AND 80;
3 of 17
🔥 Order By:
• Sorts the results in ascending or descending order.
sql
SELECT * FROM students ORDER BY name ASC;
🔥 Meaning of NULL:
• NULL indicates missing or unknown data.
🔥 IS NULL and IS NOT NULL:
• IS NULL: Finds records with NULL values.
sql
SELECT * FROM students WHERE email IS NULL;
• IS NOT NULL: Finds records without NULL values.
sql
SELECT * FROM students WHERE email IS NOT NULL;
🔥 LIKE Clause:
• Used for pattern matching.
sql
SELECT * FROM students WHERE name LIKE 'A%'; -- Names
starting with A
SELECT * FROM students WHERE name LIKE '%n'; -- Names ending
with n
🔥 UPDATE Command:
• Modi es existing records.
sql
UPDATE students
SET marks = 90
WHERE student_id = 101;
🔥 Aggregate Functions:
• MAX: Returns the maximum value.
sql
SELECT MAX(marks) FROM students;
• MIN: Returns the minimum value.
sql
SELECT MIN(marks) FROM students;
4 of 17
fi
• AVG: Returns the average value.
sql
SELECT AVG(marks) FROM students;
• SUM: Returns the total value.
sql
SELECT SUM(marks) FROM students;
• COUNT: Returns the number of records.
sql
SELECT COUNT(*) FROM students;
🔥 GROUP BY and HAVING Clause:
• GROUP BY: Groups rows by a speci c column.
sql
SELECT age, COUNT(*)
FROM students
GROUP BY age;
• HAVING: Filters groups after aggregation.
sql
SELECT age, AVG(marks)
FROM students
GROUP BY age
HAVING AVG(marks) > 75;
🔥 Joins:
1. Cartesian Product: Combines all rows of one table with all rows of another.
sql
SELECT * FROM students, courses;
2. Equi-Join: Matches rows based on equality condition.
sql
SELECT [Link], courses.course_name
FROM students
JOIN courses ON students.student_id = courses.student_id;
3. Natural Join: Automatically joins tables with the same column name.
sql
SELECT * FROM students
NATURAL JOIN courses;
🐍 4. Python-SQL Connectivity
Python can connect to SQL databases using the mysql-connector-python library.
5 of 17
fi
Steps:
1. Install the library:
bash
CopyEdit
pip install mysql-connector-python
2. Connect to SQL:
python
CopyEdit
import [Link]
conn = [Link](
host="localhost",
user="root",
password="your_password",
database="school"
)
cursor = [Link]()
3. Perform SQL Operations:
python
# Insert record
[Link]("INSERT INTO students (name, age) VALUES
('Bob', 20)")
[Link]()
4. Fetching Data:
Python
[Link]("SELECT * FROM students")
rows = [Link]()
for row in rows:
print(row)
5. Closing the connection:
python
[Link]()
[Link]()
6 of 17
✅ Sample SQL Queries – CBSE 12th Board Exam
🔥 1. DDL (Data De nition Language) Queries
1⃣ Create a Database:
sql
CREATE DATABASE school;
2⃣ Use the Database:
sql
USE school;
3⃣ Create a Table:
sql
CREATE TABLE students (
student_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
marks FLOAT,
email VARCHAR(100) UNIQUE
);
4⃣ Describe the Table:
sql
DESCRIBE students;
5⃣ Alter the Table:
• Add a new column:
sql
ALTER TABLE students ADD phone_number VARCHAR(15);
• Remove a column:
sql
ALTER TABLE students DROP COLUMN phone_number;
• Add a Primary Key (if not already de ned):
sql
ALTER TABLE students ADD PRIMARY KEY (student_id);
6⃣ Drop the Table:
7 of 17
fi
fi
sql
DROP TABLE students;
🔥 2. DML (Data Manipulation Language) Queries
1⃣ Insert Records:
sql
INSERT INTO students (student_id, name, age, marks, email)
VALUES (101, 'Alice', 18, 85.5, 'alice@[Link]');
INSERT INTO students (student_id, name, age, marks, email)
VALUES (102, 'Bob', 19, 72.0, 'bob@[Link]');
2⃣ Select Records:
• Select all columns:
sql
SELECT * FROM students;
• Select speci c columns:
sql
SELECT name, marks FROM students;
• Select with alias:
sql
SELECT name AS "Student Name", marks AS "Score" FROM
students;
3⃣ Update Records:
sql
UPDATE students
SET marks = 90
WHERE student_id = 101;
4⃣ Delete Records:
sql
DELETE FROM students
WHERE student_id = 102;
🔥 3. Filtering and Sorting Queries
1⃣ Use the WHERE Clause:
8 of 17
fi
sql
SELECT * FROM students
WHERE marks > 80;
2⃣ Use IN and BETWEEN:
• IN: Check for multiple values.
sql
SELECT * FROM students
WHERE age IN (18, 20, 22);
• BETWEEN: Select a range.
sql
SELECT * FROM students
WHERE marks BETWEEN 70 AND 90;
3⃣ Sorting with ORDER BY:
• Ascending order:
sql
SELECT * FROM students
ORDER BY marks ASC;
• Descending order:
sql
SELECT * FROM students
ORDER BY name DESC;
🔥 4. Aggregate Functions and Grouping
1⃣ Use Aggregate Functions:
• Maximum marks:
sql
SELECT MAX(marks) FROM students;
• Minimum marks:
sql
SELECT MIN(marks) FROM students;
• Average marks:
sql
SELECT AVG(marks) FROM students;
• Sum of marks:
sql
SELECT SUM(marks) FROM students;
• Count of students:
sql
9 of 17
SELECT COUNT(*) FROM students;
2⃣ Grouping and Filtering:
• Group by age:
sql
SELECT age, COUNT(*)
FROM students
GROUP BY age;
• Using HAVING with aggregation:
sql
SELECT age, AVG(marks)
FROM students
GROUP BY age
HAVING AVG(marks) > 75;
🔥 5. Joins
1⃣ Cartesian Product (Cross Join):
• Combines all rows of two tables.
sql
SELECT *
FROM students, courses;
2⃣ Equi-Join (Inner Join):
• Matches rows based on a common column.
sql
SELECT [Link], courses.course_name
FROM students
JOIN courses ON students.student_id = courses.student_id;
3⃣ Natural Join:
• Automatically joins tables with the same column name.
sql
SELECT *
FROM students
NATURAL JOIN courses;
🔥 6. Null Handling
1⃣ Select rows with NULL values:
sql
SELECT * FROM students
10 of 17
WHERE email IS NULL;
2⃣ Select rows with non-NULL values:
sql
SELECT * FROM students
WHERE email IS NOT NULL;
🔥 7. Pattern Matching with LIKE
• Names starting with "A":
sql
SELECT * FROM students
WHERE name LIKE ‘A%';
• Names ending with "e":
sql
SELECT * FROM students
WHERE name LIKE ‘%e';
• Names containing "li":
sql
SELECT * FROM students
WHERE name LIKE '%li%';
🔥 8. SQL with Python Connectivity
Here’s how you can connect Python with SQL and run queries:
python
import [Link]
# Connect to the database
conn = [Link](
host="localhost",
user="root",
password="your_password",
database="school"
)
cursor = [Link]()
# Insert query
[Link]("INSERT INTO students (student_id, name, age,
marks) VALUES (103, 'Charlie', 20, 88.5)")
[Link]()
11 of 17
# Select query
[Link]("SELECT * FROM students")
rows = [Link]()
# Displaying records
for row in rows:
print(row)
# Closing the connection
[Link]()
[Link]()
✅ Practice SQL Questions for CBSE 12th Board Exam
Here are some important SQL practice questions based on the CBSE 12th syllabus to help you
prepare effectively.
🔥 1. DDL (Data De nition Language) Questions
Q1. Write an SQL query to create a table named employees with the following structure:
• emp_id (Integer, Primary Key)
• name (Varchar(50), Not Null)
12 of 17
fi
• age (Integer)
• salary (Float)
• department (Varchar(30))
👉 Try it before checking the solution!
✅ Solution:
sql
CREATE TABLE employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT,
salary FLOAT,
department VARCHAR(30)
);
Q2. Write an SQL query to alter the employees table and add a column email of type
VARCHAR(100).
✅ Solution:
sql
ALTER TABLE employees ADD email VARCHAR(100);
Q3. Write an SQL query to drop the department column from the employees table.
✅ Solution:
sql
ALTER TABLE employees DROP COLUMN department;
Q4. Write an SQL query to delete the employees table.
✅ Solution:
sql
DROP TABLE employees;
🔥 2. DML (Data Manipulation Language) Questions
Q5. Write an SQL query to insert the following records into the employees table:
emp_i nam ag
salary email
d e e
101 John 28 50000.0 john@[Link]
mary@[Link]
102 Mary 30 60000.0
m
103 Alex 25 45000.0 alex@[Link]
13 of 17
✅ Solution:
sql
INSERT INTO employees (emp_id, name, age, salary, email)
VALUES
(101, 'John', 28, 50000.0, 'john@[Link]'),
(102, 'Mary', 30, 60000.0, 'mary@[Link]'),
(103, 'Alex', 25, 45000.0, 'alex@[Link]');
Q6. Write an SQL query to update the salary of John to 55000.0.
✅ Solution:
sql
UPDATE employees
SET salary = 55000.0
WHERE name = 'John';
Q7. Write an SQL query to delete the record of Alex from the employees table.
✅ Solution:
sql
DELETE FROM employees
WHERE name = 'Alex';
🔥 3. Querying Data with SELECT
Q8. Write an SQL query to display all records from the employees table.
✅ Solution:
sql
SELECT * FROM employees;
Q9. Write an SQL query to display only name and salary of all employees.
✅ Solution:
sql
SELECT name, salary FROM employees;
Q10. Write an SQL query to list employees whose salary is greater than 50000.
✅ Solution:
sql
SELECT * FROM employees
WHERE salary > 50000;
14 of 17
🔥 4. Filtering Data
Q11. Write an SQL query to list employees whose age is between 25 and 30.
✅ Solution:
sql
SELECT * FROM employees
WHERE age BETWEEN 25 AND 30;
Q12. Write an SQL query to display employees working in "Sales" or "HR" department.
✅ Solution:
sql
SELECT * FROM employees
WHERE department IN ('Sales', 'HR');
Q13. Write an SQL query to display employees whose name starts with 'J'.
✅ Solution:
sql
SELECT * FROM employees
WHERE name LIKE 'J%';
Q14. Write an SQL query to display employees whose name contains 'a' in any position.
✅ Solution:
sql
SELECT * FROM employees
WHERE name LIKE '%a%';
🔥 5. Sorting Data
Q15. Write an SQL query to display all employees sorted by salary in descending order.
✅ Solution:
sql
SELECT * FROM employees
ORDER BY salary DESC;
🔥 6. Aggregate Functions
15 of 17
Q16. Write an SQL query to nd the highest salary in the employees table.
✅ Solution:
sql
SELECT MAX(salary) FROM employees;
Q17. Write an SQL query to nd the total salary of all employees.
✅ Solution:
sql
SELECT SUM(salary) FROM employees;
Q18. Write an SQL query to nd the average salary of employees.
✅ Solution:
sql
SELECT AVG(salary) FROM employees;
🔥 7. Grouping and Having Clause
Q19. Write an SQL query to count the number of employees in each department.
✅ Solution:
sql
SELECT department, COUNT(*)
FROM employees
GROUP BY department;
Q20. Write an SQL query to list departments where the average salary is greater than 50000.
✅ Solution:
sql
SELECT department, AVG(salary)
FROM employees
GROUP BY department
HAVING AVG(salary) > 50000;
🔥 8. Joins
Let's assume we have another table called departments:
dept_i department_nam
d e
1 HR
2 Sales
3 IT
16 of 17
fi
fi
fi
Q21. Write an SQL query to join the employees table with departments table on
department.
✅ Solution:
sql
SELECT [Link], [Link],
departments.department_name
FROM employees
JOIN departments
ON [Link] = departments.department_name;
🔥 9. Python-SQL Connectivity Questions
Q22. Write a Python program to connect to an SQL database and fetch all records from the
employees table.
✅ Solution:
python
import [Link]
# Connect to MySQL
conn = [Link](
host="localhost",
user="root",
password="your_password",
database="school"
)
cursor = [Link]()
# Execute SQL query
[Link]("SELECT * FROM employees")
# Fetch and display data
rows = [Link]()
for row in rows:
print(row)
# Close connection
[Link]()
[Link]()
17 of 17