SQL Query Interview Questions and Answers
We have created three sample tables: Student Table, Program Table, and Scholarship Table. We will be using these tables to perform various query operations.
Student Table
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
201 |
Shivansh |
Mahajan |
8.79 |
2021-09-01 09:30:00 |
Computer Science |
202 |
Umesh |
Sharma |
8.44 |
2021-09-01 08:30:00 |
Mathematics |
203 |
Rakesh |
Kumar |
5.60 |
2021-09-01 10:00:00 |
Biology |
204 |
Radha |
Sharma |
9.20 |
2021-09-01 12:45:00 |
Chemistry |
205 |
Kush |
Kumar |
7.85 |
2021-09-01 08:30:00 |
Physics |
206 |
Prem |
Chopra |
9.56 |
2021-09-01 09:24:00 |
History |
207 |
Pankaj |
Vats |
9.78 |
2021-09-01 02:30:00 |
English |
208 |
Navleen |
Kaur |
7.00 |
2021-09-01 06:30:00 |
Mathematics |
Program Table
STUDENT_REF_ID |
PROGRAM_NAME |
PROGRAM_START_DATE |
---|---|---|
201 |
Computer Science |
2021-09-01 00:00:00 |
202 |
Mathematics |
2021-09-01 00:00:00 |
208 |
Mathematics |
2021-09-01 00:00:00 |
205 |
Physics |
2021-09-01 00:00:00 |
204 |
Chemistry |
2021-09-01 00:00:00 |
207 |
Psychology |
2021-09-01 00:00:00 |
206 |
History |
2021-09-01 00:00:00 |
203 |
Biology |
2021-09-01 00:00:00 |
Scholarship Table
STUDENT_REF_ID |
SCHOLARSHIP_AMOUNT |
SCHOLARSHIP_DATE |
---|---|---|
201 |
5000 |
2021-10-15 00:00:00 |
202 |
4500 |
2022-08-18 00:00:00 |
203 |
3000 |
2022-01-25 00:00:00 |
201 |
4000 |
2021-10-15 00:00:00 |
Let us start by taking a look at some of the most asked SQL Query interview questions:
1. Write a SQL query to fetch “FIRST_NAME” from the Student table in upper case and use ALIAS name as STUDENT_NAME.
SELECT upper(FIRST_NAME) as STUDENT_NAME from Student;
Output:
SHIVANSH
UMESH
RAKESH
RADHA
KUSH
PREM
PANKAJ
NAVLEEN
2. Write a SQL query to fetch unique values of MAJOR Subjects from Student table.
SELECT DISTINCT MAJOR from STUDENT;
or
SELECT MAJOR FROM STUDENT GROUP BY(MAJOR);
Output:
Computer Science
Mathematics
Biology
Chemistry
Physics
History
English
3. Write a SQL query to print the first 3 characters of FIRST_NAME from Student table.
SELECT SUBSTRING(FIRST_NAME, 1, 3) FROM Student;
Output:
Shi
Ume
Rak
Rad
Kus
Pre
Pan
Nav
4. Write a SQL query to find the position of alphabet (‘a’) int the first name column ‘Shivansh’ from Student table.
SELECT INSTR(FIRST_NAME, 'a') FROM Student WHERE FIRST_NAME = 'Shivansh';
Output:
5
5. Write a SQL query that fetches the unique values of MAJOR Subjects from Student table and print its length.
SELECT MAJOR,LENGTH(MAJOR) FROM Student GROUP BY(MAJOR);
or
SELECT DISTINCT MAJOR, LENGTH(MAJOR) FROM Student;
Output:
MAJOR |
LENGTH(MAJOR) |
---|---|
Computer Science |
16 |
Mathematics |
11 |
Biology |
7 |
Chemistry |
9 |
Physics |
7 |
History |
7 |
English |
7 |
6. Write a SQL query to print FIRST_NAME from the Student table after replacing ‘a’ with ‘A’.
SELECT REPLACE(FIRST_NAME, 'a', 'A') FROM Student;
Output:
ShivAnsh
Umesh
RAkesh
RAdhA
Kush
Prem
PAnkAj
NAvleen
7. Write a SQL query to print the FIRST_NAME and LAST_NAME from Student table into single column COMPLETE_NAME.
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS COMPLETE_NAME FROM Student;
Output:
Shivansh Mahajan
Umesh Sharma
Rakesh Kumar
Radha Sharma
Kush Kumar
Prem Chopra
Pankaj Vats
Navleen Kaur
8. Write a SQL query to print all Student details from Student table order by FIRST_NAME Ascending and MAJOR Subject descending .
SELECT * FROM Student ORDER BY FIRST_NAME , MAJOR DESC;
Output:
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
205 |
Kush |
Kumar |
7.85 |
2021-09-01 08:30:00 |
Physics |
208 |
Navleen |
Kaur |
7 |
2021-09-01 06:30:00 |
Mathematics |
207 |
Pankaj |
Vats |
9.78 |
2021-09-01 02:30:00 |
English |
206 |
Prem |
Chopra |
9.56 |
2021-09-01 09:24:00 |
History |
204 |
Radha |
Sharma |
9.2 |
2021-09-01 12:45:00 |
Chemistry |
203 |
Rakesh |
Kumar |
5.6 |
2021-09-01 10:00:00 |
Biology |
201 |
Shivansh |
Mahajan |
8.79 |
2021-09-01 09:30:00 |
Computer Science |
202 |
Umesh |
Sharma |
8.44 |
2021-09-01 08:30:00 |
Mathematics |
9. Write a SQL query to print details of the Students with the FIRST_NAME as ‘Prem’ and ‘Shivansh’ from Student table.
SELECT * from Student WHERE FIRST_NAME IN ('Prem' , 'Shivansh');
Output:
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
201 |
Shivansh |
Mahajan |
8.79 |
2021-09-01 09:30:00 |
Computer Science |
206 |
Prem |
Chopra |
9.56 |
2021-09-01 09:24:00 |
History |
10. Write a SQL query to print details of the Students excluding FIRST_NAME as ‘Prem’ and ‘Shivansh’ from Student table.
SELECT * from Student WHERE FIRST_NAME NOT IN ('Prem', 'Shivansh');
Output:
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
202 |
Umesh |
Sharma |
8.44 |
2021-09-01 08:30:00 |
Mathematics |
203 |
Rakesh |
Kumar |
5.6 |
2021-09-01 10:00:00 |
Biology |
204 |
Radha |
Sharma |
9.2 |
2021-09-01 12:45:00 |
Chemistry |
205 |
Kush |
Kumar |
7.85 |
2021-09-01 08:30:00 |
Physics |
207 |
Pankaj |
Vats |
9.78 |
2021-09-01 02:30:00 |
English |
208 |
Navleen |
Kaur |
7 |
2021-09-01 06:30:00 |
Mathematics |
11. Write a SQL query to print details of the Students whose FIRST_NAME ends with ‘a’.
SELECT * FROM Student WHERE FIRST_NAME LIKE '%a';
Output:
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
204 |
Radha |
Sharma |
9.2 |
2021-09-01 12:45:00 |
Chemistry |
12. Write an SQL query to print details of the Students whose FIRST_NAME ends with ‘a’ and contains six alphabets.
SELECT * FROM Student WHERE FIRST_NAME LIKE '_____a';
13. Write an SQL query to print details of the Students whose GPA lies between 9.00 and 9.99.
SELECT * FROM Student WHERE GPA BETWEEN 9.00 AND 9.99;
Output:
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
204 |
Radha |
Sharma |
9.2 |
2021-09-01 12:45:00 |
Chemistry |
206 |
Prem |
Chopra |
9.56 |
2021-09-01 09:24:00 |
History |
207 |
Pankaj |
Vats |
9.78 |
2021-09-01 02:30:00 |
English |
14. Write an SQL query to fetch the count of Students having Major Subject ‘Computer Science’.
SELECT Major, COUNT(*) as TOTAL_COUNT FROM Student WHERE MAJOR = 'Computer Science';
Output:
MAJOR |
TOTAL_COUNT |
---|---|
Computer Science |
1 |
15. Write an SQL query to fetch Students full names with GPA >= 8.5 and <= 9.5.
SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS FULL_NAME FROM Student WHERE GPA BETWEEN 8.5 and 9.5;
Output:
Shivansh Mahajan
Radha Sharma
16. Write an SQL query to fetch the no. of Students for each MAJOR subject in the descending order.
SELECT MAJOR, COUNT(MAJOR) from Student group by MAJOR order by COUNT(MAJOR);
Output:
MAJOR |
COUNT(MAJOR) |
---|---|
Biology |
1 |
Chemistry |
1 |
Computer Science |
1 |
English |
1 |
History |
1 |
Physics |
1 |
Mathematics |
2 |
17. Display the details of students who have received scholarships, including their names, scholarship amounts, and scholarship dates.
SELECT
Student.FIRST_NAME,
Student.LAST_NAME,
Scholarship.SCHOLARSHIP_AMOUNT,
Scholarship.SCHOLARSHIP_DATE
FROM
Student
INNER JOIN
Scholarship ON Student.STUDENT_ID = Scholarship.STUDENT_REF_ID;
Output:
FIRST_NAME | LAST_NAME | SCHOLARSHIP_AMOUNT | SCHOLARSHIP_DATE |
---|---|---|---|
Shivansh | Mahajan | 5000 | 2021-10-15 00:00:00 |
Umesh | Sharma | 4500 | 2022-08-18 00:00:00 |
Rakesh | Kumar | 3000 | 2022-01-25 00:00:00 |
Shivansh | Mahajan | 4000 | 2021-10-15 00:00:00 |
18. Write an SQL query to show only odd rows from Student table.
SELECT * FROM Student WHERE student_id % 2 != 0;
Output:
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
201 | Shivansh | Mahajan | 8.79 | 2021-09-01 09:30:00 | Computer Science |
203 | Rakesh | Kumar | 5.6 | 2021-09-01 10:00:00 | Biology |
205 | Kush | Kumar | 7.85 | 2021-09-01 08:30:00 | Physics |
207 | Pankaj | Vats | 9.78 | 2021-09-01 02:30:00 | English |
19. Write an SQL query to show only even rows from Student table.
SELECT * FROM Student WHERE student_id % 2 = 0;
Output:
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
202 | Umesh | Sharma | 8.44 | 2021-09-01 08:30:00 | Mathematics |
204 | Radha | Sharma | 9.2 | 2021-09-01 12:45:00 | Chemistry |
206 | Prem | Chopra | 9.56 | 2021-09-01 09:24:00 | History |
208 | Navleen | Kaur | 7 | 2021-09-01 06:30:00 | Mathematics |
20. List all students and their scholarship amounts if they have received any. If a student has not received a scholarship, display NULL for the scholarship details.
SELECT
Student.FIRST_NAME,
Student.LAST_NAME,
COALESCE(Scholarship.SCHOLARSHIP_AMOUNT, NULL) AS SCHOLARSHIP_AMOUNT,
COALESCE(Scholarship.SCHOLARSHIP_DATE, NULL) AS SCHOLARSHIP_DATE
FROM
Student
LEFT JOIN
Scholarship ON Student.STUDENT_ID = Scholarship.STUDENT_REF_ID;
21. Write an SQL query to show the top n (say 5) records of Student table order by descending GPA.
SELECT * from Student ORDER BY GPA DESC LIMIT 5;
Output:
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
207 | Pankaj | Vats | 9.78 | 2021-09-01 02:30:00 | English |
206 | Prem | Chopra | 9.56 | 2021-09-01 09:24:00 | History |
204 | Radha | Sharma | 9.2 | 2021-09-01 12:45:00 | Chemistry |
201 | Shivansh | Mahajan | 8.79 | 2021-09-01 09:30:00 | Computer Science |
202 | Umesh | Sharma | 8.44 | 2021-09-01 08:30:00 | Mathematics |
22. Write an SQL query to determine the nth (say n=5) highest GPA from a table.
SELECT * FROM Student ORDER BY GPA DESC LIMIT 5, 1;
Output:
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
205 | Kush | Kumar | 7.85 | 2021-09-01 08:30:00 | Physics |
23. Write an SQL query to determine the 5th highest GPA without using LIMIT keyword.
SELECT * FROM Student s1
WHERE 4 = (
SELECT COUNT(DISTINCT (s2.GPA))
FROM Student s2
WHERE s2.GPA >= s1.GPA
);
Output:
STUDENT_ID |
FIRST_NAME |
LAST_NAME |
GPA |
ENROLLMENT_DATE |
MAJOR |
---|---|---|---|---|---|
201 | Shivansh | Mahajan | 8.79 | 2021-09-01 09:30:00 | Computer Science |
24. Write an SQL query to fetch the list of Students with the same GPA.
SELECT s1.* FROM Student s1, Student s2 WHERE s1.GPA = s2.GPA AND s1.Student_id != s2.Student_id;
25. Write an SQL query to show the second highest GPA from a Student table using sub-query.
SELECT MAX(GPA) FROM Student
WHERE GPA NOT IN(SELECT MAX(GPA) FROM Student);
Output:
9.56
26. Write an SQL query to show one row twice in results from a table.
SELECT * FROM Student
UNION ALL
SELECT * FROM Student ORDER BY STUDENT_ID;
27. Write an SQL query to list STUDENT_ID who does not get Scholarship.
SELECT STUDENT_ID FROM Student
WHERE STUDENT_ID NOT IN (SELECT STUDENT_REF_ID FROM Scholarship);
Output:
204
205
206
207
208
28. Write an SQL query to fetch the first 50% records from a table.
SELECT * FROM Student WHERE STUDENT_ID <= (SELECT COUNT(STUDENT_ID)/2 FROM Student);
29. Write an SQL query to fetch the MAJOR subject that have less than 4 people in it.
SELECT MAJOR, COUNT(MAJOR) AS MAJOR_COUNT FROM Student GROUP BY MAJOR HAVING COUNT(MAJOR) < 4;
Output:
MAJOR | MAJOR_COUNT |
---|---|
Biology | 1 |
Chemistry | 1 |
Computer Science | 1 |
English | 1 |
History | 1 |
Mathematics | 2 |
Physics | 1 |
30. Write an SQL query to show all MAJOR subject along with the number of people in there.
SELECT MAJOR, COUNT(MAJOR) AS ALL_MAJOR FROM Student GROUP BY MAJOR;
Output:
MAJOR | ALL_MAJOR |
---|---|
Biology | 1 |
Chemistry | 1 |
Computer Science | 1 |
English | 1 |
History | 1 |
Mathematics | 2 |
Physics | 1 |
31. Write an SQL query to show the last record from a table.
SELECT * FROM Student WHERE STUDENT_ID = (SELECT MAX(STUDENT_ID) FROM STUDENT);
Output:
STUDENT_ID | FIRST_NAME | LAST_NAME | GPA | ENROLLMENT_DATE | MAJOR |
---|---|---|---|---|---|
208 | Navleen | Kaur | 7 | 2021-09-01 06:30:00 | Mathematics |
32. Write an SQL query to fetch the first row of a table.
SELECT * FROM Student WHERE STUDENT_ID = (SELECT MIN(STUDENT_ID) FROM Student);
Output:
STUDENT_ID | FIRST_NAME | LAST_NAME | GPA | ENROLLMENT_DATE | MAJOR |
---|---|---|---|---|---|
201 | Shivansh | Mahajan | 8.79 | 2021-09-01 09:30:00 | Computer Science |
33. Write an SQL query to fetch the last five records from a table.
SELECT *
FROM (
SELECT *
FROM Student
ORDER BY STUDENT_ID DESC
LIMIT 5
) AS subquery
ORDER BY STUDENT_ID;
Output:
STUDENT_ID | FIRST_NAME | LAST_NAME | GPA | ENROLLMENT_DATE | MAJOR |
---|---|---|---|---|---|
204 | Radha | Sharma | 9.2 | 2021-09-01 12:45:00 | Chemistry |
205 | Kush | Kumar | 7.85 | 2021-09-01 08:30:00 | Physics |
206 | Prem | Chopra | 9.56 | 2021-09-01 09:24:00 | History |
207 | Pankaj | Vats | 9.78 | 2021-09-01 02:30:00 | English |
208 | Navleen | Kaur | 7 | 2021-09-01 06:30:00 | Mathematics |
34. Write an SQL query to fetch three max GPA from a table using co-related subquery.
SELECT DISTINCT GPA FROM Student S1
WHERE 3 >= (SELECT COUNT(DISTINCT GPA) FROM Student S2 WHERE S1.GPA <= S2.GPA) ORDER BY S1.GPA DESC;
Output:
9.78
9.56
9.2
35. Write an SQL query to fetch three min GPA from a table using co-related subquery.
SELECT DISTINCT GPA FROM Student S1
WHERE 3 >= (SELECT COUNT(DISTINCT GPA) FROM Student S2 WHERE S1.GPA >= S2.GPA) ORDER BY S1.GPA;
Output:
5.6
7
7.85
36. Write an SQL query to fetch nth max GPA from a table.
SELECT DISTINCT GPA FROM Student S1
WHERE n >= (SELECT COUNT(DISTINCT GPA) FROM Student S2 WHERE S1.GPA <= S2.GPA) ORDER BY S1.GPA DESC;
37. Write an SQL query to fetch MAJOR subjects along with the max GPA in each of these MAJOR subjects.
SELECT MAJOR, MAX(GPA) as MAXGPA FROM Student GROUP BY MAJOR;
Output:
MAJOR | MAXGPA |
---|---|
Biology | 5.6 |
Chemistry | 9.2 |
Computer Science | 8.79 |
English | 9.78 |
History | 9.56 |
Mathematics | 8.44 |
Physics | 7.85 |
38. Write an SQL query to fetch the names of Students who has highest GPA.
SELECT FIRST_NAME, GPA FROM Student WHERE GPA = (SELECT MAX(GPA) FROM Student);
Output:
FIRST_NAME | GPA |
---|---|
Pankaj | 9.78 |
39. Write an SQL query to show the current date and time.
Query to get current date :
SELECT CURDATE();
Query to get current date and time :
SELECT NOW();
40. Write a query to create a new table which consists of data and structure copied from the other table (say Student) or clone the table named Student.
CREATE TABLE CloneTable AS SELECT * FROM Student;
41. Write an SQL query to update the GPA of all the students in ‘Computer Science’ MAJOR subject to 7.5.
UPDATE Student SET GPA = 4.0 WHERE MAJOR = 'Computer Science';
42. Write an SQL query to find the average GPA for each major.
SELECT MAJOR, AVG(GPA) AS AVERAGE_GPA FROM Student GROUP BY MAJOR;
Output:
MAJOR | AVERAGE_GPA |
---|---|
Biology | 5.6 |
Chemistry | 9.2 |
Computer Science | 4 |
English | 9.78 |
History | 9.56 |
Mathematics | 7.72 |
Physics | 7.85 |
43. Write an SQL query to show the top 3 students with the highest GPA.
SELECT * FROM Student ORDER BY GPA DESC LIMIT 3;
Output:
STUDENT_ID | FIRST_NAME | LAST_NAME | GPA | ENROLLMENT_DATE | MAJOR |
---|---|---|---|---|---|
207 | Pankaj | Vats | 9.78 | 2021-09-01 02:30:00 | English |
206 | Prem | Chopra | 9.56 | 2021-09-01 09:24:00 | History |
204 | Radha | Sharma | 9.2 | 2021-09-01 12:45:00 | Chemistry |
44. Write an SQL query to find the number of students in each major who have a GPA greater than 7.5.
SELECT MAJOR, COUNT(STUDENT_ID) AS HIGH_GPA_COUNT FROM Student WHERE GPA > 3.5 GROUP BY MAJOR;
Output:
MAJOR | HIGH_GPA_COUNT |
---|---|
Biology | 1 |
Chemistry | 1 |
Computer Science | 1 |
English | 1 |
History | 1 |
Mathematics | 2 |
Physics | 1 |
45. Write an SQL query to find the students who have the same GPA as ‘Shivansh Mahajan’.
SELECT * FROM Student WHERE GPA = (SELECT GPA FROM Student WHERE FIRST_NAME = 'Shivansh'
AND LAST_NAME = 'Mahajan');
Output:
STUDENT_ID | FIRST_NAME | LAST_NAME | GPA | ENROLLMENT_DATE | MAJOR |
---|---|---|---|---|---|
201 | Shivansh | Mahajan | 4 | 2021-09-01 09:30:00 | Computer Science |
SQL Query Interview Questions
SQL or Structured Query Language is a standard language for relational databases. SQL queries are powerful tools used to, manipulate, and manage data stored in these databases like MySQL, Oracle, PostgreSQL, etc. Whether you’re fetching specific data points, performing complex analyses, or modifying database structures, SQL queries provide a standardized language for executing these tasks efficiently.
Here, we will cover 45+ MySQL interview questions with answers that are commonly asked during interviews for Data Analyst and Data Engineer positions at MAANG and other high-paying companies. Whether you are a fresher or an experienced professional with 5, 8, or 10 years of experience, this article gives you all the confidence you need to ace your next interview.