Every example uses one small database, schooldb, with four tables: STUDENT (shown in RDM.01), TEACHER and DEPT (for joins) and FEE (for math and date functions). Open them from anywhere with the Sample tables button at the bottom right of the screen. Each example starts from these tables as they are, not from the changes an earlier example made.
Chapter 1 of 10 · DBC
Database Concepts
Why schools, banks and shops keep their records in a database instead of separate files, and the software that manages it.
DBC.01 · Database Concepts
Introduction to Database Concepts and its Need
11-IP12-CS12-IP
Data is raw facts: "Riya", "12A", "91". Once it is organised so that it means something ("Riya of 12A scored 91"), it becomes information. A database is an organised collection of related data, stored so that it can be found, changed and shared easily. A school's student records, a bank's accounts and a railway's bookings are all databases.
Why not just use files?
Before databases, each office kept its own files. The school office, the fee counter and the library each kept a file with the same students' names and classes. This file system causes problems that a database solves.
With files, a change of class has to be made in three places, and one is often missed. With a database, it is made once, through the DBMS.
Problem with files
What it means
How a database helps
Data redundancy
The same data is stored in many files
Each fact is stored once and shared
Data inconsistency
Copies disagree: one file says 12A, another 12B
One copy, so it cannot disagree
Data isolation
Data is spread over files in different formats, hard to combine
All data is in one place, in tables
Data dependence
Change a file's layout, and every program using it must change
Programs ask the DBMS; the storage can change underneath
Controlled sharing
Hard to let the fee clerk see fees but not marks
The DBMS gives each user only the access they need
Exam tip
"Why do we need a database?" Name two problems of the file system, redundancy and inconsistency, and say how a database removes them: data is stored once and shared.
DBC.02 · Database Concepts
Database Management System
11-IP12-CS12-IP
A Database Management System (DBMS) is the software used to create a database, store data in it, change it, ask it questions and control who can use it. Users and programs never touch the stored data directly; they ask the DBMS, and the DBMS does the work.
Examples: MySQL, Oracle, Microsoft SQL Server, PostgreSQL, SQLite, Microsoft Access.
A DBMS that stores data in tables (relations) is a relational DBMS (RDBMS). All the examples above are relational.
A database, its DBMS and the programs that use it together make a database system.
A DBMS…
So that…
Stores data once, in tables
There is no redundancy or inconsistency
Checks every change against rules (constraints)
Wrong data, such as a duplicate roll number, is refused
Lets many users work at the same time
The office and the fee counter can both use it at once
Gives each user only the access they need
Data stays secure and private
Keeps backups and can recover after a failure
Data is not lost if the computer crashes
Exam tip
A database is the data; a DBMS is the software that manages it. "MySQL is a database" is a common slip: MySQL is an RDBMS.
Chapter practice · DBC
Check your understanding
The same student's address is stored in three different files. This is called:
Redundancy means the same data is stored more than once.
One file says a student is in 12A and another says 12B. This problem is:
The copies disagree, so the data is inconsistent.
Which of these is a DBMS?
MySQL is a relational database management system.
Which of these is NOT a job of a DBMS?
A compiler does that; a DBMS manages data.
Your score
– / 4
Answer every question to see your score.
One-page revision
Data · informationraw facts · facts given meaning
Databaseorganised collection of related data
Redundancysame data stored many times
Inconsistencycopies of data disagree
DBMSsoftware that manages a database
RDBMSDBMS that stores data in tables
ExamplesMySQL, Oracle, SQL Server, PostgreSQL
Database systemdatabase + DBMS + programs
Chapter 2 of 10 · RDM
Relational Data Model
In the relational model, all data is kept in tables, called relations. This chapter names every part of a table, and the keys that identify rows and link tables together.
RDM.01 · Relational Data Model
Relational Model Terminology
11-IP12-CS12-IP
Here is the STUDENT table used in every example on this page, exactly as MySQL shows it. The parts are named below.
SQL · the sample table
SELECT * FROM STUDENT;
MySQL output
+--------+-------+-------+--------+-------+--------+
| RollNo | Name | Class | Gender | Marks | City |
+--------+-------+-------+--------+-------+--------+
| 1 | Riya | 12A | F | 91 | Delhi |
| 2 | Aman | 12B | M | 78 | Jaipur |
| 3 | Neha | 12A | F | 85 | Delhi |
| 4 | Kabir | 12C | M | NULL | Mumbai |
| 5 | Isha | 12B | F | 67 | Jaipur |
| 6 | Arjun | 12A | M | 72 | Kochi |
+--------+-------+-------+--------+-------+--------+
6 rows in set
A part of STUDENT: 5 attributes and 3 tuples, so its degree is 5 and its cardinality is 3. The full table above has degree 6 and cardinality 6.
A relation is a table of data with rows and columns. Each relation has a unique name, such as STUDENT. In a relation, no two rows are exactly the same, and the order of rows and columns does not matter.
A tuple is one row of a relation: all the data about one item. (2, 'Aman', '12B', 'M', 78, 'Jaipur') is one tuple of STUDENT. Another name for it is a record.
A domain is the set of all values an attribute is allowed to take. The domain of Gender is {F, M}; the domain of Class might be {12A, 12B, 12C}; the domain of Marks might be whole numbers from 0 to 100.
The cardinality of a relation is the number of tuples (rows) in it. STUDENT has 6 rows, so its cardinality is 6. The heading row is not counted.
Relational term
Everyday term
In STUDENT
Relation
Table
STUDENT
Tuple
Row, record
(1, 'Riya', '12A', 'F', 91, 'Delhi')
Attribute
Column, field
Marks
Domain
Allowed values
Gender ∈ {F, M}
Degree
Number of columns
6
Cardinality
Number of rows
6
Exam tip
Columns give the degree; rows give the cardinality (memory aid: cardinality counts records). A typical question: "a relation has 4 tuples and 3 attributes; 2 rows are added and 1 column is removed; find the new degree and cardinality" → degree 2, cardinality 6.
RDM.02 · Relational Data Model
Keys
11-IP12-CS12-IP
A key is an attribute (or a group of attributes) whose value identifies each tuple uniquely. Think of a school's admission register: two students may share a name, but never an admission number.
Every attribute that could identify rows is a candidate key. One is chosen as the primary key; the others are alternate keys.
A candidate key is any attribute (or set of attributes) that can uniquely identify every tuple: its values are never repeated and never empty. A relation can have several. Above, both AdmNo and Aadhaar are candidate keys.
The primary key is the one candidate key chosen by the database designer to identify tuples. Its values must be unique and not NULL, and a table has only one primary key. In STUDENT the primary key is RollNo. A primary key made of two or more attributes together is a composite primary key, e.g. (Class, RollNo) when roll numbers restart in every section.
An alternate key is a candidate key that was not chosen as the primary key. Alternate keys = candidate keys − primary key. Above, Aadhaar is the alternate key.
A foreign key is an attribute in one table that refers to the primary key of another table. It links the two tables. In TEACHER, DeptID is a foreign key that refers to DEPT's primary key DeptID. Its values must exist in DEPT, or be NULL; this rule keeps the tables consistent and is called referential integrity.
The foreign key (left) points to the primary key of another table (right).
MySQL enforces the rule. Adding a teacher to department D3 works; adding one to D9, which does not exist in DEPT, is refused:
SQL · a foreign key at work
INSERT INTO TEACHER VALUES (105,'Iyer','D3',60000);
INSERT INTO TEACHER VALUES (106,'Paul','D9',52000);
MySQL output
mysql> INSERT INTO TEACHER VALUES (105,'Iyer','D3',60000);
Query OK, 1 row affected
mysql> INSERT INTO TEACHER VALUES (106,'Paul','D9',52000);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`schooldb`.`teacher`, CONSTRAINT `teacher_ibfk_1` FOREIGN KEY (`DeptID`) REFERENCES `dept` (`DeptID`))
Key
Unique?
NULL allowed?
How many per table
Candidate
Yes
No
One or more
Primary
Yes
No
Exactly one
Alternate
Yes
No
Candidates − 1
Foreign
No, can repeat
Yes
Any number
Exam tip
"Which key maintains data integrity between related tables?" → foreign key. "Identify the candidate and alternate keys" in a table like EMP(EmpID, AadharNumber, MobileNumber, Name): candidate keys are the unique ones (EmpID, AadharNumber, MobileNumber); if EmpID is the primary key, the other two are alternate keys.
Chapter practice · RDM
Check your understanding
Work it out
A relation has 5 attributes and 8 tuples. 3 tuples are added and 1 attribute is deleted. New degree and cardinality?
BOOK(BookID, ISBN, Title, Author). BookID and ISBN are both unique; BookID is the primary key. Name the alternate key.
ISBN.A candidate key not chosen as primary.
Why is Name a poor choice of primary key for STUDENT?
Two students can share a name.A primary key must be unique.
Can a foreign key column hold the same value in two rows?
Yes.Many teachers can belong to department D1.
Self-check quiz
In a relation, a row is called a:
Rows are tuples; columns are attributes.
The number of attributes in a relation is its:
Degree counts columns.
"Class" can only take values from {IX, X, XI, XII}. This set is the attribute's:
A domain is the set of allowed values.
Which key may contain NULL and repeated values?
A foreign key only has to match the other table's primary key.
Candidate keys that are not chosen as the primary key are:
Alternate = candidate − primary.
Which of these is used to uniquely identify each record in a table?
The primary key is unique and not NULL.
Your score
– / 6
Answer every question to see your score.
One-page revision
Relationa table
Tuplea row (record)
Attributea column (field)
Domainset of allowed values
Degreenumber of columns
Cardinalitynumber of rows
Candidate keyany attribute that can identify rows
Primary keythe chosen one; unique, not NULL
Alternate keycandidate − primary
Foreign keyrefers to another table's primary key
Chapter 3 of 10 · SQL
Introduction to SQL
SQL (Structured Query Language) is the standard language for working with relational databases. This chapter covers why it is used, its three kinds of command, MySQL, the data types a column can have, and the constraints that guard it.
SQL.01 · Introduction to SQL
Advantages of SQL
11-IP12-IP
SQL lets you say what data you want, in words close to English, and the DBMS works out how to get it. SELECT Name FROM STUDENT WHERE Marks > 80; reads almost like a sentence.
Easy to learn: it uses simple English keywords such as SELECT, INSERT and UPDATE.
Non-procedural: you describe the result you want; you don't write loops or steps.
Standard: SQL is an ANSI and ISO standard, so the same commands work in MySQL, Oracle, SQL Server and others, with small differences.
Fast: it can search, sort and summarise millions of rows quickly.
Complete: one language creates tables, changes data, asks questions and controls access.
Works with programs: Python and other languages can send SQL to a database (Chapter 10).
Exam tip
SQL is not case-sensitive for keywords: select and SELECT are the same. Every statement ends with a semicolon in MySQL.
SQL.02 · Introduction to SQL
Types of SQL Commands
11-IP12-CS12-IP
SQL commands are grouped by what they do to a database.
The three groups in the syllabus. (Full SQL also has commands for permissions and transactions.)
DDL commands define and change the structure of the database: create, change or remove databases and tables. CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP TABLE, DROP DATABASE. Chapter 4.
DML commands change the data stored in tables, without changing their structure: INSERT adds rows, UPDATE changes values, DELETE removes rows. Chapter 5.
DQL is used to ask questions of the data and get results, without changing anything. It has one command, SELECT. Some books count SELECT as part of DML. Chapters 6 to 9.
Group
Commands
Changes degree?
Changes cardinality?
DDL
CREATE, ALTER, DROP
ALTER ADD / DROP column: yes
No
DML
INSERT, UPDATE, DELETE
No
INSERT, DELETE: yes; UPDATE: no
DQL
SELECT
No
No
Exam tip
Know which command changes what: ALTER changes the degree (adds or removes columns); INSERT and DELETE change the cardinality; UPDATE changes neither, only values. "Which is a DQL statement?" → SELECT.
SQL.03 · Introduction to SQL
Introduction to MySQL
11-IP12-IP
MySQL is a free, open-source relational database management system that uses SQL. It was first released in 1995 by the Swedish company MySQL AB and is now owned by Oracle Corporation. It runs on Windows, Linux and macOS, and powers many websites and apps.
Client and server: the MySQL server stores the databases; a client, such as the MySQL command line or a Python program, sends it SQL and shows the results.
In the command line, you type a statement at the mysql> prompt and end it with ;. A statement can go over several lines; MySQL shows -> until it sees the semicolon.
Results come back as a grid with a line such as 6 rows in set; changes report Query OK, 1 row affected; mistakes report ERROR with a number and a message. Every grid on this page is real MySQL output.
Creating a database in MySQL:CREATE DATABASE school; then USE school; to start working in it (Chapter 4).
Exam tip
MySQL is an RDBMS and it is open source. SQL is the language; MySQL is the software that understands it.
SQL.04 · Introduction to SQL
Data Types
11-IP12-CS12-IP
Every column is given a data type when the table is created. The data type decides what kind of value the column may hold and how much space it takes. MySQL refuses values that don't fit.
CHAR(n) stores fixed-length text of exactly n characters (n from 0 to 255). Shorter values are padded with spaces, so CHAR(10) always uses 10 characters of space, even for 'Riya'. Use it when every value has the same length: Gender CHAR(1), Class CHAR(3), PIN code CHAR(6).
VARCHAR(n) stores variable-length text of up to n characters. It uses only as much space as the value needs (plus a byte or two to record the length), so VARCHAR(20) holding 'Riya' uses about 5 bytes. Use it for names, addresses and emails, whose lengths differ.
INT (or INTEGER) stores whole numbers, positive or negative, from −2,147,483,648 to 2,147,483,647. It takes 4 bytes. Use it for roll numbers, quantities and marks out of 100.
DATE stores a calendar date in the format 'YYYY-MM-DD', e.g. '2026-03-14', written in quotes. MySQL checks that the date exists: 30 February is refused.
Here MySQL refuses a subject name that is too long for VARCHAR(8), and a date that does not exist:
SQL · values that don't fit
CREATE TABLE EXAM (Code CHAR(4),
Subject VARCHAR(8), Marks FLOAT, ExamDate DATE);
INSERT INTO EXAM
VALUES ('065','IP',78.5,'2026-03-14');
INSERT INTO EXAM
VALUES ('083','Computer Sc',70,'2026-03-20');
INSERT INTO EXAM
VALUES ('301','English',81,'2026-02-30');
MySQL output
mysql> CREATE TABLE EXAM (Code CHAR(4),
-> Subject VARCHAR(8), Marks FLOAT, ExamDate DATE);
Query OK, 0 rows affected
mysql> INSERT INTO EXAM
-> VALUES ('065','IP',78.5,'2026-03-14');
Query OK, 1 row affected
mysql> INSERT INTO EXAM
-> VALUES ('083','Computer Sc',70,'2026-03-20');
ERROR 1406 (22001): Data too long for column 'Subject' at row 1
mysql> INSERT INTO EXAM
-> VALUES ('301','English',81,'2026-02-30');
ERROR 1292 (22007): Incorrect date value: '2026-02-30' for column 'ExamDate' at row 1
CHAR(n)
VARCHAR(n)
Length
Fixed: always n
Variable: up to n
Space for 'Riya' in (10)
10 characters
4 characters + length byte
Best for
Values of the same length
Values of different lengths
Maximum n
255
Much larger (up to 65,535 bytes per row)
Exam tip
"Difference between CHAR and VARCHAR" is a regular 2-mark question: fixed vs variable length, with the space example. Text and dates go in quotes; numbers do not.
SQL.05 · Introduction to SQL
Constraints
12-CS
A constraint is a rule on a column that MySQL checks every time data is added or changed. If a value breaks the rule, the whole statement is refused. Constraints are written after the column's data type in CREATE TABLE.
UNIQUE means no two rows may have the same value in this column, e.g. email or phone number. A UNIQUE column may still hold NULL (unless it is also NOT NULL), and a table can have several UNIQUE columns.
PRIMARY KEY makes the column the table's primary key: it is UNIQUE and NOT NULL together. A table can have only one primary key. For a composite key, write it at the end: PRIMARY KEY (Class, RollNo).
One table with all three constraints, and an INSERT that breaks each one:
SQL · constraints refusing bad data
CREATE TABLE MEMBER (
MID INT PRIMARY KEY,
MName VARCHAR(20) NOT NULL,
Email VARCHAR(30) UNIQUE);
INSERT INTO MEMBER VALUES (1,'Riya','riya@kv.in');
INSERT INTO MEMBER VALUES (1,'Aman','aman@kv.in');
INSERT INTO MEMBER VALUES (2,NULL,'neha@kv.in');
INSERT INTO MEMBER VALUES (3,'Isha','riya@kv.in');
MySQL output
mysql> CREATE TABLE MEMBER (
-> MID INT PRIMARY KEY,
-> MName VARCHAR(20) NOT NULL,
-> Email VARCHAR(30) UNIQUE);
Query OK, 0 rows affected
mysql> INSERT INTO MEMBER VALUES (1,'Riya','riya@kv.in');
Query OK, 1 row affected
mysql> INSERT INTO MEMBER VALUES (1,'Aman','aman@kv.in');
ERROR 1062 (23000): Duplicate entry '1' for key 'member.PRIMARY'
mysql> INSERT INTO MEMBER VALUES (2,NULL,'neha@kv.in');
ERROR 1048 (23000): Column 'MName' cannot be null
mysql> INSERT INTO MEMBER VALUES (3,'Isha','riya@kv.in');
ERROR 1062 (23000): Duplicate entry 'riya@kv.in' for key 'member.Email'
Constraint
Duplicates?
NULL?
Per table
NOT NULL
Allowed
Not allowed
Any number
UNIQUE
Not allowed
Allowed
Any number
PRIMARY KEY
Not allowed
Not allowed
One
Exam tip
"Which constraint prevents duplicate values but allows NULL?" → UNIQUE. "Difference between PRIMARY KEY and UNIQUE": a primary key can't be NULL and there is only one; UNIQUE allows NULL and there can be many.
Chapter practice · SQL
Check your understanding
Choose the data type
A 6-digit PIN code such as 110021.
CHAR(6).Always 6 characters, and no arithmetic is done on it.
A customer's email address.
VARCHAR(40) or similar. Lengths vary a lot.
The price of a book, such as 349.50.
FLOAT.It has a decimal part.
The day a book was issued.
DATE, written 'YYYY-MM-DD'. MySQL can then check it and use date functions.
Self-check quiz
SQL is called non-procedural because:
The DBMS decides the steps.
Which of these is a DDL command?
ALTER changes a table's structure.
Which command can change the cardinality of a table?
DELETE removes rows; UPDATE only changes values.
MySQL is:
SQL is the language; MySQL is the RDBMS.
A column declared CHAR(8) holds 'KV'. How many characters of space does it use?
CHAR is fixed length.
In which format is a DATE written in MySQL?
For example '2026-03-14'.
Which constraint stops duplicate values but still allows NULL?
PRIMARY KEY stops both.
PRIMARY KEY is the same as:
Both rules, and only one per table.
Your score
– / 8
Answer every question to see your score.
One-page revision
SQLStructured Query Language
DDLCREATE, ALTER, DROP: structure
DMLINSERT, UPDATE, DELETE: rows
DQLSELECT: reads only
MySQLopen-source RDBMS; Oracle
CHAR(n)fixed length, up to 255
VARCHAR(n)variable length, up to n
INT · FLOATwhole · decimal numbers
DATE'YYYY-MM-DD'
NOT NULLa value is required
UNIQUEno duplicates; NULL allowed
PRIMARY KEYUNIQUE + NOT NULL; one per table
Chapter 4 of 10 · DDL
Data Definition (DDL)
The commands that build and change the containers: databases, tables, and the columns and keys of a table.
DDL.01 · Data Definition (DDL)
Database Commands
11-IP12-CS12-IP
A MySQL server can hold many databases. Each database is a container for related tables. Four commands manage them.
USE library; makes that database the current one, so the next commands work on its tables. The MySQL console replies Database changed. You must USE a database before creating tables in it.
SHOW DATABASES; lists every database on the server. Besides yours, it lists MySQL's own system databases (information_schema, mysql, performance_schema, sys).
DESCRIBE BOOK; (or the short form DESC BOOK;) shows a table's structure: each column's name, type, whether it may be NULL, and which is the key (PRI). It shows no rows.
SQL · create a table and look at it
CREATE TABLE BOOK (
BookID INT PRIMARY KEY,
Title VARCHAR(30) NOT NULL,
Price FLOAT,
Issued DATE);
SHOW TABLES;
DESCRIBE BOOK;
MySQL output
mysql> CREATE TABLE BOOK (
-> BookID INT PRIMARY KEY,
-> Title VARCHAR(30) NOT NULL,
-> Price FLOAT,
-> Issued DATE);
Query OK, 0 rows affected
mysql> SHOW TABLES;
+--------------------+
| Tables_in_schooldb |
+--------------------+
| BOOK |
| DEPT |
| FEE |
| STUDENT |
| TEACHER |
+--------------------+
5 rows in set
mysql> DESCRIBE BOOK;
+--------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| BookID | int | NO | PRI | NULL | |
| Title | varchar(30) | NO | | NULL | |
| Price | float | YES | | NULL | |
| Issued | date | YES | | NULL | |
+--------+-------------+------+-----+---------+-------+
4 rows in set
DROP TABLE STUDENT;removes the whole table: its structure and all its rows, permanently. Dropping a table that doesn't exist is an error.
SQL · remove a table
DROP TABLE EXAMS;
DROP TABLE STUDENT;
SHOW TABLES;
MySQL output
mysql> DROP TABLE EXAMS;
ERROR 1051 (42S02): Unknown table 'schooldb.exams'
mysql> DROP TABLE STUDENT;
Query OK, 0 rows affected
mysql> SHOW TABLES;
+--------------------+
| Tables_in_schooldb |
+--------------------+
| DEPT |
| FEE |
| TEACHER |
+--------------------+
3 rows in set
DELETE FROM STUDENT;
DROP TABLE STUDENT;
Type
DML
DDL
Removes
Rows only (all, or those matching WHERE)
The table itself: structure and rows
Afterwards
An empty table still exists; DESC still works
The table is gone; SELECT gives an error
Exam tip
"Which command removes a table from the database?" → DROP TABLE, not DELETE. In a CREATE TABLE answer, don't forget the commas between columns and the closing );.
DDL.03 · Data Definition (DDL)
ALTER TABLE
11-IP12-CS12-IP
ALTER TABLEchanges the structure of an existing table without losing its rows: add or remove a column, or add or remove the primary key.
ALTER TABLE LOANS ADD PRIMARY KEY (LoanNo); makes an existing column the primary key. It works only if the column has no duplicates and no NULLs, and the table has no primary key yet.
ALTER TABLE LOANS DROP PRIMARY KEY; removes the primary key constraint. The column and its values stay; only the key is gone. No column name is needed, because there is only one primary key.
SQL · add, then remove, a primary key
CREATE TABLE LOANS (LoanNo INT, BookID INT);
ALTER TABLE LOANS ADD PRIMARY KEY (LoanNo);
DESC LOANS;
ALTER TABLE LOANS DROP PRIMARY KEY;
DESC LOANS;
MySQL output
mysql> CREATE TABLE LOANS (LoanNo INT, BookID INT);
Query OK, 0 rows affected
mysql> ALTER TABLE LOANS ADD PRIMARY KEY (LoanNo);
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC LOANS;
+--------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| LoanNo | int | NO | PRI | NULL | |
| BookID | int | YES | | NULL | |
+--------+------+------+-----+---------+-------+
2 rows in set
mysql> ALTER TABLE LOANS DROP PRIMARY KEY;
Query OK, 0 rows affected
Records: 0 Duplicates: 0 Warnings: 0
mysql> DESC LOANS;
+--------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------+------+-----+---------+-------+
| LoanNo | int | NO | | NULL | |
| BookID | int | YES | | NULL | |
+--------+------+------+-----+---------+-------+
2 rows in set
Task
Command
Degree
Add a column
ALTER TABLE t ADD col type;
+1
Remove a column
ALTER TABLE t DROP [COLUMN] col;
−1
Add the primary key
ALTER TABLE t ADD PRIMARY KEY (col);
same
Remove the primary key
ALTER TABLE t DROP PRIMARY KEY;
same
Exam tip
"Which SQL command can change the degree of an existing relation?" → ALTER. Note that ALTER … DROP removes a column, but DROP TABLE removes the whole table.
Chapter practice · DDL
Check your understanding
Write the command
Add a column Email of up to 30 characters to STUDENT.
ALTER TABLE STUDENT ADD Email VARCHAR(30);ADD a new attribute.
Remove the column Gender from STUDENT.
ALTER TABLE STUDENT DROP COLUMN Gender;COLUMN may be left out.
Show the structure of table BOOK.
DESCRIBE BOOK; or DESC BOOK;It lists the columns, not the rows.
Make RegNo the primary key of table EVENTS.
ALTER TABLE EVENTS ADD PRIMARY KEY (RegNo);RegNo must have no duplicates or NULLs.
Self-check quiz
Which command makes a database the current one?
USE library; then work in it.
Which command shows the structure of a table?
DESCRIBE (or DESC) lists each column and its type.
Which command removes the table STUDENT completely?
DELETE removes rows only.
Which command correctly removes the attribute Age from table Student?
Removing a column changes the structure: ALTER.
After ALTER TABLE ... ADD, what is stored in the new column for existing rows?
See the Phone column in the example.
Your score
– / 5
Answer every question to see your score.
One-page revision
CREATE DATABASE dmake a database
USE dwork in that database
SHOW DATABASESlist all databases
DROP DATABASE ddelete it and its tables
CREATE TABLE t (…)column, type, constraint, …
SHOW TABLEStables in the current database
DESC tstructure, not rows
DROP TABLE tremove structure and rows
ALTER … ADD / DROPadd or remove a column
ALTER … PRIMARY KEYADD PRIMARY KEY (col) · DROP PRIMARY KEY
Chapter 5 of 10 · DML
Data Manipulation (DML)
Three commands change the rows of a table: INSERT adds them, UPDATE changes them and DELETE removes them. None of them changes the table's structure.
UPDATE … SET … WHERE … changes values in existing rows. SET says the new value, and it can use the old one: Marks = Marks + 5. WHERE says which rows. Several columns can be set at once: SET Marks = 80, City = 'Agra'.
SQL · give 12B five grace marks
UPDATE STUDENT SET Marks = Marks + 5
WHERE Class = '12B';
SELECT Name, Class, Marks FROM STUDENT
WHERE Class = '12B';
MySQL output
mysql> UPDATE STUDENT SET Marks = Marks + 5
-> WHERE Class = '12B';
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0
mysql> SELECT Name, Class, Marks FROM STUDENT
-> WHERE Class = '12B';
+------+-------+-------+
| Name | Class | Marks |
+------+-------+-------+
| Aman | 12B | 83 |
| Isha | 12B | 72 |
+------+-------+-------+
2 rows in set
DELETE FROM … WHERE … removes the rows that match the condition. The table and its other rows stay.
SQL · remove the Delhi students
DELETE FROM STUDENT WHERE City = 'Delhi';
SELECT RollNo, Name, City FROM STUDENT;
MySQL output
mysql> DELETE FROM STUDENT WHERE City = 'Delhi';
Query OK, 2 rows affected
mysql> SELECT RollNo, Name, City FROM STUDENT;
+--------+-------+--------+
| RollNo | Name | City |
+--------+-------+--------+
| 2 | Aman | Jaipur |
| 4 | Kabir | Mumbai |
| 5 | Isha | Jaipur |
| 6 | Arjun | Kochi |
+--------+-------+--------+
4 rows in set
Command
Form
Changes
INSERT
INSERT INTO t [(cols)] VALUES (…);
Cardinality +1 per row
UPDATE
UPDATE t SET col = value [WHERE …];
Values only
DELETE
DELETE FROM t [WHERE …];
Cardinality −1 per row
Common mistake
Without WHERE, UPDATE STUDENT SET Marks = 0; changes every row, and DELETE FROM STUDENT; empties the whole table. Check the WHERE before you press Enter.
Exam tip
Text and dates need quotes in VALUES and WHERE; numbers don't. "Which command modifies the records of an existing table?" → UPDATE (ALTER changes the structure, not the records).
Chapter practice · DML
Check your understanding
Write the command
Change Kabir's marks to 64.
UPDATE STUDENT SET Marks = 64 WHERE Name = 'Kabir';Or WHERE RollNo = 4, which is safer: roll numbers are unique.
Remove every student who has no marks.
DELETE FROM STUDENT WHERE Marks IS NULL;Never "= NULL" (Chapter 6).
Add student 9, Tara, of 12C, with nothing else known yet.
INSERT INTO STUDENT (RollNo, Name, Class) VALUES (9, 'Tara', '12C');Unnamed columns become NULL.
A table has cardinality 10. You run UPDATE on 4 rows, then DELETE 2 rows. Cardinality now?
8.UPDATE doesn't change the number of rows.
Self-check quiz
Which command adds a new row to a table?
INSERT INTO … VALUES (…).
What does DELETE FROM STUDENT; do?
Without WHERE, all rows go; the structure stays.
Which keyword in UPDATE gives the new value?
UPDATE t SET col = value.
Which SQL command will NEVER change the cardinality of a table?
UPDATE changes values in rows that already exist.
Your score
– / 4
Answer every question to see your score.
One-page revision
INSERT INTO t VALUES (…)all columns, in order
INSERT INTO t (a, b) VALUES (…)named columns; rest NULL
UPDATE t SET c = v WHERE …change values
DELETE FROM t WHERE …remove matching rows
No WHEREUPDATE / DELETE hit every row
Quotesfor text and dates, not numbers
Chapter 6 of 10 · DQL
Data Query (SELECT)
SELECT asks a table a question and shows the answer as a new, temporary table. This chapter covers choosing columns and rows, every operator allowed in WHERE, NULL, and sorting.
DQL.01 · Data Query (SELECT)
SELECT Statement
11-IP12-CS12-IP
The basic form is SELECT columns FROM table WHERE condition;. SELECT picks columns; WHERE picks rows. The original table is never changed.
SELECT DISTINCTremoves duplicate rows from the result, so each value appears once. With several columns, a row is a duplicate only if all its values match another row.
SQL · with and without DISTINCT
SELECT City FROM STUDENT;
SELECT DISTINCT City FROM STUDENT;
MySQL output
mysql> SELECT City FROM STUDENT;
+--------+
| City |
+--------+
| Delhi |
| Jaipur |
| Delhi |
| Mumbai |
| Jaipur |
| Kochi |
+--------+
6 rows in set
mysql> SELECT DISTINCT City FROM STUDENT;
+--------+
| City |
+--------+
| Delhi |
| Jaipur |
| Mumbai |
| Kochi |
+--------+
4 rows in set
An alias gives a column a new heading in the result with AS. A heading with a space needs quotes. Aliases are handy for calculated columns. Tables can have aliases too (Chapter 9).
SQL · new headings
SELECT Name AS Student, Marks + 5 AS 'With Bonus'
FROM STUDENT WHERE Class = '12A';
MySQL output
+---------+------------+
| Student | With Bonus |
+---------+------------+
| Riya | 96 |
| Neha | 90 |
| Arjun | 77 |
+---------+------------+
3 rows in set
A SELECT statement can have up to six clauses. They must be written in a fixed order, and MySQL works through them in a different order. Knowing both explains most SQL errors.
Write SELECT first and ORDER BY last. MySQL starts with FROM, filters and groups the rows, works out the SELECT columns, and sorts last of all.
Clause
Needed?
What it does
Taught in
SELECT
Always
Which columns (or calculations) to show
DQL.01
FROM
When reading a table
Which table or tables to read
DQL.01, Chapter 9
WHERE
Optional
Which rows to keep
DQL.01–03
GROUP BY
Optional
Which rows to put together in groups
GRP.01 (Class 12)
HAVING
Optional, with GROUP BY
Which groups to keep
GRP.02 (Class 12)
ORDER BY
Optional, always last
How to sort the result
DQL.04 (Class 12)
One query with all six clauses: for each class, leaving out Kochi, count the students and average the marks, keep classes with more than one student, best average first.
SQL · all six clauses
SELECT Class, COUNT(*) AS Students, AVG(Marks)
FROM STUDENT
WHERE City <> 'Kochi'
GROUP BY Class
HAVING COUNT(*) > 1
ORDER BY AVG(Marks) DESC;
MySQL output
+-------+----------+------------+
| Class | Students | AVG(Marks) |
+-------+----------+------------+
| 12A | 2 | 88.0000 |
| 12B | 2 | 72.5000 |
+-------+----------+------------+
2 rows in set
FROM STUDENT: start with all 6 rows.
WHERE City <> 'Kochi': Arjun is removed; 5 rows are left.
GROUP BY Class: 12A (Riya, Neha), 12B (Aman, Isha) and 12C (Kabir).
HAVING COUNT(*) > 1: 12C has only one student, so that group is removed.
SELECT: work out Class, the count and the average for the groups that are left.
ORDER BY AVG(Marks) DESC: 12A (88.0000) before 12B (72.5000).
Common mistake
Because WHERE runs before GROUP BY, it can't test COUNT() or AVG(): that is HAVING's job. Because SELECT runs before ORDER BY, an alias made in SELECT (AS Students) can be used in ORDER BY, but not in WHERE.
Exam tip
An alias changes only the heading in the output; the table's column is still called Marks. "Which clause specifies the table to get data from?" → FROM.
DQL.02 · Data Query (SELECT)
Operators in WHERE
11-IP12-CS12-IP
Operators build the conditions in WHERE, and calculated columns in SELECT.
+ − * / % (or MOD) work on numbers, in SELECT as well as WHERE. / always gives a decimal result in MySQL (91 / 4 = 22.7500), and % gives the remainder.
SQL · calculated columns
SELECT Name, Marks, Marks * 2 AS Doubled,
Marks / 4 AS Quarter, Marks % 10 AS LastDigit
FROM STUDENT WHERE Class = '12A';
AND needs both conditions true; OR needs at least one; NOT reverses a condition. NOT is worked out first, then AND, then OR; use brackets to make the meaning clear.
SQL · AND, OR, NOT
SELECT Name, Gender, Marks FROM STUDENT
WHERE Gender = 'F' AND Marks > 80;
SELECT Name, City FROM STUDENT
WHERE City = 'Kochi' OR City = 'Mumbai';
SELECT Name, Class FROM STUDENT
WHERE NOT Class = '12A';
MySQL output
mysql> SELECT Name, Gender, Marks FROM STUDENT
-> WHERE Gender = 'F' AND Marks > 80;
+------+--------+-------+
| Name | Gender | Marks |
+------+--------+-------+
| Riya | F | 91 |
| Neha | F | 85 |
+------+--------+-------+
2 rows in set
mysql> SELECT Name, City FROM STUDENT
-> WHERE City = 'Kochi' OR City = 'Mumbai';
+-------+--------+
| Name | City |
+-------+--------+
| Kabir | Mumbai |
| Arjun | Kochi |
+-------+--------+
2 rows in set
mysql> SELECT Name, Class FROM STUDENT
-> WHERE NOT Class = '12A';
+-------+-------+
| Name | Class |
+-------+-------+
| Aman | 12B |
| Kabir | 12C |
| Isha | 12B |
+-------+-------+
3 rows in set
LIKE matches text against a pattern with two wildcards: % stands for any number of characters (even none), and _ stands for exactly one character.
'_e%' means "any one character, then e, then anything". The grid repeats the check for five patterns on every name in STUDENT; its ticks match the MySQL results below. LIKE ignores case, which is why Riya matches '%r%'.
SQL · four patterns
SELECT Name FROM STUDENT WHERE Name LIKE 'A%';
SELECT Name FROM STUDENT WHERE Name LIKE '%a';
SELECT Name FROM STUDENT WHERE Name LIKE '_e%';
SELECT Name FROM STUDENT WHERE Name LIKE '____';
MySQL output
mysql> SELECT Name FROM STUDENT WHERE Name LIKE 'A%';
+-------+
| Name |
+-------+
| Aman |
| Arjun |
+-------+
2 rows in set
mysql> SELECT Name FROM STUDENT WHERE Name LIKE '%a';
+------+
| Name |
+------+
| Riya |
| Neha |
| Isha |
+------+
3 rows in set
mysql> SELECT Name FROM STUDENT WHERE Name LIKE '_e%';
+------+
| Name |
+------+
| Neha |
+------+
1 row in set
mysql> SELECT Name FROM STUDENT WHERE Name LIKE '____';
+------+
| Name |
+------+
| Riya |
| Aman |
| Neha |
| Isha |
+------+
4 rows in set
Pattern
Matches
Example match
'A%'
Starts with A
Aman, Arjun
'%a'
Ends with a
Riya, Neha, Isha
'%r%'
Has r anywhere
Riya, Kabir, Arjun
'_e%'
Second letter is e
Neha
'____'
Exactly 4 characters
Riya, Aman, Neha, Isha
Exam tip
LIKE needs a wildcard to be useful: LIKE 'A' means the same as = 'A'. Names starting with A: LIKE 'A%', not '%A'. MySQL's LIKE ignores case, so 'a%' finds Aman too.
NULL means a value is missing or unknown. It is not zero and not an empty string: Kabir's marks are not 0, they are simply not known yet. Any arithmetic with NULL gives NULL, because "unknown + 5" is still unknown.
SQL · arithmetic with NULL
SELECT Name, Marks, Marks + 5 FROM STUDENT
WHERE Class = '12C';
MySQL output
+-------+-------+-----------+
| Name | Marks | Marks + 5 |
+-------+-------+-----------+
| Kabir | NULL | NULL |
+-------+-------+-----------+
1 row in set
To find missing values, use IS NULL. A comparison such as Marks = NULL is never true, not even for a NULL, so it returns an empty set:
SQL · = NULL vs IS NULL
SELECT Name FROM STUDENT WHERE Marks = NULL;
SELECT Name, Marks FROM STUDENT WHERE Marks IS NULL;
MySQL output
mysql> SELECT Name FROM STUDENT WHERE Marks = NULL;
Empty set
mysql> SELECT Name, Marks FROM STUDENT WHERE Marks IS NULL;
+-------+-------+
| Name | Marks |
+-------+-------+
| Kabir | NULL |
+-------+-------+
1 row in set
SELECT Name, Marks FROM STUDENT WHERE Marks IS NOT NULL;
MySQL output
+-------+-------+
| Name | Marks |
+-------+-------+
| Riya | 91 |
| Aman | 78 |
| Neha | 85 |
| Isha | 67 |
| Arjun | 72 |
+-------+-------+
5 rows in set
Common mistake
A NULL row fails every ordinary comparison: Kabir is in neither Marks > 80 nor Marks <= 80, nor in Marks NOT BETWEEN 70 AND 90. Only IS NULL finds him.
Exam tip
Write IS NULL and IS NOT NULL, never = NULL or != NULL. "Which of the following can be NULL?" → any column that is not a primary key and not NOT NULL.
DQL.04 · Data Query (SELECT)
ORDER BY
12-CS12-IP
ORDER BYsorts the result. ASC (ascending, the default) goes from small to large or A to Z; DESC goes the other way. ORDER BY comes last in the statement. NULL counts as the smallest value: first in ASC, last in DESC.
SQL · highest marks first
SELECT Name, Marks FROM STUDENT ORDER BY Marks DESC;
MySQL output
+-------+-------+
| Name | Marks |
+-------+-------+
| Riya | 91 |
| Neha | 85 |
| Aman | 78 |
| Arjun | 72 |
| Isha | 67 |
| Kabir | NULL |
+-------+-------+
6 rows in set
With two columns, MySQL sorts by the first, and uses the second only to order rows that tie on the first:
SQL · by class, then marks within a class
SELECT Name, Class, Marks FROM STUDENT
ORDER BY Class, Marks DESC;
MySQL output
+-------+-------+-------+
| Name | Class | Marks |
+-------+-------+-------+
| Riya | 12A | 91 |
| Neha | 12A | 85 |
| Arjun | 12A | 72 |
| Aman | 12B | 78 |
| Isha | 12B | 67 |
| Kabir | 12C | NULL |
+-------+-------+-------+
6 rows in set
Exam tip
"In decreasing order of price" → ORDER BY Price DESC. Each column has its own direction: in ORDER BY Class, Marks DESC, Class is ascending and only Marks is descending.
Chapter practice · DQL
Check your understanding
SELECT builder
Build a query on STUDENT from parts. The lab writes the SQL and shows the result in MySQL's format, following MySQL's rules: NULL fails every comparison, text ignores case, and NULL sorts first. Rows that tie in ORDER BY have no fixed order, so MySQL may list those in a different order. The lab runs entirely in your browser, on its own copy of STUDENT, so it works with no MySQL installed.
Your query
MySQL output
Predict the output
SELECT Name FROM STUDENT
WHERE Marks > 80 OR City = 'Kochi';
+-------+
| Name |
+-------+
| Riya |
| Neha |
| Arjun |
+-------+
3 rows in set
Riya and Neha pass Marks > 80; Arjun passes City = 'Kochi'.
SELECT DISTINCT Class FROM STUDENT
WHERE Gender = 'F';
+-------+
| Class |
+-------+
| 12A |
| 12B |
+-------+
2 rows in set
The girls are in 12A, 12A and 12B; DISTINCT shows each class once.
SELECT Name FROM STUDENT WHERE Name LIKE '%r%';
+-------+
| Name |
+-------+
| Riya |
| Kabir |
| Arjun |
+-------+
3 rows in set
%r% means an r anywhere. Kabir and Arjun have one inside; Riya matches because LIKE ignores case, so R counts.
SELECT Name, Marks FROM STUDENT
WHERE Marks NOT BETWEEN 70 AND 90;
+------+-------+
| Name | Marks |
+------+-------+
| Riya | 91 |
| Isha | 67 |
+------+-------+
2 rows in set
91 and 67 are outside 70–90. Kabir's NULL fails NOT BETWEEN too.
Self-check quiz
Which SQL keyword is used to filter records based on a condition?
WHERE keeps the rows that meet the condition.
Which query displays all employees whose salary is from 90,000 to 1,00,000, including both?
BETWEEN includes both ends.
Which displays names starting with "A"?
A, then anything.
How do you find rows where Marks is missing?
= NULL is never true.
What does DISTINCT do?
It changes only the result, not the table.
SELECT 10 + NULL; gives:
Anything combined with NULL is NULL.
The default order of ORDER BY is:
ASC is the default.
Where does ORDER BY go in a SELECT statement?
SELECT … FROM … WHERE … ORDER BY ….
Your score
– / 8
Answer every question to see your score.
One-page revision
SELECT cols FROM tpick columns; * for all
WHEREpick rows by a condition
DISTINCTdrop duplicate rows from the result
ASalias: a new heading
+ − * / %arithmetic; / gives decimals
= <> != > < >= <=comparisons
AND · OR · NOTboth · either · reverse
BETWEEN a AND binclusive range
IN (…)any value in a list
LIKE % _any number · exactly one
IS NULLnever = NULL
ORDER BY c DESCsort; ASC by default
Chapter 7 of 10 · SQLF
SQL Functions
A function takes values and returns a result. Single-row functions (math, text, date) work on each row separately and give one answer per row. Aggregate functions look at many rows and give one answer for all of them.
SQLF.01 · SQL Functions
Math Functions
12-IP
Each function is shown twice: first on fixed values (a SELECT with no table, a quick way to try a function), then on a column of a real table, where it is worked out for every row. The FEE table, with each student's fee and when it was paid, is in the Sample tables panel (button at the bottom right).
ROUND(x, d) rounds x to d decimal places. With no d, it rounds to a whole number. A negative d rounds to the left of the decimal point: −1 to the nearest 10, −2 to the nearest 100.
Look at roll 2: ROUND(1180.5) gave 1180, but ROUND(15.5) gave 16. A typed number such as 15.5 is exact, and MySQL rounds its .5 up. Amount is a FLOAT column, stored approximately, and for those MySQL rounds a .5 to the nearest even number. Board questions use typed numbers, so "a 5 rounds up" is the rule to learn.
MOD(m, n) gives the remainder when m is divided by n. MOD(x, 2) is 0 for even numbers and 1 for odd ones, which is how to find odd or even values in a column.
SQL · fixed values, then a column
SELECT MOD(17, 5), MOD(20, 4);
SELECT Name, Marks, MOD(Marks, 2) FROM STUDENT
WHERE Class = '12A';
MySQL output
mysql> SELECT MOD(17, 5), MOD(20, 4);
+------------+------------+
| MOD(17, 5) | MOD(20, 4) |
+------------+------------+
| 2 | 0 |
+------------+------------+
1 row in set
mysql> SELECT Name, Marks, MOD(Marks, 2) FROM STUDENT
-> WHERE Class = '12A';
+-------+-------+---------------+
| Name | Marks | MOD(Marks, 2) |
+-------+-------+---------------+
| Riya | 91 | 1 |
| Neha | 85 | 1 |
| Arjun | 72 | 0 |
+-------+-------+---------------+
3 rows in set
Exam tip
Board output questions love ROUND with a negative d: ROUND(1567.8, −2) = 1600, ROUND(1567.8, −1) = 1570. Work inside out for nested calls: ROUND(MOD(29, 6) * 2.456, 1) → MOD gives 5 → 12.28 → 12.3.
SQLF.02 · SQL Functions
Text Functions
12-IP
Text functions work on strings: fixed text in quotes, or a text column such as Name or City. Positions in a string start at 1, not 0 as in Python.
SUBSTR(s, pos, len) takes len characters starting at position pos. MID and SUBSTRING are the same function with other names. Leave out len to take everything to the end. A negative pos counts from the right end.
Start at position 3 and take 4 characters. A negative start counts from the right: −3 is the i of "ics".
INSTR(s, sub) gives the position where sub first appears in s, or 0 if it doesn't appear at all. Like every text comparison in MySQL, it ignores case: in 'Aman' the capital A at position 1 counts as an 'a'.
The position of the first character of the match is returned. If there is no match, the answer is 0.
TRIM(s) removes spaces from both ends. Spaces in the middle stay. Spaces can't be seen in a result, so LENGTH shows what was removed. First on ' KV ' (two spaces each side):
Spaces at both ends go; a space between words would stay. LENGTH drops from 6 to 2.
Then on a name typed with two spaces before it and one after, as often happens with data entered by hand:
SQL · trimming a column
INSERT INTO STUDENT
VALUES (7, ' Sara ', '12C', 'F', 88, 'Pune');
SELECT Name, LENGTH(Name) AS Len,
LENGTH(LTRIM(Name)) AS L,
LENGTH(RTRIM(Name)) AS R,
LENGTH(TRIM(Name)) AS T
FROM STUDENT WHERE RollNo = 7;
MySQL output
mysql> INSERT INTO STUDENT
-> VALUES (7, ' Sara ', '12C', 'F', 88, 'Pune');
Query OK, 1 row affected
mysql> SELECT Name, LENGTH(Name) AS Len,
-> LENGTH(LTRIM(Name)) AS L,
-> LENGTH(RTRIM(Name)) AS R,
-> LENGTH(TRIM(Name)) AS T
-> FROM STUDENT WHERE RollNo = 7;
+---------+-----+------+------+------+
| Name | Len | L | R | T |
+---------+-----+------+------+------+
| Sara | 7 | 5 | 6 | 4 |
+---------+-----+------+------+------+
1 row in set
Function
Gives
Example → result
UPPER / UCASE
Capitals
UPPER('kv') → KV
LOWER / LCASE
Small letters
LOWER('KV') → kv
SUBSTR / MID / SUBSTRING
Part of a string
SUBSTR('Informatics', 3, 4) → form
LENGTH
Number of characters
LENGTH('Informatics') → 11
LEFT · RIGHT
First · last n characters
LEFT('Informatics', 2) → In
INSTR
Position of a substring, or 0
INSTR('Informatics', 'for') → 3
LTRIM · RTRIM · TRIM
Remove spaces: left · right · both
LENGTH(TRIM(' KV ')) → 2
Exam tip
Positions start at 1. INSTR returns 0 (not −1, as Python's find does) when the text is not found. On a column, the function gives one answer per row, so the output has as many rows as the WHERE keeps.
SQLF.03 · SQL Functions
Date Functions
12-IP
Date functions take a date (or date and time) and return a part of it. Dates are written 'YYYY-MM-DD'; a date with a time is 'YYYY-MM-DD HH:MM:SS'. The table examples use FEE.PaidAt, a DATETIME column that holds the date and time each fee was paid.
Each function picks one part of the value. DAYNAME (dotted arrow) needs the whole date, because the weekday depends on the year, month and day together.
NOW() gives the current date and time of the computer running MySQL. It takes no value, so it has no table version. The result below is the moment this page was built; yours will differ.
SQL · now, and today's date
SELECT NOW();
SELECT DATE(NOW());
MySQL output
mysql> SELECT NOW();
+---------------------+
| NOW() |
+---------------------+
| 2026-11-19 19:59:36 |
+---------------------+
1 row in set
mysql> SELECT DATE(NOW());
+-------------+
| DATE(NOW()) |
+-------------+
| 2026-11-19 |
+-------------+
1 row in set
DAY gives a number (14); DAYNAME gives a word (Saturday). MONTH gives 3; MONTHNAME gives March. "Which function gives the current date and time?" → NOW().
SQLF.04 · SQL Functions
Aggregate Functions
12-CS12-IP
An aggregate function takes a whole column (or a group of rows, Chapter 8) and returns one value. All of them ignore NULL, except COUNT(*).
SELECT MAX(Name), MIN(City), COUNT(Class) FROM STUDENT;
MySQL output
+-----------+-----------+--------------+
| MAX(Name) | MIN(City) | COUNT(Class) |
+-----------+-----------+--------------+
| Riya | Delhi | 6 |
+-----------+-----------+--------------+
1 row in set
Function
On STUDENT.Marks
NULL?
MAX · MIN
91 · 67
ignored
SUM
393
ignored
AVG
78.6000
ignored (÷ 5, not 6)
COUNT(Marks)
5
ignored
COUNT(*)
6
counted
Exam tip
"Which aggregate function returns the cardinality of a table?" → COUNT(*). "Total number of distinct categories" → COUNT(DISTINCT category). Aggregates cannot go in WHERE (see HAVING, GRP.02).
Chapter practice · SQLF
Check your understanding
Predict the output
SELECT ROUND(MOD(29, 6) * 2.456, 1);
+------------------------------+
| ROUND(MOD(29, 6) * 2.456, 1) |
+------------------------------+
| 12.3 |
+------------------------------+
1 row in set
+-------------------------+-------------------+
| MONTHNAME('2025-09-29') | DAY('2025-09-29') |
+-------------------------+-------------------+
| September | 29 |
+-------------------------+-------------------+
1 row in set
MONTHNAME gives the name; DAY gives the day of the month as a number.
SELECT COUNT(Marks), AVG(Marks) FROM STUDENT
WHERE Class = '12A';
+--------------+------------+
| COUNT(Marks) | AVG(Marks) |
+--------------+------------+
| 3 | 82.6667 |
+--------------+------------+
1 row in set
12A has Riya 91, Neha 85 and Arjun 72: (91 + 85 + 72) ÷ 3 = 82.6667.
Self-check quiz
ROUND(2378.56, -2) gives:
−2 rounds to the nearest hundred.
MOD(23, 4) gives:
23 = 4 × 5 + 3.
MID('INFORMATICS', 3, 4) gives:
Start at the 3rd letter, F, and take 4.
INSTR('Computer', 'x') gives:
0 means not found.
SELECT MONTHNAME('2025-09-29'); returns:
The name of the month.
Which function gives the current date and time?
NOW() gives both.
A column has values 10, NULL, 20. AVG of it is:
NULL is ignored: (10 + 20) ÷ 2.
Which counts the rows of a table, NULLs included?
COUNT(*) gives the cardinality.
Your score
– / 8
Answer every question to see your score.
One-page revision
POWER(x, y)x to the power y
ROUND(x, d)d places; −1 tens, −2 hundreds
MOD(m, n)remainder
UPPER · LOWERalso UCASE · LCASE
SUBSTR(s, p, n)also MID, SUBSTRING; from 1
LEFT · RIGHT · LENGTHfirst n · last n · how many
INSTR(s, t)position, or 0
TRIM · LTRIM · RTRIMspaces: both · left · right
NOW() · DATE()date-time now · date part
MONTH · MONTHNAME3 · March
DAY · DAYNAME · YEAR14 · Saturday · 2026
MAX MIN SUM AVGignore NULL
COUNT(col) · COUNT(*)non-NULL values · all rows
Chapter 8 of 10 · GRP
Grouping Data
Aggregate functions on their own give one answer for the whole table. GROUP BY gives one answer per group: per class, per city, per department. HAVING then chooses which groups to show.
GRP.01 · Grouping Data
GROUP BY
12-CS12-IP
GROUP BY col puts rows with the same value in col into one group, and the query returns one row per group. Aggregate functions in SELECT are then worked out separately for each group.
Six rows become three: one for each class. 12C's average is NULL because its only mark is NULL.
SQL · count and average per class
SELECT Class, COUNT(*), AVG(Marks)
FROM STUDENT GROUP BY Class;
SELECT City, MAX(Marks) AS Top FROM STUDENT
GROUP BY City ORDER BY Top DESC;
MySQL output
+--------+------+
| City | Top |
+--------+------+
| Delhi | 91 |
| Jaipur | 78 |
| Kochi | 72 |
| Mumbai | NULL |
+--------+------+
4 rows in set
In a grouped query, SELECT may list only the grouped column and aggregates. A plain column such as Name has many values in one group, so MySQL refuses:
SQL · a column that isn't grouped
SELECT Name, COUNT(*) FROM STUDENT GROUP BY Class;
MySQL output
ERROR 1055 (42000): Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'schooldb.STUDENT.Name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
GROUP BY
ORDER BY
Does
Combines rows with the same value into one row per group
Sorts the rows of the result
Rows in result
One per group (usually fewer)
Same number as before
Used with
Aggregate functions
Any columns
Order in query
After WHERE
Last
Exam tip
"Gender-wise average" or "category-wise highest price" means GROUP BY that column: SELECT Gender, AVG(Marks) FROM STUDENT GROUP BY Gender;. Put the grouped column in SELECT too, or the output won't say which group is which.
GRP.02 · Grouping Data
HAVING
12-CS12-IP
HAVING keeps only the groups that meet a condition, and the condition may use aggregate functions. It comes right after GROUP BY. To see why WHERE can't do this job, look at the order in which MySQL works through a query (there is more on this in the order of clauses):
The two highlighted steps both filter: WHERE filters single rows, HAVING filters whole groups.
SQL · cities with more than one student
SELECT City, COUNT(*) FROM STUDENT
GROUP BY City HAVING COUNT(*) > 1;
MySQL output
+--------+----------+
| City | COUNT(*) |
+--------+----------+
| Delhi | 2 |
| Jaipur | 2 |
+--------+----------+
2 rows in set
WHERE and HAVING can be used together. Here WHERE first keeps only the boys, then GROUP BY makes groups, then HAVING keeps groups whose average is above 70:
SQL · WHERE, then GROUP BY, then HAVING
SELECT Class, AVG(Marks) FROM STUDENT
WHERE Gender = 'M'
GROUP BY Class HAVING AVG(Marks) > 70;
MySQL output
+-------+------------+
| Class | AVG(Marks) |
+-------+------------+
| 12B | 78.0000 |
| 12A | 72.0000 |
+-------+------------+
2 rows in set
Putting the aggregate in WHERE instead is an error:
SQL · COUNT in WHERE
SELECT City, COUNT(*) FROM STUDENT
WHERE COUNT(*) > 1 GROUP BY City;
MySQL output
ERROR 1111 (HY000): Invalid use of group function
WHERE
HAVING
Filters
Single rows
Groups
Runs
Before GROUP BY
After GROUP BY
Aggregates allowed?
No
Yes
Example
WHERE Gender = 'M'
HAVING COUNT(*) > 1
Exam tip
"Find the error" questions: WHERE COUNT(*) > 5 must become HAVING COUNT(*) > 5, and HAVING must come after GROUP BY. Full order: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY ….
Chapter practice · GRP
Check your understanding
Write the output
SELECT Gender, MAX(Marks) FROM STUDENT
GROUP BY Gender;
+--------+------------+
| Gender | MAX(Marks) |
+--------+------------+
| F | 91 |
| M | 78 |
+--------+------------+
2 rows in set
The girls' best is Riya's 91; the boys' best is Aman's 78 (Kabir's NULL is ignored).
SELECT City, COUNT(*) FROM STUDENT
GROUP BY City HAVING MIN(Marks) > 70;
+-------+----------+
| City | COUNT(*) |
+-------+----------+
| Delhi | 2 |
| Kochi | 1 |
+-------+----------+
2 rows in set
Lowest marks: Delhi 85, Jaipur 67, Kochi 72, Mumbai NULL. NULL > 70 is not true, so Mumbai is dropped too.
Self-check quiz
Which statement correctly gives the average marks of students gender-wise?
"-wise" means GROUP BY.
Which clause filters groups made by GROUP BY?
HAVING works on groups.
What is wrong with SELECT Dept, COUNT(*) FROM staff WHERE COUNT(*) > 2 GROUP BY Dept;?
MySQL gives "Invalid use of group function".
In which order must these clauses appear?
Rows are filtered, then grouped, then groups are filtered.
Your score
– / 4
Answer every question to see your score.
One-page revision
GROUP BY colone result row per value
With aggregatesworked out per group
SELECT listgrouped columns + aggregates only
HAVINGfilters groups; aggregates OK
WHEREfilters rows; no aggregates
Clause orderWHERE → GROUP BY → HAVING → ORDER BY
Chapter 9 of 10 · JOIN
Joins
A join combines rows from two tables into one result. Here, TEACHER and DEPT are joined on their common column, DeptID.
JOIN.01 · Joins
Joins
12-CS12-IP
The two tables to be joined, as MySQL shows them. Das has no department yet (NULL), and no teacher is in Arts:
SQL · the two tables
SELECT * FROM TEACHER;
SELECT * FROM DEPT;
MySQL output
mysql> SELECT * FROM TEACHER;
+-----+-------+--------+--------+
| TID | TName | DeptID | Salary |
+-----+-------+--------+--------+
| 101 | Mehta | D1 | 62000 |
| 102 | Rao | D2 | 55000 |
| 103 | Khan | D1 | 58000 |
| 104 | Das | NULL | 50000 |
+-----+-------+--------+--------+
4 rows in set
mysql> SELECT * FROM DEPT;
+--------+----------+
| DeptID | DName |
+--------+----------+
| D1 | Science |
| D2 | Commerce |
| D3 | Arts |
+--------+----------+
3 rows in set
Listing two tables after FROM with no condition gives the Cartesian product: every row of the first table paired with every row of the second. Its degree is the sum of the two degrees, and its cardinality is the product: 4 + 2 = 6 columns, 4 × 3 = 12 rows.
Each cell is one row of SELECT * FROM TEACHER, DEPT. Most pairs mean nothing (Mehta is not in Arts); a join condition keeps only the highlighted ones.
An equi-join keeps only the pairs where the common column is equal in both tables: WHERE TEACHER.DeptID = DEPT.DeptID. When both tables have a column of the same name, write table.column to say which one. Short table aliases (TEACHER T) save typing.
Each line is one row of the equi-join; rows with no partner (dashed) don't appear. With SELECT *, DeptID appears twice, once from each table (Salary is left out of the drawing).
SQL · each teacher's department name
SELECT TName, DName, Salary
FROM TEACHER T, DEPT D
WHERE T.DeptID = D.DeptID;
A natural join joins on every column with the same name in both tables, with no condition written, and shows that common column only once, first. The rows are the same as the equi-join's.
The common column DeptID is taken from both tables and shown once, at the front. Compare the equi-join above, where SELECT * shows it twice.
"Which query gives the Cartesian product of A and B?" → SELECT * FROM A, B;. For an equi-join answer, always write the condition with the table names: WHERE GAMES.GCode = PLAYER.GCode.
Chapter practice · JOIN
Check your understanding
Predict the output
SELECT COUNT(*) FROM TEACHER, DEPT;
+----------+
| COUNT(*) |
+----------+
| 12 |
+----------+
1 row in set
The Cartesian product has 4 × 3 rows.
SELECT DName, COUNT(*) FROM TEACHER T, DEPT D
WHERE T.DeptID = D.DeptID GROUP BY DName;
An equi-join uses which operator in its condition?
"Equi" means equal.
In WHERE T.DeptID = D.DeptID, what are T and D?
Set in FROM TEACHER T, DEPT D.
How is a natural join different from an equi-join?
And no condition is written.
Your score
– / 4
Answer every question to see your score.
One-page revision
Cartesian productevery row × every row
Degree · cardinalityadd · multiply
Equi-joinWHERE A.col = B.col
table.columnwhen both tables have the column
Table aliasFROM TEACHER T
NATURAL JOINsame-named columns, shown once
Chapter 10 of 10 · PYSQL
Python and SQL Connectivity
A Python program can send SQL to MySQL and use the results, so a school's fee software or a shop's billing app can keep its data in a database. Every program here was run against the sample database; Python prints NULL as None.
PYSQL.01 · Python and SQL Connectivity
Connecting SQL with Python
12-CS
Python talks to MySQL through a connector library, mysql.connector. Install it once from the command prompt with pip install mysql-connector-python. Every program then follows the same six steps:
The same steps in every program. Step 5 depends on the query: read the rows of a SELECT, or commit a change.
mycon.connect(host=…, user=…, passwd=…, database=…) opens a connection to the MySQL server and returns a connection object. host is the computer running MySQL ('localhost' for your own); user and passwd (or password) are your MySQL login; database is the database to use. con.is_connected() returns True if it worked.
con.cursor() creates a cursor: the object that sends queries and holds their results, row by row, like a pointer moving down the result. All queries go through a cursor.
Python · connect and make a cursor
import mysql.connector as mycon
con = mycon.connect(host='localhost', user='root',
passwd='', database='schooldb')
if con.is_connected():
print('Connected to MySQL')
cur = con.cursor()
print(cur)
con.close()
Output
Connected to MySQL
CMySQLCursor: (Nothing executed yet)
Note
This page's MySQL has an empty password, so the examples use passwd=''. On your computer, put the password you set when installing MySQL. A wrong password raises mysql.connector.errors.ProgrammingError.
Exam tip
"Which line correctly connects to a MySQL database?" → mysql.connector.connect(host='localhost', user='root', passwd='…', database='school'). The method is connect() on the module, and cursor() on the connection.
After a SELECT, cur.fetchall() returns all remaining rows as a list of tuples, one tuple per row. An empty result gives an empty list [].
Python · read every row
import mysql.connector as mycon
con = mycon.connect(host='localhost', user='root',
passwd='', database='schooldb')
cur = con.cursor()
cur.execute('SELECT RollNo, Name, Marks FROM STUDENT')
rows = cur.fetchall()
for row in rows:
print(row)
print('Rows:', cur.rowcount)
con.close()
cur.fetchone() returns the next single row as a tuple, and moves the cursor on. When no rows are left it returns None. A later fetchall() returns only the rows not yet fetched.
cur.rowcount is a property, not a method (no brackets). After a SELECT it is the number of rows fetched so far; after INSERT, UPDATE or DELETE it is the number of rows affected.
Python · one row at a time
import mysql.connector as mycon
con = mycon.connect(host='localhost', user='root',
passwd='', database='schooldb')
cur = con.cursor()
cur.execute('SELECT Name, City FROM STUDENT')
print(cur.fetchone())
print(cur.fetchone())
print('rowcount so far:', cur.rowcount)
rest = cur.fetchall()
print('left over:', len(rest))
print('rowcount now:', cur.rowcount)
con.close()
Output
('Riya', 'Delhi')
('Aman', 'Jaipur')
rowcount so far: 2
left over: 4
rowcount now: 6
Changes made by INSERT, UPDATE and DELETE are only saved when you call con.commit() on the connection. If the program closes the connection without committing, MySQL throws the changes away:
Python · forgetting commit()
import mysql.connector as mycon
con = mycon.connect(host='localhost', user='root',
passwd='', database='schooldb')
cur = con.cursor()
cur.execute("DELETE FROM STUDENT WHERE City = 'Delhi'")
print(cur.rowcount, 'deleted, but not committed')
con.close()
con = mycon.connect(host='localhost', user='root',
passwd='', database='schooldb')
cur = con.cursor()
cur.execute("SELECT COUNT(*) FROM STUDENT "
"WHERE City = 'Delhi'")
print('Delhi students still there:', cur.fetchone()[0])
con.close()
Output
2 deleted, but not committed
Delhi students still there: 2
Call
On
Returns / does
execute(q)
cursor
Runs one SQL statement
fetchone()
cursor
Next row as a tuple, or None
fetchall()
cursor
Remaining rows as a list of tuples
rowcount
cursor
Rows fetched so far, or rows affected
commit()
connection
Saves INSERT / UPDATE / DELETE
close()
connection
Ends the connection
Exam tip
commit() belongs to the connection (con.commit()), not the cursor. The connector also has fetchmany(n), which returns the next n rows as a list.
PYSQL.03 · Python and SQL Connectivity
Insert, Update and Delete from Python
12-CS
Changing data from Python takes three lines each time: execute the INSERT, UPDATE or DELETE, commit it, and (optionally) check rowcount to see how many rows it affected. Long SQL strings can be split over lines; Python joins strings that are written next to each other.
Python · insert, update, delete
import mysql.connector as mycon
con = mycon.connect(host='localhost', user='root',
passwd='', database='schooldb')
cur = con.cursor()
cur.execute("INSERT INTO STUDENT VALUES "
"(7, 'Sara', '12C', 'F', 88, 'Pune')")
con.commit()
print(cur.rowcount, 'record inserted')
cur.execute("UPDATE STUDENT SET Marks = Marks + 2 "
"WHERE Class = '12B'")
con.commit()
print(cur.rowcount, 'records updated')
cur.execute("DELETE FROM STUDENT WHERE RollNo = 4")
con.commit()
print(cur.rowcount, 'record deleted')
cur.execute('SELECT COUNT(*) FROM STUDENT')
print('Students now:', cur.fetchone()[0])
con.close()
Output
1 record inserted
2 records updated
1 record deleted
Students now: 6
Common mistake
Text values inside the SQL string still need their own quotes: "WHERE Class = '12B'". Use double quotes for the Python string so the single quotes can go inside.
Exam tip
A board answer for "insert a record" must show execute() and commit(). Missing commit() costs the mark, because the row is never saved.
PYSQL.04 · Python and SQL Connectivity
Parameterised Queries
12-CS
Programs usually need queries with values that change, such as marks typed in by a user. There are two ways to put Python values into a query.
Write %s where each value goes, and pass the values as a tuple, the second argument of execute(). The connector puts each value in, with the right quotes. A single value still needs a tuple: (80,), with the comma. Use %s for every type, numbers included.
Python · values as a tuple
import mysql.connector as mycon
con = mycon.connect(host='localhost', user='root',
passwd='', database='schooldb')
cur = con.cursor()
q = 'SELECT Name, Marks FROM STUDENT ' \
'WHERE Marks > %s'
cur.execute(q, (80,))
print(cur.fetchall())
q = 'INSERT INTO STUDENT VALUES (%s, %s, %s, %s, %s, %s)'
cur.execute(q, (9, 'Tara', '12C', 'F', 79, 'Agra'))
con.commit()
print(cur.rowcount, 'row added')
con.close()
String format() (or an f-string) builds the whole query as text first, putting each value where a {} is. Text values then need quotes written around the {} by you: '{}'.
Python · building the string
import mysql.connector as mycon
con = mycon.connect(host='localhost', user='root',
passwd='', database='schooldb')
cur = con.cursor()
city, marks = 'Jaipur', 70
q = ("SELECT Name FROM STUDENT WHERE City = '{}' "
"AND Marks > {}").format(city, marks)
print(q)
cur.execute(q)
print(cur.fetchall())
con.close()
Output
SELECT Name FROM STUDENT WHERE City = 'Jaipur' AND Marks > 70
[('Aman',)]
%s placeholders
format()
Values passed
As a tuple to execute()
Inside the string, before execute()
Quotes for text
Added by the connector
Written by you: '{}'
Safety
Safe: input can't change the query
Risky if values come from a user
Exam tip
cur.execute(q, (80,)): the values go in a tuple, even one value. Don't put quotes around %s in the query; the connector adds them.
PYSQL.05 · Python and SQL Connectivity
Database Connectivity Applications
12-CS
A real application wraps each task in a function: one to add a record, one to search, one to update. Each function connects, runs its query, commits or fetches, and closes. Here, one function adds a student and another lists the toppers:
Python · a small student-records app
import mysql.connector as mycon
def connect():
return mycon.connect(host='localhost', user='root',
passwd='', database='schooldb')
def add_student(roll, name, cls, gen, marks, city):
con = connect()
cur = con.cursor()
q = 'INSERT INTO STUDENT VALUES (%s,%s,%s,%s,%s,%s)'
cur.execute(q, (roll, name, cls, gen, marks, city))
con.commit()
con.close()
def toppers(cutoff):
con = connect()
cur = con.cursor()
cur.execute('SELECT Name, Marks FROM STUDENT '
'WHERE Marks >= %s '
'ORDER BY Marks DESC', (cutoff,))
for name, marks in cur.fetchall():
print(f'{name:<6}{marks}')
con.close()
add_student(10, 'Om', '12C', 'M', 94, 'Patna')
toppers(85)
Output
Om 94
Riya 91
Neha 85
Board questions usually ask for one such function, e.g. "write AddStudent() to input the details of a student and insert them into table STUDENT of database SCHOOL". Write it the same way, with the values read by input() (and int() for numbers) instead of parameters.
A menu program puts the functions in a while loop: 1 to add, 2 to show, 3 to exit.
For a search, fetch the rows and print them, or print "not found" when fetchall() returns an empty list.
Exam tip
A full-marks function shows all six steps: import, connect (with the database name), cursor, execute (with %s and a tuple), commit or fetchall, and close.
Chapter practice · PYSQL
Check your understanding
Predict the output
import mysql.connector as mycon
con = mycon.connect(host='localhost', user='root',
passwd='', database='schooldb')
cur = con.cursor()
cur.execute("SELECT Name FROM STUDENT "
"WHERE Class = '12A'")
cur.fetchone()
data = cur.fetchall()
print(data, cur.rowcount)
con.close()
[('Neha',), ('Arjun',)] 3
fetchone() took Riya; fetchall() gets the other two; rowcount is 3, the total fetched.
Self-check quiz
Which module connects Python to MySQL in the syllabus?
Installed as mysql-connector-python.
fetchall() returns:
One tuple per row.
What does fetchone() return when no rows are left?
None means the result is used up.
After an INSERT, which call saves the change?
commit() is a connection method.
After UPDATE … WHERE Class = '12B' changes 2 rows, cur.rowcount is:
For changes, rowcount is the number of rows affected.
Which correctly passes one value to a query with %s?
A tuple with one item needs the comma.
Which method creates a cursor?
The connection makes the cursor.
Why is the %s method safer than format() for values typed by a user?