Class XII Computer Science - MySQL & SQL Queries Page 1
Table of Contents
1. Introduction to Database Management Systems
2. Relational Data Model Concepts
3. Introduction to MySQL
4. Data Types in MySQL
5. SQL Commands - DDL, DML, DCL, TCL
6. Creating and Managing Tables
7. Data Query Language (SELECT)
8. Operators and Expressions
9. MySQL Functions (Scalar & Aggregate)
10. Grouping Records (GROUP BY, HAVING)
11. Operations on Relations (Union, Intersection, Minus, Cartesian Product)
12. Joins in SQL
13. Introduction to Constraints
14. Practice Questions & Board Exam Tips
Class XII Computer Science - MySQL & SQL Queries Page 2
1. Introduction to Database Management Systems
(DBMS)
A Database Management System (DBMS) is a software package designed to define,
manipulate, retrieve and manage data in a database. It generally manipulates the
data itself, the data format, field names, record structure and file structure. It also
defines rules to validate and manipulate this data.
Key Terms:
• Database: An organized collection of structured information, or data, typically
stored electronically in a computer system.
• DBMS: The software that interacts with end users, applications, and the
database itself to capture and analyze data.
• RDBMS: Relational Database Management System. Data is organized in tables
(relations) which are linked based on data common to each.
Importance for Boards: Understanding the difference between a File System
and a DBMS is a common 2-mark question. DBMS provides data redundancy
control, data sharing, data consistency, and security.
2. Relational Data Model Concepts
The Relational Model was proposed by E.F. Codd in 1970. It represents data in the
form of relations (tables).
Terminology:
• Relation: A table with columns and rows.
• Attribute: A named column of a relation.
• Tuple: A row of a relation.
• Domain: A set of permissible values for an attribute.
• Degree: The number of attributes in a relation.
• Cardinality: The number of tuples in a relation.
Class XII Computer Science - MySQL & SQL Queries Page 3
Term Definition
A set of one or more attributes that can uniquely identify tuples
Primary Key
within the relation.
Candidate All attribute combinations that are capable of serving as a primary
Key key.
Alternate
A candidate key that is not the primary key.
Key
A non-key attribute whose values are derived from the primary
Foreign Key
key of another table.
3. SQL Commands - DDL and DML
SQL (Structured Query Language) is the standard language for dealing with
Relational Databases.
Data Definition Language (DDL)
Commands that define the structure of the database. These are auto-committed.
• CREATE: To create objects in the database.
• ALTER: To modify the structure of the database.
• DROP: To delete objects from the database.
Data Manipulation Language (DML)
Commands used for managing data within schema objects.
• SELECT: To retrieve data from the database.
• INSERT: To insert data into a table.
• UPDATE: To update existing data within a table.
• DELETE: To delete records from a table.
Class XII Computer Science - MySQL & SQL Queries Page 4
-- Example of Creating a Table
CREATE TABLE Student (
RollNo INT PRIMARY KEY,
Name VARCHAR(20) NOT NULL,
DOB DATE,
Marks DECIMAL(5,2)
);
4. MySQL Functions
Functions are built-in tools to perform operations on data. They are categorized into
Single Row Functions and Aggregate Functions.
A. Math Functions
• POWER(m, n): Returns m raised to the power n.
• ROUND(n, d): Rounds n to d decimal places.
• MOD(m, n): Returns the remainder of m divided by n.
B. String Functions
• UPPER() / UCASE(): Converts string to uppercase.
• LOWER() / LCASE(): Converts string to lowercase.
• SUBSTR(str, pos, len): Extracts a substring starting from 'pos' of length
'len'.
• LENGTH(): Returns length of string in bytes.
• INSTR(str, substr): Returns the position of the first occurrence of substr in
str.
• LTRIM() / RTRIM() / TRIM(): Removes leading, trailing or both spaces.
C. Date Functions
• NOW(): Returns current date and time.
Class XII Computer Science - MySQL & SQL Queries Page 5
• CURDATE(): Returns current date.
• MONTH() / MONTHNAME(): Returns month number/name.
• YEAR() / DAYNAME(): Returns year/day of the week.
Note: SQL is case-insensitive for commands, but data inside quotes is case-sensitive.
5. Aggregate Functions and Grouping
Aggregate functions perform a calculation on a set of values and return a single
value.
• SUM(): Returns the total sum of a numeric column.
• AVG(): Returns the average value.
• COUNT(): Returns the number of rows. COUNT(*) counts all rows including
nulls, while COUNT(column) ignores nulls.
• MAX() / MIN(): Returns largest/smallest value.
GROUP BY Clause
The GROUP BY statement groups rows that have the same values into summary rows.
SELECT Department, COUNT(*)
FROM Teacher
GROUP BY Department;
HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not be used
with aggregate functions.
Class XII Computer Science - MySQL & SQL Queries Page 6
SELECT Department, AVG(Salary)
FROM Teacher
GROUP BY Department
HAVING AVG(Salary) > 50000;
6. Joins in MySQL
A JOIN clause is used to combine rows from two or more tables, based on a related
column between them.
Equi-Join
An equi-join is a join with a join condition containing an equality operator (=).
SELECT [Link], [Link]
FROM Student, Fees
WHERE [Link] = [Link];
Natural Join
A type of equi-join which occurs implicitly by matching all identical columns (columns
with same name and data type).
7. Python-MySQL Connectivity
To connect Python with MySQL, we use the mysql-connector-python library.
Steps:
1. Import the connector module.
2. Establish connection using connect().
Class XII Computer Science - MySQL & SQL Queries Page 7
3. Create a cursor object using cursor().
4. Execute SQL query using execute().
5. Extract data using fetchone(), fetchall(), or fetchmany().
6. Close the connection.
import [Link]
mydb = [Link](
host="localhost",
user="root",
password="password123",
database="school"
)
mycursor = [Link]()
[Link]("SELECT * FROM student")
data = [Link]()
for row in data:
print(row)
[Link]()
Class XII Computer Science - MySQL & SQL Queries Page 8
8. Practice Set & Solved Queries
Consider table EMPLOYEE:
EmpID Name Salary Dept JoinDate
101 Amit 45000 HR 2020-01-12
102 Sanya 55000 IT 2019-11-05
103 Rahul NULL IT 2021-03-22
Queries:
1. Display names of employees whose salary is not known:
SELECT Name FROM EMPLOYEE WHERE Salary IS NULL;
2. Display the first 3 characters of all names:
SELECT SUBSTR(Name, 1, 3) FROM EMPLOYEE;
3. Display count of employees in each department:
SELECT Dept, COUNT(*) FROM EMPLOYEE GROUP BY Dept;
4. Display unique Departments:
SELECT DISTINCT Dept FROM EMPLOYEE;
9. Important Board Exam Tips
• Always end SQL queries with a semicolon (;) in exam answers unless specified
otherwise.
• Be careful with NULL values. NULL cannot be compared with =; use IS NULL or
IS NOT NULL.
• In Python connectivity, remember that [Link]() does not return the
data; you must use a fetch function.
• Distinguish between CHAR (Fixed length) and VARCHAR (Variable length).
• Understand the order of execution: FROM -> WHERE -> GROUP BY -> HAVING ->
SELECT -> ORDER BY.
Class XII Computer Science - MySQL & SQL Queries Page 9
*** End of Document ***
Prepared for Class 12 Computer Science Students.
Class XII Computer Science - MySQL & SQL Queries Page 10