SELECT–FROM–WHERE

Lecture 4 · the heart of SQL. Every query is a select … from … where …, and its result is always a relation.

Basic query structure

select A1, A2, ..., An     -- the columns you want    (π projection)
from   r1, r2, ..., rm     -- the relations involved  (× product)
where  P;                  -- the condition           (σ selection)

All examples below run against this instructor table:

The select clause

• SELECT * = all attributes; names are case-insensitive (Name = name).
• Duplicates are kept by default — use DISTINCT to drop them.
• Arithmetic + AS to rename the output column.

SELECT distinct dept_name
FROM instructor;

SELECT id, name, salary/30
       AS daily_salary
FROM instructor;

The where clause & predicates

SELECT id, name
FROM   instructor
WHERE  dept_name = 'CSE' AND salary > 10000;

-- range shorthand:
--   WHERE salary BETWEEN 11000 AND 100000;

String matching — LIKE

SELECT name FROM instructor WHERE name LIKE '%ras%';   -- names containing 'ras'

Patterns are case-sensitive. SQL also has || concatenation, upper/lower, length, substring, etc.

Ordering — ORDER BY

SELECT distinct name FROM instructor ORDER BY name;          -- asc is default
SELECT * FROM instructor ORDER BY dept_name, name DESC;      -- multi-key

The from clause & joins

from A, B is the Cartesian product — every pair. On its own it's rarely useful; add a where join condition to keep only matching pairs:

SELECT name, c_id
FROM   instructor, courseAllocation
WHERE  instructor.ID = courseAllocation.ID;

Self-join via rename (AS)

Rename a table to use it twice — e.g. instructors earning more than some CSE instructor:

SELECT distinct T.name
FROM   instructor AS T, instructor AS S
WHERE  T.salary > S.salary AND S.dept_name = 'CSE';   -- 'as' is optional