SQL guide: The Standard template, few keywords and the conceptual evaluation order (Part 2)

The standard and the most basic SQL template has the following form:

The Standard form:

SELECT [DISTINCT] A₀, A₁, A₂, A₃, ...
FROM T₀, T₁, T₂, T₃, ...
WHERE condition

A's represent attribute names (column names) whereas T's represent the table names or alias.

The Conceptual Evaluation order:

FROM T₀, T₁, T₂, T₃, ...

FROM is always evaluated at first. You can think of T₀, T₁, T₂, T₃, ... as a sort of cross product of all these given tables.
2.

WHERE condition

WHERE always follows FROM. Each row is included or excluded from the final SQL output on the basis of this condition. We will talk more about it in further discussions.
3.

SELECT [DISTINCT] A₀, A₁, A₂, A₃, ...

SELECT is always evaluated at last. You can think of it as a termination point of query which is executed at the end. It contains the column names which are to be displayed in the SQL output.
4. (optional)
If we have got the DISTINCT keyword, the SQL output is filtered and made devoid of duplicates.

NOTE:

SELECT and FROM are the mandatory keywords to have in any query you write while WHERE is optional.
DISTINCT is optional. Keeping this keyword in SELECT results in distinct tuples in the output (a set) whereas removing it helps us retain duplicates in the final SQL output (a multi-set).
By default, duplicate tuples are present in the output.

SELECT, FROM, DISTINCT and WHERE are SQL keywords and hence, case-insensitive.
For example, SELECT can be also written as sElEcT, select, SELect, etc.

Aliasing:
- It is a way to denote huge table names with shorthand notations.
- For example, Student S. Here, S is the shorthand notation to Student table.
- Once aliasing is done, then you cannot use the original table name again and the original table in the database is untouched. A copy of it with the name S is made available in the main memory.

Now, let's apply the conceptual evaluation order to the following query.

Cars:

Players:

Query:

SELECT DISTINCT c.model, c.buyer
FROM Cars c, Players p
WHERE p.age > 45 

The conceptual evaluation order:

1. The cross product (Cars x Players).

FROM Cars c, Players p

Intermediate Output:

2. Applying the condition on each and every tuple.

WHERE p.age > 45

Intermediate Output:

3. Project the required columns in the result (ignore DISTINCT in this step).

SELECT c.model, c.buyer

Intermediate Output:

4. Now apply DISTINCT

Final Output:

That's it for this post. In further parts, we'll explore more keywords, begin with writing complex queries and explore all common misinterpretations we carry with ourselves while writing SQL queries which might lead to incorrect results.