Query optimization strategies in DBMS Theory - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select the best index for faster query execution.

DBMS Theory

SELECT * FROM employees WHERE salary > 50000 ORDER BY [1];

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

Adepartment

Bname

Csalary

Dhire_date

Attempts:

3 left

💡 Hint

Common Mistakes

Choosing a column not used in the WHERE or ORDER BY clause.

Ignoring the column that filters the data.

The query orders results by salary, so using an index on the salary column helps speed up sorting and filtering.

2fill in blank

medium

Complete the code to rewrite the query using a join instead of a subquery for better performance.

DBMS Theory

SELECT e.name, d.department_name FROM employees e [1] departments d ON e.department_id = d.id;

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

AINNER JOIN

BLEFT JOIN

CRIGHT JOIN

DFULL JOIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using LEFT JOIN when only matching rows are needed.

Choosing a join type that returns extra rows unnecessarily.

Using an INNER JOIN replaces the subquery and returns only matching rows, which is often faster.

3fill in blank

hard

Fix the error in the query by choosing the correct clause to limit the number of rows returned.

DBMS Theory

SELECT * FROM sales [1] 10;

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

ALIMIT

BORDER BY

CGROUP BY

DWHERE

Attempts:

3 left

💡 Hint

Common Mistakes

Using WHERE to limit rows instead of filtering conditions.

Confusing ORDER BY with limiting rows.

The LIMIT clause restricts the number of rows returned by the query, which helps optimize performance when only a subset is needed.

4fill in blank

hard

Fill both blanks to create a query that uses an index and filters rows efficiently.

DBMS Theory

SELECT * FROM orders WHERE [1] = 'shipped' AND [2] > '2023-01-01';

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

Astatus

Border_date

Ccustomer_id

Dtotal_amount

Attempts:

3 left

💡 Hint

Common Mistakes

Using columns unrelated to filtering conditions.

Choosing columns that are not indexed.

Filtering by status and order_date helps the database use indexes on these columns to quickly find relevant rows.

5fill in blank

hard

Fill all three blanks to write a query that aggregates data efficiently using grouping and filtering.

DBMS Theory

SELECT [1], COUNT(*) AS total FROM sales WHERE [2] > 100 GROUP BY [3];

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

Aregion

Bamount

Ddate

Attempts:

3 left

💡 Hint

Common Mistakes

Using different columns in SELECT and GROUP BY.

Filtering on a column not related to the aggregation.

The query groups sales by region, counts the number of sales where the amount is greater than 100, and uses the same column region in SELECT and GROUP BY for correct aggregation.