How SQL communicates with the database engine - Visual Walkthrough

Concept Flow - How SQL communicates with the database engine

User writes SQL query

↓

SQL Query sent to DB Engine

↓

Parser checks syntax

↓

Query optimizer

↓

Execution plan created

↓

Query executed on data

↓

Results sent back to user

This flow shows how a SQL query is sent to the database engine, checked, optimized, executed, and results returned.

Execution Sample

SQL

SELECT name FROM employees WHERE age > 30;

This query asks the database to find names of employees older than 30.

Execution Table

Step	Action	Evaluation	Result
1	Receive SQL query	Query: SELECT name FROM employees WHERE age > 30;	Query accepted
2	Parse query	Check syntax	Syntax valid
3	Optimize query	Create execution plan	Plan: Scan employees, filter age > 30, select name
4	Execute query	Run plan on data	Rows matching condition found
5	Return results	Send names to user	Result set sent

💡 Query execution completes after results are sent back to the user.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
SQL Query	SELECT name FROM employees WHERE age > 30;	Parsed and valid	Execution plan created	Rows filtered and selected	Result set ready

Key Moments - 3 Insights

Why does the database check the query syntax before running it?

The database checks syntax first (see Step 2 in execution_table) to make sure the query is written correctly. If syntax is wrong, it stops and returns an error to avoid running a bad query.

What is the purpose of the query optimizer?

The optimizer (Step 3) creates the best plan to get data efficiently. It decides how to scan tables and filter rows before running the query.

When does the database actually read data from tables?

Data is read during execution (Step 4) after the plan is ready. This is when the database scans the table and applies filters.

Visual Quiz - 3 Questions

Look at the execution_table, what happens at Step 2?

A The query is executed on data

B The query results are sent to the user

C The query syntax is checked

D The execution plan is created

💡 Hint

Check the 'Action' and 'Evaluation' columns at Step 2 in execution_table

At which step does the database create the execution plan?

A Step 3

B Step 4

C Step 1

D Step 5

💡 Hint

Look for 'Create execution plan' in the 'Evaluation' column

If the query syntax is invalid, what will happen according to the concept_flow?

A The query will run anyway

B The database will return an error

C The optimizer will fix the query

D The results will be empty

💡 Hint

See the branch from 'Parser checks syntax' to 'Invalid?' in concept_flow

Concept Snapshot

SQL query flow:
1. User writes query
2. DB engine parses syntax
3. If valid, optimizer creates plan
4. Execute plan on data
5. Return results
Syntax errors stop execution early.

Full Transcript

When you write a SQL query, it is sent to the database engine. The engine first checks if the query is written correctly by parsing its syntax. If the syntax is wrong, it stops and returns an error. If the syntax is correct, the engine creates an execution plan to decide how to get the data efficiently. Then it runs the plan by scanning tables and filtering rows. Finally, it sends the results back to you. This process ensures your query runs correctly and quickly.

Practice

1. What is the main role of SQL when working with a database engine?

easy

A. To store data physically on the disk

B. To send instructions that the database engine executes

C. To design the hardware of the database server

D. To create user interfaces for database applications

Solution

1. Step 1: Understand SQL's purpose

   SQL is a language used to communicate commands to the database engine.

2. Step 2: Identify the database engine's role

   The database engine executes these commands to manage data.

3. Final Answer:

   To send instructions that the database engine executes -> Option B

4. Quick Check:

   SQL sends instructions = A [OK]

Hint: SQL sends commands to the engine, not hardware or UI [OK]

Common Mistakes:

• Confusing SQL with physical data storage
• Thinking SQL designs hardware
• Assuming SQL creates user interfaces

2. Which of the following is the correct way to write a SQL command to select all columns from a table named users?

easy

A. SELECT * FROM users;

B. GET ALL FROM users;

C. SHOW * FROM users;

D. FETCH ALL users;

Solution

1. Step 1: Recall SQL SELECT syntax

   The correct syntax to get all columns is SELECT * FROM table_name;.

2. Step 2: Match syntax to options

   Only SELECT * FROM users; matches the correct SQL syntax.

3. Final Answer:

   SELECT * FROM users; -> Option A

4. Quick Check:

   Correct SELECT syntax = B [OK]

Hint: Use SELECT * FROM table; to get all columns [OK]

Common Mistakes:

• Using non-SQL keywords like GET or FETCH
• Omitting FROM keyword
• Using SHOW incorrectly

3. Given the table employees with columns id, name, and salary, what will this SQL query return?

SELECT name FROM employees WHERE salary > 50000;

medium

A. Syntax error due to incorrect WHERE clause

B. All columns of employees earning more than 50000

C. Names of employees earning less than 50000

D. Names of employees earning more than 50000

Solution

1. Step 1: Analyze the SELECT clause

   The query selects only the name column.

2. Step 2: Analyze the WHERE condition

   It filters rows where salary > 50000, so only employees earning more than 50000 are included.

3. Final Answer:

   Names of employees earning more than 50000 -> Option D

4. Quick Check:

   SELECT name with salary > 50000 = D [OK]

Hint: WHERE filters rows; SELECT chooses columns [OK]

Common Mistakes:

• Confusing column selection with all columns
• Misreading > as <
• Thinking WHERE causes syntax error

4. Identify the error in this SQL query:

SELEC name FROM customers;

medium

A. Table name is incorrect

B. Missing FROM keyword

C. Misspelled SELECT keyword

D. No error, query is correct

Solution

1. Step 1: Check SQL keywords

   The keyword SELECT is misspelled as SELEC.

2. Step 2: Verify other parts

   The FROM keyword and table name customers are correct.

3. Final Answer:

   Misspelled SELECT keyword -> Option C

4. Quick Check:

   Keyword spelling error = C [OK]

Hint: Check spelling of SQL keywords carefully [OK]

Common Mistakes:

• Ignoring small typos in keywords
• Assuming table name is wrong without checking
• Thinking missing FROM when it is present

5. You want to get the total number of rows in the orders table. Which SQL command correctly communicates this request to the database engine?

hard

A. SELECT COUNT(*) FROM orders;

B. COUNT * FROM orders;

C. GET TOTAL orders;

D. SELECT TOTAL FROM orders;

Solution

1. Step 1: Recall aggregate function syntax

   To count rows, use SELECT COUNT(*) FROM table;.

2. Step 2: Match options to correct syntax

   Only SELECT COUNT(*) FROM orders; uses the correct SQL syntax with SELECT and COUNT(*).

3. Final Answer:

   SELECT COUNT(*) FROM orders; -> Option A

4. Quick Check:

   Correct COUNT syntax = A [OK]

Hint: Use SELECT COUNT(*) FROM table; to count rows [OK]

Common Mistakes:

• Omitting SELECT keyword
• Using non-SQL commands like GET or TOTAL
• Misplacing COUNT function