Why views simplify complex queries in MySQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a view named 'employee_view' that shows all columns from the 'employees' table.

MySQL

CREATE VIEW employee_view AS SELECT [1] FROM employees;

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

Aname

Bid

C*

Dsalary

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting only one column instead of all columns.

Using incorrect column names.

Using * selects all columns from the table, which is common when creating a simple view.

2fill in blank

medium

Complete the code to select only the 'name' and 'department' columns from the 'employee_view'.

MySQL

SELECT [1] FROM employee_view;

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

Aname, department

Bname, salary

Cid, department

Dsalary, department

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns not present in the view.

Forgetting to separate columns with a comma.

To get the employee's name and department, select those two columns separated by a comma.

3fill in blank

hard

Fix the error in the code to create a view that shows employees with salary greater than 50000.

MySQL

CREATE VIEW high_salary AS 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 '=' instead of '>'.

Using '<' which means less than.

The condition should be salary greater than 50000, so use the '>' operator.

4fill in blank

hard

Fill both blanks to create a view named 'dept_count' that shows each department and the number of employees in it.

MySQL

CREATE VIEW dept_count AS SELECT [1], COUNT(*) AS employee_count FROM employees GROUP BY [2];

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

Adepartment

Bsalary

Cid

Dname

Attempts:

3 left

💡 Hint

Common Mistakes

Grouping by a wrong column like salary or id.

Selecting a different column than the one grouped by.

Grouping by 'department' lets us count employees per department. We select 'department' to show it in the result.

5fill in blank

hard

Fill all three blanks to create a view 'top_earners' showing employee names, salaries, and only those earning more than 70000.

MySQL

CREATE VIEW top_earners AS SELECT [1], [2] FROM employees WHERE [3] > 70000;

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

Aname

Bsalary

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong columns in SELECT.

Filtering by a column other than salary.

Select 'name' and 'salary' columns, and filter where 'salary' is greater than 70000.