Query optimization techniques in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all columns from the employees table.

MySQL

SELECT [1] FROM employees;

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

AANY

B*

CALL

DEVERY

Attempts:

3 left

💡 Hint

Common Mistakes

Using ALL instead of * causes syntax error.

Using ANY or EVERY are not valid in this context.

The asterisk (*) selects all columns from the table.

2fill in blank

medium

Complete the code to filter employees with salary greater than 50000.

MySQL

SELECT * FROM employees WHERE salary [1] 50000;

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

A>

B<

C=

D<=

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' will select salaries less than 50000.

Using '=' will select salaries exactly 50000.

The '>' operator filters rows where salary is greater than 50000.

3fill in blank

hard

Fix the error in the query to count employees in each department.

MySQL

SELECT department, COUNT(*) [1] employees GROUP BY department;

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

AON

BIN

CFROM

DWHERE

Attempts:

3 left

💡 Hint

Common Mistakes

Using IN or ON causes syntax errors here.

WHERE is used for filtering, not specifying tables.

The FROM keyword specifies the table to select data from.

4fill in blank

hard

Fill both blanks to optimize the query by selecting only needed columns and filtering.

MySQL

SELECT [1] FROM employees WHERE [2] > 100000;

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

Aname

Bsalary

Cage

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting all columns instead of just needed ones.

Filtering by a column not related to salary.

Selecting only the 'name' column and filtering by 'salary' > 100000 improves query efficiency.

5fill in blank

hard

Fill all three blanks to create an index on the salary column to speed up queries.

MySQL

CREATE [1] INDEX [2] ON employees([3]);

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

AUNIQUE

Bidx_salary

Csalary

DPRIMARY

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY causes a syntax error.

Naming the index incorrectly.

Indexing the wrong column.

Creating a UNIQUE INDEX named 'idx_salary' on the 'salary' column helps optimize queries filtering by salary.