ASSIGNMENT 3
SCHEMA
Information Managment
Spring 2023
ASSIGNMENT 3
List of Student IDs
SELECT ID
FROM takes
List of Instructors
SELECT name
FROM instructor
List of Departments
SELECT dept_name
FROM department
Students IDs (hint: from the
takesrelation)
Instructors
Departments
Write SQL codes to get a list of:
1
Information Managment
Spring 2023
ASSIGNMENT 3
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.
Add grades to the list
Find the ID and name of
each student who has not
taken any course offered
before 2017.
Write in SQL codes to do
following queries:
2
Information Managment
Spring 2023
SELECT DISTINCT takes.ID, student.name
FROM takes, student, course
WHERE takes.course_id = course.course_id
AND takes.ID = student.ID
AND course.dept_name = 'Comp. Sci.'
SELECT DISTINCT student.ID, student.name, takes.grade
FROM student, takes, course
WHERE student.ID = takes.ID
AND takes.course_id = course.course_id
AND course.dept_name = 'Comp. Sci.'
SELECT ID, name
FROM student
WHERE ID NOT IN
(SELECT ID FROM takes
WHERE year < 2017)
ASSIGNMENT 3
For each department, find
the maximum salary of
instructors in that
department. You may
assume that every
department has at least
one instructor.
Find the lowest, across all
departments, of the per-
department maximum
salary computed by the
preceding query.
Add names to the list
Write in SQL codes to do
following queries:
2
Information Managment
Spring 2023
SELECT department.dept_name, MAX(instructor.salary) AS max_salary
FROM instructor
JOIN department ON instructor.dept_name = department.dept_name
GROUP BY department.dept_name
SELECT department.dept_name, MIN(instructor.salary) AS min_salary
FROM instructor
JOIN department ON instructor.dept_name = department.dept_name
GROUP BY department.dept_name
SELECT department.dept_name, instructor.name, MIN(instructor.salary) AS min_salary
FROM instructor
JOIN department ON instructor.dept_name = department.dept_name
GROUP BY department.dept_name
ASSIGNMENT 3
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.)
3
Information Managment
Spring 2023
SELECT DISTINCT instructor.ID, instructor.name
FROM instructor
LEFT JOIN teaches ON instructor.ID = teaches.ID
WHERE instructor.ID NOT IN (
SELECT ID
FROM takes
WHERE grade = 'A'
)
ASSIGNMENT 3
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.
4
Information Managment
Spring 2023
SELECT takes.course_id, takes.sec_id, takes.year, takes.semester, COUNT(*) AS num
FROM takes
GROUP BY takes.course_id, takes.sec_id, takes.year, takes.semester
HAVING COUNT(*) > 0
