20 Easy MySQL Practice Scenario Based Questions and Answers

MySQL Practice Scenario Based Questions

Below are 20 practice scenario based questions along with their answers to help you improve your MySQL query writing skills. Each question presents a situation along with a sample dataset table, and you will be asked to write queries based on the dataset provided.

Question 1:

Situation: You have a table named 'employees' with the following columns: 'id', 'name', 'age', and 'department'. Write a query to retrieve all the employees.

Answer 1:

SELECT * FROM employees;

Question 2:

Situation: You have a table named 'products' with the following columns: 'id', 'name', 'price', and 'quantity'. Write a query to retrieve the names of all the products.

Answer 2:

SELECT name FROM products;

Question 3:

Situation: You have a table named 'orders' with the following columns: 'id', 'customer', 'product', and 'quantity'. Write a query to retrieve the total number of orders.

Answer 3:

SELECT COUNT(*) FROM orders;

Question 4:

Situation: You have a table named 'employees' with the following columns: 'id', 'name', 'age', and 'department'. Write a query to retrieve the names of all the employees in the 'Sales' department.

Answer 4:

SELECT name FROM employees WHERE department = 'Sales';

Question 5:

Situation: You have a table named 'products' with the following columns: 'id', 'name', 'price', and 'quantity'. Write a query to retrieve the names and prices of all the products with a price greater than 50.

Answer 5:

SELECT name, price FROM products WHERE price > 50;

Question 6:

Situation: You have a table named 'orders' with the following columns: 'id', 'customer', 'product', and 'quantity'. Write a query to retrieve the total quantity of a specific product with the name 'Widget'.

Answer 6:

SELECT SUM(quantity) FROM orders WHERE product = 'Widget';

Question 7:

Situation: You have a table named 'employees' with the following columns: 'id', 'name', 'age', and 'department'. Write a query to retrieve the names and ages of all the employees younger than 30.

Answer 7:

SELECT name, age FROM employees WHERE age < 30;

Question 8:

Situation: You have a table named 'products' with the following columns: 'id', 'name', 'price', and 'quantity'. Write a query to retrieve the names and quantities of all the products with a quantity less than 10.

Answer 8:

SELECT name, quantity FROM products WHERE quantity < 10;

Question 9:

Situation: You have a table named 'orders' with the following columns: 'id', 'customer', 'product', and 'quantity'. Write a query to retrieve the total quantity of all orders placed by the customer with the name 'John'.

Answer 9:

SELECT SUM(quantity) FROM orders WHERE customer = 'John';

Question 10:

Situation: You have a table named 'employees' with the following columns: 'id', 'name', 'age', and 'department'. Write a query to retrieve the names and departments of all the employees older than 40.

Answer 10:

SELECT name, department FROM employees WHERE age > 40;

Question 11:

Situation: You have a table named 'products' with the following columns: 'id', 'name', 'price', and 'quantity'. Write a query to retrieve the names and prices of all the products with a price between 20 and 50.

Answer 11:

SELECT name, price FROM products WHERE price BETWEEN 20 AND 50;

Question 12:

Situation: You have a table named 'orders' with the following columns: 'id', 'customer', 'product', and 'quantity'. Write a query to retrieve the total quantity of all orders placed for the product with the name 'Chair'.

Answer 12:

SELECT SUM(quantity) FROM orders WHERE product = 'Chair';

Question 13:

Situation: You have a table named 'employees' with the following columns: 'id', 'name', 'age', and 'department'. Write a query to retrieve the names and ages of all the employees in the 'HR' department.

Answer 13:

SELECT name, age FROM employees WHERE department = 'HR';

Question 14:

Situation: You have a table named 'products' with the following columns: 'id', 'name', 'price', and 'quantity'. Write a query to retrieve the names and quantities of all the products with a quantity greater than 20.

Answer 14:

SELECT name, quantity FROM products WHERE quantity > 20;

Question 15:

Situation: You have a table named 'orders' with the following columns: 'id', 'customer', 'product', and 'quantity'. Write a query to retrieve the total quantity of all orders placed by the customer with the name 'Jane'.

Answer 15:

SELECT SUM(quantity) FROM orders WHERE customer = 'Jane';

Question 16:

Situation: You have a table named 'employees' with the following columns: 'id', 'name', 'age', and 'department'. Write a query to retrieve the names and departments of all the employees younger than 25.

Answer 16:

SELECT name, department FROM employees WHERE age < 25;

Question 17:

Situation: You have a table named 'products' with the following columns: 'id', 'name', 'price', and 'quantity'. Write a query to retrieve the names and prices of all the products with a price greater than 100.

Answer 17:

SELECT name, price FROM products WHERE price > 100;

Question 18:

Situation: You have a table named 'orders' with the following columns: 'id', 'customer', 'product', and 'quantity'. Write a query to retrieve the total quantity of all orders placed for the product with the name 'Table'.

Answer 18:

SELECT SUM(quantity) FROM orders WHERE product = 'Table';

Question 19:

Situation: You have a table named 'employees' with the following columns: 'id', 'name', 'age', and 'department'. Write a query to retrieve the names and ages of all the employees in the 'Marketing' department.

Answer 19:

SELECT name, age FROM employees WHERE department = 'Marketing';

Question 20:

Situation: You have a table named 'products' with the following columns: 'id', 'name', 'price', and 'quantity'. Write a query to retrieve the names and quantities of all the products with a quantity less than 5.

Answer 20:

SELECT name, quantity FROM products WHERE quantity < 5;

These practice scenario based questions will help you become more proficient in writing MySQL queries. Remember to analyze the given situation and dataset table carefully before writing your queries. Good luck!