Top 50 SQL Queries Interview Questions and Answers To Get Hired

SQL Queries Interview Questions

SQL Queries Interview Questions and Answers

Q1. List all the courses available in the system.

SELECT * FROM Course;   

Explanation

This query returns student names enrolled in the 'Database Systems course. It joins the Student and Enrollment data and filters by course name.

Output

  COURSE_ID | COURSE_NAME        | CREDITS | START_DATE
    ------------------------------------------------------
    301      | Database Systems   | 3       | 2023-01-10
    302      | Operating Systems  | 4       | 2023-01-15
    303      | Data Structures    | 3       | 2023-01-20
    304      | Machine Learning   | 4       | 2023-01-25
    305      | Computer Networks  | 3       | 2023-01-30

Q2. Retrieve the names and departments of all instructors.

SELECT FIRST_NAME, LAST_NAME, DEPARTMENT FROM Instructor;    

Explanation

This query returns students with a GPA greater than 8.0 who are also enrolled in 'Machine Learning'. It combines the Student, Enrollment, and Course data to filter and list these students.

Output

    FIRST_NAME | LAST_NAME | DEPARTMENT
    ------------------------------------
    Alice      | Johnson   | Computer Science
    Bob        | Smith     | Electrical Eng.
    Carol      | White     | Mathematics
    David      | Brown     | Computer Science
    Emma       | Davis     | Computer Science 

Q3. Find the course names that have more than 3 credits.

SELECT COURSE_NAME FROM Course WHERE CREDITS > 3;

Explanation

This query finds classes with more than two enrolled students. It employs a GROUP BY clause to count enrollments for each course and a HAVING clause to filter courses with more than two students.

Output

    COURSE_NAME
    ---------------
    Operating Systems
    Machine Learning

Q4. Get the enrollment date for student ID 502.

SELECT ENROLL_DATE FROM Enrollment WHERE STUDENT_ID = 502;   

Explanation

This query returns the total number of students registered in each subject. It joins the Course and Enrollment tables and then aggregates the data using COUNT and GROUP BY.

Output

    ENROLL_DATE
    -------------
    2023-01-16
    2023-01-22

Q5. List the instructors who belong to the 'Computer Science' department.

SELECT FIRST_NAME, LAST_NAME FROM Instructor WHERE DEPARTMENT = 'Computer Science';  

Explanation

This query returns a list of students with no enrollments. It performs an LEFT JOIN between the Student and Enrollment databases, filtering when no matching enrollments exist.

Output

    FIRST_NAME | LAST_NAME
    ----------------------
    Alice      | Johnson
    David      | Brown
    Emma       | Davis

Q6. Find the courses that start after '2023-01-20'.

SELECT COURSE_NAME FROM Course WHERE START_DATE > '2023-01-20';  

Explanation

This query determines which course has the most students. It counts enrollments per course, sorts them in descending order, and limits the result to the top course.

Output

    COURSE_NAME
    ------------------
    Machine Learning
    Computer Networks  

Q7. Retrieve the first names of instructors who have the last name 'Smith.'

SELECT FIRST_NAME FROM Instructor WHERE LAST_NAME = 'Smith';

Explanation

This query returns students with GPAs within the provided range. It employs a WHERE clause to restrict GPA values to 7.5 to 8.5 inclusive.

Output

    FIRST_NAME
    ----------------
    Alice

Q8. Count the number of courses available.

SELECT COUNT(*) FROM Course;    

Explanation

This query retrieves course names and teachers. It combines the Course and Instructor tables to link courses to teachers.

Output

    COUNT(*)
    --------
    5

Q9. List the course names and start dates for courses with 4 credits.

SELECT COURSE_NAME, START_DATE FROM Course WHERE CREDITS = 4;   

Explanation

This query determines how many classes each student has enrolled in. It organizes the data by student and counts enrollments.

Output

    COURSE_NAME | START_DATE
    -------------------------
    Operating Systems | 2023-01-15
    Machine Learning  | 2023-01-25 

Q10. Retrieve the full names of instructors who teach 'Database Systems.'

SELECT i.FIRST_NAME, i.LAST_NAME
FROM Instructor i
JOIN Course c ON i.INSTRUCTOR_ID = c.COURSE_ID
WHERE c.COURSE_NAME = 'Database Systems';    

Explanation

This query returns the top five students with the highest GPA. It sorts the students by GPA in descending order and restricts the results to 5 entries.

Output

    FIRST_NAME | LAST_NAME
    ----------------------
    Alice      | Johnson
    David      | Brown
    Emma       | Davis  

Q11. Find the unique departments of instructors.

SELECT DISTINCT DEPARTMENT FROM Instructor;   

Explanation

This query calculates the average GPA for all students. It applies the AVG function on the GPA column of the Student table.

Output

    DEPARTMENT
    ------------------------
    Computer Science
    Electrical Eng.
    Mathematics 

Q12. Get the list of all student IDs enrolled in any course.

SELECT DISTINCT STUDENT_ID FROM Enrollment;   

Explanation

This query looks for students who have received scholarships worth more than $4000. It filters the Scholarship table according to the scholarship amount.

Output

    STUDENT_ID
    ----------------
    501
    502
    503
    504
    505  

Q13. List the course names in alphabetical order.

SELECT COURSE_NAME FROM Course ORDER BY COURSE_NAME ASC;    

Explanation

This query selects courses starting in the current year. It filters the Course table based on the commencement date of the current year.

Output

    COURSE_NAME
    -------------------
    Computer Networks
    Data Structures
    Database Systems
    Machine Learning
    Operating Systems

Q14. Find the instructors whose first name starts with 'A.'

SELECT FIRST_NAME, LAST_NAME FROM Instructor WHERE FIRST_NAME LIKE 'A%';    

Explanation

This query determines which student has the lowest GPA. It uses the MIN function to determine the lowest GPA and retrieves the related student information.

Output

    FIRST_NAME | LAST_NAME
    -----------------------
    Alice      | Johnson

Q15. Retrieve the course names and credits for courses with 'Systems' in the name.

SELECT COURSE_NAME, CREDITS FROM Course WHERE COURSE_NAME LIKE '%Systems%';  

Explanation

The query retrieves the names and credits of courses containing 'Systems' in their names from the Course table. It uses the SQL LIKE operator with a wildcard to match any course name that includes the word 'Systems'.

Output

    COURSE_NAME        | CREDITS
    --------------------------------
    Database Systems   | 3
    Operating Systems  | 4

Q16. Get the number of students enrolled in 'Machine Learning.'

SELECT COUNT(STUDENT_ID) AS Student_Count
FROM Enrollment e
JOIN Course c ON e.COURSE_ID = c.COURSE_ID
WHERE c.COURSE_NAME = 'Machine Learning';  

Explanation

The query totals the number of students registered in the 'Machine Learning' course. It connects the Enrollment and Course tables based on COURSE_ID and filters the results to include the course name 'Machine Learning.'

Output

    Student_Count
    ----------------
    45  

Q17. List the courses and their start dates, ordered by start date.

SELECT COURSE_NAME, START_DATE FROM Course ORDER BY START_DATE;    

Explanation

This query returns courses with fewer than three credits. It filters the Course table using the credits column.

Output

    COURSE_NAME        | START_DATE
    ---------------------------
    Database Systems   | 2023-01-10
    Operating Systems  | 2023-01-15
    Data Structures    | 2023-01-20
    Machine Learning   | 2023-01-25
    Computer Networks  | 2023-01-30

Q18. Find the instructors in the 'Mathematics' department.

SELECT FIRST_NAME, LAST_NAME FROM Instructor WHERE DEPARTMENT = 'Mathematics';    

Explanation

The query retrieves the first and last names of instructors who are in the 'Mathematics' department. It selects records from the Instructor table where the department is 'Mathematics'.

Output

    FIRST_NAME | LAST_NAME
    ----------------------
    Carol      | White  

Q19. Get the total number of credits for all courses.

SELECT SUM(CREDITS) AS Total_Credits FROM Course;   

Explanation

This query computes the total enrollments per course. It organizes enrollments by course and counts the number of students in each.

Output

    Total_Credits
    ----------------
    15

Q20. Retrieve the courses and the number of students enrolled in each course.

SELECT c.COURSE_NAME, COUNT(e.STUDENT_ID) AS Student_Count
FROM Course c
JOIN Enrollment e ON c.COURSE_ID = e.COURSE_ID
GROUP BY c.COURSE_NAME;    

Explanation

The query retrieves the names of courses and the number of students enrolled in each course. It joins the Course and Enrollment tables on COURSE_ID, groups the results by course name, and counts the number of students in each group.

Output

    COURSE_NAME        | Student_Count
    -----------------------------
    Database Systems   | 50
    Operating Systems  | 40
    Data Structures    | 35
    Machine Learning   | 45
    Computer Networks  | 30  

Q21. Find the instructors who are teaching more than one course.

SELECT i.FIRST_NAME, i.LAST_NAME, COUNT(c.COURSE_ID) AS Course_Count
FROM Instructor i
JOIN Course c ON i.INSTRUCTOR_ID = c.COURSE_ID
GROUP BY i.FIRST_NAME, i.LAST_NAME
HAVING COUNT(c.COURSE_ID) > 1;   

Explanation

Similar to the previous query, this discovers courses with no enrollments by joining Course and Enrollment and looking for null values in Enrollment.

Output

    FIRST_NAME | LAST_NAME | Course_Count
    -------------------------------------
    John       | Smith     | 3

Q22. List the courses along with their instructors' names.

SELECT c.COURSE_NAME, i.FIRST_NAME, i.LAST_NAME
FROM Course c
JOIN Instructor i ON c.COURSE_ID = i.INSTRUCTOR_ID;    

Explanation

This query returns students who have registered in numerous courses. It categorizes students by ID and filters out those who have more than one enrollment.

Output

    COURSE_NAME       | FIRST_NAME | LAST_NAME
    ------------------------------------------
    Database Systems  | John       | Smith
    Data Structures   | Alice      | Johnson

Q23. Retrieve the details of the course with the maximum credits.

SELECT * FROM Course WHERE CREDITS = (SELECT MAX(CREDITS) FROM Course);    

Explanation

This query computes the average number of credits per course. It applies the AVG function to the CREDITS column of the Course table.

Output

    COURSE_ID | COURSE_NAME       | CREDITS | START_DATE
    -----------------------------------------------------
    103       | Operating Systems | 4       | 2023-01-15  

Q24. Get the total number of students enrolled in each course, ordered by the number of students.

SELECT c.COURSE_NAME, COUNT(e.STUDENT_ID) AS Student_Count
FROM Course c
JOIN Enrollment e ON c.COURSE_ID = e.COURSE_ID
GROUP BY c.COURSE_NAME
ORDER BY Student_Count DESC;   

Explanation

This query returns all instructors and their courses, even those who do not offer any courses. It employs LEFT JOIN between the Instructor and the Course.

Output

    COURSE_NAME       | Student_Count
    -------------------------------
    Database Systems  | 50
    Operating Systems | 40

Q25. Find the courses that do not have any enrollments.

SELECT c.COURSE_NAME
FROM Course c
LEFT JOIN Enrollment e ON c.COURSE_ID = e.COURSE_ID
WHERE e.STUDENT_ID IS NULL;   

Explanation

This query retrieves students enrolled in 'Data Science' by combining the Student, Enrollment, and Course tables and filtering by course name.

Output

    COURSE_NAME
    --------------
    Artificial Intelligence 

Q26. Retrieve the names of students who are enrolled in more than one course.

SELECT s.FIRST_NAME, s.LAST_NAME
FROM Student s
JOIN Enrollment e ON s.STUDENT_ID = e.STUDENT_ID
GROUP BY s.STUDENT_ID
HAVING COUNT(e.COURSE_ID) > 1;    

Explanation

This query returns students whose GPAs exceed 8.0. It uses a WHERE clause to filter students depending on their GPA.

Output

    FIRST_NAME | LAST_NAME
    ----------------------
    David      | Brown

Q27. Find the average credits per course.

SELECT AVG(CREDITS) AS Avg_Credits FROM Course;   

Explanation

This query finds courses with the fewest amount of credits. It utilizes a subquery to get the lowest credit value and then filters courses accordingly.

Output

    Avg_Credits
    ------------
    3.5

Q28. List the instructors and the courses they teach, including instructors with no courses.

SELECT i.FIRST_NAME, i.LAST_NAME, c.COURSE_NAME
FROM Instructor i
LEFT JOIN Course c ON i.INSTRUCTOR_ID = c.COURSE_ID;    

Explanation

This query returns students enrolled in 'Machine Learning' but not 'Artificial Intelligence'. It employs a subquery to exclude those registered in 'Artificial Intelligence'.

Output

    FIRST_NAME | LAST_NAME | COURSE_NAME
    --------------------------------------
    John       | Smith     | Database Systems
    Alice      | Johnson   | NULL

Q29. Retrieve the students who are enrolled in the 'Data Science' course.

SELECT s.FIRST_NAME, s.LAST_NAME
FROM Student s
JOIN Enrollment e ON s.STUDENT_ID = e.STUDENT_ID
JOIN Course c ON e.COURSE_ID = c.COURSE_ID
WHERE c.COURSE_NAME = 'Data Science';    

Explanation

This inquiry focuses on courses and instructors from the 'Engineering' department. It filters the Instructor and Course tables by department.

Output

    FIRST_NAME | LAST_NAME
    ----------------------
    Emily      | Davis

Q30. Get the names of students who have a GPA greater than 8.0.

SELECT FIRST_NAME, LAST_NAME FROM Student WHERE GPA > 8.0;    

Explanation

This query returns students who have no course enrollments. It uses a subquery to exclude students who do not appear in the Enrollment table.

Output

    FIRST_NAME | LAST_NAME
    ----------------------
    Michael    | Lee 

Q31. List the courses with the lowest number of credits.

SELECT COURSE_NAME FROM Course WHERE CREDITS = (SELECT MIN(CREDITS) FROM Course);    

Explanation

This query counts students for each course and organizes the results by course name. It joins the Course and Enrollment tables and sorts by course name.

Output

    COURSE_NAME
    --------------
    Basic Mathematics  

Q32. Find the students enrolled in 'Machine Learning' but not in 'Artificial Intelligence'.

SELECT s.FIRST_NAME, s.LAST_NAME
FROM Student s
JOIN Enrollment e ON s.STUDENT_ID = e.STUDENT_ID
JOIN Course c ON e.COURSE_ID = c.COURSE_ID
WHERE c.COURSE_NAME = 'Machine Learning'
AND s.STUDENT_ID NOT IN (
    SELECT e2.STUDENT_ID
    FROM Enrollment e2
    JOIN Course c2 ON e2.COURSE_ID = c2.COURSE_ID
    WHERE c2.COURSE_NAME = 'Artificial Intelligence'
);    

Explanation

This query identifies students with the most course enrollments. It utilizes a subquery to determine the maximum number of enrollments and then filters the students accordingly.

Output

    FIRST_NAME | LAST_NAME
    ----------------------
    Emily      | Clark  

Q33. Retrieve the course names and instructors for courses that are being taught in the 'Engineering' department.

SELECT c.COURSE_NAME, i.FIRST_NAME, i.LAST_NAME
FROM Course c
JOIN Instructor i ON c.COURSE_ID = i.INSTRUCTOR_ID
WHERE i.DEPARTMENT = 'Engineering';    

Explanation

This query returns the courses with the highest and lowest student enrollments. It joins two queries with UNION ALL to display both extremes.

Output

    COURSE_NAME      | FIRST_NAME | LAST_NAME
    ------------------------------------------
    Fluid Mechanics  | Robert     | White  

Q34. Find the students who have not enrolled in any course.

SELECT FIRST_NAME, LAST_NAME FROM Student
WHERE STUDENT_ID NOT IN (SELECT STUDENT_ID FROM Enrollment);    

Explanation

This query, like the last one, detects students who do not have enrollments by filtering out those who do not appear in the Enrollment table.

Output

    FIRST_NAME | LAST_NAME
    ----------------------
    Anna       | Martinez  

Q35. Retrieve the courses along with the number of students enrolled in each course, ordered by course name.

SELECT c.COURSE_NAME, COUNT(e.STUDENT_ID) AS Student_Count
FROM Course c
LEFT JOIN Enrollment e ON c.COURSE_ID = e.COURSE_ID
GROUP BY c.COURSE_NAME
ORDER BY c.COURSE_NAME;    

Explanation

This query determines the average GPA of students enrolled in 'Machine Learning'. It connects the Student, Enrollment, and Course data and calculates the average GPA.

Output

    COURSE_NAME       | Student_Count
    -------------------------------
    Algorithms        | 35  

Q36. Find the students who have enrolled in the maximum number of courses.

SELECT s.FIRST_NAME, s.LAST_NAME
FROM Student s
JOIN Enrollment e ON s.STUDENT_ID = e.STUDENT_ID
GROUP BY s.STUDENT_ID
HAVING COUNT(e.COURSE_ID) = (
    SELECT MAX(Course_Count)
    FROM (
        SELECT COUNT(COURSE_ID) AS Course_Count
        FROM Enrollment
        GROUP BY STUDENT_ID
    ) AS SubQuery
);    

Explanation

This query counts the number of instructors in each department. It organizes the results by department and uses COUNT to calculate the number of instructors.

Output

    FIRST_NAME | LAST_NAME
    ----------------------
    Jack       | Robinson 

Q37. Retrieve the course with the highest and lowest number of students enrolled.

SELECT c.COURSE_NAME, COUNT(e.STUDENT_ID) AS Student_Count
FROM Course c
JOIN Enrollment e ON c.COURSE_ID = e.COURSE_ID
GROUP BY c.COURSE_NAME
ORDER BY Student_Count DESC
LIMIT 1
UNION ALL
SELECT c.COURSE_NAME, COUNT(e.STUDENT_ID) AS Student_Count
FROM Course c
JOIN Enrollment e ON c.COURSE_ID = e.COURSE_ID
GROUP BY c.COURSE_NAME
ORDER BY Student_Count ASC
LIMIT 1;    

Explanation

This query discovers students enrolled in courses from several departments. It counts each student's distinct departments and filters out those with more than one.

Output

    COURSE_NAME       | Student_Count
    ---------------------------------
    Data Science      | 55
    Basic Mathematics | 10  

Q38. Get the names of students who are not enrolled in any courses.

SELECT FIRST_NAME, LAST_NAME
FROM Student
WHERE STUDENT_ID NOT IN (SELECT STUDENT_ID FROM Enrollment);    

Explanation

This query returns courses beginning in January 2023 with more than three credits. It filters the Course table by start date and credit criterion.

Output

    FIRST_NAME | LAST_NAME
    ----------------------
    Anna       | Martinez  

Q39. Find the average GPA of students enrolled in 'Machine Learning'.

SELECT AVG(s.GPA) AS Average_GPA
FROM Student s
JOIN Enrollment e ON s.STUDENT_ID = e.STUDENT_ID
JOIN Course c ON e.COURSE_ID = c.COURSE_ID
WHERE c.COURSE_NAME = 'Machine Learning';    

Explanation

This query counts enrollments for each course, including those with no enrollments. It employs an LEFT JOIN to incorporate classes that do not have any students.

Output

    Average_GPA
    ------------
    8.7

Q40. List the departments and the number of instructors in each department.

SELECT DEPARTMENT, COUNT(INSTRUCTOR_ID) AS Instructor_Count
FROM Instructor
GROUP BY DEPARTMENT;    

Explanation

This query returns instructors who have taught more than five courses. It is grouped by instructor and counts the courses taught, then filters by count.

Output

    DEPARTMENT       | Instructor_Count
    -------------------------------------
    Computer Science | 5
    Engineering      | 7

Q41. Retrieve the details of students who have enrolled in courses from more than one department.

SELECT e.STUDENT_ID, COUNT(DISTINCT i.DEPARTMENT) AS Dept_Count
FROM Enrollment e
JOIN Course c ON e.COURSE_ID = c.COURSE_ID
JOIN Instructor i ON c.COURSE_ID = i.INSTRUCTOR_ID
GROUP BY e.STUDENT_ID
HAVING COUNT(DISTINCT i.DEPARTMENT) > 1;    

Explanation

Output

    STUDENT_ID | Dept_Count
    -----------------------
    102        | 2
    203        | 3

SELECT COURSE_NAME
FROM Course
WHERE START_DATE BETWEEN '2023-01-

01' AND '2023-01-31'
AND CREDITS > 3;    

    COURSE_NAME
    ------------
    Advanced Data Structures

SELECT c.COURSE_NAME, COUNT(e.STUDENT_ID) AS Student_Count
FROM Course c
LEFT JOIN Enrollment e ON c.COURSE_ID = e.COURSE_ID
GROUP BY c.COURSE_NAME;    

    COURSE_NAME       | Student_Count
    -------------------------------
    Algorithms        | 35
    Basic Mathematics | 0

SELECT i.FIRST_NAME, i.LAST_NAME, COUNT(c.COURSE_ID) AS Course_Count
FROM Instructor i
JOIN Course c ON i.INSTRUCTOR_ID = c.COURSE_ID
GROUP BY i.FIRST_NAME, i.LAST_NAME
HAVING COUNT(c.COURSE_ID) > 5;   

    FIRST_NAME | LAST_NAME | Course_Count
    -------------------------------------
    John       | Doe       | 6 

SELECT c.COURSE_NAME
FROM Course c
JOIN Instructor i ON c.COURSE_ID = i.INSTRUCTOR_ID
WHERE i.TITLE LIKE '%Dr.%';   

    COURSE_NAME
    ------------
    Quantum Physics

SELECT s.FIRST_NAME, s.LAST_NAME, c.COURSE_NAME
FROM Student s
JOIN Enrollment e ON s.STUDENT_ID = e.STUDENT_ID
JOIN Course c ON e.COURSE_ID = c.COURSE_ID
WHERE s.GPA > 8.0;    

    FIRST_NAME | LAST_NAME | COURSE_NAME
    -------------------------------------
    Emily      | Clark     | Data Science

SELECT COURSE_NAME
FROM Course
WHERE START_DATE BETWEEN '2023-06-01' AND '2023-08-31';    

    COURSE_NAME
    ------------
    Summer Workshop  

SELECT s.FIRST_NAME, s.LAST_NAME, SUM(sc.SCHOLARSHIP_AMOUNT) AS Total_Scholarship
FROM Student s
JOIN Scholarship sc ON s.STUDENT_ID = sc.STUDENT_ID
GROUP BY s.FIRST_NAME, s.LAST_NAME;    

    FIRST_NAME | LAST_NAME | Total_Scholarship
    ------------------------------------------
    Anna       | Martinez  | 10000  

SELECT s.FIRST_NAME, s.LAST_NAME
FROM Student s
JOIN Scholarship sc ON s.STUDENT_ID = sc.STUDENT_ID
WHERE YEAR(sc.SCHOLARSHIP_DATE) IN (2021, 2022)
GROUP BY s.FIRST_NAME, s.LAST_NAME
HAVING COUNT(DISTINCT YEAR(sc.SCHOLARSHIP_DATE)) = 2;    

    FIRST_NAME | LAST_NAME
    ----------------------
    Jack       | Robinson 

SELECT s.FIRST_NAME, s.LAST_NAME, COUNT(e.COURSE_ID) AS Course_Count
FROM Student s
JOIN Enrollment e ON s.STUDENT_ID = e.STUDENT_ID
GROUP BY s.STUDENT_ID
HAVING COUNT(e.COURSE_ID) > 3;   

    FIRST_NAME | LAST_NAME | Course_Count
    -------------------------------------
    Emily      | Clark     | 4

Summary

Finally, knowing how to answer SQL Queries interview questions is essential for getting a job as a data analyst or engineer in a top-tier company. By performing these 50 queries and knowing their solutions, you will be well-equipped to demonstrate your SQL knowledge. Remember, confidence and practice are essential for succeeding in your next interview. Also, consider our SQL server certification course training for a better understanding of other SQL server concepts.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE