Assignment 3 - February 19th, 2026

Information Management

Task One: Write SQL codes to get a list of: Students, Instructors, & Departments

SQL CODE:

SELECT DISTINCT *
FROM student;

SELECT *
FROM instructor;

SELECT *
FROM department;

RESULT:

Or Click to Show/Hide Result

Task Two: Write SQL codes to do following queries: i. Find the ID and name of each student who has taken at least one Comp. Sci. course; make sure there are no duplicate names in the result.

2. Add grades to the list

3. Find the ID and name of each student who has not taken any course offered before 2017.

4. For each department, find the maximum salary of instructors in that department. You may assume that every department has at least one instructor.

5. Find the lowest, across all departments, of the per-department maximum salary computed by the preceding query.

6. Add names to the list

SQL CODE for I & II:

SELECT s.ID,
       s.name,
       GROUP_CONCAT(t.grade, ', ') AS grades
FROM student AS s
JOIN takes  AS t ON t.ID = s.ID
JOIN course  AS c ON c.course_id = t.course_id
WHERE c.dept_name = 'Comp. Sci.'
GROUP BY s.ID, s.name;

RESULT for I & II:

Or Click to Show/Hide Result

SQL CODE FOR III:

SELECT s.ID, s.name
FROM student AS s
WHERE NOT EXISTS (
  SELECT 1
  FROM takes AS t
  WHERE t.ID = s.ID
    AND t.year < 2017
);

RESULT FOR III:

Or Click to Show/Hide Result

SQL CODE FOR IV & VI:

SELECT i.dept_name, i.name, i.salary
FROM instructor AS i
JOIN (
    SELECT dept_name, MAX(salary) AS max_salary
    FROM instructor
    GROUP BY dept_name
) AS dept_max
ON i.dept_name = dept_max.dept_name
AND i.salary = dept_max.max_salary;

RESULT FOR IV & VI:

Or Click to Show/Hide Result

SQL CODE FOR V & VI:

SELECT i.dept_name, i.name, i.salary
FROM instructor AS i
JOIN (
    SELECT dept_name, MAX(salary) AS max_salary
    FROM instructor
    GROUP BY dept_name
    ORDER BY max_salary
    LIMIT 1
) AS dept_max
ON i.dept_name = dept_max.dept_name
AND i.salary = dept_max.max_salary;

RESULT FOR V & VI:

Or Click to Show/Hide Result

Task Three: Find instructor (with name and ID) who has never given an A grade in any course she or he has taught. (Instructors who have never taught a course trivially satisfy this condition.)

SQL CODE:

SELECT i.ID, i.name
FROM instructor AS i
WHERE NOT EXISTS (
  SELECT 1
  FROM teaches AS te
  JOIN takes   AS ta
    ON ta.course_id = te.course_id
   AND ta.sec_id    = te.sec_id
   AND ta.semester  = te.semester
   AND ta.year      = te.year
  WHERE te.ID = i.ID
    AND ta.grade = 'A'
);

RESULT:

Or Click to Show/Hide Result

Task Four:Write SQL query to find the number of students in each section. The result columns should appear in the order “courseid, secid, year, semester, num”. You do not need to output sections with 0 students.

SQL CODE:

SELECT course_id,
       sec_id,
       year,
       semester,
       COUNT(ID) AS num
FROM takes
GROUP BY course_id, sec_id, year, semester;

RESULT:

Or Click to Show/Hide Result