这是一个美国的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.

**程序代写代做C/C++/JAVA/安卓/PYTHON/留学生/PHP/APP开发/MATLAB**

本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！

**E-mail:** itcsdx@outlook.com **微信:**itcsdx

如果您使用手机请先保存二维码，微信识别。如果用电脑，直接掏出手机果断扫描。