1. (10 pts each) Formulate a SQL query for each of the following questions. The
ID: 3731884 • Letter: 1
Question
1. (10 pts each) Formulate a SQL query for each of the following questions. The prefix […], if any, in front of a question specifies limitations which the SQL query must be constrained by. For each of the questions, your answer must include the SQL query (input, text) and the corresponding screenshot of the result set (query output).
1. For each department in department table, find out the department’s total number of students in ascending order, including department without any student.
2. [nested query with IN] Find out unique course ids offered in Fall 2009 but not in Spring 2010.
3. [nested query with EXISTS] Find out unique course ids offered in Fall 2009 but not in Spring 2010.
The enclosed.sql file defines the following university database where primary key for each table is underlined department.dept_name is primary key (i.e., unique) course.title is not unique instructor.name is not globally unique but is unique within the department student.name is not globally unique but is unique within the department student takes ID ID name dept name tot cred section course department advisor title dept name credits s id i id semester building budget time slot ilding room no time slot id end time instructor classroom ID ame dept name teaches ID sec id ear capacity salaryExplanation / Answer
1) SELECT dept_name, tota_students
FROM (SELECT d.dept_name, COUNT(*) as total_students
FROM department d NATURAL JOIN student s
GROUP BY d.dept_name) AS dept_students
ORDER BY total_students ASC;
2) SELECT DISTINCT course_id
FROM section
WHERE semester = 'Fall' AND year= 2009 AND
course_id NOT IN (SELECT course_id
FROM section
WHERE semester = 'Spring' AND year= 2010);
3) SELECT DISTINCT course_id
FROM section s
WHERE semester = 'Fall' AND year= 2009 AND
NOT EXISTS (SELECT course_id
FROM section s1
WHERE semester = 'Spring' AND year= 2010 AND s.course_id=s1.course_id);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.