Do not answer if you cannot answer all of them.

Problem 2. Answer 2.1-2.3 using university schema classroom (building, room number, capacity) department (dept name, building, budget) course (course id, title, dept_name, credits) instructor (ID, name, dept_name, salary) section (course id, sec id, semester, year, building, room_number, time_slot_id) teaches (ID, course id, sec id, semester, year) student (ID, name, dept_name, tot_cred) takes (ID, course id, sec id semester, year grade) advisor (s ID i ID) time slot (time slot id, day, start time, end_time) prereq (course id, prereq id) Figure 2.1 Schema of the University database. course_dept department dept_name building budget inst_dept stud_dept instructor ID name salary advisor student ID name tot_cred teaches takes grade section course course id sec_course seci Senester vor sec_time_slot time_slot time_slot id day start_time end_time credits prereq course_id prereq_id sec_class classroom building room_member capacity Figure 2.2 E-R diagram for University database. 2.1 Write the following queries in SQL, using the university schema. a. Find the titles of courses in the Comp. Sci. department that have 3 credits. b. Find the IDs of all students who were taught by an instructor named Einstein; make sure there are no duplicates in the result. c. Find the highest salary of any instructor. d. Find all instructors earning the highest salary (there may be more than one with the same salary). e. Find the enrollment of each section that was offered in Autumn 2009. f. Find the maximum enrollment, across all sections, in Autumn 2009. g. Find the sections that had the maximum enrollment in Autumn 2009. 2.2 Suppose you are given a relation grade points (grade, points), which provides a conversion from letter grades in the takes relation to numeric scores; for example an A grade could be specified to correspond to 4 points, an A-" to 3.7 points, a B+ to 3.3 points, a B to 3 points, and so on. The grade points earned by a student for a course offering (section) is defined as the number of credits for the course multiplied by the numeric points for the grade that the student received. Given the above relation, and our university schema, write each of the following queries in SQL. You can assume for simplicity that no takes tuple has the null value for grade. a. Find the total grade-points earned by the student with ID 12345, across all courses taken by the student. b. Find the grade-point average (GPA) for the above student, that is, the total grade-points divided by the total credits for the associated courses. c. Find the ID and the grade-point average of every student. 2.3 Write the following inserts, deletes or updates in SQL, using the university schema. a. Increase the salary of each instructor in the Comp. Sci. department by 10%. b. Delete all courses that have never been offered (that is, do not occur in the section relation) c. Insert every student whose tot_cred attribute is greater than 100 as an instructor in the same department, with a salary of $10,000