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.