How SQL communicates with the database engine - Practice Exercises

Challenge - 5 Problems

🧠 Conceptual

intermediate

How does SQL send commands to the database engine?

When you write an SQL query, how does it reach the database engine to be executed?

A The database engine reads SQL queries from a text file on the disk automatically every minute.

B The SQL query is parsed and sent through a database driver or connector which communicates with the database engine.

C SQL queries are directly typed into the database engine's memory without any intermediate step.

D SQL queries are converted into machine code by the operating system before reaching the database engine.

💡 Hint

Think about how applications connect to databases to send queries.

SQL queries are sent through a database driver or connector (like ODBC, JDBC, or native drivers). This driver parses and transmits the query to the database engine, which then executes it.

❓ query_result

intermediate

What is the output of this SQL query communication step?

Consider the following SQL query sent to a database engine:

SELECT name FROM employees WHERE id = 5;

What does the database engine return after processing this query?

A The number 5 as a confirmation of the id searched.

B An error message because the query is incomplete.

C A result set containing the name of the employee with id 5.

D A list of all employee IDs in the database.

💡 Hint

Think about what a SELECT query does and what the WHERE clause filters.

The database engine executes the query and returns a result set with the requested column(s) for rows matching the condition. Here, it returns the name of the employee whose id is 5.

📝 Syntax

advanced

Which SQL command correctly communicates to update a record?

You want to update the salary of an employee with id 10 to 60000. Which SQL command correctly sends this update to the database engine?

A UPDATE employees SET salary = 60000 WHERE id = 10;

B MODIFY employees salary = 60000 WHERE id = 10;

C CHANGE employees SET salary TO 60000 WHERE id = 10;

D ALTER employees salary = 60000 WHERE id = 10;

💡 Hint

Recall the standard SQL command to change existing data in a table.

The correct SQL command to update data is UPDATE. The other options use invalid SQL keywords and will cause syntax errors.

❓ optimization

advanced

How can SQL communication be optimized for faster query execution?

Which of the following methods helps reduce the communication overhead between SQL and the database engine for repeated queries?

A Restarting the database engine before each query to clear memory.

B Writing the full SQL query text every time without any caching.

C Sending queries as plain text files via email to the database administrator.

D Using prepared statements to send the query once and execute multiple times with different parameters.

💡 Hint

Think about how to avoid sending the full query text repeatedly.

Prepared statements allow the database engine to parse and plan the query once, then execute it multiple times with different parameters, reducing communication and processing time.

🔧 Debug

expert

Why does this SQL query fail to communicate properly with the database engine?

Given this SQL query sent to the database engine:

SELEC name FROM employees WHERE id = 3;

What error will the database engine return?

A Syntax error near 'SELEC' because the keyword is misspelled.

B No error; the query runs and returns the name for id 3.

C Permission denied error because SELECT is a restricted command.

D Runtime error due to missing semicolon at the end.

💡 Hint

Check the spelling of SQL keywords carefully.

The keyword SELECT is misspelled as SELEC, causing a syntax error. The database engine cannot parse the query and returns an error indicating the problem near the misspelled word.

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