Why table design affects performance in MySQL - Test Your Understanding

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'

A*

BALL

Ccolumns

Deverything

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'ALL' instead of '*'

Writing 'columns' or 'everything' which are not valid SQL keywords

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

2fill in blank

medium

Complete the code to create an index on the 'email' column to improve search speed.

MySQL

CREATE INDEX idx_email ON users([1]);

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

Ausername

Bpassword

Cemail

Did

Attempts:

3 left

💡 Hint

Common Mistakes

Indexing columns that are rarely searched

Indexing large text columns unnecessarily

Creating an index on the 'email' column helps speed up queries filtering by email.

3fill in blank

hard

Fix the error in the query to select 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 '=' which selects only salaries equal to 50000

Using '<' which selects salaries less than 50000

The '>' operator selects salaries greater than 50000.

4fill in blank

hard

Fill both blanks to create a table with an auto-incrementing primary key.

MySQL

CREATE TABLE products (id [1] [2] PRIMARY KEY, name VARCHAR(100));

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

AINT

BPRIMARY KEY

CAUTO_INCREMENT

DVARCHAR

Attempts:

3 left

💡 Hint

Common Mistakes

Using VARCHAR for the id column

Forgetting AUTO_INCREMENT for unique IDs

The 'id' column should be an INT and AUTO_INCREMENT to generate unique IDs automatically.

5fill in blank

hard

Fill all three blanks to select the average salary grouped by department.

MySQL

SELECT [1], AVG([2]) FROM employees GROUP BY [3];

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

Adepartment

Bsalary

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by name instead of department

Averaging the wrong column

We select the department, calculate the average salary, and group results by department.