SQL Query Interview Questions

SQL, or Structured Query Language, is the standard language for managing and manipulating relational databases such as MySQL, Oracle, and PostgreSQL. It serves as a powerful tool for efficiently handling data whether retrieving specific data points, performing complex analysis, or modifying database structures.

In this article, we cover 45+ essentialMySQL interview questions and answers frequently asked during interviews for Data Analyst and Data Engineer roles at leading companies, including MAANG and other high-paying organizations. Designed for both freshers and experienced professionals (with 5, 8, or 10 years of experience), this comprehensive resource will equip us with the knowledge and confidence to excel in our next interview.

SQL Query Interview Questions and Answers

To help us understand and practice SQL queries, we have created three sample tables: Student Table, Program Table, and Scholarship Table. These tables serve as the basis for various query operations and scenarios commonly encountered in interviews. Below is a brief overview of these tables:

Student Table

Program Table

Scholarship Table

Let us start by exploring some of the most commonly asked SQL query interview questions. These questions are designed to test our understanding of SQL concepts and our ability to manipulate and analyze data effectively. Here’s a comprehensive list of questions and explanations

1. Write a SQL query to fetch "FIRST_NAME" from the Student table in upper case and use ALIAS name as STUDENT_NAME.

Query:

SELECT upper(FIRST_NAME) as STUDENT_NAME from Student;

Output:

Explanation:

This query uses the UPPER() function to convert the FIRST_NAME values to uppercase. The alias STUDENT_NAME is applied to the output column for better readability and relevance.

2. Write a SQL query to fetch unique values of MAJOR Subjects from Student table.

Query:

SELECT DISTINCT MAJOR from STUDENT; 
or
SELECT MAJOR FROM STUDENT GROUP BY(MAJOR);

Output:

Computer Science
Mathematics
Biology
Chemistry
Physics
History
English

Explanation:

The DISTINCTkeyword ensures that only unique values from the MAJOR column are retrieved, removing duplicates and providing a clean list of all subjects.

3. Write a SQL query to print the first 3 characters of FIRST_NAME from Student table.

Query:

SELECT SUBSTRING(FIRST_NAME, 1, 3)  FROM Student;

Output:

Explanation:

The SUBSTRING() function extracts a portion of the string. Here, the function starts at position 1 and retrieves the next 3 characters from FIRST_NAME.

4. Write a SQL query to find the position of alphabet ('a') int the first name column 'Shivansh' from Student table.

Query:

SELECT INSTR(FIRST_NAME, 'a') FROM Student WHERE FIRST_NAME = 'Shivansh'; 

Output:

Explanation:

The INSTR()function finds the position of the first occurrence of the specified character ('a') in the string. For the name 'Shivansh', 'a' is at the 5th position.

5. Write a SQL query that fetches the unique values of MAJOR Subjects from Student table and print its length.

Query:

SELECT MAJOR,LENGTH(MAJOR) FROM Student GROUP BY(MAJOR);                                                         
or                                                                                                                                                                                                                 
SELECT DISTINCT MAJOR, LENGTH(MAJOR) FROM Student;    

Output:

Explanation:

The query combines the GROUP BY clause with the LENGTH() function to calculate the length of each unique value in the MAJOR column. The results show both the subject name and its character count.

6. Write a SQL query to print FIRST_NAME from the Student table after replacing 'a' with 'A'.

Query:

SELECT REPLACE(FIRST_NAME, 'a', 'A') FROM Student;

Output:

Explanation:

The REPLACE() function substitutes every occurrence of the letter 'a' in FIRST_NAME with 'A'. Only names containing 'a' show a difference in output.

7. Write a SQL query to print the FIRST_NAME and LAST_NAME from Student table into single column COMPLETE_NAME.

Query:

SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS COMPLETE_NAME FROM Student;

Output:

Explanation:

The CONCAT() function combines FIRST_NAME and LAST_NAME, separated by a space, into a single column named COMPLETE_NAME.

8. Write a SQL query to print all Student details from Student table order by FIRST_NAME Ascending and MAJOR Subject descending .

Query:

SELECT * FROM Student ORDER BY FIRST_NAME , MAJOR DESC;

Output:

Explanation:

The ORDER BYclause sorts records first by FIRST_NAME in ascending order, then by MAJOR in descending order.

9. Write a SQL query to print details of the Students with the FIRST_NAME as 'Prem' and 'Shivansh' from Student table.

Query:

SELECT * from Student WHERE FIRST_NAME IN ('Prem' , 'Shivansh');

Output:

Explanation:

The IN operator checks if FIRST_NAME matches 'Prem' or 'Shivansh' and retrieves only the matching rows.

10. Write a SQL query to print details of the Students excluding FIRST_NAME as 'Prem' and 'Shivansh' from Student table.

Query:

SELECT * from Student WHERE FIRST_NAME NOT IN ('Prem', 'Shivansh');

Output:

Explanation:

The NOT IN operator filters out rows where FIRST_NAME is 'Prem' or 'Shivansh'.

11. Write a SQL query to print details of the Students whose FIRST_NAME ends with 'a'.

Query:

SELECT * FROM Student WHERE FIRST_NAME LIKE '%a';

Output:

Explanation:

The LIKEoperator with %a matches any FIRST_NAME ending with the letter 'a'. % is a wildcard representing any number of characters preceding 'a'. This query retrieves only the row for Radha, as her FIRST_NAME satisfies the condition.

12. Write an SQL query to print details of the Students whose FIRST_NAME ends with ‘a’ and contains five alphabets.

Query:

SELECT * FROM Student WHERE FIRST_NAME LIKE '_____a';

Output:

Explanation:

The LIKE operator with _____a ensures that the FIRST_NAME is exactly five characters long (denoted by four underscores) and ends with 'a'. Only Radha meets this criterion.

13. Write an SQL query to print details of the Students whose GPA lies between 9.00 and 9.99.

Query:

SELECT * FROM Student WHERE GPA BETWEEN 9.00 AND 9.99;

Output:

Explanation:

The BETWEEN operator retrieves rows where the GPA falls within the inclusive range of 9.00 to 9.99. This query outputs the students who scored high GPAs in the given range, showcasing the top performers.

14. Write an SQL query to fetch the count of Students having Major Subject ‘Computer Science’.

Query:

SELECT Major, COUNT(*) as TOTAL_COUNT FROM Student WHERE MAJOR = 'Computer Science';

Output:

Explanation:

The COUNT(*) function calculates the total number of rows where the MAJOR is 'Computer Science'. The result 1 indicates there is only one student enrolled in this subject.

15. Write an SQL query to fetch Students full names with GPA >= 8.5 and <= 9.5.

Query:

SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) AS FULL_NAME FROM Student WHERE GPA BETWEEN 8.5 and 9.5;

Output:

Explanation:

This query uses CONCAT() to merge FIRST_NAME and LAST_NAME into a full name. It filters students whose GPA falls between 8.5 and 9.5, showcasing high-achieving students in this range.

16. Write an SQL query to fetch the no. of Students for each MAJOR subject in the descending order.

Query:

SELECT MAJOR, COUNT(MAJOR) from Student group by MAJOR order by COUNT(MAJOR) DESC;

Output:

Explanation:

The GROUP BY groups rows by MAJOR, and COUNT(*) computes the number of students in each group. The ORDER BY ensures the result is sorted in descending order of student count.

17. Display the details of students who have received scholarships, including their names, scholarship amounts, and scholarship dates.

Query:

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:

Explanation:

The INNER JOINcombines data from Student and Scholarship tables where STUDENT_ID matches STUDENT_REF_ID. This retrieves students who received scholarships, along with their scholarship details.

18. Write an SQL query to show only odd rows from Student table.

Query:

SELECT * FROM Student WHERE student_id % 2 != 0;

Output:

Explanation:

The condition STUDENT_ID % 2 != 0 filters out rows where STUDENT_ID is odd. This is useful for segmenting or analyzing data based on ID parity.

19. Write an SQL query to show only even rows from Student table.

Query:

SELECT * FROM Student WHERE student_id % 2 = 0;

Output:

Explanation:

The condition STUDENT_ID % 2 = 0 retrieves rows where STUDENT_ID is even. This complements the odd-row query, enabling complete segmentation by ID parity.

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.

Query:

SELECT 
    Student.FIRST_NAME,
    Student.LAST_NAME,
    Scholarship.SCHOLARSHIP_AMOUNT,
    Scholarship.SCHOLARSHIP_DATE
FROM 
    Student
LEFT JOIN 
    Scholarship ON Student.STUDENT_ID = Scholarship.STUDENT_REF_ID;

Output:

Explanation:

A LEFT JOIN retrieves all students, including those without scholarships. For students with no matching record in the Scholarship table, SCHOLARSHIP_AMOUNT and SCHOLARSHIP_DATE are NULL.

21. Write an SQL query to show the top n (say 5) records of Student table order by descending GPA.

Query:

SELECT * from Student ORDER BY GPA DESC LIMIT 5;

Output:

Explanation:

This query sorts the students by GPA in descending order and limits the result to the top 5 records. It is commonly used to display the highest-performing students.

22. Write an SQL query to determine the nth (say n=5) highest GPA from a table.

Query:

SELECT * FROM Student ORDER BY GPA DESC LIMIT 4, 1;

Output:

Explanation:

This query skips the top 4 records (LIMIT 5) and fetches the 5th record (LIMIT 4, 1). It can be generalized to retrieve any nth record based on specific sorting criteria.

23. Write an SQL query to determine the 5th highest GPA without using LIMIT keyword.

Query:

SELECT * FROM Student s1 
WHERE 4 = (
    SELECT COUNT(DISTINCT (s2.GPA)) 
    FROM Student s2
    WHERE s2.GPA >= s1.GPA
);

Output:

Explanation:

This query uses a correlated subquery to count how many unique GPAs are greater than or equal to the current student's GPA. When this count equals 4, it retrieves the 5th highest GPA.

24. Write an SQL query to fetch the list of Students with the same GPA.

Query:

SELECT s1.* FROM Student s1, Student s2 WHERE s1.GPA = s2.GPA AND s1.Student_id != s2.Student_id;

Output:

SQL query successfully executed. However, the result set is empty.

Explanation:

This query identifies students who share the same GPA by performing a self-join on the Student table. The condition ensures that the matched records are not the same student.

25. Write an SQL query to show the second highest GPA from a Student table using sub-query.

Query:

SELECT MAX(GPA) FROM Student
WHERE GPA NOT IN(SELECT MAX(GPA) FROM Student);

Output:

Explanation:

The subquery fetches the highest GPA, and the main query excludes this value to find the second highest GPA. This approach can be generalized to fetch other rankings by nesting more subqueries.

26. Write an SQL query to show one row twice in results from a table.

Query:

SELECT * FROM Student 
UNION ALL
SELECT * FROM Student ORDER BY STUDENT_ID;

Output:

Explanation:

This query uses UNION ALL to combine the Student table with itself, resulting in duplicate rows being included in the output. Unlike UNION, which removes duplicates, UNION ALL retains all rows from both queries. The ORDER BY ensures the results are sorted by STUDENT_ID. This is useful for scenarios where duplicates are intentionally required.

27. Write an SQL query to list STUDENT_ID who does not get Scholarship.

Query:

SELECT STUDENT_ID FROM Student 
WHERE STUDENT_ID NOT IN (SELECT STUDENT_REF_ID FROM Scholarship);

Output:

Explanation:

This query uses the NOT IN operator to identify students whose IDs are not listed in the Scholarship table. It is helpful to determine the unprivileged group.

28. Write an SQL query to fetch the first 50% records from a table.

SELECT * 
FROM Student 
LIMIT (SELECT COUNT(*) FROM Student) / 2;

29. Write an SQL query to fetch the MAJOR subject that have less than 4 people in it.

Query:

SELECT MAJOR, COUNT(MAJOR) AS MAJOR_COUNT FROM Student GROUP BY MAJOR HAVING COUNT(MAJOR) < 4;

Output:

Explanation:

This query groups students by their MAJOR subject that have less than 4 people. It provides a breakdown of how many major subjects have less than 4 people in it.

30. Write an SQL query to show all MAJOR subject along with the number of people in there.

Query:

SELECT MAJOR, COUNT(MAJOR) AS ALL_MAJOR FROM Student GROUP BY MAJOR;

Output:

Explanation:

This query groups students by their MAJOR and counts the number of students in each group. It provides a breakdown of how many students are enrolled in each major.

31. Write an SQL query to show the last record from a table.

Query:

SELECT * FROM Student WHERE STUDENT_ID = (SELECT MAX(STUDENT_ID) FROM STUDENT);

Output:

Explanation:

This query identifies the last record in the Student table by selecting the record where the STUDENT_ID is equal to the maximum value of STUDENT_ID in the table. This is useful in scenarios where STUDENT_ID is a primary key and follows an incremental order

32. Write an SQL query to fetch the first row of a table.

Query:

SELECT * FROM Student WHERE STUDENT_ID = (SELECT MIN(STUDENT_ID) FROM Student);

Output:

Explanation:

This query retrieves the first row of the Student table by identifying the record with the smallest STUDENT_ID. This approach assumes that STUDENT_ID is a unique identifier (such as a primary key) and that it increases incrementally, ensuring that the row with the smallest ID represents the first entry in the table.

33. Write an SQL query to fetch the last five records from a table.

Query:

SELECT * 
FROM (
    SELECT * 
    FROM Student 
    ORDER BY STUDENT_ID DESC 
    LIMIT 5
) AS subquery
ORDER BY STUDENT_ID;

Output:

Explanation:

This query first retrieves the last five records based on descending STUDENT_ID using a subquery. The outer query reorders these records in ascending order of STUDENT_ID to maintain the original sequence.

34. Write an SQL query to fetch three max GPA from a table using co-related subquery.

Query:

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:

Explanation:

The subquery counts how many unique GPAs are greater than or equal to the current GPA (S1.GPA). If the count is 3 or less, it means the GPA is among the top 3 highest values. The DISTINCT keyword ensures only unique GPAs are considered.

35. Write an SQL query to fetch three min GPA from a table using Correlated subquery.

Query:

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:

Explanation:

This query works similarly to the previous one but retrieves the bottom 3 smallest GPAs by reversing the comparison in the subquery (S1.GPA >= S2.GPA) and ordering the results in ascending order.

36. Write an SQL query to fetch nth max GPA from a table.

Query:

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;

Explanation:

This query dynamically fetches the GPA ranked nth in descending order. The value of n can be replaced with the desired rank (e.g., 2 for second max). It uses a correlated subquery to count how many GPAs are greater than or equal to the current GPA.

37. Write an SQL query to fetch MAJOR subjects along with the max GPA in each of these MAJOR subjects.

Query:

SELECT MAJOR, MAX(GPA) as MAXGPA FROM Student GROUP BY MAJOR;

Output:

Explanation:

The query groups the students by MAJOR and calculates the maximum GPA for each group using the MAX function. It is commonly used to analyze the best performance across different categories.

38. Write an SQL query to fetch the names of Students who has highest GPA.

Query:

SELECT FIRST_NAME, GPA FROM Student WHERE GPA = (SELECT MAX(GPA) FROM Student);

Output:

Explanation:

This query identifies the student(s) with the highest GPA by comparing their GPA with the maximum GPA in the table, which is fetched using a subquery.

39. Write an SQL query to show the current date and time.

Query:

SELECT CURDATE(); -- To get the current date
SELECT NOW(); -- To get the current date and time

Output:

Explanation:

CURDATE() returns the current date, while NOW() provides both the current date and time. These functions are useful for logging or timestamping data.

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.

Query:

CREATE TABLE CloneTable AS SELECT * FROM Student;

Explanation:

This query creates a new table CloneTable that contains the same structure and data as the Student table. It is a simple way to back up or duplicate a table for testing or other purposes.

41. Write an SQL query to update the GPA of all the students in 'Computer Science' MAJOR subject to 7.5.

Query:

UPDATE Student SET GPA = 7.5 WHERE MAJOR = 'Computer Science';

Output:

Explanation:

This query updates the GPA of all students whose MAJOR is "Computer Science" to 7.5. It demonstrates the use of the UPDATE statement with a WHERE clause for conditional updates.

42. Write an SQL query to find the average GPA for each major.

Query:

SELECT MAJOR, AVG(GPA) AS AVERAGE_GPA FROM Student GROUP BY MAJOR;

Output:

Explanation:

This query calculates the average GPA for students in each major using the AVG function. It groups the results by MAJOR to show separate averages for each category.

43. Write an SQL query to show the top 3 students with the highest GPA.

Query:

SELECT * FROM Student ORDER BY GPA DESC LIMIT 3;

Output:

Explanation:

This query sorts the Student table by GPA in descending order and limits the results to the top 3 records using the LIMIT clause. This is ideal for identifying the top performers in the dataset.

44. Write an SQL query to find the number of students in each major who have a GPA greater than 7.5.

Query:

SELECT MAJOR, COUNT(STUDENT_ID) AS HIGH_GPA_COUNT FROM Student WHERE GPA > 3.5 GROUP BY MAJOR;

Output:

Explanation:

This query filters students with a GPA greater than 7.5 using the WHERE clause and groups them by MAJOR. The COUNT(STUDENT_ID) function calculates the number of students in each group. The result highlights the number of high-performing students in each field of study.

45. Write an SQL query to find the students who have the same GPA as 'Shivansh Mahajan'.

Query:

SELECT * FROM Student WHERE GPA = (SELECT GPA FROM Student WHERE FIRST_NAME = 'Shivansh' 
AND LAST_NAME = 'Mahajan');

Output:

Explanation:

This query uses a subquery to fetch the GPA of "Shivansh Mahajan" and compares it with the GPA of other students in the main query. It identifies all students who share the same GPA as "Shivansh Mahajan." If no other student shares the same GPA, the output will only include "Shivansh Mahajan."

Conclusion

Mastering SQL queries is important for excelling in roles like data analysts, data engineers, and business analysts. This guide has covered a comprehensive range of SQL interview questions and answers, equipping us with the tools to tackle real-world scenarios confidently. By practicing these queries, we not only enhance our SQL proficiency but also build a strong foundation for data manipulation and analysis key skills demanded in today's tech-driven industries.

S

shivanshmahajan876

Follow

Improve

Article Tags :

• DBMS
• SQL
• SQL Quiz
• Databases
• DBMS-SQL
• placement
• Interview-Questions

+3 More