Home » Exercises » MySQL Exercises » MySQL Select and Filter

MySQL Select and Filter

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:

Tutorials Class - Output Window
COLUMN_NAME
id
name
city
marks
course_id

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:

Tutorials Class - Output Window
name city
Robin Delhi
Maria Mumbai
John Pune
Asha Jaipur
Vikram Delhi
Neha Mumbai

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:

Tutorials Class - Output Window
name marks
Maria 92

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:

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

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:

Tutorials Class - Output Window
id name course_id
5 Vikram NULL

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:

Tutorials Class - Output Window
name_upper
ROBIN
MARIA
JOHN
ASHA
VIKRAM
NEHA

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

Tutorials Class - Output Window
name first_four
Robin Robi
Maria Mari
John John
Asha Asha
Vikram Vikr
Neha Neha

How it works

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

Read String Functions.

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:

Tutorials Class - Output Window
name
Asha

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:

Tutorials Class - Output Window
id name city marks course_id
2 Maria Mumbai 92 2
4 Asha Jaipur 88 3

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.