Learn MySql

MySql

22 November 2025 by

DATA REVOLUTION CONSULTING GROUP

seamlessly.

Sample schema (MySQL) — use for all questions

-- Customers
customers(customer_id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100), city VARCHAR(50), created_at DATE);

-- Products
products(product_id INT PRIMARY KEY, name VARCHAR(100), category VARCHAR(50), price DECIMAL(10,2), supplier_id INT);

-- Orders
orders(order_id INT PRIMARY KEY, customer_id INT, order_date DATE, status VARCHAR(20), total DECIMAL(12,2));

-- Order items
order_items(item_id INT PRIMARY KEY, order_id INT, product_id INT, quantity INT, unit_price DECIMAL(10,2));

-- Employees
employees(employee_id INT PRIMARY KEY, name VARCHAR(100), dept_id INT, hire_date DATE, salary DECIMAL(12,2));

-- Departments
departments(dept_id INT PRIMARY KEY, dept_name VARCHAR(50));

-- Suppliers
suppliers(supplier_id INT PRIMARY KEY, supplier_name VARCHAR(100), country VARCHAR(50));

50 SQL questions & answers (MySQL 8+)

Basics (1–10)

Q: List all customers.
A:
```
SELECT * FROM customers;
```
Returns all customer rows.
Q: Select customer names and emails.
A:
```
SELECT name, email FROM customers;
```

Q: Find customers in city 'Mumbai'.

SELECT * FROM customers WHERE city = 'Mumbai';

Q: Count total number of customers.

SELECT COUNT(*) AS total_customers FROM customers;

Q: List distinct product categories.

SELECT DISTINCT category FROM products;

Q: Get orders placed after 2024-01-01.

SELECT * FROM orders WHERE order_date > '2024-01-01';

Q: Show order details for order_id = 101.

SELECT o.*, oi.* 
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
WHERE o.order_id = 101;

Q: Insert a new product.

INSERT INTO products (product_id, name, category, price, supplier_id)
VALUES (501, 'Notebook', 'Stationery', 59.99, 10);

Q: Update a customer's city.

UPDATE customers SET city = 'Bengaluru' WHERE customer_id = 12;

Q: Delete an order (and its items).

START TRANSACTION;
DELETE FROM order_items WHERE order_id = 200;
DELETE FROM orders WHERE order_id = 200;
COMMIT;

Intermediate (11–25)

Q: Total sales (sum of orders total).

SELECT SUM(total) AS total_sales FROM orders;

Q: Total sales by month.

SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total) AS sales
FROM orders
GROUP BY month
ORDER BY month;

Q: Top 5 products by quantity sold.

SELECT p.product_id, p.name, SUM(oi.quantity) AS total_qty
FROM products p
JOIN order_items oi ON p.product_id = oi.product_id
GROUP BY p.product_id, p.name
ORDER BY total_qty DESC
LIMIT 5;

Q: Customers who never placed an order.

SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Q: Average order value.

SELECT AVG(total) AS avg_order_value FROM orders;

Q: Orders with total > average order.

SELECT * FROM orders
WHERE total > (SELECT AVG(total) FROM orders);

Q: Quantity and revenue for each order item.

SELECT oi.order_id, oi.product_id, oi.quantity, (oi.quantity * oi.unit_price) AS revenue
FROM order_items oi;

Q: Product price increase by 10% for category 'Electronics'.

UPDATE products
SET price = price * 1.10
WHERE category = 'Electronics';

Q: Get last order date per customer.

SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id;

Q: Use CASE to classify orders as 'Small', 'Medium', 'Large'.

SELECT order_id, total,
  CASE
    WHEN total < 100 THEN 'Small'
    WHEN total BETWEEN 100 AND 500 THEN 'Medium'
    ELSE 'Large'
  END AS size
FROM orders;

Q: Find products with price between 50 and 200.

SELECT * FROM products WHERE price BETWEEN 50 AND 200;

Q: Customers count per city (having > 5).

SELECT city, COUNT(*) AS customers
FROM customers
GROUP BY city
HAVING COUNT(*) > 5;

Q: Create a view for monthly sales summary.

CREATE VIEW monthly_sales AS
SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total) AS sales
FROM orders
GROUP BY month;

Q: Use a common table expression (CTE) for top customers by sales.

WITH cust_sales AS (
  SELECT customer_id, SUM(total) AS total_spent
  FROM orders
  GROUP BY customer_id
)
SELECT c.customer_id, c.name, cs.total_spent
FROM cust_sales cs
JOIN customers c ON cs.customer_id = c.customer_id
ORDER BY cs.total_spent DESC;

Q: Combine product name and category as single column.

SELECT CONCAT(name, ' (', category, ')') AS product_label FROM products;

Advanced joins & subqueries (26–35)

Q: Orders with product names (flatten one row per item).

SELECT o.order_id, o.order_date, c.name AS customer, p.name AS product, oi.quantity, oi.unit_price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

Q: Customers and their total order count & total spent.

SELECT c.customer_id, c.name, COUNT(o.order_id) AS orders_count, COALESCE(SUM(o.total),0) AS total_spent
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.name;

Q: Orders that include at least one product from supplier_id = 5.

SELECT DISTINCT o.order_id
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
WHERE p.supplier_id = 5;

Q: Find customers who bought all products in a given category (set division).

-- Customers who purchased every product in category 'Books'
SELECT c.customer_id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM products p
  WHERE p.category = 'Books'
  AND NOT EXISTS (
    SELECT 1 FROM order_items oi
    JOIN orders o ON oi.order_id = o.order_id
    WHERE o.customer_id = c.customer_id AND oi.product_id = p.product_id
  )
);

Q: Top product per month by revenue (use window functions).

SELECT month, product_id, name, revenue
FROM (
  SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month,
         p.product_id,
         p.name,
         SUM(oi.quantity * oi.unit_price) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(o.order_date, '%Y-%m') ORDER BY SUM(oi.quantity * oi.unit_price) DESC) AS rn
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN products p ON oi.product_id = p.product_id
  GROUP BY month, p.product_id, p.name
) t
WHERE rn = 1;

Q: Find second highest salary in employees.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

Q: Rank employees by salary within department.

SELECT e.employee_id, e.name, e.dept_id, e.salary,
  RANK() OVER (PARTITION BY e.dept_id ORDER BY e.salary DESC) AS dept_rank
FROM employees e;

Q: Cumulative sales by date.

SELECT order_date,
       SUM(total) AS daily_sales,
       SUM(SUM(total)) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_sales
FROM orders
GROUP BY order_date
ORDER BY order_date;

Q: Delete duplicate customers keeping the lowest customer_id.

DELETE c1 FROM customers c1
INNER JOIN customers c2
  ON c1.email = c2.email AND c1.customer_id > c2.customer_id;

Q: Find orders where any item quantity > 100 (exists).

SELECT o.*
FROM orders o
WHERE EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.order_id = o.order_id AND oi.quantity > 100
);

Performance, indexing & optimization (36–42)

Q: Create an index to speed up orders by customer_id and order_date.

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

Q: Show query execution plan for a slow query.

EXPLAIN SELECT * FROM orders o JOIN customers c ON o.customer_id = c.customer_id WHERE o.order_date > '2024-01-01';

Q: Rewrite a correlated subquery to a JOIN (example).

-- correlated subquery
SELECT o.order_id, o.total FROM orders o
WHERE o.total > (SELECT AVG(total) FROM orders);

-- equivalent with JOIN + derived
SELECT o.order_id, o.total
FROM orders o
JOIN (SELECT AVG(total) AS avg_total FROM orders) a ON o.total > a.avg_total;

Q: Partitioned queries concept (example creating range partition by year).

CREATE TABLE orders_y (
  order_id INT PRIMARY KEY,
  customer_id INT,
  order_date DATE,
  total DECIMAL(12,2)
)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION pmax VALUES LESS THAN MAXVALUE
);

Q: Use ANALYZE TABLE to update optimizer statistics.
A:
```
ANALYZE TABLE orders;
```
Q: Use OPTIMIZE TABLE to reclaim space after large deletes.
A:
```
OPTIMIZE TABLE order_items;
```
Q: Identify missing index suggestions using performance_schema or EXPLAIN — simple guideline.
A:
Use EXPLAIN to see full table scans; add indexes on columns used in WHERE, JOIN, ORDER BY. (No single SQL answer — check EXPLAIN output.)

Advanced features, JSON, regex, security (43–50)

Q: Store product attributes as JSON and query them.

-- product table with json column
ALTER TABLE products ADD COLUMN attrs JSON;

-- query products where attrs->>'$.color' = 'red'
SELECT * FROM products WHERE JSON_UNQUOTE(JSON_EXTRACT(attrs, '$.color')) = 'red';

Q: Aggregate product names as comma-separated list per order.

SELECT o.order_id, GROUP_CONCAT(p.name SEPARATOR ', ') AS products
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id
GROUP BY o.order_id;

Q: Use regular expressions to find emails from a specific domain.
A:
```
SELECT * FROM customers WHERE email REGEXP '@example\\.com$';
```

Q: Implement a stored procedure to add an order (simplified).

DELIMITER //
CREATE PROCEDURE add_order(IN cid INT, IN odate DATE, IN total_val DECIMAL(12,2))
BEGIN
  INSERT INTO orders (customer_id, order_date, total) VALUES (cid, odate, total_val);
END //
DELIMITER ;

Q: Use transactions with rollback on error (pseudo).

START TRANSACTION;
INSERT INTO orders (...) VALUES (...);
INSERT INTO order_items (...) VALUES (...);
-- if error: ROLLBACK; else COMMIT;
COMMIT;

Q: Row-level locking for update to avoid race condition.

START TRANSACTION;
SELECT stock FROM products WHERE product_id = 10 FOR UPDATE;
-- update stock after checks
UPDATE products SET stock = stock - 1 WHERE product_id = 10;
COMMIT;

Q: Create a pivot table via SQL: monthly sales per category.

SELECT month,
  SUM(CASE WHEN category = 'Electronics' THEN revenue ELSE 0 END) AS Electronics,
  SUM(CASE WHEN category = 'Books' THEN revenue ELSE 0 END) AS Books,
  SUM(revenue) AS Total
FROM (
  SELECT DATE_FORMAT(o.order_date, '%Y-%m') AS month, p.category, SUM(oi.quantity * oi.unit_price) AS revenue
  FROM orders o
  JOIN order_items oi ON o.order_id = oi.order_id
  JOIN products p ON oi.product_id = p.product_id
  GROUP BY month, p.category
) t
GROUP BY month
ORDER BY month;

Q: Use LAG to compute month-over-month change for sales.

WITH monthly AS (
  SELECT DATE_FORMAT(order_date, '%Y-%m') AS month, SUM(total) AS sales
  FROM orders
  GROUP BY month
)
SELECT month, sales,
       LAG(sales) OVER (ORDER BY month) AS prev_sales,
       sales - LAG(sales) OVER (ORDER BY month) AS diff,
       CASE WHEN LAG(sales) OVER (ORDER BY month) = 0 THEN NULL
            ELSE (sales - LAG(sales) OVER (ORDER BY month)) / LAG(sales) OVER (ORDER BY month)
       END AS pct_change
FROM monthly;

in Blog