SELECT COUNT(instructor_id) FROM instructors WHERE instructor_name LIKE 'Li%';

This query counts the number of instructors whose last name starts with "Li" by using the LIKE operator with a wildcard pattern.

Problem 2: Find students who scored higher in course "01" than in course "02"

SELECT st.student_id, crs1.score AS course01_score, crs2.score AS course02_score
FROM students st
LEFT JOIN (SELECT student_id, score FROM grades WHERE course_id = '01') crs1 ON st.student_id = crs1.student_id
LEFT JOIN (SELECT student_id, score FROM grades WHERE course_id = '02') crs2 ON st.student_id = crs2.student_id
WHERE crs1.score > crs2.score;

This solution creates two temporary result sets for each course and joins them to compare scores, returning only those students who performed better in course "01" than in course "02".

Problem 3: Find students with average score greater than 60

SELECT student_id, AVG(grade) FROM grades
GROUP BY student_id
HAVING AVG(grade) > 60;

This query calculates the average grade for each student and filters the results to include only those with an average above 60, using GROUP BY and HAVING clauses.

Problem 4: Find student ID, name, number of courses taken, and total score for all students

SELECT st.student_id, st.name, COALESCE COUNT(gr.course_id), 0) AS courses_taken, 
COALESCE(SUM(gr.grade), 0) AS total_score
FROM students st
LEFT JOIN grades gr ON st.student_id = gr.student_id
GROUP BY st.student_id;

This query uses a LEFT JOIN to ensure all students are included, even those without grades. The COALESCE function replaces NULL values with zeros for students who haven't taken any courses.

Problem 5: Find students who haven't taken any courses taught by "Professor Smith"

SELECT student_id, name FROM students
WHERE student_id NOT IN (
    SELECT gr.student_id
    FROM grades gr
    WHERE gr.course_id = (
        SELECT crs.course_id
        FROM courses crs
        WHERE crs.instructor_id = (
            SELECT inst.instructor_id
            FROM instructors inst
            WHERE inst.name = 'Professor Smith'
        )
    )
);

This nested subquery first finds the instructor ID for "Professor Smith", then identifies their courses, and finally selects students who haven't enrolled in any of those courses.

Problem 6: Find students who have taken all courses taught by "Professor Smith"

SELECT DISTINCT st.student_id, st.name
FROM students st
JOIN grades gr ON st.student_id = gr.student_id
WHERE gr.course_id IN (
    SELECT crs.course_id
    FROM courses crs
    WHERE crs.instructor_id = (
        SELECT inst.instructor_id
        FROM instructors inst
        WHERE inst.name = 'Professor Smith'
    )
);

This query identifies students enrolled in courses taught by "Professor Smith" by first finding the instructor's courses and then matching students who have taken those courses.

Problem 7: Find students who have taken both course "01" and course "02"

SELECT st.student_id, st.name
FROM students st
JOIN grades gr1 ON st.student_id = gr1.student_id AND gr1.course_id = '01'
JOIN grades gr2 ON st.student_id = gr2.student_id AND gr2.course_id = '02';

This solution uses two JOIN conditions to ensure students have records for both courses, effectively finding the intersection of students enrolled in each course.

Problem 8: Find total score for course "02"

SELECT course_id, SUM(grade) FROM grades WHERE course_id = '02';

This straightforward query calculates the sum of all grades for course "02" using the SUM aggregate function.

Problem 9: Find students with all course scores below 60

SELECT DISTINCT st.student_id, st.name
FROM students st
LEFT JOIN grades gr ON st.student_id = gr.student_id
WHERE gr.grade < 60 OR gr.grade IS NULL;

This query includes students who either have scores below 60 in all their courses or have no recorded grades (NULL values), using DISTINCT to avoid duplicate results.

Problem 10: Find students who haven't taken all courses

SELECT st.student_id, st.name
FROM students st
WHERE st.student_id NOT IN (
    SELECT gr.student_id
    FROM grades gr
    GROUP BY gr.student_id
    HAVING COUNT(DISTINCT gr.course_id) = (SELECT COUNT(*) FROM courses)
);

This query identifies students who haven't enrolled in every available course by comparing the count of distinct courses each student has taken against the total number of courses.

Problem 11: Find students who have taken at least one course that student "01" has taken

SELECT DISTINCT st.student_id, st.name
FROM students st
JOIN grades gr ON st.student_id = gr.student_id
WHERE gr.course_id IN (
    SELECT course_id
    FROM grades
    WHERE student_id = '01'
) AND st.student_id != '01';

This query finds students who share at least one course with student "01" by first identifying courses taken by student "01" and then finding other students enrolled in those same courses.

Problem 12: Find students who have taken exactly the same courses as student "01"

SELECT st.student_id, st.name
FROM students st
WHERE st.student_id != '01' AND st.student_id IN (
    SELECT gr.student_id
    FROM grades gr
    GROUP BY gr.student_id
    HAVING COUNT(DISTINCT gr.course_id) = (
        SELECT COUNT(DISTINCT course_id)
        FROM grades
        WHERE student_id = '01'
    )
);

This query identifies students who have taken the exact same number ofcourses as student "01" by comparing course counts, excluding student "01" from the results.

Problem 13: Find students who haven't taken any courses taught by "Professor Johnson"

SELECT DISTINCT st.student_id, st.name
FROM students st
WHERE st.student_id NOT IN (
    SELECT DISTINCT gr.student_id
    FROM grades gr
    WHERE gr.course_id IN (
        SELECT crs.course_id
        FROM courses crs
        WHERE crs.instructor_id = (
            SELECT inst.instructor_id
            FROM instructors inst
            WHERE inst.name = 'Professor Johnson'
        )
    )
);

This query uses nested subqueries to first identify courses taught by "Professor Johnson", then find students enrolled in those courses, and finally select students not in that group.

Problem 14: Find students with two or more failing courses and their average scores

SELECT st.student_id, st.name, AVG(gr.grade) AS average_score
FROM students st
JOIN grades gr ON st.student_id = gr.student_id
WHERE gr.grade < 60
GROUP BY st.student_id
HAVING COUNT(gr.grade) >= 2;

This query identifeis students with multiple failing grades (below 60) and calculates their average score, filtering results to include only those with at least two failing courses.

Problem 15: Find students who scored below 60 in course "01", ordered by score descending

SELECT DISTINCT st.student_id, st.name, st.birth_date, st.gender
FROM students st
JOIN grades gr ON st.student_id = gr.student_id
WHERE gr.course_id = '01' AND gr.grade < 60
ORDER BY gr.grade DESC;

This query retrieves student information for those who scored below 60 in course "01" and sorts the results in descending order of their scores, showing the highest failing scores first.