Creating views in MySQL - Interactive Practice

Practice - 5 Tasks

1fill in blank

easy

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

MySQL

CREATE VIEW employee_view AS SELECT * FROM [1];

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

Aemployees

Bemployee_view

Cemployee

Demployee_table

Attempts:

3 left

💡 Hint

Common Mistakes

Using the view name instead of the table name.

Using a singular form of the table name.

The view should select from the existing table named 'employees'.

2fill in blank

medium

Complete the code to create a view named active_customers that selects all columns from customers where the status is 'active'.

MySQL

CREATE VIEW active_customers AS SELECT * FROM customers WHERE status = '[1]';

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

Apending

Binactive

Cactive

Ddeleted

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'inactive' or other statuses instead of 'active'.

Forgetting to put the status value in quotes.

The view filters customers whose status is 'active'.

3fill in blank

hard

Fix the error in the code to create a view named order_summary that shows order_id and total_amount from the orders table.

MySQL

CREATE VIEW order_summary AS SELECT order_id, [1] FROM orders;

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

Atotal_amount

Btotal-amount

CtotalAmount

Dtotalamount

Attempts:

3 left

💡 Hint

Common Mistakes

Using camelCase or missing underscores in column names.

Using hyphens which are invalid in column names.

The correct column name is 'total_amount' with an underscore.

4fill in blank

hard

Fill both blanks to create a view named high_salary_employees that selects name and salary from employees where salary is greater than 50000.

MySQL

CREATE VIEW high_salary_employees AS SELECT [1], [2] FROM employees WHERE salary > 50000;

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

Aname

Bsalary

Cage

Ddepartment

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting columns not related to salary or name.

Forgetting to include both required columns.

The view should select 'name' and 'salary' columns.

5fill in blank

hard

Fill all three blanks to create a view named customer_orders that selects customer_id, order_id, and order_date from the orders table where order_date is after '2023-01-01'.

MySQL

CREATE VIEW customer_orders AS SELECT [1], [2], [3] FROM orders WHERE order_date > '2023-01-01';

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

Acustomer_id

Border_id

Corder_date

Dstatus

Attempts:

3 left

💡 Hint

Common Mistakes

Missing one or more required columns.

Including columns not specified in the instruction.

The view selects 'customer_id', 'order_id', and 'order_date' columns.

Practice

1. What is the main purpose of creating a view in MySQL?

easy

A. To delete data from multiple tables at once

B. To save a SELECT query as a virtual table for easy reuse

C. To permanently store data physically in the database

D. To create a backup of the database

Solution

1. Step 1: Understand what a view is

   A view is a virtual table that stores a SELECT query, not data itself.

2. Step 2: Identify the purpose of views

   Views simplify complex queries and allow reuse without storing data physically.

3. Final Answer:

   To save a SELECT query as a virtual table for easy reuse -> Option B

4. Quick Check:

   Views = virtual tables for queries [OK]

Hint: Views store queries, not data, for easy reuse [OK]

Common Mistakes:

• Thinking views store data physically
• Confusing views with backups
• Assuming views delete data

2. Which of the following is the correct syntax to create a view named employee_view showing id and name from employees table?

easy

A. VIEW CREATE employee_view AS SELECT id, name FROM employees;

B. CREATE employee_view VIEW AS SELECT id, name FROM employees;

C. CREATE VIEW employee_view AS SELECT id, name FROM employees;

D. CREATE VIEW employee_view SELECT id, name FROM employees;

Solution

1. Step 1: Recall the correct CREATE VIEW syntax

   The syntax is: CREATE VIEW view_name AS SELECT ...

2. Step 2: Match the syntax with options

   CREATE VIEW employee_view AS SELECT id, name FROM employees; matches the correct syntax exactly.

3. Final Answer:

   CREATE VIEW employee_view AS SELECT id, name FROM employees; -> Option C

4. Quick Check:

   CREATE VIEW ... AS SELECT ... [OK]

Hint: Remember: CREATE VIEW name AS SELECT ... [OK]

Common Mistakes:

• Swapping keywords CREATE and VIEW
• Omitting AS keyword
• Incorrect keyword order

3. Given the table products with columns id, name, and price, what will be the output of SELECT * FROM cheap_products; if cheap_products is created as CREATE VIEW cheap_products AS SELECT id, name FROM products WHERE price < 50;?

medium

A. Only id and name columns of products with price less than 50

B. All columns (id, name, price) of products with price less than 50

C. Only products with price greater than or equal to 50

D. Syntax error because price column is missing in view

Solution

1. Step 1: Analyze the view definition

   The view selects only id and name columns where price is less than 50.

2. Step 2: Understand the SELECT * from the view

   Since the view only has id and name, SELECT * returns only these columns for matching rows.

3. Final Answer:

   Only id and name columns of products with price less than 50 -> Option A

4. Quick Check:

   View columns = selected columns only [OK]

Hint: View shows only columns defined in its SELECT [OK]

Common Mistakes:

• Expecting all original table columns
• Confusing filter condition with columns selected
• Assuming syntax error if columns omitted

4. What is wrong with this statement?
CREATE VIEW sales_view AS SELECT order_id, total FROM orders WHERE;

medium

A. Using SELECT instead of INSERT

B. Missing AS keyword before SELECT

C. Incorrect view name syntax

D. Missing condition after WHERE clause

Solution

1. Step 1: Check the syntax after WHERE

   The WHERE clause must have a condition; here it is empty.

2. Step 2: Confirm other parts are correct

   AS keyword is present, view name is valid, SELECT is correct for views.

3. Final Answer:

   Missing condition after WHERE clause -> Option D

4. Quick Check:

   WHERE requires a condition [OK]

Hint: WHERE must always have a condition [OK]

Common Mistakes:

• Leaving WHERE empty
• Confusing AS placement
• Using wrong SQL command for views

5. You want to create a view active_customers that shows customer id, name, and their total orders from customers and orders tables. Which query correctly creates this view?

hard

A. CREATE VIEW active_customers AS SELECT c.id, c.name, COUNT(o.order_id) AS total_orders FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name;

B. CREATE VIEW active_customers AS SELECT id, name, total_orders FROM customers;

C. CREATE VIEW active_customers AS SELECT c.id, c.name, SUM(o.price) FROM customers c JOIN orders o ON c.id = o.customer_id;

D. CREATE VIEW active_customers AS SELECT c.id, c.name, COUNT(o.order_id) FROM customers c, orders o WHERE c.id = o.customer_id;

Solution

1. Step 1: Understand the requirement

   The view must show customer id, name, and total orders count, including customers with zero orders.

2. Step 2: Analyze each option

   CREATE VIEW active_customers AS SELECT c.id, c.name, COUNT(o.order_id) AS total_orders FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name; uses LEFT JOIN to include all customers and counts orders grouped by customer, which is correct.
   CREATE VIEW active_customers AS SELECT id, name, total_orders FROM customers; misses orders data.
   CREATE VIEW active_customers AS SELECT c.id, c.name, SUM(o.price) FROM customers c JOIN orders o ON c.id = o.customer_id; sums price but misses GROUP BY and alias.
   CREATE VIEW active_customers AS SELECT c.id, c.name, COUNT(o.order_id) FROM customers c, orders o WHERE c.id = o.customer_id; uses implicit join without GROUP BY, causing incorrect aggregation.

3. Final Answer:

   CREATE VIEW active_customers AS SELECT c.id, c.name, COUNT(o.order_id) AS total_orders FROM customers c LEFT JOIN orders o ON c.id = o.customer_id GROUP BY c.id, c.name; -> Option A

4. Quick Check:

   LEFT JOIN + GROUP BY + COUNT = correct aggregation [OK]

Hint: Use LEFT JOIN and GROUP BY to count related rows [OK]

Common Mistakes:

• Forgetting GROUP BY with aggregation
• Using INNER JOIN and missing customers without orders
• Not aliasing aggregated columns