MySQL Top Exercises

Write a query to show the total balance using debit and credit entries

Description: A table transactions has the columns id, account_id, type (credit or debit) and amount. Show the balance of each account. Credit adds money and debit takes money.

Hint:

• Use CASE to turn a debit into a negative amount.
• Then SUM() the amounts with GROUP BY account_id.

MySQL query for the balance from debit and credit

-- sample data
CREATE TABLE transactions (id INT, account_id INT, type VARCHAR(10), amount INT);
INSERT INTO transactions VALUES
  (1, 1, 'credit', 1000),
  (2, 1, 'debit', 200),
  (3, 2, 'credit', 500),
  (4, 2, 'debit', 100),
  (5, 1, 'credit', 300);

-- the query
SELECT account_id,
       SUM(CASE WHEN type = 'credit' THEN amount ELSE -amount END) AS balance
FROM transactions
GROUP BY account_id
ORDER BY account_id;

Output:

How it works

• For account 1: 1000 – 200 + 300 = 1100.
• For account 2: 500 – 100 = 400.
• CASE works like if else inside a query.

Write a query to show the count of students by city

Description: Show the number of students in each city.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• Use COUNT(*).
• Group the rows with GROUP BY city.

MySQL query for the count of students by city

SELECT city, COUNT(*) AS total_students
FROM students
GROUP BY city
ORDER BY city;

Output:

How it works

• GROUP BY city makes one group for each city.
• COUNT(*) counts the rows of each group.

Read GROUP BY and HAVING.

Write a query to show the student name who has the second highest marks

Description: Show the name and marks of the student with the second highest marks.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• The second highest value is the highest value that is smaller than the maximum.
• Use a subquery inside a subquery.

MySQL query for the second highest marks

SELECT name, marks
FROM students
WHERE marks = (
  SELECT MAX(marks)
  FROM students
  WHERE marks < (SELECT MAX(marks) FROM students)
);

Output:

How it works

• The innermost query finds the maximum marks: 92.
• The middle query finds the maximum of the marks that are smaller than 92: 88.
• The outer query returns the student with 88 marks.
• Another way is ORDER BY marks DESC LIMIT 1 OFFSET 1.

Read MySQL Subqueries.

Write a query to select top 3 students by marks

Description: Select the names and marks of the top 3 students.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• Sort with ORDER BY marks DESC.
• Take only 3 rows with LIMIT 3.

MySQL query for the top 3 students

SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 3;

Output:

How it works

• ORDER BY marks DESC puts the highest marks first.
• LIMIT 3 keeps only the first three rows.

Read MySQL LIMIT.

Write a query to select the rows if a column has no value

Description: Show the students who are not enrolled in any course.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• A missing value is NULL.
• Do not use = NULL. Use IS NULL.

MySQL query for rows with no value

SELECT id, name, course_id
FROM students
WHERE course_id IS NULL;

Output:

How it works

• NULL is not equal to anything, not even to NULL, so = NULL never works.
• Use IS NOT NULL for the opposite.

Read IN, BETWEEN and IS NULL.

Write a query to show the names of students with their course names

Description: Show each student with the name of the course that they have joined.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• Join students.course_id with courses.id.

MySQL query for students with course names

SELECT s.name, c.course_name
FROM students s
INNER JOIN courses c ON s.course_id = c.id
ORDER BY s.id;

Output:

How it works

• INNER JOIN keeps only the students that have a matching course.
• Vikram has no course, so he is not in the result. Use LEFT JOIN to include him.

Read MySQL JOINs.