Structured Query Language (SELECT, FROM, WHERE)

Structured Query Language (SQL) is the standard declarative programming language used worldwide to retrieve, manipulate, and manage data within relational database management systems.

1. The Basic SQL Query Structure

To retrieve specific data from a database table, a query uses three fundamental clauses:

SELECT <Field1>, <Field2>, ...
FROM <TableName>
WHERE <SearchCondition>;

The Roles of the Three Clauses:

1. SELECT (What columns to display):

• Lists the fields (columns) you want returned in the final result table.
• To retrieve all fields from a table, use the asterisk wildcard symbol * (e.g., SELECT * FROM Products).

1. FROM (Where the data is stored):

• Specifies the exact table name containing the requested records.

1. WHERE (Which rows to include):

• Specifies the logical filter criteria. Only records for which the condition evaluates to TRUE are extracted.
• The WHERE clause is optional; omitting it returns all records in the table.

2. SQL Comparison & Logical Operators

The WHERE clause uses standard mathematical comparison operators:

Combining Conditions with Logical Operators:

• AND: Returns records only if all conditions separated by AND are TRUE.

WHERE ClassGrade = 10 AND FeesPaid = TRUE;

• OR: Returns records if at least one condition is TRUE.

WHERE Subject = 'Physics' OR Subject = 'Chemistry';

• NOT: Inverts the condition.

WHERE NOT (Status = 'Graduated');

3. Worked Example: Tracing Query Execution

Suppose we have a table named ExamResults:

+------------+--------------+-----------+-------+
| CandidateID| Subject      | MarkScore | Passed|
+------------+--------------+-----------+-------+
| C101       | CS           | 88        | TRUE  |
| C102       | Maths        | 92        | TRUE  |
| C103       | CS           | 44        | FALSE |
| C104       | CS           | 76        | TRUE  |
| C105       | Physics      | 81        | TRUE  |
+------------+--------------+-----------+-------+

Query:

SELECT CandidateID, MarkScore
FROM ExamResults
WHERE Subject = 'CS' AND MarkScore >= 50;

Step-by-Step Row Evaluation:

1. Row 1 (C101): Subject = 'CS' (TRUE) AND 88 >= 50 (TRUE) $\rightarrow$ MATCH (Included).
2. Row 2 (C102): Subject = 'Maths' (FALSE) $\rightarrow$ NO MATCH (Skipped).
3. Row 3 (C103): Subject = 'CS' (TRUE) AND 44 >= 50 (FALSE) $\rightarrow$ NO MATCH (Skipped).
4. Row 4 (C104): Subject = 'CS' (TRUE) AND 76 >= 50 (TRUE) $\rightarrow$ MATCH (Included).
5. Row 5 (C105): Subject = 'Physics' (FALSE) $\rightarrow$ NO MATCH (Skipped).

Result Table:

+------------+-----------+
| CandidateID| MarkScore |
+------------+-----------+
| C101       | 88        |
| C104       | 76        |
+------------+-----------+

4. Key Syntax Rules & Exam Pitfalls

1. Text Literals: In SQL, text strings must always be enclosed in single quotation marks (e.g., 'CS', 'London'). Numbers are written without quotes (e.g., 50).
2. Clauses Order: The order SELECT ... FROM ... WHERE ... is fixed. Putting FROM before SELECT causes a syntax error.
3. Punctuation: SQL statements conventionally conclude with a semicolon (;).

Selecting every field

The asterisk below means all fields. It is not multiplication here.

SELECT * FROM Results;

Choose only the required fields when a question specifies them.