SQL you can run: queries for the board
Part of the free LogicWiz Python course, Pack A: CBSE Class 12 Board Pack. The examples and practice run in the app.
Databases are 20 of your 70 theory marks, and 4 more come from SQL queries in the practical. Most students only ever write SQL on paper. Python ships with a small SQL database called SQLite, so here you can run every query and see the answer.
You'll learn to
- Use the board terms: relation, degree, cardinality, keys, DDL and DML
- Create a table, insert rows, and filter with WHERE, IN, BETWEEN, LIKE and IS NULL
- Sort and summarise with ORDER BY, aggregates, GROUP BY and HAVING
- Update and delete rows, and join two tables
Learn
The words the board uses
- A table is a relation; a row is a tuple; a column is an attribute.
- Degree = number of columns. Cardinality = number of rows.
- Primary key: uniquely identifies each row, never NULL. Other columns that could be the key are candidate keys; the ones not chosen are alternate keys. A foreign key refers to the primary key of another table.
- DDL (Data Definition Language) changes structure: CREATE, ALTER, DROP.
- DML (Data Manipulation Language) changes data: INSERT, UPDATE, DELETE, and reads it with SELECT.
Our two practice tables
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(20), stream CHAR(3), marks INT, city VARCHAR(15));
INSERT INTO student VALUES (101, 'Aarav', 'SCI', 91, 'Delhi');
INSERT INTO student VALUES (102, 'Meher', 'COM', 78, 'Mumbai');
INSERT INTO student VALUES (103, 'Kabir', 'SCI', 85, 'Pune');
INSERT INTO student VALUES (104, 'Riya', 'HUM', 88, 'Delhi');
INSERT INTO student VALUES (105, 'Sneha', 'COM', 67, 'Jaipur');
INSERT INTO student VALUES (106, 'Ishaan', 'SCI', 59, NULL);
INSERT INTO student VALUES (107, 'Ananya', 'HUM', 94, 'Mumbai');
INSERT INTO student VALUES (108, 'Rohan', 'COM', 72, 'Delhi');
CREATE TABLE teacher (stream CHAR(3) PRIMARY KEY, tname VARCHAR(20), room INT);
INSERT INTO teacher VALUES ('SCI', 'Mr Rao', 12);
INSERT INTO teacher VALUES ('COM', 'Ms Kaur', 14);
INSERT INTO teacher VALUES ('HUM', 'Mrs Iyer', 9);
""")
def show(sql):
for row in cur.execute(sql):
print(row)
print("student:")
show("SELECT * FROM student")
print("teacher:")
show("SELECT * FROM teacher")
student has degree 5 and cardinality 8. Ishaan's city is NULL (unknown), which Python shows as None. show() is a 3-line helper that runs a query and prints each row.
WHERE with IN, BETWEEN, LIKE, IS NULL
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(20), stream CHAR(3), marks INT, city VARCHAR(15));
INSERT INTO student VALUES (101, 'Aarav', 'SCI', 91, 'Delhi');
INSERT INTO student VALUES (102, 'Meher', 'COM', 78, 'Mumbai');
INSERT INTO student VALUES (103, 'Kabir', 'SCI', 85, 'Pune');
INSERT INTO student VALUES (104, 'Riya', 'HUM', 88, 'Delhi');
INSERT INTO student VALUES (105, 'Sneha', 'COM', 67, 'Jaipur');
INSERT INTO student VALUES (106, 'Ishaan', 'SCI', 59, NULL);
INSERT INTO student VALUES (107, 'Ananya', 'HUM', 94, 'Mumbai');
INSERT INTO student VALUES (108, 'Rohan', 'COM', 72, 'Delhi');
CREATE TABLE teacher (stream CHAR(3) PRIMARY KEY, tname VARCHAR(20), room INT);
INSERT INTO teacher VALUES ('SCI', 'Mr Rao', 12);
INSERT INTO teacher VALUES ('COM', 'Ms Kaur', 14);
INSERT INTO teacher VALUES ('HUM', 'Mrs Iyer', 9);
""")
def show(sql):
for row in cur.execute(sql):
print(row)
print("IN:")
show("SELECT name, city FROM student WHERE city IN ('Delhi', 'Pune')")
print("BETWEEN (both ends included):")
show("SELECT name, marks FROM student WHERE marks BETWEEN 72 AND 88")
print("LIKE '_a%' (second letter a):")
show("SELECT name FROM student WHERE name LIKE '_a%'")
print("IS NULL:")
show("SELECT name FROM student WHERE city IS NULL")
= NULL never matches anything. NULL means "unknown", and unknown = unknown is not true. Always write IS NULL / IS NOT NULL.
In LIKE, % means any number of characters (even zero) and _ means exactly one character. BETWEEN 72 AND 88 includes 72 and 88.
DISTINCT, ORDER BY, aggregates, GROUP BY, HAVING
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(20), stream CHAR(3), marks INT, city VARCHAR(15));
INSERT INTO student VALUES (101, 'Aarav', 'SCI', 91, 'Delhi');
INSERT INTO student VALUES (102, 'Meher', 'COM', 78, 'Mumbai');
INSERT INTO student VALUES (103, 'Kabir', 'SCI', 85, 'Pune');
INSERT INTO student VALUES (104, 'Riya', 'HUM', 88, 'Delhi');
INSERT INTO student VALUES (105, 'Sneha', 'COM', 67, 'Jaipur');
INSERT INTO student VALUES (106, 'Ishaan', 'SCI', 59, NULL);
INSERT INTO student VALUES (107, 'Ananya', 'HUM', 94, 'Mumbai');
INSERT INTO student VALUES (108, 'Rohan', 'COM', 72, 'Delhi');
CREATE TABLE teacher (stream CHAR(3) PRIMARY KEY, tname VARCHAR(20), room INT);
INSERT INTO teacher VALUES ('SCI', 'Mr Rao', 12);
INSERT INTO teacher VALUES ('COM', 'Ms Kaur', 14);
INSERT INTO teacher VALUES ('HUM', 'Mrs Iyer', 9);
""")
def show(sql):
for row in cur.execute(sql):
print(row)
show("SELECT DISTINCT stream FROM student")
print("--")
show("SELECT name, marks FROM student WHERE marks > 80 ORDER BY marks DESC")
print("--")
show("SELECT COUNT(*), COUNT(city), MAX(marks), MIN(marks), SUM(marks) FROM student")
print("--")
show("SELECT stream, COUNT(*), AVG(marks) FROM student GROUP BY stream")
print("--")
show("SELECT stream, COUNT(*) FROM student GROUP BY stream HAVING COUNT(*) > 2")
COUNT(*) counts rows (8); COUNT(city) skips NULLs (7). WHERE filters rows before grouping; HAVING filters groups after grouping.
Clause order is fixed: SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY …. The sample paper has a 1-mark question where a query puts HAVING before GROUP BY and you must correct it. You also can't use an aggregate like COUNT(*) in WHERE; that is HAVING's job.
UPDATE, DELETE, ALTER
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(20), stream CHAR(3), marks INT, city VARCHAR(15));
INSERT INTO student VALUES (101, 'Aarav', 'SCI', 91, 'Delhi');
INSERT INTO student VALUES (102, 'Meher', 'COM', 78, 'Mumbai');
INSERT INTO student VALUES (103, 'Kabir', 'SCI', 85, 'Pune');
INSERT INTO student VALUES (104, 'Riya', 'HUM', 88, 'Delhi');
INSERT INTO student VALUES (105, 'Sneha', 'COM', 67, 'Jaipur');
INSERT INTO student VALUES (106, 'Ishaan', 'SCI', 59, NULL);
INSERT INTO student VALUES (107, 'Ananya', 'HUM', 94, 'Mumbai');
INSERT INTO student VALUES (108, 'Rohan', 'COM', 72, 'Delhi');
CREATE TABLE teacher (stream CHAR(3) PRIMARY KEY, tname VARCHAR(20), room INT);
INSERT INTO teacher VALUES ('SCI', 'Mr Rao', 12);
INSERT INTO teacher VALUES ('COM', 'Ms Kaur', 14);
INSERT INTO teacher VALUES ('HUM', 'Mrs Iyer', 9);
""")
def show(sql):
for row in cur.execute(sql):
print(row)
cur.execute("UPDATE student SET city = 'Noida' WHERE roll = 106")
print("Updated:", cur.rowcount)
cur.execute("DELETE FROM student WHERE marks < 70")
print("Deleted:", cur.rowcount)
cur.execute("ALTER TABLE student ADD COLUMN phone VARCHAR(10)")
show("SELECT * FROM student WHERE stream = 'COM'")
rowcount tells you how many rows the last UPDATE or DELETE touched. An UPDATE or DELETE without WHERE changes every row.
Joins: Cartesian, equi, natural
import sqlite3
con = sqlite3.connect(":memory:")
cur = con.cursor()
cur.executescript("""
CREATE TABLE student (roll INT PRIMARY KEY, name VARCHAR(20), stream CHAR(3), marks INT, city VARCHAR(15));
INSERT INTO student VALUES (101, 'Aarav', 'SCI', 91, 'Delhi');
INSERT INTO student VALUES (102, 'Meher', 'COM', 78, 'Mumbai');
INSERT INTO student VALUES (103, 'Kabir', 'SCI', 85, 'Pune');
INSERT INTO student VALUES (104, 'Riya', 'HUM', 88, 'Delhi');
INSERT INTO student VALUES (105, 'Sneha', 'COM', 67, 'Jaipur');
INSERT INTO student VALUES (106, 'Ishaan', 'SCI', 59, NULL);
INSERT INTO student VALUES (107, 'Ananya', 'HUM', 94, 'Mumbai');
INSERT INTO student VALUES (108, 'Rohan', 'COM', 72, 'Delhi');
CREATE TABLE teacher (stream CHAR(3) PRIMARY KEY, tname VARCHAR(20), room INT);
INSERT INTO teacher VALUES ('SCI', 'Mr Rao', 12);
INSERT INTO teacher VALUES ('COM', 'Ms Kaur', 14);
INSERT INTO teacher VALUES ('HUM', 'Mrs Iyer', 9);
""")
def show(sql):
for row in cur.execute(sql):
print(row)
rows = cur.execute("SELECT * FROM student, teacher").fetchall()
print("Cartesian product:", len(rows), "rows of", len(rows[0]), "columns")
print("Equi-join:")
show("SELECT name, tname FROM student, teacher WHERE student.stream = teacher.stream AND marks > 85")
print("Natural join:")
show("SELECT name, stream, room FROM student NATURAL JOIN teacher WHERE room < 10")
A Cartesian product pairs every row with every row: 8 × 3 = 24 rows, 5 + 3 = 8 columns. An equi-join keeps the pairs where the common column is equal; that column appears twice. A natural join does the same but shows the common column only once.
SQLite here, MySQL in the exam. The queries are the same. MySQL-only commands you must know on paper: CREATE DATABASE school; · USE school; · SHOW DATABASES; · SHOW TABLES; · DESCRIBE student; (or DESC) · ALTER TABLE student DROP COLUMN phone; · ALTER TABLE student ADD PRIMARY KEY (roll);. And DROP TABLE removes the whole table, while DELETE FROM removes rows and keeps the table.
In one line
SELECT … FROM … WHERE … GROUP BY … HAVING … ORDER BY; use IS NULL, LIKE with % and _, and never UPDATE or DELETE without WHERE unless you mean every row.