MySQL All Exercises & Assignments

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 show the average marks of each city

Description: Show each city with the average marks of its 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 AVG(marks) with GROUP BY city.
• Use ROUND() to show 2 decimals.

MySQL query for the average marks by city

SELECT city, ROUND(AVG(marks), 2) AS average_marks
FROM students
GROUP BY city
ORDER BY city;

Output:

How it works

• For Delhi: (85 + 65) / 2 = 75.
• For Mumbai: (92 + 45) / 2 = 68.5.

Read Aggregate Functions.

Write a query to get all column names of a database table

Description: Get the names of all columns of the students 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:

• Use SHOW COLUMNS FROM.
• Or read the information_schema.columns table.

MySQL query to get the column names

-- way 1
SHOW COLUMNS FROM students;

-- way 2: only the names
SELECT COLUMN_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'school' AND TABLE_NAME = 'students'
ORDER BY ORDINAL_POSITION;

Output:

How it works

• information_schema is a database that has information about all other databases.
• Change 'school' to the name of your database.

Read MySQL CREATE TABLE (the DESCRIBE command shows the same details).

Write a query to get data of selected columns from a table

Description: Show only the name and the city of every student.

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:

• Write the column names after SELECT, separated by commas.

MySQL query for selected columns

SELECT name, city
FROM students;

Output:

How it works

• Only the listed columns are returned.
• SELECT * returns all the columns.

Read MySQL SELECT.

Write a query to select the student with the maximum marks

Description: Show the name and marks of the student who has the 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:

• MAX(marks) gives the highest marks.
• Or sort in descending order and take one row.

MySQL query for the student with maximum marks

-- with a subquery
SELECT name, marks
FROM students
WHERE marks = (SELECT MAX(marks) FROM students);

-- with ORDER BY and LIMIT
SELECT name, marks
FROM students
ORDER BY marks DESC
LIMIT 1;

Output:

How it works

• The subquery returns 92, and the outer query finds the row with 92 marks.
• The subquery way returns more than one row if two students have the same top marks. The LIMIT 1 way returns only one.

Read 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 find the cities that have more than one student

Description: Show only those cities where two or more students live.

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:

• Group by city, then filter the groups with HAVING COUNT(*) > 1.

MySQL query for cities with more than one student

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

Output:

How it works

• WHERE cannot use COUNT(), because it runs before the grouping.
• HAVING filters the groups after they are made.

Read GROUP BY and HAVING.

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:

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:

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 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 fetch the names of the students in upper case

Description: Show every student name in capital letters.

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 the UPPER() function.
• Give the result a name with AS.

MySQL query for upper case names

SELECT UPPER(name) AS name_upper
FROM students;

Output:

How it works

• UPPER() changes the text to capital letters in the result only. The stored data is not changed.
• LOWER() does the opposite.

Read String Functions.

Write a query to fetch the number of students from Delhi

Description: Count the students who live in Delhi.

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(*) with WHERE city = 'Delhi'.

MySQL query to count the students of Delhi

SELECT COUNT(*) AS students_from_delhi
FROM students
WHERE city = 'Delhi';

Output:

How it works

• WHERE keeps only the Delhi rows: Robin and Vikram.
• COUNT(*) counts them.

Read Aggregate Functions.

Write a query to retrieve the first four characters of the name

Description: Show the first four letters of every student name.

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 LEFT(name, 4) or SUBSTRING(name, 1, 4).

MySQL query for the first four characters

SELECT name, LEFT(name, 4) AS first_four
FROM students;

Output:

How it works

• LEFT(text, n) returns the first n characters.
• Short names are returned fully.

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:

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 find the names of students that begin with ‘A’

Description: Find all the students whose name starts with the letter A.

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 LIKE 'A%'.

MySQL query for names that begin with A

SELECT name
FROM students
WHERE name LIKE 'A%';

Output:

How it works

• % matches any number of characters after the A.
• For names ending with A use '%a'.

Read MySQL LIKE.

Write a query to fetch the top 2 records

Description: Fetch the 2 students with the highest marks. Then think how to fetch the top N.

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 first, then limit.

MySQL query for the top 2 records

SELECT * FROM students
ORDER BY marks DESC
LIMIT 2;

Output:

How it works

• Change the number after LIMIT to get the top N.
• Without ORDER BY the rows are not guaranteed to be the “top” ones.

Read MySQL LIMIT.

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.

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:

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.