Home » Exercises » MySQL Exercises » MySQL All Exercises & Assignments » Page 2

MySQL All Exercises & Assignments

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.

Write a query to delete the students who scored less than 50 marks

Description: Delete the students who have less than 50 marks. Do it on a copy of the table.

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:

  • First check the rows with a SELECT and the same WHERE.
  • Then change SELECT * to DELETE.

MySQL query to delete rows with a condition

CREATE TABLE students_copy AS SELECT * FROM students;

DELETE FROM students_copy WHERE marks < 50;

SELECT COUNT(*) AS remaining FROM students_copy;

Output:

Tutorials Class - Output Window
remaining
5

How it works

  • Only Neha (45 marks) is deleted, so 5 rows remain.
  • The original students table is safe, because we worked on a copy.

Read MySQL DELETE.