Home » Exercises » MySQL Exercises » MySQL Joins and Subqueries

MySQL Joins and Subqueries

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:

Tutorials Class - Output Window
name marks
Asha 88

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 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:

Tutorials Class - Output Window
name course_name
Robin Python
Maria Java
John Python
Asha Web Design
Neha Java

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.

Write a query to count the students of each course, including empty courses

Description: Show every course with the number of its students. A course without students must show 0.

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:

  • Start from the courses table and use LEFT JOIN.
  • Count a column of the students table, like s.id, so that NULL is not counted.

MySQL query to count the students of each course

SELECT c.course_name, COUNT(s.id) AS students
FROM courses c
LEFT JOIN students s ON s.course_id = c.id
GROUP BY c.id, c.course_name
ORDER BY c.id;

Output:

Tutorials Class - Output Window
course_name students
Python 2
Java 2
Web Design 1
C Programming 0

How it works

  • LEFT JOIN keeps all the courses, even those without students.
  • COUNT(s.id) counts only real students. COUNT(*) would give 1 for C Programming.

Read MySQL JOINs.

Write a query to find the students who scored above the average marks

Description: Show the students whose marks are higher than the average marks of all 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:

  • The average comes from a subquery: (SELECT AVG(marks) FROM students).

MySQL query for students above the average

SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students)
ORDER BY marks DESC;

Output:

Tutorials Class - Output Window
name marks
Maria 92
Asha 88
Robin 85
John 78

How it works

  • The average marks are 453 / 6 = 75.5.
  • Four students are above it.

Read Subqueries.