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

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


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

blank

发表评论