Home » Exercises » MySQL Exercises » MySQL Insert, Update and Delete

MySQL Insert, Update and Delete

Write a query to update a column based on a condition

Description: Add 5 marks to every student who has less than 50 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:

  • Use UPDATE ... SET ... WHERE.
  • The new value can use the old value: marks + 5.

MySQL query to update a column with a condition

UPDATE students
SET marks = marks + 5
WHERE marks < 50;

SELECT name, marks FROM students WHERE name = 'Neha';

Output:

Tutorials Class - Output Window
name marks
Neha 50

How it works

  • Only Neha has less than 50 marks, so only her row is changed (45 + 5 = 50).
  • Without WHERE all rows would change.
  • To restore the data: UPDATE students SET marks = 45 WHERE name = 'Neha';

Read MySQL UPDATE.

Write a query to update the value of a column using concatenation

Description: Change the name of every student into the form “Name (City)”. 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:

  • Make a copy of the table first, so the original data stays safe.
  • Use UPDATE ... SET name = CONCAT(...).

MySQL query to update a column with CONCAT

CREATE TABLE students_copy AS SELECT * FROM students;

UPDATE students_copy
SET name = CONCAT(name, ' (', city, ')');

SELECT id, name FROM students_copy LIMIT 3;

Output:

Tutorials Class - Output Window
id name
1 Robin (Delhi)
2 Maria (Mumbai)
3 John (Pune)

How it works

  • CONCAT() joins the old name, a space, the city in brackets.
  • Because we used a copy, the real students table is not changed. Remove the copy with DROP TABLE students_copy;.

Read String Functions.

Write a query to create a new table with the data and structure of another table

Description: Create a table named students_backup with all the data of 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:

  • Use CREATE TABLE new_table AS SELECT * FROM old_table.

MySQL query to copy a table

CREATE TABLE students_backup AS
SELECT * FROM students;

SELECT COUNT(*) AS rows_in_backup FROM students_backup;

Output:

Tutorials Class - Output Window
rows_in_backup
6

How it works

  • The new table gets the same columns and all 6 rows.
  • Primary keys and indexes are not copied. Use CREATE TABLE ... LIKE if you need them, and then INSERT INTO ... SELECT.

Read the interview question create a new table from another table.

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.