Unit 3 · Database Management

Chapter 9
SQL

One language for creating tables, changing them, and asking them questions. Every query in this deck was run against the two tables from Chapter 8.

1.1 The two tables, once more

Learn these ten rows now. Every query in this chapter runs against them, so you can spend your attention on the query instead of the data.

STUDENT

RollNo (PK)	Name	SCode (FK)	Marks	City	Fee
101	Riya	S01	88	Delhi	4500
102	Aarav	S01	91	Mumbai	4500
103	Kabir	S02	76	Delhi	3800
104	Meera	S01	94	Pune	4500
105	Dev	S02	59	Mumbai	3800
106	Ishan	S03	67	Delhi	4000
107	Nisha	S02	NULL	Pune	3800

STREAM

SCode (PK)	SName	HOD
S01	Computer Science	Sharma
S02	Informatics	Iyer
S03	Physics	Rao

Four facts to memorise about this data

① 7 students, 3 streams.
② Nisha's Marks is NULL.
③ Delhi has 3 students; Mumbai and Pune have 2 each.
④ S01 has 3 students, S02 has 3, S03 has 1.

How every slide from here is built

The query in a blue frame → you predict → the result set in a table. The result is always revealed last. Say the row count out loud before you look; it is the fastest way to catch a wrong answer.

1.2 What SQL is, and its two halves

Definition

SQL — Structured Query Language — the standard language for creating, modifying and querying data in a relational database. It is declarative: you state what you want, not how to find it.

Declarative — why it matters

In Python you write the loop that scans the records. In SQL you write WHERE Marks > 85 and the DBMS decides how to find them. That is why one line of SQL can replace fifteen lines of Chapter 4 file handling.

Sublanguage	Stands for	Deals with	Commands
DDL	Data Definition Language	the structure of the database	`CREATE` `ALTER` `DROP`
DML	Data Manipulation Language	the data inside the structure	`SELECT` `INSERT` `UPDATE` `DELETE`

The distinction the paper asks for

DDL changes the shape of the table; DML changes what is in it.
DROP TABLE STUDENT is DDL — the table stops existing.
DELETE FROM STUDENT is DML — the table remains, empty.

Conventions used in this chapter

SQL keywords in capitals, table and column names as they were created, and every statement ends in a semicolon. SQL itself is not case-sensitive for keywords, but the marking scheme is written this way and it reads far better.

2.1 `CREATE TABLE`

create the parent table firstCREATE TABLE STREAM (
    SCode CHAR(3) PRIMARY KEY,
    SName VARCHAR(20) NOT NULL,
    HOD   VARCHAR(20)
);

then the child tableCREATE TABLE STUDENT (
    RollNo INT PRIMARY KEY,
    Name   VARCHAR(20) NOT NULL,
    SCode  CHAR(3),
    Marks  INT,
    City   VARCHAR(15),
    Fee    DECIMAL(8,2) DEFAULT 4000,
    FOREIGN KEY (SCode) REFERENCES STREAM(SCode)
);

Order matters

STREAM must exist before STUDENT, because STUDENT's foreign key references it. Creating them the other way round fails.

Data type	Holds	Example
`INT`	whole numbers	101, 88
`DECIMAL(p,d)`	p digits, d after the point	`DECIMAL(8,2)` → 4500.00
`CHAR(n)`	fixed-length text	`CHAR(3)` → 'S01'
`VARCHAR(n)`	variable-length text, up to n	`VARCHAR(20)` → 'Riya'
`DATE`	a date, as YYYY-MM-DD	'2008-04-17'

CHAR vs VARCHAR

CHAR(3) always occupies 3 characters, padding with spaces if needed — right for codes like S01 that are always the same length.
VARCHAR(20) occupies only what it uses — right for names, which vary. Choosing correctly is a mark.

2.2 Constraints — rules the DBMS enforces

Constraint	Guarantees
`PRIMARY KEY`	unique and never NULL; one per table
`NOT NULL`	a value must always be supplied
`UNIQUE`	no two rows share this value (NULL allowed)
`DEFAULT v`	v is used when no value is given
`CHECK (cond)`	only values satisfying cond are accepted
`FOREIGN KEY`	the value must exist in the referenced table

PRIMARY KEY vs UNIQUE

Both forbid duplicates. But UNIQUE allows a NULL and a table may have many UNIQUE columns; PRIMARY KEY forbids NULL and there is exactly one. That is the Chapter 8 candidate / alternate key distinction, written in SQL.

all six, in one tableCREATE TABLE STUDENT (
    RollNo  INT PRIMARY KEY,
    Aadhaar CHAR(12) UNIQUE,
    Name    VARCHAR(20) NOT NULL,
    Marks   INT CHECK (Marks >= 0 AND Marks <= 100),
    City    VARCHAR(15) DEFAULT 'Delhi',
    SCode   CHAR(3),
    FOREIGN KEY (SCode) REFERENCES STREAM(SCode)
);

What each one now blocks

Marks = 950 → refused by CHECK.
two students with RollNo 101 → refused by PRIMARY KEY.
a student with no name → refused by NOT NULL.
SCode = 'S09' → refused by FOREIGN KEY.
no city supplied → quietly stored as 'Delhi' by DEFAULT.

This is the answer to "why a database?"

Not one line of checking code was written. The rules live with the data, so every program that touches the table obeys them — including a careless one.

2.3 `ALTER TABLE`

add a columnALTER TABLE STUDENT
ADD Phone VARCHAR(10);

change a column's typeALTER TABLE STUDENT
MODIFY City VARCHAR(25);

remove a columnALTER TABLE STUDENT
DROP Phone;

add a constraint laterALTER TABLE STUDENT
ADD PRIMARY KEY (RollNo);

After ADD Phone, the existing rows gain the column with a NULL in it:

Degree rises from 6 to 7; cardinality is unchanged at 7.
RollNo	Name	Marks	Phone
101	Riya	88	NULL
102	Aarav	91	NULL
103	Kabir	76	NULL
…	…	…	NULL

ALTER changes structure, not data

ALTER is DDL. It never changes the value in an existing cell — that is what UPDATE is for. The one thing it does to data is destroy it, when you drop a column.

A note on MODIFY

MODIFY is MySQL's keyword and the one the syllabus uses. Some other systems write ALTER COLUMN. Use MODIFY in the exam.

2.4 `DROP` vs `DELETE` vs `TRUNCATE`

DDL — removes the tableDROP TABLE STUDENT;

DROP

The table, its structure, its data and its constraints all cease to exist. SELECT * FROM STUDENT afterwards is an error — there is no such table.

DML — removes rowsDELETE FROM STUDENT
WHERE Marks < 60;

-- or every row:
DELETE FROM STUDENT;

DELETE

Removes the rows that match. The table still exists, with its structure intact — SELECT * FROM STUDENT returns an empty result, not an error.

DDL — empties the tableTRUNCATE TABLE STUDENT;

TRUNCATE

Removes all rows at once and cannot take a WHERE. Faster than DELETE for a full clear-out, and the structure survives.

"Differentiate between DROP and DELETE" is a routine 2-mark question. The middle row is the answer.
	DROP	DELETE	TRUNCATE
Type	DDL	DML	DDL
Removes	the whole table	selected rows	all rows
Structure survives?	No	Yes	Yes
Can use `WHERE`?	No	Yes	No

3.1 `INSERT INTO`

all columns, in orderINSERT INTO STUDENT
VALUES (108, 'Tara', 'S03', 82, 'Delhi', 4000);

named columns — saferINSERT INTO STUDENT (RollNo, Name, SCode, City)
VALUES (109, 'Omar', 'S01', 'Pune');

What are Omar's Marks and Fee? 🤔

Result after both inserts — the last two rows:

RollNo	Name	SCode	Marks	City	Fee
107	Nisha	S02	NULL	Pune	3800
108	Tara	S03	82	Delhi	4000
109	Omar	S01	NULL	Pune	4000

The two unspecified columns

Marks becomes NULL — no value was given and there is no default.
Fee becomes 4000 — the DEFAULT declared in §2.1 supplied it.

Three rules for INSERT

① Text and dates go in single quotes; numbers do not.
② Without a column list, you must supply every value in the created order.
③ Write NULL without quotes — 'NULL' stores the four-letter word.

3.2 `UPDATE` — before and after

Before

the three S02 rows
RollNo	Name	SCode	Marks	Fee
103	Kabir	S02	76	3800
105	Dev	S02	59	3800
107	Nisha	S02	NULL	3800

query 1 — one rowUPDATE STUDENT
SET Marks = 70
WHERE Name = 'Nisha';

query 2 — many rows, computedUPDATE STUDENT
SET Fee = Fee + 500
WHERE SCode = 'S02';

How many rows does each change? 🤔

After

RollNo	Name	SCode	Marks	Fee
103	Kabir	S02	76	4300
105	Dev	S02	59	4300
107	Nisha	S02	70	4300

Query 2 is the one worth studying

SET Fee = Fee + 500 uses the column's own current value, and it does so per row. Three rows matched, so three different original values were each raised by 500.

The most dangerous omission in SQL

UPDATE STUDENT SET Fee = 5000; — with no WHERE — changes every row in the table. There is no undo. Write the WHERE clause first, then go back and write the SET.

3.3 `DELETE` — before and after

Before — all 7 rows

RollNo	Name	Marks
101	Riya	88
102	Aarav	91
103	Kabir	76
104	Meera	94
105	Dev	59
106	Ishan	67
107	Nisha	NULL

queryDELETE FROM STUDENT
WHERE Marks < 60;

How many rows go? Careful — think about NULL. 🤔

After — 6 rows

RollNo	Name	Marks
101	Riya	88
102	Aarav	91
103	Kabir	76
104	Meera	94
106	Ishan	67
107	Nisha	NULL

Only one row was deleted — and Nisha survived

NULL < 60 is not true. It is not false either — it is unknown, and WHERE keeps only rows where the condition is definitely true. So Nisha's row was never selected for deletion.

Remember this for §4.6

NULL fails every ordinary comparison — <, >, =, even = NULL. That is why SQL has a separate IS NULL operator, and it is examined every year.

4.1 The shape of every `SELECT`

the full skeleton — in this orderSELECT   columns
FROM     tables
WHERE    row condition
GROUP BY column
HAVING   group condition
ORDER BY column [ASC|DESC];

The order of the clauses is fixed

You may omit any of them, but you may not reorder them. ORDER BY before WHERE is a syntax error.

query 1 — everythingSELECT * FROM STREAM;

3 rows
SCode	SName	HOD
S01	Computer Science	Sharma
S02	Informatics	Iyer
S03	Physics	Rao

query 2 — chosen columnsSELECT Name, Marks FROM STUDENT;

7 rows — selecting columns never removes rows
Name	Marks
Riya	88
Aarav	91
Kabir	76
Meera	94
Dev	59
Ishan	67
Nisha	NULL

4.2 `DISTINCT` and `WHERE`

query 1SELECT City FROM STUDENT;

7 rows, with repeats
City
Delhi
Mumbai
Delhi
Pune
Mumbai
Delhi
Pune

query 2SELECT DISTINCT City FROM STUDENT;

3 rows
City
Delhi
Mumbai
Pune

query 3SELECT Name, Marks FROM STUDENT
WHERE Marks > 85;

3 rows
Name	Marks
Riya	88
Aarav	91
Meera	94

query 4 — two conditionsSELECT Name, City FROM STUDENT
WHERE City = 'Delhi' AND Marks > 70;

2 rows — Ishan is in Delhi but scored 67
Name	City
Riya	Delhi
Kabir	Delhi

SELECT picks columns, WHERE picks rows

Two independent choices. Queries 1–2 change the columns; queries 3–4 change the rows. Every SELECT is some combination of the two.

4.3 `BETWEEN` and `IN`

query 1SELECT Name, Marks FROM STUDENT
WHERE Marks BETWEEN 60 AND 90;

Is 90 included? Is Nisha? 🤔

3 rows
Name	Marks
Riya	88
Kabir	76
Ishan	67

Both ends are included

BETWEEN 60 AND 90 means Marks >= 60 AND Marks <= 90. A student scoring exactly 60 or exactly 90 would appear. Nisha does not, because NULL fails the comparison.

query 2SELECT Name, City FROM STUDENT
WHERE City IN ('Delhi', 'Pune');

5 rows
Name	City
Riya	Delhi
Kabir	Delhi
Meera	Pune
Ishan	Delhi
Nisha	Pune

Both are shorthand

IN ('Delhi','Pune') is City = 'Delhi' OR City = 'Pune'.
BETWEEN 60 AND 90 is Marks >= 60 AND Marks <= 90.
Both forms are accepted in the exam, but the shorthand is shorter to write and harder to get wrong.

Negating them

NOT BETWEEN and NOT IN both work, and both also exclude NULL rows — for the same reason as above.

4.4 `LIKE` — pattern matching

The two wildcards

% — matches any number of characters, including none.
_ — matches exactly one character.

Pattern	Means
`'A%'`	starts with A
`'%a'`	ends with a
`'%ee%'`	contains ee anywhere
`'_i%'`	second letter is i
`'____'`	exactly four characters

query 1SELECT Name FROM STUDENT WHERE Name LIKE 'A%';

1 row
Name
Aarav

query 2SELECT Name FROM STUDENT WHERE Name LIKE '%a';

3 rows
Name
Riya
Meera
Nisha

query 3SELECT Name FROM STUDENT WHERE Name LIKE '_i%';

Second letter is i — who? 🤔

2 rows — Riya and Nisha
Name
Riya
Nisha

The mistake to avoid

WHERE Name = 'A%' uses =, which looks for a name that literally is the two characters A% — and matches nothing. Patterns need LIKE.

4.5 `IS NULL` — the operator NULL forces

WRONGSELECT Name FROM STUDENT
WHERE Marks = NULL;

0 rows — and no error is reported
Name

Why it silently fails

NULL means "unknown". Asking whether an unknown value equals another unknown value gives unknown, never true. So no row qualifies — and because it is not a syntax error, nothing warns you.

RIGHTSELECT Name, Marks FROM STUDENT
WHERE Marks IS NULL;

1 row
Name	Marks
Nisha	NULL

and the oppositeSELECT Name FROM STUDENT
WHERE Marks IS NOT NULL;

6 rows — everyone except Nisha
Name
Riya
Aarav
Kabir
Meera
Dev
Ishan

The rule in one line

NULL is tested with IS, never with =. Six rows plus one row equals the seven in the table — a good arithmetic check that you have not lost anybody.

4.6 `ORDER BY`

query 1 — ascending is the defaultSELECT Name, Marks FROM STUDENT
ORDER BY Marks;

Where does Nisha's NULL go? 🤔

NULL sorts **first** in ascending order
Name	Marks
Nisha	NULL
Dev	59
Ishan	67
Kabir	76
Riya	88
Aarav	91
Meera	94

query 2 — descendingSELECT Name, Marks FROM STUDENT
ORDER BY Marks DESC;

NULL now sorts **last**
Name	Marks
Meera	94
Aarav	91
Riya	88
Kabir	76
Ishan	67
Dev	59
Nisha	NULL

query 3 — two levelsSELECT Name, City, Marks FROM STUDENT
ORDER BY City, Marks DESC;

City A→Z, and **within** each city, Marks high→low
Name	City	Marks
Riya	Delhi	88
Kabir	Delhi	76
Ishan	Delhi	67
Aarav	Mumbai	91
Dev	Mumbai	59
Meera	Pune	94
Nisha	Pune	NULL

The DESC applies to Marks only

Each column takes its own direction. To reverse both you must write ORDER BY City DESC, Marks DESC.

5.1 Five functions that collapse many rows into one

query 1SELECT SUM(Marks), MAX(Marks), MIN(Marks)
FROM STUDENT;

Seven rows in — how many out? 🤔

**1 row.** That is what "aggregate" means.
SUM(Marks)	MAX(Marks)	MIN(Marks)
475	94	59

query 2SELECT ROUND(AVG(Marks), 2) FROM STUDENT;

475 ÷ 6, not ÷ 7
ROUND(AVG(Marks), 2)
79.17

Only `COUNT(*)` counts a NULL row. Everything else skips it.
Function	Returns	Ignores NULL?
`COUNT(*)`	number of rows	No
`COUNT(col)`	number of non-NULL values	Yes
`SUM(col)`	total	Yes
`AVG(col)`	average	Yes
`MAX(col)`	largest	Yes
`MIN(col)`	smallest	Yes

Why the average is 79.17 and not 67.86

AVG ignores Nisha's NULL entirely — it does not treat it as zero. So the sum 475 is divided by the 6 rows that have a value, not by 7. Dividing by 7 would give 67.86, which is the wrong answer and the one most students write.

Why ROUND

The raw AVG(Marks) is 79.166666… and different systems display a different number of decimal places. ROUND(AVG(Marks), 2) gives a single definite answer, and it is what an exam answer should show.

5.2 `COUNT(*)` vs `COUNT(column)`

query 1SELECT COUNT(*) FROM STUDENT;

every row
COUNT(*)
7

query 2SELECT COUNT(Marks) FROM STUDENT;

Nisha's NULL is not counted
COUNT(Marks)
6

query 3SELECT COUNT(DISTINCT City) FROM STUDENT;

Delhi, Mumbai, Pune
COUNT(DISTINCT City)
3

Three answers from one table — 7, 6 and 3

COUNT(*) asks "how many rows?"
COUNT(Marks) asks "how many students have a mark?"
COUNT(DISTINCT City) asks "how many different cities?"
Three genuinely different questions — and examiners set them side by side precisely to see whether you know which is which.

The exam habit that saves you

Whenever you see COUNT, look for a NULL in the column. If there is one, COUNT(*) and COUNT(col) will differ, and that difference is almost certainly what the question is testing.

Aggregates cannot be mixed with plain columns

SELECT Name, MAX(Marks) FROM STUDENT; is meaningless — there are seven names but only one maximum. To pair them you need GROUP BY, which is next.

6.1 `GROUP BY` — one answer per group

query 1SELECT City, COUNT(*)
FROM STUDENT
GROUP BY City;

How many rows come back? 🤔

**3 rows** — one per distinct City
City	COUNT(*)
Delhi	3
Mumbai	2
Pune	2

query 2SELECT SCode, COUNT(*), ROUND(AVG(Marks),2)
FROM STUDENT
GROUP BY SCode;

3 rows
SCode	COUNT(*)	ROUND(AVG(Marks),2)
S01	3	91.0
S02	3	67.5
S03	1	67.0

Read the S02 row carefully

COUNT(*) is 3 — Kabir, Dev and Nisha.
But the average is 67.5, which is (76 + 59) ÷ 2.

Within one group, COUNT(*) counts three rows while AVG uses only the two that have a value. Both are correct, and they disagree — that is exactly the point.

The rule you must not break

Every column in the SELECT list must either appear in the GROUP BY clause or be inside an aggregate function.

SELECT City, Name, COUNT(*) … GROUP BY City is invalid — the Delhi group holds three different names, so there is no single Name to show.

What GROUP BY really does

It sorts the rows into piles, one pile per distinct value, then runs the aggregate on each pile separately and returns one row per pile. Picture the piles and the output is obvious.

6.2 `HAVING` — filtering the groups

query 1SELECT City, COUNT(*)
FROM STUDENT
GROUP BY City
HAVING COUNT(*) > 2;

1 row — Mumbai and Pune have only 2 each
City	COUNT(*)
Delhi	3

query 2SELECT SCode, MAX(Marks)
FROM STUDENT
GROUP BY SCode
HAVING MAX(Marks) > 80;

1 row — S02 tops out at 76, S03 at 67
SCode	MAX(Marks)
S01	94

The distinction, stated exactly

WHERE filters individual rows, before they are grouped. It cannot contain an aggregate function.
HAVING filters whole groups, after the grouping and aggregation have happened. It normally does contain an aggregate.

both together — and the order is fixedSELECT City, COUNT(*)
FROM STUDENT
WHERE Marks > 60
GROUP BY City;

Dev (59) and Nisha (NULL) were removed **before** grouping
City	COUNT(*)
Delhi	3
Mumbai	1
Pune	1

Trace the order of operations

① WHERE throws out Dev and Nisha → 5 rows survive.
② GROUP BY piles those 5 by city → Delhi 3, Mumbai 1, Pune 1.
③ the aggregate runs on each pile.
A HAVING, if present, would filter after step ③.

The error to remember

WHERE COUNT(*) > 2 is invalid. At the moment WHERE runs, no group exists yet, so there is nothing to count. Conditions on aggregates always go in HAVING.

7.1 Cartesian product — every row with every row

query — two tables, no conditionSELECT * FROM STUDENT, STREAM;

7 rows and 3 rows. How many come back? 🤔

**21 rows** = 7 × 3 · **9 columns** = 6 + 3
RollNo	Name	SCode	SCode	SName
101	Riya	S01	S01	Computer Science
101	Riya	S01	S02	Informatics
101	Riya	S01	S03	Physics
102	Aarav	S01	S01	Computer Science
102	Aarav	S01	S02	Informatics
…	…	…	…	…

Definition

Cartesian product (cross join) — combines every tuple of the first relation with every tuple of the second.

rows = m × n · degree = p + q

Here: 7 × 3 = 21 rows, and 6 + 3 = 9 columns.

Almost every row is nonsense

Riya is paired with Informatics and with Physics — she is in neither. Only the green rows, where the two SCode values match, are true.

Of the 21 rows, exactly 7 are meaningful. Adding the matching condition is what turns a Cartesian product into a join.

The exam question

"If table A has 5 rows and 3 columns and table B has 4 rows and 2 columns, what is the degree and cardinality of A × B?" → cardinality 20, degree 5. Rows multiply; columns add.

7.2 Equi-join — keeping only the matching rows

query 1 — the join conditionSELECT Name, SName
FROM STUDENT, STREAM
WHERE STUDENT.SCode = STREAM.SCode;

**7 rows** — down from 21
Name	SName
Riya	Computer Science
Aarav	Computer Science
Kabir	Informatics
Meera	Computer Science
Dev	Informatics
Ishan	Physics
Nisha	Informatics

query 2 — join plus a filterSELECT Name, Marks, SName, HOD
FROM STUDENT, STREAM
WHERE STUDENT.SCode = STREAM.SCode
  AND Marks > 80;

3 rows
Name	Marks	SName	HOD
Riya	88	Computer Science	Sharma
Aarav	91	Computer Science	Sharma
Meera	94	Computer Science	Sharma

Definition

Equi-join — a join in which tuples are combined only where the values of two specified columns are equal. The matching column appears twice in the result of SELECT *.

Why the table name is written in front

SCode exists in both tables, so writing it alone is ambiguous and the DBMS rejects the query. STUDENT.SCode says exactly which one you mean.

the same query, with aliasesSELECT S.Name, T.SName
FROM STUDENT S, STREAM T
WHERE S.SCode = T.SCode
  AND T.HOD = 'Iyer';

3 rows
Name	SName
Kabir	Informatics
Dev	Informatics
Nisha	Informatics

Aliases

FROM STUDENT S renames the table to S for the rest of the query. It is shorter and it is what real SQL looks like — and it is fully accepted in the exam.

7.3 Natural join

querySELECT Name, SName
FROM STUDENT NATURAL JOIN STREAM;

7 rows — identical to the equi-join
Name	SName
Riya	Computer Science
Aarav	Computer Science
Kabir	Informatics
Meera	Computer Science
Dev	Informatics
Ishan	Physics
Nisha	Informatics

Definition

Natural join — a join that automatically matches on all columns having the same name in both relations, and shows the common column only once.

	Cartesian product	Equi-join	Natural join
Written as	`FROM A, B`	`FROM A, B WHERE A.c = B.c`	`FROM A NATURAL JOIN B`
Condition	none	you write it	automatic, on same-named columns
Rows here	21	7	7
Common column appears	twice	twice	once

The relationship between the three

Equi-join = Cartesian product + a matching condition.
Natural join = equi-join, with the condition inferred and the duplicate column removed.
All three produce the same 7 rows here — they differ in how much you write and what the columns look like.

Why natural join can bite

It matches on every identically-named column. If both tables happened to have a Name column too, it would silently require those to match as well and you would get far fewer rows. The explicit equi-join is safer, and it is the form most marking schemes show.

8.1 Everything on one screen

DDL — the structure

CREATE TABLE ALTER TABLE ADD / MODIFY / DROP DROP TABLE — the table is gone PRIMARY KEY · FOREIGN KEY · NOT NULL · UNIQUE · DEFAULT · CHECK

DML — the data

INSERT INTO … VALUES UPDATE … SET … WHERE DELETE FROM … WHERE no WHERE = every row

SELECT

DISTINCT BETWEEN — both ends included IN (list) LIKE — % many, _ one IS NULL, never = NULL ORDER BY … ASC / DESC

Aggregates, groups and joins

COUNT(*) counts rows; COUNT(col) skips NULL SUM AVG MAX MIN all ignore NULL GROUP BY — one row per group WHERE before grouping, HAVING after Cartesian = m × n rows equi-join = product + matching condition natural join — common column once

8.2 Exam question 1 — write the queries

6 marks — one each

Using STUDENT and STREAM, write SQL for:

Display the names of students whose city is Delhi or Pune.
Display all names beginning with the letter M.
Display the names of students who have not been given any marks.
Increase the fee of all Informatics (S02) students by 10%.
Display each city with the number of students in it.
Display the name of each student along with the name of their stream.

Write all six before you look. 🤔

Answer

1SELECT Name FROM STUDENT
WHERE City IN ('Delhi', 'Pune');

2SELECT Name FROM STUDENT
WHERE Name LIKE 'M%';

3SELECT Name FROM STUDENT
WHERE Marks IS NULL;

4UPDATE STUDENT
SET Fee = Fee * 1.1
WHERE SCode = 'S02';

5SELECT City, COUNT(*)
FROM STUDENT
GROUP BY City;

6SELECT Name, SName
FROM STUDENT, STREAM
WHERE STUDENT.SCode = STREAM.SCode;

Where marks are lost

3. WHERE Marks = NULL returns nothing — it must be IS NULL.
4. Forgetting the WHERE raises everyone's fee.
6. Forgetting the join condition gives 21 nonsense rows.

8.3 Exam question 2 — predict the output

4 marks

Give the output of each query on the STUDENT table.

1SELECT COUNT(*), COUNT(Marks) FROM STUDENT;
2SELECT MAX(Marks) - MIN(Marks) FROM STUDENT;
3SELECT City, COUNT(*) FROM STUDENT
  GROUP BY City HAVING COUNT(*) > 2;
4SELECT Name FROM STUDENT WHERE Name LIKE '_i%';

Are you ready for the answer? 🤔

Answer

query 1 — the NULL is the difference
COUNT(*)	COUNT(Marks)
7	6

query 2 — 94 − 59
MAX(Marks) - MIN(Marks)
35

query 3 — only Delhi has more than 2
City	COUNT(*)
Delhi	3

query 4 — second letter is i
Name
Riya
Nisha

Query 2 is worth a comment. MAX and MIN both ignore the NULL, so the range is computed from the six real marks. It is 35, not "unknown".

8.4 Exam question 3 — theory

2 + 2 + 2 marks

Differentiate between DDL and DML, with two commands of each.
Differentiate between WHERE and HAVING.
Differentiate between DELETE and DROP.

Are you ready for the answer? 🤔

Answer 1

DDL (Data Definition Language) defines and modifies the structure of database objects. Commands: CREATE, ALTER, DROP.

DML (Data Manipulation Language) works with the data stored inside those structures. Commands: SELECT, INSERT, UPDATE, DELETE.

Answer 2

WHERE filters individual rows before grouping, and cannot contain an aggregate function.

HAVING filters groups after grouping and aggregation, and normally does contain an aggregate function.

So WHERE Marks > 60 is valid but WHERE COUNT(*) > 2 is not; the latter must be written as HAVING COUNT(*) > 2.

Answer 3

DELETE is a DML command that removes rows from a table. It can take a WHERE clause, and the table's structure remains, so records can be inserted again afterwards.

DROP is a DDL command that removes the entire table — structure, data and constraints. The table no longer exists, and referring to it afterwards is an error.

8.5 One last challenge

Challenge

Predict the output of both, and explain why they differ.

ASELECT SCode, COUNT(*), COUNT(Marks), SUM(Marks)
FROM STUDENT
GROUP BY SCode;

BSELECT SCode, COUNT(*), COUNT(Marks), SUM(Marks)
FROM STUDENT
WHERE Marks IS NOT NULL
GROUP BY SCode;

One row differs. Which, and why? 🤔

Answer

Query A
SCode	COUNT(*)	COUNT(Marks)	SUM(Marks)
S01	3	3	273
S02	3	2	135
S03	1	1	67

Query B
SCode	COUNT(*)	COUNT(Marks)	SUM(Marks)
S01	3	3	273
S02	2	2	135
S03	1	1	67

Only the S02 COUNT(*) changes — 3 becomes 2.

In A, Nisha's row is present in the S02 group. COUNT(*) counts it; COUNT(Marks) and SUM ignore it. In B the WHERE removed her row before grouping, so even COUNT(*) no longer sees her.

The sums are identical in both — because SUM was already ignoring the NULL. That is the whole lesson of this chapter's NULL thread, in one comparison.

End of Chapter 9

Next: Python with MySQL

You can now ask a database anything. Chapter 10 puts these queries inside a Python program — so the answers come back as tuples you can loop over, write to a file, or push onto a stack.

Carry these three forward

① A row comes back to Python as a tuple — Chapter 1 §7, used for real.
② Any INSERT, UPDATE or DELETE must be followed by commit(), or the change is silently discarded.
③ Never paste user input into a query string — Chapter 10 shows what goes wrong and how parameterised queries fix it.

Chapter 9 — SQL

Chapter 9SQL

1.1 The two tables, once more

STUDENT

STREAM

1.2 What SQL is, and its two halves

2.1 CREATE TABLE

2.2 Constraints — rules the DBMS enforces

2.3 ALTER TABLE

2.4 DROP vs DELETE vs TRUNCATE

3.1 INSERT INTO

3.2 UPDATE — before and after

Before

After

3.3 DELETE — before and after

Before — all 7 rows

After — 6 rows

4.1 The shape of every SELECT

4.2 DISTINCT and WHERE

4.3 BETWEEN and IN

4.4 LIKE — pattern matching

4.5 IS NULL — the operator NULL forces

4.6 ORDER BY

5.1 Five functions that collapse many rows into one

5.2 COUNT(*) vs COUNT(column)

6.1 GROUP BY — one answer per group

6.2 HAVING — filtering the groups

7.1 Cartesian product — every row with every row

7.2 Equi-join — keeping only the matching rows

7.3 Natural join

8.1 Everything on one screen

DDL — the structure

DML — the data

SELECT

Aggregates, groups and joins

8.2 Exam question 1 — write the queries

8.3 Exam question 2 — predict the output

8.4 Exam question 3 — theory

8.5 One last challenge

Next: Python with MySQL

Chapter 9
SQL

2.1 `CREATE TABLE`

2.3 `ALTER TABLE`

2.4 `DROP` vs `DELETE` vs `TRUNCATE`

3.1 `INSERT INTO`

3.2 `UPDATE` — before and after

3.3 `DELETE` — before and after

4.1 The shape of every `SELECT`

4.2 `DISTINCT` and `WHERE`

4.3 `BETWEEN` and `IN`

4.4 `LIKE` — pattern matching

4.5 `IS NULL` — the operator NULL forces

4.6 `ORDER BY`

5.2 `COUNT(*)` vs `COUNT(column)`

6.1 `GROUP BY` — one answer per group

6.2 `HAVING` — filtering the groups