1. (10 pts each) Formulate a SQL query for each of the following questions. The

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).

a). [nested query with EXISTS] Find out unique course ids offered in Fall 2009 but not in Spring 2010.

b). [nested query without GROUP BY] Find out unique course ids (in ascending order) which were offered more than once in year 2009. (NOTE: Two sections of course A is counted as 2.)

The enclosed.sql file defines the following university database where primary key for each table is underlined department.dept-name is primary key (i.., 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 10 time slot id end time instructor classroom ID ame dept name teaches ID sec id ear capacity salary

Explanation / Answer

Part a)

As it has not been menioned which attribute will identify the Fall 2009 I am just assuming semester will have values 'Fall' and 'Spring' and Year will have values '2009', '2010'.

SELECT course_id

FROM course

WHERE EXIST (SELECT section_id FROM section WHERE year = '2009' AND semester = 'Fall' AND year <> '2010' AND semester <> 'Spring';

Part b)

For this subquery is used in WHERE clause to get the course_id's which appear more than once in year 2009. Order by clause is used to sort the result in ascending order.

SELECT course_id

FROM course

WHERE course_id IN (SELECT course_id FROM section WHERE year = '2009' HAVING COUNT(course_id) >1)

ORDER BY course_id ASC;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.