MODULE 1 OF 8: FOUNDATIONS • LESSON 1 OF 4~12 min read

Introduction to SQL and Relational Databases

Start with the mental model behind SQL: applications store facts in relational tables, and queries ask precise questions about those facts. By the end of this lesson, you should be able to explain what SQL is, how a relational database is organized, and why a query is fundamentally different from changing stored data.

•

Previous Next Lesson

Key Takeaways at a Glance:

•SQL is declarative: you declare WHAT data you need; the RDBMS optimizer determines HOW to physically fetch it.
•A relational table has a defined schema (degree/columns) and holds tuples (cardinality/rows) with no inherent natural order.
•A SELECT query reads and projects data into an ephemeral result set without mutating stored records on disk.
•SQL is an ANSI standard language; MySQL, PostgreSQL, SQLite, and Oracle are distinct DBMS engines with dialect differences.

In Simple Words (The Mental Model)

In imperative programming (Java, Python, C++), you write loops, maintain index pointers, and instruct the CPU step-by-step how to find a record. In SQL, you simply describe the desired result set ('give me names of customers in Jaipur'), and the database engine's cost-based optimizer generates the most efficient disk access path.

Real-World Analogy

Think of dining at a restaurant. You are the customer declaring your order ('I would like a Margherita Pizza') from the menu. You do not step into the kitchen, adjust oven temperatures, or slice tomatoes step-by-step—the chef (the Database Execution Engine) optimizes the preparation process and serves you the finished plate (the Result Set).

Execution Flow Internals

The SQL Query Lifecycle Inside the RDBMS

Step 1 of 5

SQL Query String

Phase 1: Client

Client application sends raw SQL text over the network (TCP connection pool).

Foundational Principles

Core Concept Breakdown

Essential database invariants, relational theory, and execution behavior.

1What is SQL? (Declarative vs. Imperative Mental Model)

SQL stands for Structured Query Language. It is the international standard language (ANSI/ISO) for managing and querying relational database management systems (RDBMS). Unlike general-purpose programming languages like Python, C++, or Java—which are imperative (you write step-by-step algorithms and loops)—SQL is declarative. You specify the desired properties of the output relation, and the database engine automatically compiles, optimizes, and executes the physical retrieval strategy.

2Relational Model Anatomy: Tables, Tuples, Attributes & Domains

A relational database organizes data into relations (commonly called tables). In formal database theory and technical interviews, these terms are used interchangeably:

•

Relation = Table: A structured 2D grid containing facts about a specific entity or event.

•

Tuple = Row (Record): A single instance of an entity (e.g., Customer #101).

•

Attribute = Column (Field): A specific property or measurement (e.g., email, display_name).

•

Domain = Data Type: The set of permitted values for a column (e.g., INT, VARCHAR, DATE).

•

Cardinality: The total number of rows (tuples) in a table.

•

Degree (Arity): The total number of columns (attributes) in a table.

3SQL is a Standard Language, Not a Database Product

A frequent interview trap is confusing the SQL query language with an RDBMS product. SQL is the standardized specification (ANSI SQL). MySQL, PostgreSQL, Microsoft SQL Server, Oracle Database, and SQLite are distinct commercial and open-source database servers that implement the SQL engine. While 90% of basic syntax is portable, each engine features unique extensions, dialect variations (e.g., DATE_TRUNC vs DATE_FORMAT), storage engines (InnoDB, RocksDB), and concurrency models (MVCC).

4The 3 Core Questions Before Writing Any Query

Before writing a single line of SQL, always decompose the business requirement into three fundamental questions:

1Where do the facts live? (Identify the source table or join candidates in the FROM clause).

2Which attributes are requested? (Identify the projected fields in the SELECT clause).

3Which specific records qualify? (Identify the row filtering criteria in the WHERE clause).

Practicing this structured habit eliminates syntax hesitation and ensures your queries directly mirror real-world business logic.

5Data Invariance: Why Queries Never Mutate Stored Records

A fundamental invariant of relational databases is that a SELECT query is purely a read operation. It reads stored pages into database buffer memory, applies filtering and transformations, and streams an ephemeral result set back to the client application. Executing mathematical expressions, string concatenations, or column aliasing (e.g., annual_pay / 12 AS monthly_pay) changes only the client output presentation—the underlying records on persistent disk remain completely unchanged.

Architectural Trade-offs

Comparative Analysis: Declarative SQL vs. Imperative Code

Aspect / Dimension	Declarative SQL	Imperative Code (Java / Python / C++)
Programming Paradigm	Declarative: You specify WHAT result set is needed.	Imperative: You write step-by-step loops and control flow instructing HOW to compute it.
Execution Optimization	Database Query Optimizer evaluates table statistics to pick the fastest index or join algorithm.	Developer must manually choose data structures, hash maps, binary searches, and loop nesting.
Data Locality	Filters and aggregates data directly on the database server next to the storage disk.	Requires fetching records across the network into application heap memory before filtering.
Result Ordering	Unordered by definition. Order is NEVER guaranteed unless an explicit ORDER BY clause is provided.	Array or List order is deterministic based on insertion and loop traversal order.
Concurrency & State	ACID transactions handle multi-user locks and MVCC snapshots automatically.	Developer must manage thread locks, mutexes, race conditions, and shared state.

Canonical Syntax Template

Standard SQL Query Template

SQLsyntax-template.sql

1SELECT column_1, column_2, expression AS alias_name

2FROM source_table

3WHERE row_filter_condition;

Visual Data Transformations

Interactive Query Walkthroughs

Inspect the source tables, examine the query logic, and verify the resulting dataset.

1Example 1 — Read One Specific Column (Projection)

Table: customers

5 rows

customer_id	display_name	city	signup_date
101	Asha Verma	Jaipur	2024-01-15
102	Rahul Mehta	Mumbai	2024-02-10
103	Priya Sharma	Bengaluru	2024-02-18
104	Karan Kapoor	Jaipur	2024-03-01
105	Sneha Patel	Ahmedabad	2024-03-12

Source table: 5 rows representing registered customer accounts

SQLexample-1.sql

1SELECT display_name

2FROM customers;

Query Result Set

5 rows

display_name
Asha Verma
Rahul Mehta
Priya Sharma
Karan Kapoor
Sneha Patel

Result: 1 column projected for all 5 customer rows

What Happens: The SELECT clause projects only the 'display_name' attribute. Other attributes (city, signup_date) are discarded during projection, reducing I/O and network transfer bandwidth.

2Example 2 — Read Multiple Columns with Selective Attributes

Table: customers

5 rows

customer_id	display_name	city	signup_date
101	Asha Verma	Jaipur	2024-01-15
102	Rahul Mehta	Mumbai	2024-02-10
103	Priya Sharma	Bengaluru	2024-02-18
104	Karan Kapoor	Jaipur	2024-03-01
105	Sneha Patel	Ahmedabad	2024-03-12

Source table: Customer records

SQLexample-2.sql

1SELECT display_name, city

2FROM customers;

Query Result Set

5 rows

display_name	city
Asha Verma	Jaipur
Rahul Mehta	Mumbai
Priya Sharma	Bengaluru
Karan Kapoor	Jaipur
Sneha Patel	Ahmedabad

Result: 2 specific columns returned for all 5 rows

What Happens: Each returned record contains exactly the display name and city. Avoid using SELECT * in production code because explicit column lists protect your API contract and allow covering index optimization.

3Example 3 — Filter Qualified Rows with WHERE Condition

Table: customers

5 rows

customer_id	display_name	city	signup_date
101	Asha Verma	Jaipur	2024-01-15
102	Rahul Mehta	Mumbai	2024-02-10
103	Priya Sharma	Bengaluru	2024-02-18
104	Karan Kapoor	Jaipur	2024-03-01
105	Sneha Patel	Ahmedabad	2024-03-12

Source table: Notice customers located in Jaipur vs other cities

SQLexample-3.sql

1SELECT display_name, city

2FROM customers

3WHERE city = 'Jaipur';

Query Result Set

2 rows

display_name	city
Asha Verma	Jaipur
Karan Kapoor	Jaipur

Result: Only the 2 rows matching city = 'Jaipur'

What Happens: The WHERE clause filters rows during retrieval. Only tuples where the boolean predicate evaluates to TRUE are passed forward to the SELECT projection.

4Example 4 — Aggregate Function Returning a Scalar Summary

Table: customers

5 rows

customer_id	display_name	city
101	Asha Verma	Jaipur
102	Rahul Mehta	Mumbai
103	Priya Sharma	Bengaluru
104	Karan Kapoor	Jaipur
105	Sneha Patel	Ahmedabad

Source table: 5 total customer records

SQLexample-4.sql

1SELECT COUNT(*) AS total_customers

2FROM customers;

Query Result Set

1 row

total_customers
5

Result: 1 summary row and 1 column

What Happens: COUNT(*) computes the total number of rows matching the query criteria. The AS keyword assigns a meaningful output alias 'total_customers' to the calculated scalar result.

5Example 5 — Column Aliasing and Calculated Expressions

Table: employees

3 rows

emp_id	full_name	annual_salary
1	Vikram Joshi	1200000
2	Ananya Roy	1800000
3	Deepak Nair	960000

Source table: Employee annual compensation

SQLexample-5.sql

1SELECT full_name, annual_salary / 12 AS monthly_salary

2FROM employees;

Query Result Set

3 rows

full_name	monthly_salary
Vikram Joshi	100000
Ananya Roy	150000
Deepak Nair	80000

Result: Calculated monthly amount without updating source table

What Happens: The mathematical expression (annual_salary / 12) is evaluated dynamically for each tuple. The source table on disk remains unmodified—the calculation exists purely within the stream of the result set.

Important Invariants & Best Practices

SQL is declarative: you describe what rows and columns you want, not how to iterate over them.
A table's rows have no defined natural order; without an explicit ORDER BY clause, row return order is unpredictable.
SELECT statements are non-destructive read operations; they never modify data stored on disk.
SQL keywords are case-insensitive by convention (SELECT vs select), but uppercase is the universal industry standard for readability.
ANSI SQL is the standard; PostgreSQL, MySQL, and SQLite are database engines with dialect differences.

Frequent Interview Pitfalls & Candidate Traps

Avoid these common misconceptions that lead to bugs or failed technical interviews:

•Assuming the first row returned by a query is always the oldest, newest, or first-inserted record. An explicit ORDER BY is required whenever ordering matters.
•Using SELECT * in production API endpoints and backend queries. This breaks schema contracts, transfers unnecessary bytes, and defeats covering indexes.
•Believing that column aliases or mathematical expressions in a SELECT clause permanently update the underlying table on disk.
•Confusing SQL (the language standard) with MySQL (a specific database management system product).

Placement Interview Perspective

In technical interviews, interviewers use SQL fundamentals to test whether you have a relational mental model or just memorize syntax. Be prepared to explain: (1) Why SQL is declarative while Java/Python is imperative, (2) The 5 stages of query execution (Parse -> Validate -> Optimize -> Execute -> Fetch), and (3) Why omitting ORDER BY means the database is free to return rows in any order depending on physical disk layout or parallel worker execution.