SELECT–FROM–WHERE, DISTINCT, aliases, LIKE, BETWEEN, ORDER BY; bags vs sets

0 · Recall Warm-up

Answer from memory first. Answers are in the Answer Vault at the bottom.

1. After UPDATE T SET a = b, b = a on (1, 2), what is the row? from Lesson 3.3.2
2. How many tuples are in πdept(STUDENT) in RA? from Lesson 3.1.1
3. What does natural join match on? from Lesson 3.1.3

1 · The Hook

SELECT dept FROM STUDENT; In relational algebra the answer has 3 rows. In SQL it has 4: CS appears twice. SQL tables and results are bags (multisets), not sets. This one difference explains a large share of GATE SQL questions, so this lesson starts with it.

2 · Predict First

How many rows does each return? (a) SELECT dept FROM STUDENT; (b) SELECT DISTINCT dept FROM STUDENT; (c) SELECT * FROM STUDENT, COURSE;

Make a guess in your head. The answer opens Section 3.

3 · Core Concept

Predict First — the answer

(a) 4 (duplicates kept). (b) 3. (c) 16: listing two tables in FROM forms their Cartesian product.

The basic query

SELECT   [DISTINCT] column list        -- π  (keeps duplicates unless DISTINCT)
FROM     table list                    -- ×  of all listed tables
WHERE    condition                     -- σ
ORDER BY column [ASC | DESC], ...      -- sort the output

Relational algebra meaning: πlist(σcondition(T₁ × T₂ × …)), except that SELECT keeps duplicates.

Conceptual order of evaluation: FROM → WHERE → SELECT → DISTINCT → ORDER BY. (Real systems optimise, but results must match this order.)

Bags vs sets

• SQL keeps duplicate rows in tables without a key and in query results.
• DISTINCT removes duplicates from the result.
• SELECT * returns all columns.

Joining in FROM–WHERE

SELECT S.name, C.title
FROM   STUDENT S, ENROLL E, COURSE C          -- aliases S, E, C
WHERE  S.roll = E.roll AND E.cid = C.cid AND E.grade = 'A';

Result: (Asha, DBMS), (Asha, Circuits), (Chirag, Networks). Aliases (tuple variables, like TRC) are required when a table appears twice: FROM STUDENT S1, STUDENT S2. AS may be written: STUDENT AS S; column aliases rename output: SELECT cgpa * 10 AS score.

Useful conditions

ORDER BY

ORDER BY cgpa DESC, name ASC sorts by cgpa high to low, ties by name. ASC is the default. ORDER BY can use columns not in the SELECT list (in most systems) and runs last.

Arithmetic and expressions

SELECT name, cgpa * 10 AS percent FROM STUDENT WHERE cgpa * 10 > 80;

4 · Visual

SELECT dept FROM STUDENT;            SELECT DISTINCT dept FROM STUDENT;
dept                                 dept
CS                                   CS
EE                                   EE
CS      <- duplicate kept (bag)      ME
ME
4 rows                               3 rows (set, like RA's π)

5 · Worked Examples

Example 1 Easy

SELECT name FROM STUDENT WHERE cgpa BETWEEN 6.9 AND 8.4 ORDER BY cgpa;

Solution

6.9 and 8.4 are included: Diya, Bhavin, Chirag (in that order).

Example 2 Medium

How many rows: SELECT E.cid FROM ENROLL E, STUDENT S WHERE E.roll = S.roll AND S.dept = 'CS'; With DISTINCT?

Solution

CS students 1 and 3 have 3 + 2 = 5 enrolments → 5 rows (C1, C2, C3, C1, C2). With DISTINCT: 3 (C1, C2, C3).

Example 3 GATE

SELECT S1.name FROM STUDENT S1, STUDENT S2 WHERE S1.cgpa > S2.cgpa; How many rows, and how many with DISTINCT?

Solution

Pairs where the first CGPA beats the second: Asha beats 3, Chirag beats 2, Bhavin beats 1, Diya beats 0 → 6 rows. DISTINCT names: 3 (everyone except the lowest, Diya).

8 · Connect the Dots

9 · Self-Test

GATE format. Try every question before opening the vault.

Q1 · NAT · 1 mark(s) — Bags

How many rows does SELECT grade FROM ENROLL; return?

Numerical answer: ________

Q2 · NAT · 1 mark(s) — DISTINCT

How many rows does SELECT DISTINCT grade FROM ENROLL; return?

Numerical answer: ________

Q3 · NAT · 2 mark(s) — Join count

How many rows does SELECT S.name FROM STUDENT S, ENROLL E WHERE S.roll = E.roll AND E.grade IN ('A', 'B'); return?

Numerical answer: ________

Q4 · MCQ · 1 mark(s) — LIKE

Which names match name LIKE '%a' in STUDENT (Asha, Bhavin, Chirag, Diya)?

1. (A) none
2. (B) Asha, Bhavin, Chirag, Diya
3. (C) Asha, Diya
4. (D) Asha only

Q5 · MCQ · 2 mark(s) — Order of evaluation

In the conceptual evaluation of SELECT–FROM–WHERE–ORDER BY, which comes first?

1. (A) ORDER BY
2. (B) WHERE
3. (C) FROM
4. (D) SELECT

11 · Concept Ledger

Unlocked in this lesson. From here on, these terms may be used.

SELECT–FROM–WHEREbag (multiset) semanticsDISTINCTalias (AS)SELECT *BETWEENIN (list)LIKE, %, _ORDER BY ASC/DESCconceptual evaluation order