SQL Query Interview Questions -
GeeksforGeeks
TABLE STRUCTURES
1. Student Table
Columns: STUDENT_ID, FIRST_NAME, LAST_NAME, GPA, ENROLLMENT_DATE,
MAJOR
2. Program Table
Columns: PROGRAM_ID, PROGRAM_NAME, DURATION_MONTHS
3. Scholarship Table
Columns: SCHOLARSHIP_ID, STUDENT_REF_ID, SCHOLARSHIP_AMOUNT,
SCHOLARSHIP_DATE
Sample Data:
========================================
SQL INTERVIEW QUESTIONS (45 QUESTIONS)
========================================
DIFFICULTY LEVELS:
EASY (Questions 1-7): Basic string functions and simple SELECT statements
MEDIUM (Questions 8-34): Filtering, sorting, JOINs, aggregation, and subqueries
HARD (Questions 35-45): Advanced subqueries, correlated subqueries, complex logic, and
optimization
Q1. 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;
Explanation: Uses the UPPER() function to convert FIRST_NAME values to uppercase. The
alias STUDENT_NAME is applied to the output column.
---
Q2. 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);
Explanation: The DISTINCT keyword ensures only unique values from the MAJOR column
are retrieved, removing duplicates.
---
Q3. 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;
Explanation: The SUBSTRING() function extracts a portion of the string. Here, it starts at
position 1 and retrieves the next 3 characters.
---
Q4. Write a SQL query to find the position of alphabet a in the first name column Shivansh
from Student table.
Query:
SELECT INSTR(LOWER(FIRST_NAME), 'a') FROM Student WHERE FIRST_NAME =
'Shivansh';
Explanation: The INSTR() function finds the position of the first occurrence of the specified
character in the string.
---
Q5. Write a SQL query that fetches the unique values of MAJOR Subjects from Student
table and print its length.
Query:
SELECT DISTINCT MAJOR, LENGTH(MAJOR) FROM Student;
Explanation: Combines DISTINCT with the LENGTH() function to calculate the length of
each unique value in the MAJOR column.
---
Q6. 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;
Explanation: The REPLACE() function substitutes every occurrence of the letter 'a' in
FIRST_NAME with 'A'.
---
Q7. 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;
Explanation: The CONCAT() function combines FIRST_NAME and LAST_NAME, separated
by a space, into a single column.
---
Q8. 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;
Explanation: The ORDER BY clause sorts records first by FIRST_NAME in ascending order,
then by MAJOR in descending order.
---
Q9. 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');
Explanation: The IN operator checks if FIRST_NAME matches specific values and retrieves
only matching rows.
---
Q10. 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');
Explanation: The NOT IN operator filters out rows where FIRST_NAME matches specified
values.
---
Q11. 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';
Explanation: The LIKE operator with %a matches any FIRST_NAME ending with the letter
'a'. % is a wildcard representing any characters preceding 'a'.
---
Q12. 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';
Explanation: The LIKE operator with _____a ensures that the FIRST_NAME is exactly five
characters long and ends with 'a'.
---
Q13. 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;
Explanation: The BETWEEN operator retrieves rows where the GPA falls within the inclusive
range of 9.00 to 9.99.
---
Q14. 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';
Explanation: The COUNT(*) function calculates the total number of rows where the MAJOR
is 'Computer Science'.
---
Q15. 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;
Explanation: This query uses CONCAT() to merge FIRST_NAME and LAST_NAME into a
full name and filters by GPA range.
---
Q16. 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;
Explanation: The GROUP BY groups rows by MAJOR, and COUNT(*) computes the number
of students in each group. ORDER BY sorts in descending order.
---
Q17. 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;
Explanation: The INNER JOIN combines data from Student and Scholarship tables where
STUDENT_ID matches STUDENT_REF_ID.
---
Q18. Write an SQL query to show only odd rows from Student table.
Query:
SELECT * FROM Student WHERE student_id % 2 != 0;
Explanation: The condition STUDENT_ID % 2 != 0 filters out rows where STUDENT_ID is
odd.
---
Q19. Write an SQL query to show only even rows from Student table.
Query:
SELECT * FROM Student WHERE student_id % 2 = 0;
Explanation: The condition STUDENT_ID % 2 = 0 retrieves rows where STUDENT_ID is
even.
---
Q20. 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;
Explanation: LEFT JOIN retrieves all students, including those without scholarships. For
students with no matching scholarship record, the fields are NULL.
---
Q21. 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;
Explanation: This query sorts the students by GPA in descending order and limits the result
to the top 5 records.
---
Q22. 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;
Explanation: This query skips the top 4 records and fetches the 5th record using LIMIT offset
and count.
---
Q23. Write an SQL query to determine the 5th highest GPA without using LIMIT keyword.
Query:
SELECT * FROM Student s1
WHERE 5 = (
SELECT COUNT(DISTINCT ([Link]))
FROM Student s2
WHERE [Link] >= [Link]
);
Explanation: Uses a correlated subquery to count how many unique GPAs are greater than
or equal to the current student's GPA.
---
Q24. Write an SQL query to fetch the list of Students with the same GPA.
Query:
SELECT s1.* FROM Student s1, Student s2 WHERE [Link] = [Link] AND
s1.Student_id != s2.Student_id;
Explanation: This query identifies students who share the same GPA by performing a self-
join on the Student table.
---
Q25. 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);
Explanation: The subquery fetches the highest GPA, and the main query excludes this value
to find the second highest GPA.
---
Q26. 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;
Explanation: This query uses UNION ALL to combine the Student table with itself, resulting
in duplicate rows. Unlike UNION, UNION ALL retains all rows from both queries.
---
Q27. 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);
Explanation: This query uses the NOT IN operator to identify students whose IDs are not
listed in the Scholarship table.
---
Q28. Write an SQL query to fetch the first 50% records from a table.
Query:
SET @half_count = (SELECT FLOOR(COUNT(*) / 2) FROM Student);
SELECT * FROM Student LIMIT @half_count;
Explanation: This query calculates 50% of the total records and limits the result accordingly.
---
Q29. 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;
Explanation: This query groups students by their MAJOR and uses HAVING to filter groups
with less than 4 people.
---
Q30. 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;
Explanation: This query groups students by their MAJOR and counts the number of students
in each group.
---
Q31. 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);
Explanation: This query identifies the last record in the Student table by selecting the record
where STUDENT_ID equals the maximum value.
---
Q32. 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);
Explanation: This query retrieves the first row by identifying the record with the smallest
STUDENT_ID.
---
Q33. 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;
Explanation: This query first retrieves the last five records based on descending
STUDENT_ID, then reorders them in ascending order.
---
Q34. 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 [Link] <=
[Link])
ORDER BY [Link] DESC;
Explanation: The subquery counts how many unique GPAs are greater than or equal to the
current GPA. If the count is 3 or less, it means the GPA is among the top 3 highest values.
---
Q35. 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 [Link] >=
[Link])
ORDER BY [Link];
Explanation: This query works similarly but retrieves the bottom 3 smallest GPAs by
reversing the comparison and ordering in ascending order.
---
Q36. 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 [Link] <=
[Link])
ORDER BY [Link] DESC;
Explanation: This query dynamically fetches the GPA ranked nth in descending order.
Replace 'n' with the desired rank.
---
Q37. 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;
Explanation: The query groups the students by MAJOR and calculates the maximum GPA
for each group using the MAX function.
---
Q38. 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);
Explanation: This query identifies the student(s) with the highest GPA by comparing their
GPA with the maximum GPA in the table.
---
Q39. 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
Explanation: CURDATE() returns the current date, while NOW() provides both the current
date and time.
---
Q40. 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.
---
Q41. 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';
Explanation: This query updates the GPA of all students whose MAJOR is Computer
Science to 7.5.
---
Q42. 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;
Explanation: This query calculates the average GPA for students in each major using the
AVG function.
---
Q43. Write an SQL query to show the top 3 students with the highest GPA.
Query:
SELECT * FROM Student ORDER BY GPA DESC LIMIT 3;
Explanation: This query sorts the Student table by GPA in descending order and limits the
results to the top 3 records.
---
Q44. 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 > 7.5
GROUP BY MAJOR;
Explanation: WHERE GPA > 7.5 filters individual students before grouping. GROUP BY
MAJOR groups the remaining students. COUNT(STUDENT_ID) counts the students in each
major.
---
Q45. 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
STUDENT_ID = 201);