Query execution plans in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to display the query execution plan in PostgreSQL.

DBMS Theory

EXPLAIN [1] SELECT * FROM employees;

Drag options to blanks, or click blank then click option'

AANALYZE

BPLAN

CSHOW

DDESCRIBE

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXPLAIN PLAN which is not valid in PostgreSQL.

Using SHOW or DESCRIBE which do not display execution plans.

The EXPLAIN ANALYZE command runs the query and shows the actual execution plan with timing.

2fill in blank

medium

Complete the code to get the execution plan in MySQL.

DBMS Theory

[1] SELECT * FROM orders WHERE order_date > '2023-01-01';

Drag options to blanks, or click blank then click option'

ADESCRIBE

BEXPLAIN

CSHOW

DPLAN

Attempts:

3 left

💡 Hint

Common Mistakes

Using DESCRIBE which shows table structure, not execution plan.

Using SHOW which is for server info, not query plans.

In MySQL, the EXPLAIN keyword before a query shows the execution plan.

3fill in blank

hard

Fix the error in the query to show the execution plan in SQL Server.

DBMS Theory

[1] SHOWPLAN_ALL ON; SELECT * FROM customers; [1] SHOWPLAN_ALL OFF;

Drag options to blanks, or click blank then click option'

ARUN

BENABLE

CEXEC

DSET

Attempts:

3 left

💡 Hint

Common Mistakes

Using EXEC or RUN which execute queries but do not set options.

Using ENABLE which is not a valid SQL Server command.

In SQL Server, SET SHOWPLAN_ALL ON enables the display of the execution plan without running the query.

4fill in blank

hard

Fill both blanks to create a dictionary comprehension that maps table names to their row counts if count is greater than 1000.

DBMS Theory

{table: [1] for table in tables if [2] > 1000}

Drag options to blanks, or click blank then click option'

Aget_row_count(table)

Ccount

Dtable

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'table' instead of count in the condition.

Not calling the function to get counts.

The comprehension uses a function get_row_count(table) to get counts and filters where count > 1000.

5fill in blank

hard

Fill all three blanks to create a dictionary comprehension that maps query names to their execution times if time is less than 5 seconds.

DBMS Theory

{ [1]: [2] for [3] in queries if [2] < 5 }

Drag options to blanks, or click blank then click option'

Aquery.name

Bexecution_time

Cquery

Dquery.time

Attempts:

3 left

💡 Hint

Common Mistakes

Using query.time instead of execution_time variable.

Using wrong variable names in the loop.

The comprehension maps query.name to execution_time for each query where time is less than 5 seconds.

Practice

1. What is the main purpose of a query execution plan in a database?

easy

A. To backup the database

B. To show how the database will execute a query step-by-step

C. To create new tables automatically

D. To store the query results permanently

Solution

1. Step 1: Understand what a query execution plan is

   A query execution plan explains the steps the database takes to run a query.

2. Step 2: Identify the main purpose

   The plan helps users see how the database processes the query to optimize performance.

3. Final Answer:

   To show how the database will execute a query step-by-step -> Option B

4. Quick Check:

   Query execution plan = execution steps [OK]

Hint: Execution plans show query steps clearly [OK]

Common Mistakes:

• Confusing plans with data storage
• Thinking plans create tables
• Assuming plans backup data

2. Which SQL command is commonly used to view the query execution plan before running a query?

easy

A. SHOW PLAN

B. RUN

C. EXPLAIN

D. DESCRIBE

Solution

1. Step 1: Recall the command to view execution plans

   The SQL command EXPLAIN is used to display how a query will be executed.

2. Step 2: Differentiate from other commands

   RUN executes queries, SHOW PLAN is not standard, and DESCRIBE shows table structure.

3. Final Answer:

   EXPLAIN -> Option C

4. Quick Check:

   View plan = EXPLAIN [OK]

Hint: Use EXPLAIN to see query plans [OK]

Common Mistakes:

• Using RUN to view plans
• Confusing DESCRIBE with EXPLAIN
• Assuming SHOW PLAN is standard

3. Given the query SELECT * FROM employees WHERE department_id = 5;, what does the execution plan likely show?

medium

A. A full table scan of employees

B. A join operation with another table

C. A sort operation on employee names

D. An index scan on department_id if indexed

Solution

1. Step 1: Analyze the query condition

   The query filters employees by department_id = 5. If department_id has an index, the database uses it.

2. Step 2: Understand execution plan behavior

   If indexed, the plan shows an index scan to quickly find matching rows instead of scanning the whole table.

3. Final Answer:

   An index scan on department_id if indexed -> Option D

4. Quick Check:

   Indexed filter = index scan [OK]

Hint: Indexed columns use index scan in plans [OK]

Common Mistakes:

• Assuming full table scan always happens
• Expecting join when none exists
• Thinking sorting is automatic

4. A query execution plan shows a full table scan but the query filters on a column that has an index. What is the likely cause?

medium

A. The query uses a function on the indexed column

B. The index is corrupted or unusable

C. The database always prefers full scans

D. The table is empty

Solution

1. Step 1: Understand why indexes may be ignored

   If a query applies a function (like LOWER or CAST) on an indexed column, the index cannot be used directly.

2. Step 2: Rule out other options

   Corrupted indexes are rare and usually cause errors; databases do not always prefer full scans; empty tables do not cause full scans.

3. Final Answer:

   The query uses a function on the indexed column -> Option A

4. Quick Check:

   Functions on indexed columns block index use [OK]

Hint: Functions on indexed columns disable index use [OK]

Common Mistakes:

• Assuming index corruption without error
• Believing full scans are default
• Thinking empty tables cause scans

5. You want to optimize a slow query that joins two large tables. The execution plan shows a nested loop join causing delays. What is a good approach to improve performance?

hard

A. Create indexes on the join columns to enable hash or merge joins

B. Remove all indexes to force full table scans

C. Rewrite the query to use subqueries instead of joins

D. Increase the database cache size without changing the query

Solution

1. Step 1: Identify the cause of slow join

   Nested loop joins are slow on large tables without indexes on join columns.

2. Step 2: Apply indexing to improve join method

   Creating indexes on join columns allows the database to use faster join methods like hash or merge joins.

3. Step 3: Evaluate other options

   Removing indexes slows queries; rewriting to subqueries may not help; increasing cache alone may not fix join inefficiency.

4. Final Answer:

   Create indexes on the join columns to enable hash or merge joins -> Option A

5. Quick Check:

   Indexes on join keys speed joins [OK]

Hint: Index join columns to speed up joins [OK]

Common Mistakes:

• Dropping indexes thinking it helps
• Assuming subqueries are always faster
• Relying only on cache size increase