A Python program uses mysql.connector to run an INSERT statement through a cursor. Which call makes the new row permanent in the database?

connection.commit() on the connection object

Structured Query Language (SQL)

SQL is the language every relational database understands: one statement creates a table, another fills it, and a third asks a question of it. The syllabus uses MySQL, a free and open-source relational DBMS. This chapter covers the data types and constraints used to define a table, the statements that create, change and remove tables (DDL) and those that add, change and delete rows (DML), and then the heart of the paper — SELECT, with its filters, sorting, functions, grouping and joins. It ends with how a Python program talks to MySQL. Most SQL questions give a table and ask for the result of a query, so every rule here is illustrated on the STUDENT table below.

Data Types and Constraints

Common MySQL data types
Type	Holds
CHAR(n)	fixed-length text of n characters (0–255); a shorter value is padded with spaces to n
VARCHAR(n)	variable-length text of up to n characters; only the actual length is stored
INT	whole numbers (4 bytes)
FLOAT	numbers with a decimal point
DATE	a date in the format 'YYYY-MM-DD'

NOT NULL — the column must have a value in every row.
UNIQUE — no two rows may have the same value in the column.
DEFAULT — a value used when none is supplied.
PRIMARY KEY — identifies each row: unique and not NULL together.
FOREIGN KEY — values must exist in the primary key of the referenced table.

DDL and DML Statements

SQL statements fall into two families. Data Definition Language (DDL) works on the structure: CREATE DATABASE, CREATE TABLE, ALTER TABLE (to add, drop or modify a column, or add a primary or foreign key) and DROP TABLE / DROP DATABASE. Data Manipulation Language (DML) works on the rows: INSERT INTO … VALUES …, UPDATE … SET … WHERE … and DELETE FROM … WHERE …. USE dbname selects a database, SHOW TABLES lists its tables and DESCRIBE tablename (or DESC) shows a table's structure. Keywords are not case-sensitive, and each statement ends with a semicolon.

For example, the STUDENT table used throughout this chapter can be created and given a row like this:
CREATE TABLE STUDENT (RollNo INT PRIMARY KEY, Name VARCHAR(20) NOT NULL, Class CHAR(3), Marks INT, City VARCHAR(20));
INSERT INTO STUDENT VALUES (1, 'Asha', '12A', 88, 'Delhi');
UPDATE STUDENT SET Marks = Marks + 2 WHERE RollNo = 5;

⚠️ DELETE, DROP and a missing WHERE

DELETE FROM STUDENT; removes every row but leaves the empty table in place — it is DML. DROP TABLE STUDENT; removes the table itself, structure and all — it is DDL. An UPDATE or DELETE without WHERE acts on every row, which is rarely what was meant.

Querying with SELECT

All the examples that follow use this table, in which Meena's marks have not been entered and are NULL:

Table: STUDENT
RollNo	Name	Class	Marks	City
1	Asha	12A	88	Delhi
2	Ravi	12B	72	Pune
3	Meena	12A	NULL	Delhi
4	Kabir	12B	91	Jaipur
5	Tara	12A	65	Pune

SELECT Name, Marks FROM STUDENT WHERE City = 'Pune'; — choose columns, filter rows. SELECT * takes every column; DISTINCT removes duplicate values (SELECT DISTINCT City gives 3 rows); AS renames a column in the output.
Relational and logical operators: =, <>, <, >, <=, >= with AND, OR and NOT.
BETWEEN a AND b is inclusive of both ends: Marks BETWEEN 72 AND 88 gives Asha (88) and Ravi (72).
IN (list) tests membership: City IN ('Pune', 'Jaipur').
LIKE matches patterns: % stands for any number of characters (including none) and _ for exactly one. Name LIKE '_a%' — a as the second letter — gives Ravi, Kabir and Tara.
NULL is tested only with IS NULL / IS NOT NULL; Marks = NULL is never true. Any comparison with NULL is unknown, so Meena fails Marks > 70 — yet she is returned by Marks > 70 OR City = 'Delhi', because the second condition is true.
ORDER BY column sorts the result, ascending by default; add DESC for descending.

Functions, GROUP BY and HAVING

Single-row functions — one result per row
Kind	Function and example
Math	`POWER(2, 3)` → 8; `ROUND(25.678, 1)` → 25.7; `MOD(17, 5)` → 2
String	`UPPER('sql')` / `UCASE` → SQL; `LOWER` / `LCASE`; `LENGTH('India')` → 5; `LEFT('database', 4)` → data; `RIGHT('database', 4)` → base; `MID('Informatics', 3, 4)` (also SUBSTRING) → form; `INSTR('computer', 'put')` → 4; `LTRIM`, `RTRIM`, `TRIM` remove spaces
Date	`NOW()` current date and time; `DATE()`, `MONTH()`, `MONTHNAME()`, `YEAR()`, `DAY()`, `DAYNAME()` extract parts — `MONTHNAME('2024-08-15')` → August

Aggregate functions take a whole column and return one value: MAX, MIN, SUM, AVG and COUNT. They ignore NULL — on STUDENT, SUM(Marks) is 316 and AVG(Marks) is 316 / 4 = 79.0000 (MySQL shows four decimal places), not 316 / 5. COUNT(*) counts rows, so it gives 5, while COUNT(Marks) counts non-NULL values and gives 4. GROUP BY splits the rows into groups with the same value and applies the aggregate to each: SELECT City, SUM(Marks) FROM STUDENT GROUP BY City; gives Delhi 88, Jaipur 91 and Pune 137. HAVING filters those groups, as WHERE filters rows: SELECT Class, COUNT(*) FROM STUDENT GROUP BY Class HAVING COUNT(*) > 2; returns only 12A with 3.

⚠️ WHERE before grouping, HAVING after

An aggregate cannot appear in WHERE, because WHERE is applied to individual rows before any groups exist. WHERE COUNT(*) > 2 is an error; HAVING COUNT(*) > 2 is correct. A condition on an ordinary column, such as Marks > 60, belongs in WHERE.

Operations on Relations and Joins

Relations can be combined like sets. UNION gives the tuples in either of two relations with the same attributes, without duplicates; INTERSECT the tuples in both; and MINUS (set difference) the tuples in the first but not the second. The Cartesian product of two relations pairs every tuple of one with every tuple of the other: its degree is the sum of the two degrees and its cardinality the product of the two cardinalities — a 5-row, 5-column table and a 3-row, 2-column table give 15 rows of 7 columns.

A join keeps only the meaningful pairs of a Cartesian product. An equi-join keeps the pairs whose common attribute is equal: with a table FEES(RollNo, Paid), SELECT S.Name, F.Paid FROM STUDENT S, FEES F WHERE S.RollNo = F.RollNo; pairs each fee record with its student and shows the common column twice if both are selected with *. A natural join (NATURAL JOIN) matches on the columns with the same name automatically and shows that common column only once.

Connecting Python with MySQL

A Python program reaches MySQL through the mysql.connector module. The steps are always the same: import the module; open a connection with connect(), giving host, user, password and database; create a cursor from the connection; run SQL with cursor.execute(query); read results with fetchone() (the next row as a tuple, or None when none remain), fetchmany(n) or fetchall() (all remaining rows as a list of tuples); and, after any INSERT, UPDATE or DELETE, call commit() on the connection, or the change is not saved. cursor.rowcount gives the number of rows the last statement fetched or affected.

A complete example that raises Tara's marks and then reads the Pune students back:
import mysql.connector
con = mysql.connector.connect(host="localhost", user="root", password="xyz", database="school")
cur = con.cursor()
cur.execute("UPDATE STUDENT SET Marks = 70 WHERE RollNo = 5")
con.commit()
cur.execute("SELECT Name, Marks FROM STUDENT WHERE City = %s", ("Pune",))
for row in cur.fetchall():
print(row)
con.close()
The %s placeholder with a tuple of values lets the connector insert the value safely instead of building the query by joining strings.

Key takeaways

CHAR is fixed-length, VARCHAR variable-length; PRIMARY KEY means unique and NOT NULL together.
DDL (CREATE, ALTER, DROP) changes structure; DML (INSERT, UPDATE, DELETE) changes rows. DELETE empties a table, DROP removes it.
BETWEEN is inclusive; LIKE uses % for any run of characters and _ for exactly one; NULL is tested only with IS NULL.
Aggregates ignore NULL: COUNT(*) counts rows, COUNT(column) counts non-NULL values, AVG divides by the non-NULL count.
GROUP BY forms groups; HAVING filters groups and may use aggregates, WHERE filters rows and may not.
A Cartesian product adds degrees and multiplies cardinalities; an equi-join keeps pairs with equal common values.
mysql.connector: connect(), cursor(), execute(), fetchone()/fetchall(), and commit() after any change.

Practice questions (8)

Attempt each one before opening the answer. Every explanation names the tempting wrong option as well as the right one, because that is where marks are lost.

Table: STUDENT
RollNo Name Class Marks City
1 Asha 12A 88 Delhi
2 Ravi 12B 72 Pune
3 Meena 12A NULL Delhi
4 Kabir 12B 91 Jaipur
5 Tara 12A 65 Pune
What is the output of SELECT COUNT(*), COUNT(Marks) FROM STUDENT; on the table above?
1. 5 and 5
2. 4 and 4
3. 5 and 4
4. 4 and 5
Show answer
Answer: C — 5 and 4
COUNT() counts rows, whatever they contain, so it gives 5. COUNT(Marks) counts only the non-NULL values in that column, and Meena's marks are NULL, so it gives 4. Five and five treats NULL as a value to be counted; four and four applies the NULL rule to COUNT() as well, which counts every row.
Table: STUDENT
RollNo Name Class Marks City
1 Asha 12A 88 Delhi
2 Ravi 12B 72 Pune
3 Meena 12A NULL Delhi
4 Kabir 12B 91 Jaipur
5 Tara 12A 65 Pune
What does SELECT AVG(Marks) FROM STUDENT; return for the table above?
1. 63.2000
2. 79.0000
3. NULL
4. 316
Show answer
Answer: B — 79.0000
AVG ignores NULL, so it adds the four known marks, 88 + 72 + 91 + 65 = 316, and divides by 4, giving 79 (MySQL displays 79.0000). 63.2 divides 316 by all five rows, as if Meena had scored zero. A single NULL does not make the whole aggregate NULL, and 316 is the SUM, not the average.
Table: STUDENT
RollNo Name Class Marks City
1 Asha 12A 88 Delhi
2 Ravi 12B 72 Pune
3 Meena 12A NULL Delhi
4 Kabir 12B 91 Jaipur
5 Tara 12A 65 Pune
Which names are returned by SELECT Name FROM STUDENT WHERE Name LIKE '_a%'; on the table above?
1. Asha, Meena and Tara
2. Asha only
3. Ravi and Tara only
4. Ravi, Kabir and Tara
Show answer
Answer: D — Ravi, Kabir and Tara
The underscore matches exactly one character and % any number after it, so the pattern asks for names whose second letter is a: Ravi, Kabir and Tara. Asha, Meena and Tara are the names ending in a, the result of '%a'. Asha alone reads the pattern as "starting with a", and leaving out Kabir misses that % also matches the remaining letters of a longer name.
Table: STUDENT
RollNo Name Class Marks City
1 Asha 12A 88 Delhi
2 Ravi 12B 72 Pune
3 Meena 12A NULL Delhi
4 Kabir 12B 91 Jaipur
5 Tara 12A 65 Pune
What is the output of SELECT Class, COUNT(*) FROM STUDENT GROUP BY Class HAVING COUNT(*) > 2; on the table above?
1. One row: 12A, 3
2. Two rows: 12A, 3 and 12B, 2
3. One row: 12A, 2
4. No rows, since no class has more than 2 students with marks
Show answer
Answer: A — One row: 12A, 3
GROUP BY Class forms two groups, 12A with three rows (Asha, Meena, Tara) and 12B with two; HAVING keeps only groups with more than two, so one row, 12A and 3, remains. Showing 12B as well ignores HAVING. COUNT(*) counts rows, so Meena's NULL marks do not reduce 12A to two — that reasoning would apply to COUNT(Marks).
Table: STUDENT
RollNo Name Class Marks City
1 Asha 12A 88 Delhi
2 Ravi 12B 72 Pune
3 Meena 12A NULL Delhi
4 Kabir 12B 91 Jaipur
5 Tara 12A 65 Pune
How many rows does SELECT * FROM STUDENT WHERE Marks > 70 OR City = 'Delhi'; return for the table above?
1. 3
2. 2
3. 4
4. 5
Show answer
Answer: C — 4
Asha (88), Ravi (72) and Kabir (91) satisfy Marks > 70. Meena's marks are NULL, so that comparison is unknown, but City = 'Delhi' is true, and unknown OR true is true — she is returned too, making 4. Answering 3 drops Meena by treating any NULL comparison as excluding the row; that would be right with AND, not OR. Tara fails both conditions.
Which of the following SQL commands is a Data Definition Language (DDL) command?
1. UPDATE
2. INSERT
3. DELETE
4. ALTER
Show answer
Answer: D — ALTER
ALTER TABLE changes the structure of a table — adding, dropping or modifying a column or a key — and structure is what DDL defines. UPDATE, INSERT and DELETE all act on the rows stored in a table without changing its design, which makes them DML. DELETE is the usual trap, because it sounds like DROP, but it only removes rows.
What is the output of SELECT MID('Informatics', 3, 4); in MySQL?
1. form
2. orma
3. nfor
4. for
Show answer
Answer: A — form
MID(string, position, length) counts positions from 1 in SQL: I is 1, n is 2, f is 3, so four characters starting at position 3 are f, o, r, m — form. nfor counts from 0, as Python indexing does, and orma starts one place late. for takes only three characters, as if the third argument were an end position rather than a length.
A Python program uses mysql.connector to run an INSERT statement through a cursor. Which call makes the new row permanent in the database?
1. cursor.fetchall()
2. connection.commit() on the connection object
3. cursor.execute() a second time
4. print(cursor.rowcount)
Show answer
Answer: B — connection.commit() on the connection object
execute() sends the INSERT, but the change is saved only when commit() is called on the connection; without it the row is lost when the connection closes. fetchall() reads the results of a SELECT and changes nothing, running execute() again would only repeat the insertion, and rowcount merely reports how many rows the last statement affected.