SQL Query Interview Questions

SQL queries are widely used to retrieve, analyze and manage data stored in relational databases. The following examples use three sample tables to demonstrate commonly used SQL concepts and query techniques.

• Data Retrieval and Filtering
• JOIN Operations
• Aggregate Functions
• GROUP BY and HAVING Clauses

The following tables will be used to demonstrate the SQL queries.

Student Table

Program Table

Scholarship Table

The following examples demonstrate commonly used SQL queries and concepts.

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:

• Converts first names to uppercase using UPPER().
• Displays the result as STUDENT_NAME.

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:

• Retrieves unique values from the MAJOR column.
• Removes duplicate majors using DISTINCT.

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:

• Extracts the first 3 characters from FIRST_NAME.
• Uses the SUBSTRING() function.

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

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

Output:

• Finds the position of the first occurrence of 'a'.
• For Grace, 'a' is at position 3.

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

SELECT DISTINCT MAJOR, LENGTH(MAJOR) FROM Student;    

Output:

• Groups records by MAJOR.
• Calculates the length of each major using LENGTH().

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:

• Replaces 'a' with 'A' in FIRST_NAME.
• Affects only names containing 'a'.

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:

• Combines FIRST_NAME and LAST_NAME into a single column.
• Displays the result as COMPLETE_NAME.

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:

• Sorts records by FIRST_NAME in ascending order.
• Then sorts by MAJOR in descending order.

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

SELECT * from Student WHERE FIRST_NAME IN ('Noah' , 'Ava');

Output:

• Retrieves rows where FIRST_NAME is Noah or Avah.
• Uses the IN operator to match multiple values.

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

SELECT * from Student WHERE FIRST_NAME NOT IN ('Jack', 'Liam');

Output:

• Excludes rows where FIRST_NAME is Jack or Liam.
• Uses the NOT IN operator to filter records.

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:

• Retrieves names ending with 'a'.
• Uses %a with the LIKE operator to match the pattern.

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

SELECT * 
FROM Student 
WHERE FIRST_NAME LIKE '_____a';

Output:

• Searches for names with exactly 5 characters ending in 'a'.
• No records match this condition, so the result is empty.

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:

• Retrieves students with GPA between 9.00 and 9.99.
• Uses the BETWEEN operator to filter records.

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:

• Counts the number of students in Computer Science.
• Returns the total using COUNT(*).

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:

• Combines FIRST_NAME and LAST_NAME into a full name.
• Displays students with GPA between 8.5 and 9.5.

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) DESC;

Output:

• Groups students by MAJOR.
• Counts students in each major using COUNT(*).
• Sorts the result by student count in descending order.

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:

• Joins the Student and Scholarship tables using INNER JOIN.
• Displays students who received scholarships along with their scholarship details.

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

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

Output:

• Filters records with odd STUDENT_ID values.
• Uses the condition STUDENT_ID % 2 != 0.

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

SELECT * FROM Student WHERE student_id % 2 = 0;

Output:

• Filters records with even STUDENT_ID values.
• Uses the condition STUDENT_ID % 2 = 0.

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,
    Scholarship.SCHOLARSHIP_AMOUNT,
    Scholarship.SCHOLARSHIP_DATE
FROM 
    Student
LEFT JOIN 
    Scholarship ON Student.STUDENT_ID = Scholarship.STUDENT_REF_ID;

Output:

• Retrieves all students using LEFT JOIN.
• Shows NULL for scholarship details if a student has not received a scholarship.

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:

• Sorts students by GPA in descending order.
• Displays the top 5 students with the highest GPA.

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 4, 1;

Output:

• Skips the first 4 records and retrieves the 5th record.
• Uses LIMIT and OFFSET for row selection.

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

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

Output:

• Finds the 5th highest GPA.
• Uses a correlated subquery to rank GPAs.

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;

Output:

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

• Identifies students with the same GPA.
• Uses a self-join and excludes matching the same student.

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:

• Finds the second highest GPA.
• Excludes the highest GPA using a subquery.

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;

Output:

• Combines results from two queries using UNION ALL.
• Includes duplicate rows and sorts them 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:

• Identifies students who have not received a scholarship.
• Uses the NOT IN operator to exclude scholarship recipients.

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

SET @half_count = (SELECT FLOOR(COUNT(*) / 2) FROM Student);

SELECT * FROM Student
LIMIT @half_count;

Output:

• Counts the total number of records in the table.
• Retrieves the first 50% of records using LIMIT.

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:

• Groups students by MAJOR.
• Displays majors having fewer than 4 students.

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:

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.

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

Output:

• Retrieves the last record from the Student table.
• Uses the maximum STUDENT_ID to identify the latest record.

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:

• Retrieves the first record from the Student table.
• Uses the minimum STUDENT_ID to identify the first entry.

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:

• Retrieves the last 5 records from the table.
• Reorders them in ascending STUDENT_ID order.

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:

• Retrieves the top 3 highest distinct GPAs.
• Uses a subquery to rank GPAs based on their values.

35. Write an SQL query to fetch three min GPA from a table using Correlated 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:

• Retrieves the bottom 3 lowest distinct GPAs.
• Uses a subquery to rank GPAs in ascending order.

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

SELECT DISTINCT GPA FROM Student S1 
WHERE 2  = (SELECT COUNT(DISTINCT GPA) FROM Student S2 WHERE S1.GPA <= S2.GPA) 
ORDER BY S1.GPA DESC;

Output:

• Finds the 2nd highest GPA.
• Uses a correlated subquery to rank GPAs.

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:

• Groups students by major.
• Finds the highest GPA in each major using MAX().

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:

• Finds the student(s) with the highest GPA.
• Uses a subquery to get the maximum GPA.

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

SELECT CURDATE();
SELECT NOW(); 

Output:

• CURDATE() returns the current date.
• NOW() returns the current date and time.

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;

Output:

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.

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

Output:

• Updates the GPA of Computer Science students to 7.5.
• Uses the UPDATE statement with a WHERE clause.

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:

• Calculates the average GPA for each major.
• Groups the results by major.

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:

• Sorts students by GPA in descending order.
• Displays the top 3 students with the highest GPA.

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 > 7.5
GROUP BY MAJOR;

Output:

• Filters students with GPA greater than 7.5.
• Groups the students by major.
• Counts the number of students in each major.

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

SELECT * FROM Student WHERE GPA = (SELECT GPA FROM Student WHERE STUDENT_ID = 201);

Output:

• Displays students having the same GPA as Jack Smith. If no one else shares it, only Jack Smith is returned.