Sql代写|CSE 132A Assignment #1 (SQL)


The university registrar database has the following schema:

student: sid (string)
course: cid (string)
prerequisite: cid (string), pre cid (string)
record: sid (string), cid (string), qtr (string), year (integer), grade (integer)

Relation student provides the ids of all students enrolled in the university, and course provides all offered courses. Relation prerequisite provides the prerequisites pre cid for each course cid. Relation record indicates which courses were taken by each student in a given year and quarter (F, W,S), and the grade obtained (for simplicity, grades are provided as integers 4; 3; 2; 1; 0, corresponding to A, B, C, D, F). Assume that a class can only be taken once (even if the grade is 0). Note that students may have gaps in enrollment, i.e. they need not take courses every quarter.

In addition, the following hold:

• sid is the primary key of student

• cid is the primary key of course

• fcid; pre cidg is the primary key of prerequisite; cid and pre cid are foreign keys referencing course.

• fsid; cid; qtr; yearg is the primary key of record (so grades are unique for each course taken by a student in a given year and quarter), sid is a foreign key referencing student and cid is a foreign key referencing course.

Assume that instances have no null values and no duplicates. A sample instance will be posted separately, together with outputs to the queries in the assignment.

Write the following queries in SQL (of course, the queries must work on all data, not just the sample one):

1. List all pairs of students who have taken at least one course together in Fall 2021. The answer should have attributes fsid1, sid2g. Avoid listing pairs of the form < a; a >, or listing both < a; b > and < b; a >.
Specifically, list only pairs < a; b > where a is strictly less than b in alphabetical order.

2. List the students who have taken all prerequisites for CSE132X with a grade of 2 or higher (this refers to direct prerequisites only, not to prerequisites of prerequisites). The answer should have one attribute sid. Note that, if CSE132X has no prerequisites, then all students should be in the answer.

3. Find the number of courses each student has taken in Fall 2021. If a student has taken no class that quarter, the number of classes should be zero. The answer should have attributes fsid; numg.

4. For each quarter occurring in record, list the number of courses with fewer than 5 students (but at least one student) enrolled that quarter.
If there is not such course for a given quarter, the number returned should be zero. The answer should have attributes fqtr; year; numg.

5. Compute the GPA for the academic year1 2020/2021 for all students who have taken at least one class during this academic year. The answer should have attributes fsid, gpag.

6. List, for each academic year with entries in record, the student(s) with the highest GPA for that academic year. The answer should have attributes fyear1, year2, sidg, where year1=year2 identifies the aca demic year (so year2 = year1 + 1).

7. Find the students whose quarterly GPA has never gone down in con secutive quarters they have been enrolled (their GPA in each quarter is at least as high as their GPA in previous quarters). Students who have never been enrolled, or have been enrolled for only one quarter,should be included in the answer. Recall that students may have gaps in enrollment. Hint: It may be helpful to observe that the order of occurrence of quarters in a given calendar year is in reverse alpha betical order (’W’ > ’S’ > ’F’). For example, the quarters occurring in 2021 are, in chronological order, W, S and F.

8. Update relation record by swapping enrollments in CSE132A and CSE132B in Fall 2021, without explicitly naming the students involved. That is,all students enrolled in CSE132A should be dropped from that class and enrolled in CSE132B, and conversely. You may use several update commands if needed. Do not create new relations. You may assume that class codes (such as CSE132A) contain both numbers and letters.

Make sure that your updates do not violate the foreign key constraints.